Summing up multiple line items per invoice number (RefNumber)

  • Thread starter Thread starter Brett
  • Start date Start date
B

Brett

This is the result I get with the following query:

RefNumber InvoiceLineAmount InvoiceLineQuantity
65914 7.72 1
65914 500 1
65922 110.05 1
65925 77.11 1
65925 7.72 1

SELECT InvoiceLine.RefNumber, InvoiceLine.InvoiceLineAmount,
InvoiceLine.InvoiceLineQuantity
FROM InvoiceLine
WHERE (((InvoiceLine.TxnDate)>=[Enter Beginning Date] And
(InvoiceLine.TxnDate)<=[Enter Ending Date])

Basically, I only want the Sum of the the InvoiceLineAmount and
InvoiceLineQuantity PER RefNumber. If I use the aggregate Sum function
for either it creates a grand total of all the lines on all the
invoices. Any suggestions?

Thank you!
Brett
 
get your query in design grid view, rather than sql view....

then select the SUM function (greek E) and it will default all 3 columns to
'Group by' change the last 2 to 'Sum' ....
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

SELECT RefNumber, SUM(InvoiceLineAmount) As TotAmt,
SUM(InvoiceLineQuantity) As TotQty
FROM InvoiceLine
WHERE TxnDate BETWEEN [Enter Beginning Date] And
[Enter Ending Date]
GROUP BY RefNumber
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR9cpn4echKqOuFEgEQLgxQCgun0uvJi1nPpTYXVA7Y7m4KBi+34AoJoc
lE0W7AthMoBQliHePykrubv1
=+JU9
-----END PGP SIGNATURE-----
 
That doesn't work for some reason:

RefNumber SumOfInvoiceLineAmount SumOfInvoiceLineQuantity
65914 7.72 1
65914 500 1
65922 110.05 1
65925 7.72 1
65925 77.11 1

is produced from:

SELECT InvoiceLine.RefNumber, Sum(InvoiceLine.InvoiceLineAmount) AS
SumOfInvoiceLineAmount, Sum(InvoiceLine.InvoiceLineQuantity) AS
SumOfInvoiceLineQuantity
FROM InvoiceLine
GROUP BY InvoiceLine.RefNumber, InvoiceLine.TxnDate,
InvoiceLine.InvoiceLineItemRefFullName, InvoiceLine.InvoiceLineDesc
HAVING (((InvoiceLine.TxnDate)>=[Enter Beginning Date] And
(InvoiceLine.TxnDate)<=[Enter Ending Date]) AND
((InvoiceLine.InvoiceLineItemRefFullName)="FB_Item") AND
((InvoiceLine.InvoiceLineDesc) Like "[0-9]*"));

Any ideas?
 
I figured out why it wasn't working for me: it was changing the
parameters to a HAVING clause as opposed to a WHERE clause. Now it
works. Thanks for helping me realize this.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The reason it doesn't work as expected is you are including the TxnDate,
InvoiceLineItemRefFullName & InvoiceLineDesc in the GROUP BY clause.

It is better to have the criteria in a WHERE clause rather than in the
HAVING clause. The HAVING criteria is applied AFTER all the records
have been selected; using the WHERE clause causes only the records that
meet the WHERE clause criteria to be selected.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR9c0XYechKqOuFEgEQJ2QQCeMUtz0dbnCxjv5SktX98MPXcp4zwAoPf8
BgJZAG8DalitHZbaJ/Mugeqd
=+1zu
-----END PGP SIGNATURE-----
 
Back
Top