Aggregate Expression error.

I am getting this error:

Error: Tried to execute a query that does not include the specified expression as part of an aggregate expression

From this query:



SELECT Invoices.InvoiceID, Invoices.InvoiceNumber, Companies.CompanyName, Invoices.BilledDate, Invoices.InvoiceAmount, Invoices.InvoiceStatusId, InvoiceStatus.Description, InvoiceDetails.OrderDetailId, Sum([InvoiceDetails].[QtyInvoiced]*[InvoiceDetails].[PricePerUnit]) AS LineTotal
FROM InvoiceStatus INNER JOIN ((Companies INNER JOIN Invoices ON (Companies.CompanyId = Invoices.BuyerId) AND (Companies.CompanyId = Invoices.BuyerId)) INNER JOIN InvoiceDetails ON Invoices.InvoiceID = InvoiceDetails.InvoiceId) ON InvoiceStatus.InvoiceStatusId = Invoices.InvoiceStatusId



Can anyone see what I need to do? In SQL server it is a 'Group by' issue, I am still trying to figure out to do this in Access.

Thanks.

This is a Group By issue in Access too. You have a SUM function in your query which makes it an aggregate query. In an aggregate query, any field in the field list which is not in an aggregate expression must be grouped by.

However, if you are just producing a LineTotal, you don't need the SUM. This should do it:

[InvoiceDetails].[QtyInvoiced]*[InvoiceDetails].[PricePerUnit] AS LineTotal

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Roger Twomey" wrote in message ...
I am getting this error:

Error: Tried to execute a query that does not include the specified expression as part of an aggregate expression

From this query:



SELECT Invoices.InvoiceID, Invoices.InvoiceNumber, Companies.CompanyName, Invoices.BilledDate, Invoices.InvoiceAmount, Invoices.InvoiceStatusId, InvoiceStatus.Description, InvoiceDetails.OrderDetailId, Sum([InvoiceDetails].[QtyInvoiced]*[InvoiceDetails].[PricePerUnit]) AS LineTotal
FROM InvoiceStatus INNER JOIN ((Companies INNER JOIN Invoices ON (Companies.CompanyId = Invoices.BuyerId) AND (Companies.CompanyId = Invoices.BuyerId)) INNER JOIN InvoiceDetails ON Invoices.InvoiceID = InvoiceDetails.InvoiceId) ON InvoiceStatus.InvoiceStatusId = Invoices.InvoiceStatusId



Can anyone see what I need to do? In SQL server it is a 'Group by' issue, I am still trying to figure out to do this in Access.

Thanks.

Thanks.

Can you give me an example of a group by, having one sum and more than one field to return?
"Roger Carlson" wrote in message ...
This is a Group By issue in Access too. You have a SUM function in your query which makes it an aggregate query. In an aggregate query, any field in the field list which is not in an aggregate expression must be grouped by.

However, if you are just producing a LineTotal, you don't need the SUM. This should do it:

[InvoiceDetails].[QtyInvoiced]*[InvoiceDetails].[PricePerUnit] AS LineTotal

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Roger Twomey" wrote in message ...
I am getting this error:

Error: Tried to execute a query that does not include the specified expression as part of an aggregate expression

From this query:



SELECT Invoices.InvoiceID, Invoices.InvoiceNumber, Companies.CompanyName, Invoices.BilledDate, Invoices.InvoiceAmount, Invoices.InvoiceStatusId, InvoiceStatus.Description, InvoiceDetails.OrderDetailId, Sum([InvoiceDetails].[QtyInvoiced]*[InvoiceDetails].[PricePerUnit]) AS LineTotal
FROM InvoiceStatus INNER JOIN ((Companies INNER JOIN Invoices ON (Companies.CompanyId = Invoices.BuyerId) AND (Companies.CompanyId = Invoices.BuyerId)) INNER JOIN InvoiceDetails ON Invoices.InvoiceID = InvoiceDetails.InvoiceId) ON InvoiceStatus.InvoiceStatusId = Invoices.InvoiceStatusId



Can anyone see what I need to do? In SQL server it is a 'Group by' issue, I am still trying to figure out to do this in Access.

Thanks.

Sorry,

Never mind. I found a document which said the command was "Group_By" it isn't. It is "Group By" without the underscore.
"Roger Carlson" wrote in message ...
This is a Group By issue in Access too. You have a SUM function in your query which makes it an aggregate query. In an aggregate query, any field in the field list which is not in an aggregate expression must be grouped by.

However, if you are just producing a LineTotal, you don't need the SUM. This should do it:

[InvoiceDetails].[QtyInvoiced]*[InvoiceDetails].[PricePerUnit] AS LineTotal

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

"Roger Twomey" wrote in message ...
I am getting this error:

Error: Tried to execute a query that does not include the specified expression as part of an aggregate expression

From this query:



SELECT Invoices.InvoiceID, Invoices.InvoiceNumber, Companies.CompanyName, Invoices.BilledDate, Invoices.InvoiceAmount, Invoices.InvoiceStatusId, InvoiceStatus.Description, InvoiceDetails.OrderDetailId, Sum([InvoiceDetails].[QtyInvoiced]*[InvoiceDetails].[PricePerUnit]) AS LineTotal
FROM InvoiceStatus INNER JOIN ((Companies INNER JOIN Invoices ON (Companies.CompanyId = Invoices.BuyerId) AND (Companies.CompanyId = Invoices.BuyerId)) INNER JOIN InvoiceDetails ON Invoices.InvoiceID = InvoiceDetails.InvoiceId) ON InvoiceStatus.InvoiceStatusId = Invoices.InvoiceStatusId



Can anyone see what I need to do? In SQL server it is a 'Group by' issue, I am still trying to figure out to do this in Access.

Thanks.