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

[Don]

I have run into a problem which if I recall correctly is related to how
reports process SQL strings. In the OnLoad method, I use Me.RecordSource
with an SQL string to define the records to use. However, when I run the
report, the following error message is displayed:

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

The report has the following headers for sorting (and I think this is where
the problem comes from):

Department (shipping, engineering, etc)
Position (unique ID number)
Person (can be more than one per position due to pending retirements,
etc)


If I create a stand-alone table based on this query and use the table as the
data source for the report, it works as expected. The reason I am using an
SQL string is that the report is in a front end database and the data is
currently in an Access end database. In the future, we are planning on
connecting the front end to a SQL server.

Any thoughts on how to approach this problem?

Thanks!

Don

 

[Don]

Can the ordering of the columns in the SELECT clause make a difference?

Grasping for straws here!

Don

 

[Don]

Instead of using an SQL string in the RecordSource property, would it make
more sense to use the report's RecordSet property?

Don