How do I display 1 line per Invoice Summary w/totals from Invoice Detail ?

[mthornblad]

I enter Invoice Summary and Invoice Detail information via a Form/
Subform . The Invoice Summary contains only Customer Number, Date,
and Invoice Number. The Quantity, Unit Price and Tax information is
stored in the Invoice Detail.

I want to write a query that will display 1 line for each Invoice
number which totals the info in the Invoice Detail records for that
invoice number.

I want the output to be something like:

Invoice Number Invoice Date Customer Number Total Qty Total Ext.
Total Tax Total Invoice
---------------------- -----------------
-------------------------- ------------ --------------
------------ -----------------

123456 06/22/2007 12345
20 $50.00 $4.00 $54.00
123457 06/23/2007 23456
10 $10.00 $0.80 $10.80

etc.....

Please let me know how to do this with a query.

Thanks in advance
Mark

 

[Marshall Barton]

I enter Invoice Summary and Invoice Detail information via a Form/
Subform . The Invoice Summary contains only Customer Number, Date,
and Invoice Number. The Quantity, Unit Price and Tax information is
stored in the Invoice Detail.

I want to write a query that will display 1 line for each Invoice
number which totals the info in the Invoice Detail records for that
invoice number.

I want the output to be something like:

Invoice Number Invoice Date Customer Number Total Qty Total Ext.
Total Tax Total Invoice
---------------------- -----------------
-------------------------- ------------ --------------
------------ -----------------

123456 06/22/2007 12345
20 $50.00 $4.00 $54.00
123457 06/23/2007 23456
10 $10.00 $0.80 $10.80

I think this will do that:

SELECT I.[Customer Number], I.[Date], I.[Invoice Number],
Sum(D.Quantity) As TotalQty,
Sum(D.Quantity * D.[Unit Price]) As TotalExt,
Sum(D.Tax * D.Quantity * D.[Unit Price]) As TotalTax,
SumOfID + TotalTax As TotalInvoice
FROM Invoices As I INNER JOIN InvoiceDetails As D
ON I.[Invoice Number] = D.[Invoice Number]
GROUP BY I.[Customer Number], I.[Date], I.[Invoice Number]

 

[mthornblad]

I enter Invoice Summary and Invoice Detail information via a Form/
Subform . The Invoice Summary contains only Customer Number, Date,
and Invoice Number. The Quantity, Unit Price and Tax information is
stored in the Invoice Detail.

I want to write a query that will display 1 line for each Invoice
number which totals the info in the Invoice Detail records for that
invoice number.

I want the output to be something like:

Invoice Number Invoice Date Customer Number Total Qty Total Ext.
Total Tax Total Invoice
---------------------- -----------------
-------------------------- ------------ --------------
------------ -----------------

123456 06/22/2007 12345
20 $50.00 $4.00 $54.00
123457 06/23/2007 23456
10 $10.00 $0.80 $10.80

I think this will do that:

SELECT I.[Customer Number], I.[Date], I.[Invoice Number],
Sum(D.Quantity) As TotalQty,
Sum(D.Quantity * D.[Unit Price]) As TotalExt,
Sum(D.Tax * D.Quantity * D.[Unit Price]) As TotalTax,
SumOfID + TotalTax As TotalInvoice
FROM Invoices As I INNER JOIN InvoiceDetails As D
ON I.[Invoice Number] = D.[Invoice Number]
GROUP BY I.[Customer Number], I.[Date], I.[Invoice Number]

Thanks Marsh

That's the SQL code. How would I generate the same code using a
Query ??

Mark

 

[Marshall Barton]

I enter Invoice Summary and Invoice Detail information via a Form/
Subform . The Invoice Summary contains only Customer Number, Date,
and Invoice Number. The Quantity, Unit Price and Tax information is
stored in the Invoice Detail.

I want to write a query that will display 1 line for each Invoice
number which totals the info in the Invoice Detail records for that
invoice number.

I want the output to be something like:

Invoice Number Invoice Date Customer Number Total Qty Total Ext.
Total Tax Total Invoice
---------------------- -----------------
-------------------------- ------------ --------------
------------ -----------------

123456 06/22/2007 12345
20 $50.00 $4.00 $54.00
123457 06/23/2007 23456
10 $10.00 $0.80 $10.80

I think this will do that:

SELECT I.[Customer Number], I.[Date], I.[Invoice Number],
Sum(D.Quantity) As TotalQty,
Sum(D.Quantity * D.[Unit Price]) As TotalExt,
Sum(D.Tax * D.Quantity * D.[Unit Price]) As TotalTax,
SumOfID + TotalTax As TotalInvoice
FROM Invoices As I INNER JOIN InvoiceDetails As D
ON I.[Invoice Number] = D.[Invoice Number]
GROUP BY I.[Customer Number], I.[Date], I.[Invoice Number]

That's the SQL code. How would I generate the same code using a
Query ??

An SQL statement IS the query. The QBE is just a limited
user interface for entering the basic information that
Access uses to create an SQL statement.

If you create a new query without selecting a table or
anything and immediately switch to SQL view, then you can
Copy/Paste what I posted over whatever stuff Access thought
was a good starting point. Then double check that I got the
table and field names right. If you then switch back to
design view you will see what you could have done in the
QBE to specify the same query. (Note that switching to the
QBE allows Access to add all kinds of extra [ ] and ( )
making the SQL view more difficutl to read.)

 

[mthornblad]

(e-mail address removed) wrote:
I enter Invoice Summary and Invoice Detail information via a Form/
Subform . The Invoice Summary contains only Customer Number, Date,
and Invoice Number. The Quantity, Unit Price and Tax information is
stored in the Invoice Detail.
I want to write a query that will display 1 line for each Invoice
number which totals the info in the Invoice Detail records for that
invoice number.
I want the output to be something like:
Invoice Number Invoice Date Customer Number Total Qty Total Ext.
Total Tax Total Invoice
---------------------- -----------------
-------------------------- ------------ --------------
------------ -----------------
123456 06/22/2007 12345
20 $50.00 $4.00 $54.00
123457 06/23/2007 23456
10 $10.00 $0.80 $10.80
I think this will do that:
SELECT I.[Customer Number], I.[Date], I.[Invoice Number],
Sum(D.Quantity) As TotalQty,
Sum(D.Quantity * D.[Unit Price]) As TotalExt,
Sum(D.Tax * D.Quantity * D.[Unit Price]) As TotalTax,
SumOfID + TotalTax As TotalInvoice
FROM Invoices As I INNER JOIN InvoiceDetails As D
ON I.[Invoice Number] = D.[Invoice Number]
GROUP BY I.[Customer Number], I.[Date], I.[Invoice Number]

That's the SQL code. How would I generate the same code using a
Query ??

An SQL statement IS the query. The QBE is just a limited
user interface for entering the basic information that
Access uses to create an SQL statement.

If you create a new query without selecting a table or
anything and immediately switch to SQL view, then you can
Copy/Paste what I posted over whatever stuff Access thought
was a good starting point. Then double check that I got the
table and field names right. If you then switch back to
design view you will see what you could have done in the
QBE to specify the same query. (Note that switching to the
QBE allows Access to add all kinds of extra [ ] and ( )
making the SQL view more difficutl to read.)

Thanks Marsh

I appreciate your help. I will do what you suggested. Maybe it would
be a good idea to always write my own
SQL for all my queries.

Mark