Using SUM and GROUP problems

[jhleblanc]

Here is what I need I am running a report the report shows all the invoices between two dates and total all the subtotals of the invoices and the taxes on the invoices. Here is the code.

strSQL= "SELECT Sum(tblinvoices .fldsubtotal) As fldsubtotal, Sum(tblinvoices .fldpst) As fldpst, tblinvoices.fld date FROM tblinvoices GROUP BY tblinvoices.fld date HAVING tblinvoices.fld date BETWEEN #" & strStartDate & "# AND #" & strEndDate & "#"

But it only gives me one of the invoices the GROUP BY makes a mess of it but I need the date as it is part of the record filter. The SUM works but I can't get the right totals without the date.

[bwestover]

Here is what I need I am running a report the report shows all the invoices between two dates and total all the subtotals of the invoices and the taxes on the invoices. Here is the code.

strSQL= "SELECT Sum(tblinvoices .fldsubtotal) As fldsubtotal, Sum(tblinvoices .fldpst) As fldpst, tblinvoices.fld date FROM tblinvoices GROUP BY tblinvoices.fld date HAVING tblinvoices.fld date BETWEEN #" & strStartDate & "# AND #" & strEndDate & "#"

But it only gives me one of the invoices the GROUP BY makes a mess of it but I need the date as it is part of the record filter. The SUM works but I can't get the right totals without the date.

Do you want subtotal's by day, or do you want a single line with the final total on it? You should be able to just eliminate date from your select, eliminate your group by altogether and change your HAVING into a WHERE.

[jhleblanc]

What I need is the total in one value for all items between those dates. I can sum up the total of the invoice but the date I am using as a filter. It is is the query so I have to have it in the group and it's making the problem.

[jhleblanc]

Never mind, I got it. I was thinking that if I SUM the date then system can not use the BETWEEN to filter the records but I was wrong so I used SUM on each of the SQL items and IT WORKS!!