Aggregate Expression error.

[Roger Twomey]

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.

 

[Roger Carlson]

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

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.

 

[Roger Twomey]

Thanks.

Can you give me an example of a group by, having one sum and more than one field to return?
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

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.

 

[Roger Twomey]

Sorry,

Never mind. I found a document which said the command was "Group_By" it isn't. It is "Group By" without the underscore.
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

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.