Summing up multiple line items per invoice number (RefNumber)

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
 
N

NetworkTrade

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' ....
 
M

MGFoster

-----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-----
 
B

Brett

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?
 
B

Brett

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

MGFoster

-----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-----
 
Top