Create a data input form (name it customerFRM) for customers that can be used to update the customer table
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 customers 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. (Dont 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
PLACE THIS ORDER OR A SIMILAR ORDER WITH BEST NURSING TUTORS TODAY AND GET AN AMAZING DISCOUNT
The post Create a data input form (name it customerFRM) for customers that can be used to update the customer table appeared first on BEST NURSING TUTORS .
Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!
NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.
