Totals problem 1

[knuckelhead]

I am trying to make just 1 query instead of 2. I could make a second query sum up the values. But i am stubborn. The hangup is my Invoice date field. Maybe there is an SQL addition to allow me to have just 1 qry?

I am taking many invoice records and summing up the Revenue$. I use a Date Range in the Invoice Date field in the Qry. But the date range forces each invoice row to appear. I want just 1 record to show in the sample below worth $20.85. Not 5 records.

FormulaID SoldtoID SumOfRev$
11020201 111975 $3.90
11020201 111975 $3.90
11020201 111975 $3.90
11020201 111975 $3.90
11020201 111975 $5.25

How can I collapse the records yet still use a Date Range in the Invoice Date field, short of making a second qry? I wonder if there is an SQL thing to add in. Here is the present qry in SQL form.

SELECT tblInvoice.FormulaID, tblInvoice.SoldtoID, Sum(tblInvoice.[Rev$]) AS [SumOfRev$]
FROM tblInvoice
GROUP BY tblInvoice.FormulaID, tblInvoice.SoldtoID, tblInvoice.InvoiceDate
HAVING (((tblInvoice.InvoiceDate) Between [Start Date] And [End Date]))
ORDER BY tblInvoice.FormulaID, tblInvoice.SoldtoID;


thanks

 

[Golom]

Try this

SELECT FormulaID, SoldtoID, Sum([Rev$]) AS [SumOfRev$]

FROM tblInvoice

WHERE InvoiceDate Between [Start Date] And [End Date]

GROUP BY FormulaID, SoldtoID 

ORDER BY FormulaID, SoldtoID

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[knuckelhead]

It woiks Moe. Thank you so much. I will donate to the club.
Knucklehead