Microsoft Access Database Design and Implementation
Business Scenario
Your company has been using Excel to track customer orders. They are having difficulty analyzing the data and producing meaningful reports. They have asked you to implement an Access database which will allow them to add, modify and delete data as well as generate reports based on the information contained in the database.
1. Create Database ?Your first task is to create a database in Microsoft Access populated with data from OriginalData.xlsx spreadsheet which includes: ?§??the material worksheet?§??the customer worksheet?§??the salesorder worksheet?§??the salesorderitem worksheet ?Start with a €œblank€ Access database, name this database YourName.accdb. Import data from each worksheet in the Excel file OriginalData.xlsx. Note that each worksheet should be imported as a new Access table and given the same name as the Excel worksheet. Hint: Be careful in setting the datatypes of each field during the import process € remember foreign keys (an example is the CustomerID in the SalesOrder table) MUST be of the same data type as the corresponding primary key in a related table. Use text for ID numbers in all tables.
2. Create Relationships & Forms for Data Entry?a. Build the relationships for the three tables per the model shown in Figure 1. ?Figure 1: Model of the Relationships

ITM 220 Access Assignment Dr. Papp
3. Create Forms for Data Entry
a. Create a data input form (name it €œcustomerFRM€ ) for customers that can be used to update the customer table. Use the Create form wizard with €œsplit form€ . Add one company to the database as a new customer that is a Hypermart in Fulda (Germany) with postal code of 36048. Use your UT student ID for the customerID.
Figure 2
b. Create a form to display each salesorder as well as salesorderitem details on a subform. (Use the Forms Wizard, style €œoffice€ .) The form should look like Figure 3 below.
For Order number 10 add an additional line item using your new form as follows: MaterialID: AA-02

Quantity: 100 Price: 525

Figure 3 ITM 220
Access Assignment Dr. Papp
4. Create Queries to Analyze Information
(a) Create a query called Q1-ListAllCustomers which displays each customer€„¢s ID, postal code, city, and searchterm. Sort the list alphabetically by city (hint change setting in €œSort€ row of query design grid).
(b) Create a query called Q2-ListCustomersFulda that displays the ID, postal code, city, and searchterm for each customer in Fulda. Sort this list from largest to smallest by customerID.
(c) Create a new query called Q3-PriceStatistics that displays price in six columns but computes different values for each display. Display 1) the sum, 2) the minimum, 3) the maximum value, 4) a count, 5) the average, and 6) the standard deviation of price. (Hint: use the Sigma summation symbol to display the €œTotal:€ row in the query grid)
(d) Create a query called Q4-CustomerOrderbyItem that displays data from all four tables. Specifically include: Customer, City, SearchTerm, SalesOrderDate, SalesOrderID, MaterialID, Quantity, Price, and Material description. Sort by MaterialID. This query will be used as an input to the next query and to show we can reassemble the data from the original document used to build the system.
(e) Create a query called Q5-OrderDetailsCrosstab, which produces a table such as is shown below (Hint: use the crosstab function in the Query Wizard and the previous query as your input to create this query)
(f) Create a query called Q6-CustomersLessThan15000. Start by copying Q4 and modify the name. Display only data for total prices less than 15000 Euros.
(Hint: use the €œcriteria€ row by entering the appropriate inequality and numeric value to generate the correct data.)

ITM 220 Access Assignment Dr. Papp
5. Create Reports for Management
(a) Create a report €œR1-CustomerbyCity€ that looks like the one below.
€¢ Use the Create | Report with Customer table. (Don€„¢t use Report Wizard.)
€¢ Change the title to include your name.
€¢ Use €œlayout view€ to customize report (column order, width and sort by city,) ?(b) Create a second report €œR2-SalesReport€ as shown below using Q4 as your input source. ?€¢?Use Create | Report again but with Q4 as your source. You can use Report Wizard or create the report manually. Note: €œright click€ is your friend as you modify the default report format to achieve: ?o Group on MaterialID?o Group on MaterialDescription?o Adjust layout to €œStack MaterialDescription€ as shown?o Sort by OrderDate?o Compute subtotals and grand totals for price and quantity?o Adjust format to align columns and change labels as necessary