sum formula?

[amavadia]

Hi, i have a customers table, products table, and a transaction table that brings them together
fields in tblTransaction:
Transaction ID
Order Number
Customer ID
Title ID
Date
Status

I want to create a query so that you can type a name of a customer in, and it will bring up all the items they have bought, and will add up all the prices (from tblTitle) and show it at the bottom but im not sure how to get this calculated field into the query? Whats the sum formula that you put into a query?

 

[buddythebest]

What are the fields of the other tables, like is there quantity of product too?

 

[amavadia]

Fields of tblCustomer:
Customer ID
First Name
Surname
Address 1
Address 2
Address 3
Post Code
Email Address
Telephone
Mobile

Fields of tblTitle:
Title ID
Name
Artist ID
Record Label
Release Date
Source
In Stock
Quantity
Genre
Buy Price
Sell Price

There is no need for quantity of product because each item is classed as a different transaction but if more than one item is bought in an order, it will be grouped in a query as each transaction has an order number. I want to total the sell price for the customer's transactions with the query.

 

[Pat Hartman]

1. Open the QBE in design view.
2. Select the three tables from the dialog box.
3. If you have properly defined relationships, Access will automatically draw the necessary join lines. Otherwise, you'll need to draw them your self by selecting the foreign key field and dragging it to the primary key it relates to.
4. Select the columns you need from each table.
5. Change the query type to Totals by pressing the sigma button on the toolbar. Access will add "group by" to each selected field.
6. Change the "group by" to "Sum" for the field you want to sum.

Be careful to not select anything from the detail table except the amount field or the query will not aggregate as you anticipate.

 

[amavadia]

I have done this but when i run the query, there is no total, it simply shows the prices of each indiviual product, how do i get a total at end of it?

 

[neileg]

I don't usually disagree with Pat, but I do now!

Your problem is best addressed in a form. Use your query to populate the detail section of the form and calculate the total in the form footer. You don't need to do this in a query.

 

[amavadia]

Neil, thing is i want to do it in a query so that i can produce a report based on it but its not working the way pat said to do it. any ideas how to do it in his way?

 

[workmad3]

The problem is that to put the total on the bottom really requires a row that doesnt exist in the DB. Its not something that can be handled entirely in SQL, because you need a to stick 2 queries together in a way that SQL cant handle.

 

[amavadia]

how would you suggest to do this then workmad3?
if you are on msn messenger, please can you add me as i really need some help quickly
amavadia@gmail.com

 

[neileg]

Neil, thing is i want to do it in a query so that i can produce a report based on it but its not working the way pat said to do it. any ideas how to do it in his way?

You can do the sum in the report, not the query.

 

[neileg]

The problem is that to put the total on the bottom really requires a row that doesnt exist in the DB. Its not something that can be handled entirely in SQL, because you need a to stick 2 queries together in a way that SQL cant handle.

You can, actually. All you need is to create a totals query and Union that with the original query.

But that's not the answer here.

 

[Pat Hartman]

If you want both detail and totals, do the summing in the report. You can do sub totalling by adding group footers. You cannot add aggregate functions to page footers. They must be placed in group or report footers.

If you want to sum data to export or use for charting, summarize the data in a query.

Does that make you feel better neileg

 

[neileg]

Does that make you feel better neileg

Absolutely splendid