error - SQL statement

[aussie_user]

Hi,

I'm not sure if this question should go under queries or reports so I am putting it here.

I have a complex query that involves 2 tables and another query. Because there are so many fields I double clicked on one table so that all of the fields would be in the query.
There are 40 fields in the table that I double clicked to get all the fields into the query, 37 fields in the other table and 59 fields in the other query. The only common field is the Project ID.
I have 5 different types of projects that can be differentiated by setting criteria on the ProjectID field. (eg Like **SMALL**). The first ** accounts for the year of the project and the last ** accounts for the number of the project within the given year.

My problem is with the ProjectID field which is common to both tables and the other query. I want to set criteria on this field. I already have 4 copies of this query (with different names) that work fine. I added the ProjectID field again so that it appears twice in the query (once where the entire table is listed and again from the source query) with the criteria set. However on the last query that I want to do, the query seems to work fine but when I link it to a report I get the following error message "The specified field "ProjectID" could refer to more than one table listed in the from clause of your SQL statement.

I can't understand why this is happening as the queries and reports are all copied from the one query and one report. I simply changed the name of the query, report and changed the criteria.

Can anyone suggest why 4 of the queries and related reports work but the fifth report won't? Any ideas on what I can try to get it to work.


Thanks

 

[mdemarte]

probably query

Check your criteria for your query carefully, probably one of them lists the 'duplicate' field name without listing the table or query from which you want to limit the data. You can re-build the criteria if necessary. If that is all clear, then check the report sorting and totalling, the 'duplicate' field should be listed with the table or query name, just pick it off the list.