Error message when running report

[Slez via AccessMonster.com]

When I add a field called "Estim" to a report, I get the following message,
and the report will not run:

"Multi-level GROUP BY clause is not allowed in a subquery."

The field "Estim" has the following expression in the report's source query:
Estim: (SELECT First(Bid.Estimator) FROM Bid WHERE Bid.ProjectID= Project.
[ProjectID])
The query runs fine and returns the proper records.

The report has 2 grouping levels: NextFollowUpDate, and ProjectName

If I leave the Estim control off of the form, the report runs properly. It
is only when I add it that the message pops up. What might be the cause of
this?
Thanks
Slez

 

[Duane Hookom]

Are you attempting to sort or group by the subquery generated value? Are you
attempting to bind this field to a control in a group header or footer?

Do you realize that "First" is consider the same as "Any" when selecting
from a table?

 

[Slez via AccessMonster.com]

I am not trying to sort by the Estim field (at least not intentionally). I
am trying to place the control in the ProjectName header of the report, but I
am not sure what you mean by "bind" the field to a control. No matter where
in the report (detail, header, etc.) I try to place this control, I get the
same error.

I didn't realize that "First" is the same as "Any", but it really won't
matter given the values I'm pulling and the reason for them.

Thanks
Slez

Are you attempting to sort or group by the subquery generated value? Are you
attempting to bind this field to a control in a group header or footer?

Do you realize that "First" is consider the same as "Any" when selecting
from a table?

When I add a field called "Estim" to a report, I get the following message,
and the report will not run:

[quoted text clipped - 13 lines]

Thanks
Slez

 

[Duane Hookom]

It should work to first create a totals query:
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
Then remove the subquery and add this query to your report's record soruce
query. Join the ProjectID fields and create your report.

--
Duane Hookom
Microsoft Access MVP

I am not trying to sort by the Estim field (at least not intentionally). I
am trying to place the control in the ProjectName header of the report, but I
am not sure what you mean by "bind" the field to a control. No matter where
in the report (detail, header, etc.) I try to place this control, I get the
same error.

I didn't realize that "First" is the same as "Any", but it really won't
matter given the values I'm pulling and the reason for them.

Thanks
Slez

Are you attempting to sort or group by the subquery generated value? Are you
attempting to bind this field to a control in a group header or footer?

Do you realize that "First" is consider the same as "Any" when selecting
from a table?

When I add a field called "Estim" to a report, I get the following message,
and the report will not run:

[quoted text clipped - 13 lines]

Thanks
Slez

 

[Slez via AccessMonster.com]

I'm not quite sure what you mean by a "totals" query. Are you saying create
another separate query just for this data?

I also am not sure what you mean by adding this query to my report's record
source. I was not aware I could base a report off of 2 different queries.
Am I understanding you correctly?

To me, the wierd part is that running the query returns exactly the right
number of records, and it seems odd that the report won't recognize the same
thing.

Please explain in more detail if you can. If this is blatantly simple, I
apologize for my idiocy! I'm in the final 2 days before I allow users access
to this new database and my brain is a bit like "mush"!
Thanks!
Slez

It should work to first create a totals query:
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
Then remove the subquery and add this query to your report's record soruce
query. Join the ProjectID fields and create your report.

I am not trying to sort by the Estim field (at least not intentionally). I
am trying to place the control in the ProjectName header of the report, but I

[quoted text clipped - 19 lines]

 

[Slez via AccessMonster.com]

I'm not quite sure what you mean by a "totals" query. Are you saying create
another separate query just for this data?

I also am not sure what you mean by adding this query to my report's record
source. I was not aware I could base a report off of 2 different queries.
Am I understanding you correctly?

To me, the wierd part is that running the query returns exactly the right
number of records, and it seems odd that the report won't recognize the same
thing.

Please explain in more detail if you can. If this is blatantly simple, I
apologize for my idiocy! I'm in the final 2 days before I allow users access
to this new database and my brain is a bit like "mush"!
Thanks!
Slez

It should work to first create a totals query:
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
Then remove the subquery and add this query to your report's record soruce
query. Join the ProjectID fields and create your report.

I am not trying to sort by the Estim field (at least not intentionally). I
am trying to place the control in the ProjectName header of the report, but I

[quoted text clipped - 19 lines]

 

[Duane Hookom]

Just try do what I suggested
=====================
It should work to first create a totals query:
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
=====================
Save this query as qgrpProj1stEstimator
=====================
Then remove the subquery and add this query to your report's record
source query. Join the ProjectID fields and create your report.
=====================
This means to remove the [Estim] column from your report's record source
query. While you are in the design view of this query, add
qgrpProj1stEstimator and join the ProjectID fields. Use [AnyEstimator] in
your report.
--
Duane Hookom
Microsoft Access MVP

I'm not quite sure what you mean by a "totals" query. Are you saying create
another separate query just for this data?

I also am not sure what you mean by adding this query to my report's record
source. I was not aware I could base a report off of 2 different queries.
Am I understanding you correctly?

To me, the wierd part is that running the query returns exactly the right
number of records, and it seems odd that the report won't recognize the same
thing.

Please explain in more detail if you can. If this is blatantly simple, I
apologize for my idiocy! I'm in the final 2 days before I allow users access
to this new database and my brain is a bit like "mush"!
Thanks!
Slez

It should work to first create a totals query:
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
Then remove the subquery and add this query to your report's record soruce
query. Join the ProjectID fields and create your report.

I am not trying to sort by the Estim field (at least not intentionally). I
am trying to place the control in the ProjectName header of the report, but I

[quoted text clipped - 19 lines]

Thanks
Slez

 

[Slez via AccessMonster.com]

OK Duane... it's official... I'm clueless!
I'm not even successful at creating the totals query. I've tried numerous
combinations and gotten everything from syntax errors to "You did not select
from the list". I tried to enter numerous variations in the query such as:

Field: ProjectID.First(Bid.Estimator) AnyEstimator
Table: Bid
Total: ProjectID

Anytime I enter SELECT, FROM, or GROUP BY, I start to get syntax errors.

If I do get this figured out, I'm sure I will then be questioning how to
"join" the ProjectID fields in my reports record source query. You may be
thinking I'm a lost cause, so if you choose not to continue replying I will
fully understand!

Just try do what I suggested
=====================
It should work to first create a totals query:
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
=====================
Save this query as qgrpProj1stEstimator
=====================
Then remove the subquery and add this query to your report's record
source query. Join the ProjectID fields and create your report.
=====================
This means to remove the [Estim] column from your report's record source
query. While you are in the design view of this query, add
qgrpProj1stEstimator and join the ProjectID fields. Use [AnyEstimator] in
your report.

I'm not quite sure what you mean by a "totals" query. Are you saying create
another separate query just for this data?

[quoted text clipped - 25 lines]

 

[Duane Hookom]

To create the totals query, open a new blank query and view the SQL view.
Then paste this
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
Then view the results.
Does this look good? Are you seeing the expected Estimator? Did you get any
errors?

--
Duane Hookom
Microsoft Access MVP

OK Duane... it's official... I'm clueless!
I'm not even successful at creating the totals query. I've tried numerous
combinations and gotten everything from syntax errors to "You did not select
from the list". I tried to enter numerous variations in the query such as:

Field: ProjectID.First(Bid.Estimator) AnyEstimator
Table: Bid
Total: ProjectID

Anytime I enter SELECT, FROM, or GROUP BY, I start to get syntax errors.

If I do get this figured out, I'm sure I will then be questioning how to
"join" the ProjectID fields in my reports record source query. You may be
thinking I'm a lost cause, so if you choose not to continue replying I will
fully understand!

Just try do what I suggested
=====================
It should work to first create a totals query:
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
=====================
Save this query as qgrpProj1stEstimator
=====================
Then remove the subquery and add this query to your report's record
source query. Join the ProjectID fields and create your report.
=====================
This means to remove the [Estim] column from your report's record source
query. While you are in the design view of this query, add
qgrpProj1stEstimator and join the ProjectID fields. Use [AnyEstimator] in
your report.

I'm not quite sure what you mean by a "totals" query. Are you saying create
another separate query just for this data?

[quoted text clipped - 25 lines]

Thanks
Slez

 

[Slez via AccessMonster.com]

Knowing I was supposed to put this directly into SQL view helps. I've never
created a query that way before. I tried it and when I run the query I get
an error message:

Syntax error (missing operator) in query expression 'First(Bid.Estimator)
AnyEstimator'

Thanks
Slez

To create the totals query, open a new blank query and view the SQL view.
Then paste this
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
Then view the results.
Does this look good? Are you seeing the expected Estimator? Did you get any
errors?

OK Duane... it's official... I'm clueless!
I'm not even successful at creating the totals query. I've tried numerous

[quoted text clipped - 33 lines]

 

[Duane Hookom]

You may need to add "AS"
SELECT ProjectID, First(Bid.Estimator) AS AnyEstimator
FROM Bid
GROUP BY ProjectID;
This assumes you have a table named "Bid" with fields named ProjectID and
Estimator.

If you get an error message, come back with your exact SQL.

--
Duane Hookom
Microsoft Access MVP

Knowing I was supposed to put this directly into SQL view helps. I've never
created a query that way before. I tried it and when I run the query I get
an error message:

Syntax error (missing operator) in query expression 'First(Bid.Estimator)
AnyEstimator'

Thanks
Slez

To create the totals query, open a new blank query and view the SQL view.
Then paste this
SELECT ProjectID, First(Bid.Estimator) AnyEstimator
FROM Bid
GROUP BY ProjectID;
Then view the results.
Does this look good? Are you seeing the expected Estimator? Did you get any
errors?

OK Duane... it's official... I'm clueless!
I'm not even successful at creating the totals query. I've tried numerous

[quoted text clipped - 33 lines]

Thanks
Slez

 

[Slez via AccessMonster.com]

Duane,
Adding "AS" to the statement worked! I was then able to add that to my
report's record source query, and ultimately [AnyEstimator] to my report.
Everything works perfectly!

I really appreciate your extended help! I could not have accomplished this
without it!
Slez

You may need to add "AS"
SELECT ProjectID, First(Bid.Estimator) AS AnyEstimator
FROM Bid
GROUP BY ProjectID;
This assumes you have a table named "Bid" with fields named ProjectID and
Estimator.

If you get an error message, come back with your exact SQL.

Knowing I was supposed to put this directly into SQL view helps. I've never
created a query that way before. I tried it and when I run the query I get

[quoted text clipped - 20 lines]

 

[Duane Hookom]

Glad to hear you got this sorted out.
--
Duane Hookom
Microsoft Access MVP

Duane,
Adding "AS" to the statement worked! I was then able to add that to my
report's record source query, and ultimately [AnyEstimator] to my report.
Everything works perfectly!

I really appreciate your extended help! I could not have accomplished this
without it!
Slez

You may need to add "AS"
SELECT ProjectID, First(Bid.Estimator) AS AnyEstimator
FROM Bid
GROUP BY ProjectID;
This assumes you have a table named "Bid" with fields named ProjectID and
Estimator.

If you get an error message, come back with your exact SQL.

Knowing I was supposed to put this directly into SQL view helps. I've never
created a query that way before. I tried it and when I run the query I get

[quoted text clipped - 20 lines]

Thanks
Slez