Need urgent help! School project...can't figure out calculated field

[LaurenH]

Most of the project is done...I am stumped on calculating a field for the report...any help is greatly appreciated..thanks so much.

Use a Microsoft Access database to create the tables and column headings show in Figures 1-3 and discussed in the Background section. Create the tables in the design view. Populate the tables with the data as shown. Make sure you create a composite primary key field for the ORDERS table. The key should consist of the fields Invoice Number and Product Number to allow customers to order more than one item.


Customer
Customer Number Customer School Customer Street Customer City Customer State Customer Zip Contact Name
105 Central High 123 Main Street Denver CO 80208 Mary
107 Central Middle 35 South Park Denver CO 80278 Sue
423 JKF Elementary 69 5th Avenue Cheyenne WY 90200 Pat
516 Toddler Time 8000 Parkway Ft. Collins CO 80487 Jan
799 Georgetown High 819 Rt. 909 Boulder CO 80303 Jack






Product
Product Number Product Name Product Price
10001 Paper Plates $10.00
10002 Paper Cups $20.00
10003 Plastic Knives $18.00
10004 Plastic Glasses $25.00
10005 Paper Napkins $12.00


Orders
Invoice Number Customer Number Product Number Date Quantity
1 107 10001 9/24/2005 4
2 107 10003 10/3/2005 3
3 107 10005 10/3/2005 5
4 423 10005 11/4/2005 4
5 799 10001 11/15/2005 10
6 799 10002 11/15/2005 2


Now establish relationships between your tables.

Creating Queries and Reports

Creating a Select Query

The owner wants to find out how much of each product is selling for the date ll/15/2005. Develop a query in the design view to give that information using the headings Date, Product Name, and Quantity. Save the query as Today’s Sales.

Creating another Select Query

Tomorrow, the owner is going to be in Denver, CO, and she would like to call on the customers there personally. Make a query to display the contact name, school, and street address ONLY for those schools in Denver. Save the query as Denver Schools.

Generating a Sales Report

Finally, make a report to show the dollar amount sold to each customer for each product. To create the report, you will need to:

• Make a query first and then bring that query into the Report Wizard.
• The query will contain a calculated field for Total Owed.
• The query should show the fields Customer School, Customer City, Date, Product Name, Quantity, and the calculated field, Total Owed.
• Name this query “Report Data”
• The report will be grouped by Customer School and Customer City, with subtotals for each school’s sales and a grand total.
• Make sure you delete any bogus total lines, and make all appropriate fields currency.


Deliverables

Three tables
Query output: Today’s Sales
Query output: Denver Schools
Query output: Report data
Report: Current School Sales
Defined relationships (I can press the relationships button and will see that the relationships between tables are defined)

 

[Pat Hartman]

The query will contain a calculated field for Total Owed.

I'm assuming this is what you don't know how to do.
You will need to join the Order table to the product table so you have the price available. In the query you will multiply quantity * price. You will also need to join to the customer table to get the customer information.

Select [Customer School], [Customer City], [Date], [Product Name],[Customer School], [Customer City], [Date], [Product Name], [Quantity], [Quantity] * [Price] As [Total Owed]
From ...;

Use the QBE to build the query so you get the joins right. Then add the calculated field in an empty cell at the end of the selected fields. Insert the following into the "field" cell:
[Total Owed]:[Quantity] * [Price]

BTW - your teacher is doing you a disservice by allowing you to use names with spaces or special characters in them. You should also NOT use the names of functions such as "Date", "Year" or properties such as "Name", "Value". When you use one of these BAD names, you MUST embed it within square brackets [].

Also, an order database usually has an Order table and an OrderDetails table. That way you have a place to store data that occurs once per order such as customer, shipping address, order number, etc and a separate table in which to order the items that were ordered. Take a look at the Northwinds database that is normally installed with Access to see a very basic order entry database.

 

[GaryPanic]

What I would do is get whatever your field name is to be totalled

product price - and in the page footer put a text field =Sum([Product Price]) this will give a total - you will have to group the report otherwise it will total all of the orders together - so try and get each customer onto 1 page - this is done in report design mode and is the little box top lefthand side
on a side note I have found out the hard way - try not to have field names with spaces in it like product Price - should you ever need to do VB coding it can cause some grief as you have to put [] around every field -

Hope this helps

Most of the project is done...I am stumped on calculating a field for the report...any help is greatly appreciated..thanks so much.

Use a Microsoft Access database to create the tables and column headings show in Figures 1-3 and discussed in the Background section. Create the tables in the design view. Populate the tables with the data as shown. Make sure you create a composite primary key field for the ORDERS table. The key should consist of the fields Invoice Number and Product Number to allow customers to order more than one item.


Customer
Customer Number Customer School Customer Street Customer City Customer State Customer Zip Contact Name
105 Central High 123 Main Street Denver CO 80208 Mary
107 Central Middle 35 South Park Denver CO 80278 Sue
423 JKF Elementary 69 5th Avenue Cheyenne WY 90200 Pat
516 Toddler Time 8000 Parkway Ft. Collins CO 80487 Jan
799 Georgetown High 819 Rt. 909 Boulder CO 80303 Jack






Product
Product Number Product Name Product Price
10001 Paper Plates $10.00
10002 Paper Cups $20.00
10003 Plastic Knives $18.00
10004 Plastic Glasses $25.00
10005 Paper Napkins $12.00


Orders
Invoice Number Customer Number Product Number Date Quantity
1 107 10001 9/24/2005 4
2 107 10003 10/3/2005 3
3 107 10005 10/3/2005 5
4 423 10005 11/4/2005 4
5 799 10001 11/15/2005 10
6 799 10002 11/15/2005 2


Now establish relationships between your tables.

Creating Queries and Reports

Creating a Select Query

The owner wants to find out how much of each product is selling for the date ll/15/2005. Develop a query in the design view to give that information using the headings Date, Product Name, and Quantity. Save the query as Today’s Sales.

Creating another Select Query

Tomorrow, the owner is going to be in Denver, CO, and she would like to call on the customers there personally. Make a query to display the contact name, school, and street address ONLY for those schools in Denver. Save the query as Denver Schools.

Generating a Sales Report

Finally, make a report to show the dollar amount sold to each customer for each product. To create the report, you will need to:

• Make a query first and then bring that query into the Report Wizard.
• The query will contain a calculated field for Total Owed.
• The query should show the fields Customer School, Customer City, Date, Product Name, Quantity, and the calculated field, Total Owed.
• Name this query “Report Data”
• The report will be grouped by Customer School and Customer City, with subtotals for each school’s sales and a grand total.
• Make sure you delete any bogus total lines, and make all appropriate fields currency.


Deliverables

Three tables
Query output: Today’s Sales
Query output: Denver Schools
Query output: Report data
Report: Current School Sales
Defined relationships (I can press the relationships button and will see that the relationships between tables are defined)