Solved by verified expert:I need help with an attached assignment (Data for tables is provided below)
_access_2010_level_2.pdf
accessiidata_1___1_.docx
Unformatted Attachment Preview
L E S S O N
5
–
W O R K I N G
W I T H
R E P O R T S
Class Project – Teddy Bears
You are the owner of Veronica’s Teddy Bear store. Create a new database called
“Teddy Bear”
Create a Products table and add at least 6 products into the table. Include such fields
as Item description, cost, and Product ID. Set a validation rule on the cost field so
that any value entered must be at least $15.00. The cost field should be formatted as
Currency.
Create a Customers database and add at least 10 customers into the table. Include
such fields as Customer ID, Name, Address, Phone. Make First Name and Last
Name required fields.
Create an orders table. Include such fields as Order date, Date Shipped, Quantity,
Customer ID, and ProductID. Set the data type of both the Customer and Product
fields to number.
Establish the appropriate relationships between your tables. (Customer ID >
Customer, Product ID > Product, etc.)
Create a lookup-field in your Orders table that looks up the Customer ID number
from the Customer Table. Set the field to display the customer first and last name
and to store the Customer ID field in the Customer field in the Orders table. Create
another lookup field that looks up the Product ID field from the Products table. Set
the field to display the Product name and store the Product ID field in the Product
field in the Orders table. Enter at least 5 orders into your Orders table using a
different customer for each order.
Create a multi-table query that displays a list of Customers and the Products they
have ordered. Save the query with the name “qryOrders”. Include the following
fields: Last Name, First Name, Product description, Quantity and Price. Make sure
that even if a customer has not placed an order that they will display when you run
the query.
Create a Form and a Report based upon the qryOrders query. Include a calculated
field both on the form and report that adds together the cost and the quantity.
Name the new field “Total”. Format the form and report so that they are as eye
appealing as possible. Save the form as “frmProducts” and the report as
“rptProducts”.
Access 2010 Level 2
242
L E S S O N
5
–
W O R K I N G
W I T H
R E P O R T S
Create a Main Form using your customers table that includes a subform based upon
your Orders table. Rearrange and format the controls on your form to make it as
appealing to the eye as possible. Save the form as “frmOrderEntry”.
Create a query that includes all fields from your products table. Save the query with
the name “qryProducts”. Add a calculated field to add a $3.00 shipping charge to
the cost of each product. Name the new field “ShipTotal”
Close the database.
© 2010 PCM Courseware, LLC
243
Customer Table Data (Primary key: Customer ID)
CustomerID
10021
10022
10023
10024
10025
10026
10027
10028
10029
10030
First Name
James
Bethany
John
Cynthia
Amy
Anna
Stacy
Joy
Yolanda
Mathew
Last Name
Anderson
Sanders
Cole
Beckman
Jackson
Thomas
Pearl
Scott
Campbell
Curran
Address
20191
22350
62269
33194
23803
23518
22030
22039
23324
75070
Phone
703-444-3211
703-876-0038
618-342-9087
305-189-6543
757-324-0097
757-890-4321
703-876-3456
703-675-4328
903-452-0983
469-000-3490
Products Table (Primary key: ProductID)
ProductID
501
502
503
504
505
506
ProductDesc
Black Bear
Panda
Polar Bear
Hello Kitty
Pink Bunny
Fluffy Cat
Cost
15
20
30
40
18
15
Orders Table (Primary key: OrderID)
OrderID
1
2
3
4
5
6
7
8
9
10
11
12
CustomerID
10030
10028
10025
10030
10030
10026
10022
10023
10028
10027
10029
10024
ProductID
501
506
502
503
504
501
505
503
502
505
502
501
OrderDate
01/13/2014
09/15/2014
05/10/2014
04/17/2014
07/13/2014
09/04/2014
08/08/2014
09/01/2014
02/05/2014
04/09/2014
08/13/2014
02/12/2014
DateShipped
01/28/2014
09/24/2014
05/30/2014
04/18/2014
07/25/2014
09/13/2014
08/19/2014
09/13/2014
05/23/2015
04/10/2014
08/20/2014
02/19/2014
Quantity
5
2
1
2
1
3
3
1
2
1
2
2
…
Purchase answer to see full
attachment
