Access Project -
The Access project requires a substantial amount of effort. Success is feasible if you begin the project after you complete Module land continue working till the due date. The project is based upon each of the lessons you will learn.
Concept - The idea behind the Access Project:
You are an entrepreneur beginning a new business. It can be a totally new concept, such as a new nail salon, a pizza restaurant or service you provide. You could also be opening a franchise, such as Model sporting store, or a Subway fast food, Domino's Pizza or even a big hotel such as Hilton. Some past students have used the companies they work for as the basis of their project. However, do not use company proprietary information. The access database project supports this new business you are starting. You will be creating Access objects for the database.
Requirements Section -
1. Build a Database - Tables, Fields and Records
a) Create a new database with a name that corresponds to the topic you have chosen. For example, a sports club database would be called "Bestbody Sports Club".
b) The database must have two related *tables with meaningful names that correspond to the data in the table. There must be one common field on both tables to enable you to link the two tables, forming the relationship. The two tables must be related. One key must be a foreign key in one of the tables. Referential Integrity must be enforced. Please note: it is helpful to have a customer or client table in your database as this is a business.
To understand related tables, refer to the Blackboard folder, Access Help, in the General Help folder of the Information folder/Information Booth. You can also contact me for help in completing this.
c) Each table must have a minimum of eight fields (you can have more)
d) Each table must have a primary key that is unique. Do not use last name as that is not unique. You can also make up unique alpha numeric characters for the primary key such as EN2014 or simply sequential numbers.
i. Both tables must have all non-currency, non-date, and non-hyperlink, fields data types defined as Short Text with reasonable field sizes. For example, First Name, Last Name, Company, etc. The field size cannot be the default 255 characters.
ii. The date field must have a date format done in Field Properties.
e) The following data types are to be included in at least one table:
iii. Long Text (Memo)
iv. Number - minimum 1 fields.
v. Currency- maximum 2 fields.
vi. Date (you chose the date format)
vii. All fields must have a description.
f) All fields must be filled in with data. You can make up the values.
g) Create the following properties for selected fields (again you can use one table):
i) One field must be defined as Required
ii) There must be an Input Mask on at least one field. (example: an input mask for a zip code or telephone number)
iii) All Fields of one table, must have the Caption (logical name of the field) defined (hint: you do this in Field Properties).
h) Create a Validation Rule with corresponding text for one field in only one table.
(1) There should be a default set for this field with the default value being one of the values in the validation rule.
(2) If your validation rules contains alpha numeric values, it should be set to force upper case.
(3) Must have a logical and meaningful Validation Text (the message one sees when entering the incorrect value).
i) Create a Lookup field providing a drop down list with valid entries for one field. It cannot be the same field you used for the Validation process. You can either use an existing table in the database for the lookup or create your own lookup values when you use the Lookup wizard.
j) All Validation and Lookup fields must be filled in completely with both the lookup values and validation values.
2) Forms
Create one form using the Form Wizard. You chose the style and which fields go on the form. The form should have logical design for it purpose. Include the following:
a) Header Section
1) Must have a form title in the Form Header. The title must have a font style that is different from Calibri and be of a larger font size than 11. It cannot be truncated (cut off)
ii) Include a logo in the Form Header that represents your business (can be a google image, an image copied from a website, or clip art). Place the image in an appropriate position, but not on or covering the title.
iii) The form header must be formatted with background color. You can also use a picture but make sure all the text can be seen.
iv) Include the Date and Time in the Form Header.
b) Detail Section
i) Provide a good design of the fields in the form with some type of special effects added to the field label controls (can be beveled, shadowed, etc.) and must be uniform. This is not the font.
ii) Change the color of the fonts in the field controls only, not the control labels. Choose a color that compliments your overall theme.
iii) Be sure all data displays and none is truncated (#### are not acceptable).
iv) You must create one calculated field in your form. You cannot use a function, such as SUM, AVG. etc. Your calculation must start with an equal sign. It should be properly labeled and run accurately. (*If you need help with this go to the Information Kiosk in Blackboard/ Help How to Folder/Access folder for instructions.)
c) Save the form with a meaningful name.
d) Add one record using the form.
b) Subform
a) Create a second form with a subform. You decide which tables are appropriate
b) Your logo must be in the form header and an appropriate title displayed.
c) Save as Subform.
c) Create one split form using the table with the Memo (long text) field.
a. In the datasheet section of the form adjust all columns so that no data is truncated.
b. The form must have a logo and a logical title in the form header that is related to the subject topic.
c. No data can be truncated. Make sure the datasheet section (lower section) has no truncation of data.
d. Save the form as Split Form.
e. Update the Memo field values in the table for all records in the table to which you added the memo field.
3) Queries
Create the following queries that make logical sense in your database:
a. Create a query with comparison operators (use either ›, <, > = and <= or between). Run the query and save it with as Comparison Query. You cannot use Sum, Average, max or min as these are functions
b. Create a compound query using either an AND or an OR. Run the query and save it with as Compound Query.
c. Create a query with a wild card using one of the fields. Run the query and save it as Wild Card Query.
d. Create a parameter query. Run it and save it as Parameter Query.
e. Create a query that has a calculation that you developed. Run it and save it as Calculation Query. Note: do not use a function such as SUM, MAX, etc.). Your calculation would start with an equal (=) sign and use fields in one of your tables. (*If you need help with this go to the Information Kiosk in Blackboard/ Help How to Folder/Access folder for instructions. Additional help can be found in the professor assignment Querying the Sales Database)
f. Create a query displaying totals using the Sum function. Save it as Sum Query.
g. Do the same for Max. Save as Max Query.
h. Using one of your tables, create an Update action query. Run the query and save it as Update Query
i. Using one of your tables, create a Delete action query. DO NOT run the query but save it as Delete Query. If you run it you will delete data that may be needed for other functions.
4. Reports
1. Create a Report
a. Create a report from one table using the Report Wizard.
b. The report must be grouped and sorted on a field of choice.
c. There must be a calculation in the detail section that you create. This cannot be a function such as SUM. MIN or MAX
d. Apply a theme to the report
e. Save as My Report
Suggestions for Project Business
What students have used in the past:
- A student database
- A pizza shop
- A hair or nail salon
- A car dealership (such as Toyota or Mercedes)
- A Restaurant
- Sports club/Gym (you can use a real one such as Planet Fitness, etc.)
- An Internet store
- A Retail store (new or existing such as Sports Authority)
- A consulting company
- A hardware store
- A lawn maintenance business
- A dog grooming company
- A fast food restaurant, such as McDonalds or Wendy's
When complete, submit the database in the Access Project drop box of Blackboard.
Attachment:- Access Project.rar