Aggregate Function Error

G

Guest

When I try to run a report I get this message:

"You tried to execute a query that does not include the specified expression
'[PLA Practitioners Contact List vJan23].[PRAXIS ID]' as part of an aggregate
function."

From lurking I other posts, I removed the "Total" row from the query but it
still locked me out of the report (same error message).

What I REALLY trying to do is get a report that looks like a "spreadsheet".
Quite simple (or so I thought):

Region |# of Practicums | # of 2nd Groups | # of 3rd Groups | etc.
a
b
c

Below is the SQL from my query with the total row in (I think I need it for
the number of each type of group (practicum, 2nd group, etc.) in each region).

I'm new to Access (using v2000 on XP), and would greatly appreciate any
suggestions.

cheers,
karen

SELECT [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]) AS [CountOfGroup #], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners Contact
List vJan23].[LastName], [PLA Practitioners Contact List vJan23].[PRAXIS ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Region], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners Contact
List vJan23].[LastName], [PLA Practitioners Contact List vJan23].[PRAXIS ID]
HAVING ((([PLA Practitioners Contact List vJan23].[PRAXIS ID])<300))
ORDER BY [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]);
 
T

Tom Ellison

Dear Klam:

I reproduce your query here, edited for my reading preferences:

SELECT Region, Count([Group #]) AS [CountOfGroup #],
FirstName, LastName, [PRAXIS ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY Region, FirstName, LastName, [PRAXIS ID]
HAVING [PRAXIS ID] < 300
ORDER BY [Region], Count([Group #])

Does this look better?

The error message you're getting looks bogus. Could you try the code I show
above to see if it helps any? I couldn't see the cause. First, try to run
this query as is, not with the report. If it succeeds, then try the report
on this query as well.

What happens? Please reply with any error message you receive.

Tom Ellison
 
G

Guest

Hi Tom,

Thx a mint for taking a look at it and your reply!

Your code worked fine with the query and the report too. Not sure what you
meant by the error being "bogus"...you mean it should not have been an error?
Is Access unstable like that?

thx again,
karen

Tom Ellison said:
Dear Klam:

I reproduce your query here, edited for my reading preferences:

SELECT Region, Count([Group #]) AS [CountOfGroup #],
FirstName, LastName, [PRAXIS ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY Region, FirstName, LastName, [PRAXIS ID]
HAVING [PRAXIS ID] < 300
ORDER BY [Region], Count([Group #])

Does this look better?

The error message you're getting looks bogus. Could you try the code I show
above to see if it helps any? I couldn't see the cause. First, try to run
this query as is, not with the report. If it succeeds, then try the report
on this query as well.

What happens? Please reply with any error message you receive.

Tom Ellison


klam said:
When I try to run a report I get this message:

"You tried to execute a query that does not include the specified
expression
'[PLA Practitioners Contact List vJan23].[PRAXIS ID]' as part of an
aggregate
function."

From lurking I other posts, I removed the "Total" row from the query but
it
still locked me out of the report (same error message).

What I REALLY trying to do is get a report that looks like a
"spreadsheet".
Quite simple (or so I thought):

Region |# of Practicums | # of 2nd Groups | # of 3rd Groups | etc.
a
b
c

Below is the SQL from my query with the total row in (I think I need it
for
the number of each type of group (practicum, 2nd group, etc.) in each
region).

I'm new to Access (using v2000 on XP), and would greatly appreciate any
suggestions.

cheers,
karen

SELECT [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]) AS [CountOfGroup #], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners Contact
List vJan23].[LastName], [PLA Practitioners Contact List vJan23].[PRAXIS
ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Region], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners Contact
List vJan23].[LastName], [PLA Practitioners Contact List vJan23].[PRAXIS
ID]
HAVING ((([PLA Practitioners Contact List vJan23].[PRAXIS ID])<300))
ORDER BY [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]);
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Klam:

Based on what you posted, I could not see where it could have caused this
particular error. I could be mistaken.

No, this is not typical of Access. With some experimentation, you could
probably find out what caused this. Make changes in small increments from
what you had toward what I posted that works. I'd like to know just what it
was.

Tom Ellison


klam said:
Hi Tom,

Thx a mint for taking a look at it and your reply!

Your code worked fine with the query and the report too. Not sure what
you
meant by the error being "bogus"...you mean it should not have been an
error?
Is Access unstable like that?

thx again,
karen

Tom Ellison said:
Dear Klam:

I reproduce your query here, edited for my reading preferences:

SELECT Region, Count([Group #]) AS [CountOfGroup #],
FirstName, LastName, [PRAXIS ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY Region, FirstName, LastName, [PRAXIS ID]
HAVING [PRAXIS ID] < 300
ORDER BY [Region], Count([Group #])

Does this look better?

The error message you're getting looks bogus. Could you try the code I
show
above to see if it helps any? I couldn't see the cause. First, try to
run
this query as is, not with the report. If it succeeds, then try the
report
on this query as well.

What happens? Please reply with any error message you receive.

Tom Ellison


klam said:
When I try to run a report I get this message:

"You tried to execute a query that does not include the specified
expression
'[PLA Practitioners Contact List vJan23].[PRAXIS ID]' as part of an
aggregate
function."

From lurking I other posts, I removed the "Total" row from the query
but
it
still locked me out of the report (same error message).

What I REALLY trying to do is get a report that looks like a
"spreadsheet".
Quite simple (or so I thought):

Region |# of Practicums | # of 2nd Groups | # of 3rd Groups | etc.
a
b
c

Below is the SQL from my query with the total row in (I think I need it
for
the number of each type of group (practicum, 2nd group, etc.) in each
region).

I'm new to Access (using v2000 on XP), and would greatly appreciate any
suggestions.

cheers,
karen

SELECT [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]) AS [CountOfGroup #], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Region], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
HAVING ((([PLA Practitioners Contact List vJan23].[PRAXIS ID])<300))
ORDER BY [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]);
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Tom,

I know very little about Access and only go to SQL as I have read in other
posts that it is helpful/crucial to paste it in the post to figure out the
problem. But, when I look at it, it seems like mine has an extra [Group #]
in the SELECT and GROUP BY lines.

I asked about stability as sometimes when I tried to test it in Print
Preview, it would lock me out of the report with the aggregate error, I'd
start from scratch again and it would ask for Parameters when I tried Print
Preview and sometimes not. I couldn't tell why it was acting one way or the
other.

thx again,
karen

Tom Ellison said:
Dear Klam:

Based on what you posted, I could not see where it could have caused this
particular error. I could be mistaken.

No, this is not typical of Access. With some experimentation, you could
probably find out what caused this. Make changes in small increments from
what you had toward what I posted that works. I'd like to know just what it
was.

Tom Ellison


klam said:
Hi Tom,

Thx a mint for taking a look at it and your reply!

Your code worked fine with the query and the report too. Not sure what
you
meant by the error being "bogus"...you mean it should not have been an
error?
Is Access unstable like that?

thx again,
karen

Tom Ellison said:
Dear Klam:

I reproduce your query here, edited for my reading preferences:

SELECT Region, Count([Group #]) AS [CountOfGroup #],
FirstName, LastName, [PRAXIS ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY Region, FirstName, LastName, [PRAXIS ID]
HAVING [PRAXIS ID] < 300
ORDER BY [Region], Count([Group #])

Does this look better?

The error message you're getting looks bogus. Could you try the code I
show
above to see if it helps any? I couldn't see the cause. First, try to
run
this query as is, not with the report. If it succeeds, then try the
report
on this query as well.

What happens? Please reply with any error message you receive.

Tom Ellison


When I try to run a report I get this message:

"You tried to execute a query that does not include the specified
expression
'[PLA Practitioners Contact List vJan23].[PRAXIS ID]' as part of an
aggregate
function."

From lurking I other posts, I removed the "Total" row from the query
but
it
still locked me out of the report (same error message).

What I REALLY trying to do is get a report that looks like a
"spreadsheet".
Quite simple (or so I thought):

Region |# of Practicums | # of 2nd Groups | # of 3rd Groups | etc.
a
b
c

Below is the SQL from my query with the total row in (I think I need it
for
the number of each type of group (practicum, 2nd group, etc.) in each
region).

I'm new to Access (using v2000 on XP), and would greatly appreciate any
suggestions.

cheers,
karen

SELECT [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]) AS [CountOfGroup #], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Region], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
HAVING ((([PLA Practitioners Contact List vJan23].[PRAXIS ID])<300))
ORDER BY [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]);
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Karen:

Are you fixed now, or what?

Tom Ellison


klam said:
Hi Tom,

I know very little about Access and only go to SQL as I have read in other
posts that it is helpful/crucial to paste it in the post to figure out the
problem. But, when I look at it, it seems like mine has an extra [Group
#]
in the SELECT and GROUP BY lines.

I asked about stability as sometimes when I tried to test it in Print
Preview, it would lock me out of the report with the aggregate error, I'd
start from scratch again and it would ask for Parameters when I tried
Print
Preview and sometimes not. I couldn't tell why it was acting one way or
the
other.

thx again,
karen

Tom Ellison said:
Dear Klam:

Based on what you posted, I could not see where it could have caused this
particular error. I could be mistaken.

No, this is not typical of Access. With some experimentation, you could
probably find out what caused this. Make changes in small increments
from
what you had toward what I posted that works. I'd like to know just what
it
was.

Tom Ellison


klam said:
Hi Tom,

Thx a mint for taking a look at it and your reply!

Your code worked fine with the query and the report too. Not sure what
you
meant by the error being "bogus"...you mean it should not have been an
error?
Is Access unstable like that?

thx again,
karen

:

Dear Klam:

I reproduce your query here, edited for my reading preferences:

SELECT Region, Count([Group #]) AS [CountOfGroup #],
FirstName, LastName, [PRAXIS ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY Region, FirstName, LastName, [PRAXIS ID]
HAVING [PRAXIS ID] < 300
ORDER BY [Region], Count([Group #])

Does this look better?

The error message you're getting looks bogus. Could you try the code
I
show
above to see if it helps any? I couldn't see the cause. First, try
to
run
this query as is, not with the report. If it succeeds, then try the
report
on this query as well.

What happens? Please reply with any error message you receive.

Tom Ellison


When I try to run a report I get this message:

"You tried to execute a query that does not include the specified
expression
'[PLA Practitioners Contact List vJan23].[PRAXIS ID]' as part of an
aggregate
function."

From lurking I other posts, I removed the "Total" row from the query
but
it
still locked me out of the report (same error message).

What I REALLY trying to do is get a report that looks like a
"spreadsheet".
Quite simple (or so I thought):

Region |# of Practicums | # of 2nd Groups | # of 3rd Groups | etc.
a
b
c

Below is the SQL from my query with the total row in (I think I need
it
for
the number of each type of group (practicum, 2nd group, etc.) in
each
region).

I'm new to Access (using v2000 on XP), and would greatly appreciate
any
suggestions.

cheers,
karen

SELECT [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]) AS [CountOfGroup #],
[PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Region], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
HAVING ((([PLA Practitioners Contact List vJan23].[PRAXIS ID])<300))
ORDER BY [PLA Practitioners Contact List vJan23].[Region],
Count([PLA
Practitioners Contact List vJan23].[Group #]);
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Tom,

Sorry for my ambiguous reply. Yes, I have it working now.

Thx again for your help (=

-karen

Tom Ellison said:
Dear Karen:

Are you fixed now, or what?

Tom Ellison


klam said:
Hi Tom,

I know very little about Access and only go to SQL as I have read in other
posts that it is helpful/crucial to paste it in the post to figure out the
problem. But, when I look at it, it seems like mine has an extra [Group
#]
in the SELECT and GROUP BY lines.

I asked about stability as sometimes when I tried to test it in Print
Preview, it would lock me out of the report with the aggregate error, I'd
start from scratch again and it would ask for Parameters when I tried
Print
Preview and sometimes not. I couldn't tell why it was acting one way or
the
other.

thx again,
karen

Tom Ellison said:
Dear Klam:

Based on what you posted, I could not see where it could have caused this
particular error. I could be mistaken.

No, this is not typical of Access. With some experimentation, you could
probably find out what caused this. Make changes in small increments
from
what you had toward what I posted that works. I'd like to know just what
it
was.

Tom Ellison


Hi Tom,

Thx a mint for taking a look at it and your reply!

Your code worked fine with the query and the report too. Not sure what
you
meant by the error being "bogus"...you mean it should not have been an
error?
Is Access unstable like that?

thx again,
karen

:

Dear Klam:

I reproduce your query here, edited for my reading preferences:

SELECT Region, Count([Group #]) AS [CountOfGroup #],
FirstName, LastName, [PRAXIS ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY Region, FirstName, LastName, [PRAXIS ID]
HAVING [PRAXIS ID] < 300
ORDER BY [Region], Count([Group #])

Does this look better?

The error message you're getting looks bogus. Could you try the code
I
show
above to see if it helps any? I couldn't see the cause. First, try
to
run
this query as is, not with the report. If it succeeds, then try the
report
on this query as well.

What happens? Please reply with any error message you receive.

Tom Ellison


When I try to run a report I get this message:

"You tried to execute a query that does not include the specified
expression
'[PLA Practitioners Contact List vJan23].[PRAXIS ID]' as part of an
aggregate
function."

From lurking I other posts, I removed the "Total" row from the query
but
it
still locked me out of the report (same error message).

What I REALLY trying to do is get a report that looks like a
"spreadsheet".
Quite simple (or so I thought):

Region |# of Practicums | # of 2nd Groups | # of 3rd Groups | etc.
a
b
c

Below is the SQL from my query with the total row in (I think I need
it
for
the number of each type of group (practicum, 2nd group, etc.) in
each
region).

I'm new to Access (using v2000 on XP), and would greatly appreciate
any
suggestions.

cheers,
karen

SELECT [PLA Practitioners Contact List vJan23].[Region], Count([PLA
Practitioners Contact List vJan23].[Group #]) AS [CountOfGroup #],
[PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Region], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
HAVING ((([PLA Practitioners Contact List vJan23].[PRAXIS ID])<300))
ORDER BY [PLA Practitioners Contact List vJan23].[Region],
Count([PLA
Practitioners Contact List vJan23].[Group #]);
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Karen:

GREAT!

Tom Ellison


klam said:
Hi Tom,

Sorry for my ambiguous reply. Yes, I have it working now.

Thx again for your help (=

-karen

Tom Ellison said:
Dear Karen:

Are you fixed now, or what?

Tom Ellison


klam said:
Hi Tom,

I know very little about Access and only go to SQL as I have read in
other
posts that it is helpful/crucial to paste it in the post to figure out
the
problem. But, when I look at it, it seems like mine has an extra
[Group
#]
in the SELECT and GROUP BY lines.

I asked about stability as sometimes when I tried to test it in Print
Preview, it would lock me out of the report with the aggregate error,
I'd
start from scratch again and it would ask for Parameters when I tried
Print
Preview and sometimes not. I couldn't tell why it was acting one way
or
the
other.

thx again,
karen

:

Dear Klam:

Based on what you posted, I could not see where it could have caused
this
particular error. I could be mistaken.

No, this is not typical of Access. With some experimentation, you
could
probably find out what caused this. Make changes in small increments
from
what you had toward what I posted that works. I'd like to know just
what
it
was.

Tom Ellison


Hi Tom,

Thx a mint for taking a look at it and your reply!

Your code worked fine with the query and the report too. Not sure
what
you
meant by the error being "bogus"...you mean it should not have been
an
error?
Is Access unstable like that?

thx again,
karen

:

Dear Klam:

I reproduce your query here, edited for my reading preferences:

SELECT Region, Count([Group #]) AS [CountOfGroup #],
FirstName, LastName, [PRAXIS ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY Region, FirstName, LastName, [PRAXIS ID]
HAVING [PRAXIS ID] < 300
ORDER BY [Region], Count([Group #])

Does this look better?

The error message you're getting looks bogus. Could you try the
code
I
show
above to see if it helps any? I couldn't see the cause. First,
try
to
run
this query as is, not with the report. If it succeeds, then try
the
report
on this query as well.

What happens? Please reply with any error message you receive.

Tom Ellison


When I try to run a report I get this message:

"You tried to execute a query that does not include the specified
expression
'[PLA Practitioners Contact List vJan23].[PRAXIS ID]' as part of
an
aggregate
function."

From lurking I other posts, I removed the "Total" row from the
query
but
it
still locked me out of the report (same error message).

What I REALLY trying to do is get a report that looks like a
"spreadsheet".
Quite simple (or so I thought):

Region |# of Practicums | # of 2nd Groups | # of 3rd Groups |
etc.
a
b
c

Below is the SQL from my query with the total row in (I think I
need
it
for
the number of each type of group (practicum, 2nd group, etc.) in
each
region).

I'm new to Access (using v2000 on XP), and would greatly
appreciate
any
suggestions.

cheers,
karen

SELECT [PLA Practitioners Contact List vJan23].[Region],
Count([PLA
Practitioners Contact List vJan23].[Group #]) AS [CountOfGroup
#],
[PLA
Practitioners Contact List vJan23].[FirstName], [PLA
Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
FROM [PLA Practitioners Contact List vJan23]
GROUP BY [PLA Practitioners Contact List vJan23].[Region], [PLA
Practitioners Contact List vJan23].[FirstName], [PLA
Practitioners
Contact
List vJan23].[LastName], [PLA Practitioners Contact List
vJan23].[PRAXIS
ID]
HAVING ((([PLA Practitioners Contact List vJan23].[PRAXIS
ID])<300))
ORDER BY [PLA Practitioners Contact List vJan23].[Region],
Count([PLA
Practitioners Contact List vJan23].[Group #]);
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I query companies not having members w/certain level of mgm 5
query to overcome field type problem? 4
Crosstab Query Multiple 12
Individuals who have not taken course 3
Computing total sales with a query 2
Sum of Services Provided Check Box 2
Nz function 6
Create Aggregate Function 1

Top