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.

 

[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.

 

[neileg]

Does that make you feel better neileg

Absolutely splendid