Error Message: Could Refer to more than one table

  • Thread starter Thread starter Nik
  • Start date Start date
N

Nik

This is the error message I am recieving: "The Specified field
'[Posters-2000].State' could refer to more than one table listed in the

FROM clause of your SQL statement."


I have looked in the sql code and could not find anything wrong, below
is the code:


SELECT [Posters-2000].[Document Number], [Posters-2000].[Needs Update],

[Posters-2000].[Last Update], [Posters-2000].[New Update Date], [Orders

Table].[Poster(s) Ordered], [Orders Table].[Order Type], [Orders
Table].[State Ordered], [Locations Table].Company, [Orders
Table].[Order Date], [Posters-2000].Title, [Locations Table].City,
[Locations Table].State, [Orders Table].[Quantity Ordered], [Locations
Table].Service, [Locations Table].[Location ID], [Locations
Table].Division, [Locations Table].Location, [Locations Table].[Contact

First Name], [Locations Table].[Contact Last Name],
[Posters-2000].State
FROM [Posters-2000] INNER JOIN ([qry Poster Offerings] INNER JOIN
([Orders Table] INNER JOIN [Locations Table] ON [Orders
Table].[Location ID] = [Locations Table].[Location ID]) ON [qry Poster
Offerings].[Poster Set] = [Orders Table].[Poster(s) Ordered]) ON
[Posters-2000].State = [Orders Table].[State Ordered]
WHERE ((([Posters-2000].[Needs Update])=Yes) AND (([Orders
Table].[Order Type])="New") AND (([Locations Table].Service) Is Not
Null));


It appears that everything has a table associated to it, if needed.
Maybe I just have been staring at it for way to long to notice
anything.
 
Nik, I would expect that SQL statement to work correctly too.

Make sure the Name AutoCorrect boxes are unchecked under:
Tools | Options | General
Then compact the database.
Then make a design change to the query.
Hopefully that will be enough to unconfuse Access.

If that does not work, you appear to have a query as a source "table" for
this query. If that query also uses the Posters-2000 table, you might
consider aliasing the table in that query.

Actually you might consider aliasing the table name in this query anyway.
There's a chance that the hyphen in the table name might be what is
confusing Access. That does happen with data in a primary key field, but I
would not have expected it to be an issue in a table name. If that does
solve the problem, you might want to locate msjet40.dll on your hard disk,
and see if it needs a service pack applied from:
http://support.microsoft.com/kb/239114
 
Tried the first part, but didnt fix anything. The query does use
Posters-2000, so it looks like I need to alias that table name.

I have never alaised anything, could you give me some insight on how
to.

Thanks.
 
Open the query in design view.
Right-click the table in the upper pane, and choose Properties.
The first property in the Properties box is the Alias.
 
Back
Top