Requirements - Please create an MS Access File named "Sales Data" and do the following in this file:
1. Create a table (name the table "Customers") with the following fields which adhere to the stated requirements:
a. Customer ID
i. Field data type - AutoNumber
ii. This should be the Primary Field
b. Customer Name
i. Field data type - Short text
ii. First alphabet must be capital, following alphabets lower case
iii. This field is required and zero length should not be allowed
c. Address
i. Field data type - Short text
ii. This field is required and zero length should not be allowed
d. State
i. Field data type - Short text
ii. Field Size should be 3
iii. Create a validation rule, where you will only be able to enter the following for state: VIC, WA, TAS, NT or QLD (states should be all uppercase)
iv. This field is required and zero length should not be allowed
e. Sales Rep ID
i. Field data type - Number
ii. This field is required
f. Initial sales date
i. Example of how date should be keyed in and how date should appear: 21-Jan-2017
- Month should be only 3 alphabets, first letter must be capital, remaining 2 letters must be lower case
- Year must have 4 numbers
2. Fill in customer records included in the word document e-mailed to you
3. Create another table (name the table "Sales Rep") with the following fields which adhere to the stated requirements:
a. Sales Rep ID
i. Field data type - AutoNumber
ii. This should be the Primary Field
b. Sales Rep Name
i. Field data type - Short text
ii. First alphabet must be capital, following alphabets lower case
iii. This field is required and zero length should not be allowed
4. Fill in Sales Rep records included in word document e-mailed to you
5. Import data from excel spreadsheet e-mailed to you and name the new table "Orders"
6. Use design view to ensure "Sales Rep ID" in "Orders" table has the same "Field Size" as "Sales Rep ID" in the "Sales Rep" table.
7. Use design view to ensure "Customer ID" in "Orders" table has the same "Field Size" as the "Customer ID" in the "Customers" table
8. Create a one to many relationships between the "Sales Rep" and "Customers" tables and ensure that a non-existence Sales Rep ID number will not be able to be entered in customer table. (Hint: Click "Enforce Referential Integrity".
9. Create a one to many relationships between the "Sales Rep" and "Orders" tables and ensure that a non-existence Sales Rep ID number will not be able to be entered in Order table. (Hint: Click "Enforce Referential Integrity".
10. Create a one to many relationships between the "Customers" and "Orders" tables and ensure that a non-existence Customer ID number will not be able to be entered in Order table. (Hint: Click "Enforce Referential Integrity".
11. Create a query with user input fulfilling the following requirement (name the query "Sales by individual sales rep"):
a. Input required should be sales person's name (sales rep's name should not appear on the query)
b. Other information that should be included
i. Customer Name (from "Customers" table)
ii. Product Code (from "Orders" table)
iii. Quantity (from "Orders" table)
12. Create a report from the query above. Format the report so that each time a report is prepared, the sales rep's name will appear on the report. (make sure the label for this is "Sales Rep")