The specified Field Could refer to more than one table... Access 2

[Thorson]

I was building my database in Access 2003 and recently converted my database
to Access 2007. I have a report that works perfectly in 2003 however when I
try to open it in 2007 I get the following error:

The specified Field 'tblDispositionRecords.DispDate' Could refer to more
than one table listed in the FROM clause of your SQL Statment.

I understand that it is saying it can't tell the difference between two
fields labeled "DispDate" but I can't seem to fix the problem.

The report is built from a query which has a joint relationship between a
tblDispositionRecords and another query
qryDSACMonthlyDispositionRecordsTransferred. the second query
(qryDSACMonthlyDispositionRecordsTransferred) is also a query based on
tblDispositionRecords. In the query the report is built on there is only 1
field named "DispDate".

I tried to set the text box in the report specifically equally to the
queryname.dispdate instead of just DispDate, but this did not fix the
problem. I also tried deleting the field "DispDate" in the first query
(qryDSACMonthlyDispositionRecordsTransferred), this also did not fix the
problem.

I ended up changing everything to exactly match what I have in my 2003 file
copy, but again, no changes

Is there something specific I should check or change? Like I said, it works
fine in 2003. Any suggestions?

 

[Allen Browne]

Firstly, make sure the Name AutoCorrect boxes are unchecked. Reasons and
how-to for both versions:
http://allenbrowne.com/bug-03.html
Then compact the database.

Next, alias the table. Right-click the table in the upper pane of query
design, and choose Properties. Set the Alias property to some other unused
name, e.g. atDispositionRecords (if that non-standard prefix hints it is an
'aliased table'.) Access will change its name, and the instances in the SQL
statement that use this name.

Hopefully providing the alias, and removing the Name AutoCorrect confusion
will make it clear enough for both versions of Access to understand what you
want.

 

[Thorson]

That fixed it, Thanks for the help!

Firstly, make sure the Name AutoCorrect boxes are unchecked. Reasons and
how-to for both versions:
http://allenbrowne.com/bug-03.html
Then compact the database.

Next, alias the table. Right-click the table in the upper pane of query
design, and choose Properties. Set the Alias property to some other unused
name, e.g. atDispositionRecords (if that non-standard prefix hints it is an
'aliased table'.) Access will change its name, and the instances in the SQL
statement that use this name.

Hopefully providing the alias, and removing the Name AutoCorrect confusion
will make it clear enough for both versions of Access to understand what you
want.

 

[David R]

Hello, I hope you are still receiving replies to this thread, I was hoping
you could help me with a similar problem.

My database also works in Access 03, but with Access 07, I receive the error:

The specified file 'qselEmplActive.LastName' could refer to more
than one table listing in the FROM clause of your SQL statement.

I've unchecked the boxes for AutoCorrect and compacted the database, but I'm
running into trouble with the last step. What should I do differently in this
situation?

Much appreciated!

 

[Allen Browne]

I'm guessing that some of these queries draw from the same tables, or tables
that have a LastName field. In the lower level query, you can alias the
LastName by typing another name and colon in the Field row before the field
name, e.g.:
Surname: LastName

Then in the upper query (the one that uses that one, use Surname instead.
Since the names are different, it should solve JET's confusion about which
one is intended.

If that doesn't work, post the SQL statement. If qselEmplActive is also a
query, post that one too.

 

[David R]

That did the trick, thank you much!