"Aggregate" error when running query

[Guest]

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.

 

[John Vinson]

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]

 

[Guest]

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.

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]

 

[John Vinson]

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]