If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
problems initiating mplay32.exe | electricrainbowfish | Powerpoint | 8 | July 8th, 2004 09:37 AM |
Continual Error 1321 Trying to Install Office 2003 | Chad Harris | General Discussions | 9 | June 11th, 2004 08:19 AM |
Expression Error-On Click | Rich | Using Forms | 2 | June 10th, 2004 03:29 PM |
ERROR on expression in my form | Nadia | Using Forms | 5 | June 4th, 2004 08:46 PM |
Error 2427 You entered an expression that has no value | M Skabialka | Using Forms | 4 | June 4th, 2004 07:05 PM |