A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"Aggregate" error when running query



 
 
Thread Tools Display Modes
  #1  
Old January 24th, 2007, 05:04 AM posted to microsoft.public.access.queries
Graham
external usenet poster
 
Posts: 306
Default "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  
Old January 24th, 2007, 06:49 AM posted to microsoft.public.access.queries
John Vinson
external usenet poster
 
Posts: 4,033
Default "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  
Old January 24th, 2007, 08:18 AM posted to microsoft.public.access.queries
Graham
external usenet poster
 
Posts: 306
Default "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  
Old January 26th, 2007, 12:58 AM posted to microsoft.public.access.queries
John Vinson
external usenet poster
 
Posts: 4,033
Default "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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:26 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.