Query to return sum of sales between 2 dates

[cloverdog]

Hello. I need to return the SUM of sales between two dates and save them into a variable. I have a query that returns all the sales but I can’t see how to get it to sum them all up and return a single cell with the value of all the sales ie 5,756.34 instead of a long column of values.

I have a book by Alison Balter which seems to show an example where this was done to return a 1 row 1 column single cell with the total value, but unfortunately not the how. I have been using the query grid and the SQL from that is as below:


SELECT tblPayments2006.PaymentDue, tblPayments2006.Amount
FROM tblPayments2006
WHERE (((tblPayments2006.PaymentDue)>=#6/1/2012# And (tblPayments2006.PaymentDue)<=#6/30/2012#));

I have tried various combinations of GroupBy and Sum but just can't see how to do it.

Thank you for any help.

 

[cloverdog]

I managed to do it using subqueries.

For anyone else who may be looking here it is:

SELECT Sum(Mysum) AS MyTot
FROM (SELECT tblPayments2006.PaymentDue, tblPayments2006.Amount AS MySum
FROM tblPayments2006
WHERE (((tblPayments2006.PaymentDue)>=#6/1/2012# And (tblPayments2006.PaymentDue)<=#6/30/2012#))) AS MyTable;

Thank you to all those who give their time to help others.

 

[PHV]

Why not simply this ?
SELECT Sum(Amount) AS MyTot
FROM tblPayments2006
WHERE PaymentDue Between #6/1/2012# And #6/30/2012#

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886