Computer MS ACCESS

Savina230

LabExercise-CokeNewVersion1.docx

Home >Computer Science homework help >Computer MS ACCESS

Lab Exercise: Database Entry, Queries and Reports

This document contains 5 pages.

Use the Coke database to perform the following steps:

1. Enter the data in the tables in the specified order (SEE PAGE 2 FOR DATA):

a. Customer

b. Distributor

c. Category

d. Product

e. Order

f. OrderLine

2. Create a query with fields Customers name and phone whose orders were supplied by the distributor Van Distributors.

3. Create a query to show the number of orders that contains Coca-Cola.

4. Create a query to show the total amount of items ordered by category (Note: you would want to use the category name instead of the CategoryId).

5. Create a query to show the distributor that distributed the most orders.

6. Create a query to show the customer, his order and the total quantity of products in each order.

7. Create a report to show the total fee due to each distributor.

8. Create a report to show the daily invoice for each customer (prices for products and their totals).

PLEASE SEE PAGE 2 FOR THE DATA

Data for Coke Database tables:

Customer
CustomerId	CustomerName	Address	City	State	Zip	Phone	Email
C1	Dave's Sub Shop	200 Atlantic Avenue	Brooklyn	NY	11256	7184347000	[email protected]
C2	Pizza Palace	100 Chambers Street	New York	Ny	10007	2122208000	[email protected]
C3	T's Fun Zone	150 Flatbush Avenue	Brooklyn	NY	11345	7188345000	[email protected]

Distributor
DistributorId	DistributorName	Address	City	State	Zip	Phone	Email
D1	ABC Trucking	120 Lawton Avenue	Bronx	NY	10454	7182006000	[email protected]
D2	Hawkins Shipping	440 Fordham Road	Bronx	NY	10452	7183007000	[email protected]
D3	Van Distributors	500 68th Street	Brooklyn	NY	11220	7184503000	[email protected]

Category
CategoryId	Description
Cat1	Light Soda
Cat2	Dark Soda
Cat3	Zero Soda
Cat4	Fruit Soda

Product
ProductId	ProductDescription	Price	CategoryId
P1	Coca-Cola	$0.65	Cat2
P2	Diet Coke	$0.55	Cat2
P3	Sprite	$0.55	Cat1
P4	Diet Sprite	$0.55	Cat3
P5	Vanilla Coke	$0.55	Cat4

Order
OrderId	OrderDate	CustomerId	DistributorId	DistributorFee	TotalDue
O1	7/4/2008	C1	D1	$22.00	$153.25
O2	8/6/2008	C1	D1	$12.95	$132.95
O3	6/5/2008	C2	D2	$29.50	$84.50
O4	5/5/2008	C3	D3	$40.00	$150.00

Orderline
OrderId	LineItem	ProductId	Quantity
O1	1	P1	75
O1	2	P2	50
O1	3	P3	100
O2	1	P5	100
O2	2	P1	100
O3	1	P2	100
O4	1	P3	50
O4	2	P4	50
O4	3	P5	100

Here are the steps to assist with any query:

1. Look at the Relationship diagram for the database to see how the tables are joined (or connected) together.

2. Identify the fields needed in the query from the description of the query.

3. Identify the tables that contain the fields needed in the query.

4. If more than one table is part of the query, then include all the other tables that join the query tables.

5. Create the query

6. Specify the criteria.

a. For example, if your query needs to show the items by category, then it must contain a group by clause because you are using it to group rows that have the same values.

b. Similarly, if your query needs to show the number of items by category, then it must contain a group by clause with some aggregate function (count, or sum, or average, etc)

7. Run the query

Here are the steps to create and run query 1 (Question2):

1. The Relationships diagram shows that there are 6 tables with the Customer and Distributor joined to the Order table. Also, the Category table is joined to the Product table which is joined to the Orderline table which is joined to the Order table.

2. The fields are CustomerName, Phone, and DistributorName (they are explicitly stated).

3. The CustomerName and Phone are in the Customer table. The DistributorName is in the Distributor table.

4. The Customer table and the Distributor table are joined by the Order table, so all three must be included in the query.

5. Create, Query Design, Add the tables Customer, Order, and Distributor from the Show Table dialog. Then drag the CustomerName to the field row and first column in the grid, drag the Phone to the field row and second column in the grid, drag the DistributorName to the field row and the third column.

6. Specify the criteria by entering the words Van Distributor in the Criteria row under the third column.

7. Run the query by clicking !Run.

See query video for a demo

Here are the steps to create and run query 2(Question 3):

2. The fields are ProductDescription and OrderId (they are not explicitly stated, therefore you will have to infer them). One is ProductDescription because the names of the soda are in the ProductDistribution field. The other is OrderId because it specifies the order.

3. The ProductDescription is in the Product table. The OrderId is in the OrderLine and Order tables. You could choose the OrderLine table because the OrderId is similar for an order regardless of the amount of items it contains.

4. The Product table and the OrderLine table are joined to each other so there is no need to include any other table in the query.

5. Create, Query Design, Add the tables Product and OrderLine from the Show Table dialog. Then drag the ProductDescription to the field row and first column in the grid and drag the OrderId to the field row and second column in the grid.

6. Specify the criteria by entering the words Coca-Cola in the Criteria row under the first column. Click the Totals icon in the Show/Hide group because you need to do something (count the number of orders) for all orders that contain Coca-Cola. (Note: A new row called Total will be added to the grid). This will cause the query to group by both fields which is just another way of saying show all the records that contain CoCa-Cola in the ProductDescription field and distinct values within the OrderId field. Click the Group By cell and select Count from the drop by choices. This enables you to count the number of OrderId that is present in the records.

7. Run the query by clicking !Run.

See query video for a demo

For help with query 3, query 4, and query 5

Apply the same technique above and view query video(s)

Here are the steps to assist with any report:

1. Look at the Relationship diagram for the database to see how the tables are joined (or connected) together.

2. Identify the fields needed in the report from the description of the report.

3. Identify the tables that contain the fields needed in the report.

4. Create the report by selecting Create, Report Wizard

5. Choose the table (or query) that contains the fields you need for the report and add the required fields to the Selected Fields box.

6. Repeat for any other table (or query) and add the required fields to the Selected Fields box.

7. Click Next

8. Select how do you want to view your data

9. Click Next

10. Add any grouping if necessary

11. Click Next

12. Add Sorting on any fields and proceed to step 13 if Summary Options are needed, otherwise proceed to step 14.

13. Click Summary Options (this allows you to add/min/max/avg on numeric fields in the detail and/or summary area) and Click Ok