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" error when running query
Access 2002
I have constructed a Select query (using the Wizard) and when it is run the following message is displayed You tried to execute a query that does not include the specified expression "ProgNameID" as part of an aggregate function The SQl is SELECT tMstrProgram.*, tMstrProgramName.*, tMstrAgeGrp.*, First([GroupID]) AS Expr1 FROM (tMstrProgram LEFT JOIN tMstrProgramName ON tMstrProgram.ProgNameID = tMstrProgramName.ProgNameID) LEFT JOIN tMstrAgeGrp ON tMstrProgram.FundingID = tMstrAgeGrp.FundingID; ProgNameID is a field in botht MstrProgram and tMstrProgramName GroupID is a field in tMstrAgeGrp My SQL is extremely basic. -- Graham |
#2
|
|||
|
|||
"Aggregate" error when running query
On Tue, 23 Jan 2007 20:04:00 -0800, Graham
wrote: Access 2002 I have constructed a Select query (using the Wizard) and when it is run the following message is displayed You tried to execute a query that does not include the specified expression "ProgNameID" as part of an aggregate function The SQl is SELECT tMstrProgram.*, tMstrProgramName.*, tMstrAgeGrp.*, First([GroupID]) AS Expr1 FROM (tMstrProgram LEFT JOIN tMstrProgramName ON tMstrProgram.ProgNameID = tMstrProgramName.ProgNameID) LEFT JOIN tMstrAgeGrp ON tMstrProgram.FundingID = tMstrAgeGrp.FundingID; ProgNameID is a field in botht MstrProgram and tMstrProgramName GroupID is a field in tMstrAgeGrp My SQL is extremely basic. The First() operator is a totals-query operator, so it assumes you're doing some sort of totals query; you don't have a GROUP BY expression but you're including all the fields from all three tables, so it's not clear to me what you're TRYING to do. What are these tables? What are you trying to accomplish? The wizard doesn't (in my experience) throw in aggregate functions like First(); did you add this yourself - and if so, why? John W. Vinson[MVP] |
#3
|
|||
|
|||
"Aggregate" error when running query
Hi John
Yes, I did manually include "First" in an effort to achieve the result below. The problem is that there are 2 records in tMstrAgeGrp for each FundingID and, therefore when the query is run it results in duplication of tMstrProgram records. Whereas, I only want 1 tMstrProgram record returned if there is a matching record in tMstrAgeGrp. All fields were included out of laziness - when the problem is resolved, I will look critically at just which fields are required. Hopefully, this will make my goal clearer. "John Vinson" wrote: On Tue, 23 Jan 2007 20:04:00 -0800, Graham wrote: Access 2002 I have constructed a Select query (using the Wizard) and when it is run the following message is displayed You tried to execute a query that does not include the specified expression "ProgNameID" as part of an aggregate function The SQl is SELECT tMstrProgram.*, tMstrProgramName.*, tMstrAgeGrp.*, First([GroupID]) AS Expr1 FROM (tMstrProgram LEFT JOIN tMstrProgramName ON tMstrProgram.ProgNameID = tMstrProgramName.ProgNameID) LEFT JOIN tMstrAgeGrp ON tMstrProgram.FundingID = tMstrAgeGrp.FundingID; ProgNameID is a field in botht MstrProgram and tMstrProgramName GroupID is a field in tMstrAgeGrp My SQL is extremely basic. The First() operator is a totals-query operator, so it assumes you're doing some sort of totals query; you don't have a GROUP BY expression but you're including all the fields from all three tables, so it's not clear to me what you're TRYING to do. What are these tables? What are you trying to accomplish? The wizard doesn't (in my experience) throw in aggregate functions like First(); did you add this yourself - and if so, why? John W. Vinson[MVP] |
#4
|
|||
|
|||
"Aggregate" error when running query
On Tue, 23 Jan 2007 23:18:33 -0800, Graham
wrote: Hi John Yes, I did manually include "First" in an effort to achieve the result below. The problem is that there are 2 records in tMstrAgeGrp for each FundingID and, therefore when the query is run it results in duplication of tMstrProgram records. Whereas, I only want 1 tMstrProgram record returned if there is a matching record in tMstrAgeGrp. But you want to update the Form? If so, WHICH matching record in tMstrProgram do you want to update? What is the LOGICAL relationship between these tables? All fields were included out of laziness - when the problem is resolved, I will look critically at just which fields are required. You're making your job harder, not easier. John W. Vinson[MVP] |
Thread Tools | |
Display Modes | |
|
|