SQL to SELECT Invoices with InvoiceDetails

[FreeriderUK]

I am trying to generate a report showing a list of Invoices using Access DB.

I want to SELECT all Invoices in the table, but also include a total from the InvoiceDetails.

As long as I don't try to ORDER BY the InvTotalAmount, it works as expected:

Date      Inv No.   Total
--------------------------
01/02/03  11223344  525.65

My SQL looks similar to this:

"SELECT InvoiceFields, SUM(tbInvDTL.Amount) AS InvTotalAmount 
FROM (tbInvDTL INNER JOIN tbInvoices ON tbInvDTL.InvID = tbInvoices.InvID)
GROUP BY [same fields as SELECT] 
ORDER BY "  & SortOrderFieldName

When I do try to ORDER BY the InvTotalAmount, my report shows a line for each InvoiceDetail.

Date      Inv No.   Total
--------------------------
01/02/03  11223344  120.15
01/02/03  11223344  285.20
01/02/03  11223344  120.30

I think it must be unique to MS Access, since it works in SQL Server Management Studio.

Does anyone have any suggestions?

 

[cjard]

What a stupid DB! Anyway, try:

"
SELECT * FROM
(
[INDENT]SELECT InvoiceFields, SUM(tbInvDTL.Amount) AS InvTotalAmount 
FROM (tbInvDTL INNER JOIN tbInvoices ON tbInvDTL.InvID = tbInvoices.InvID)
GROUP BY[/INDENT] [same fields as SELECT]
)
ORDER BY "  & SortOrderFieldName

 

[FreeriderUK]

What a stupid DB!

Are you referring to *my* DB or Access in general?

 

[cjard]

Access in general..