Wicked error- could refer to more than one table in the FROM

[Marcus]

I have one table with data in it.

I have two queries: 1) a simple select query that filters out some records
based on values as follows:

SELECT [Wins-all].RecordNm, [Wins-all].Type, [Wins-all].[IP Address],
[Wins-all].State, [Wins-all].Static, [Wins-all].Owner, [Wins-all].Version,
[Wins-all].Expiration
FROM [Wins-all]
WHERE ((([Wins-all].Type) Like "*1Eh*") AND (([Wins-all].State) Like
"Active"));

and 2) a join query that matches the first query with data in the original
table (matched in IP Address).

SELECT [Group Names].RecordNm, [Group Names].Type, [Group Names].[IP
Address], [Group Names].State, [Group Names].Static, [Wins-all].RecordNm,
[Wins-all].Type, [Wins-all].[IP Address], [Wins-all].Expiration
FROM [Wins-all] INNER JOIN [Group Names] ON [Wins-all].[IP Address]=[Group
Names].[IP Address]
WHERE ((([Group Names].State) Like "Active") AND (([Wins-all].Type) Like
"*workstation*"));

The query works as expected - shows results etc.

BUT, when trying to use this query to do things such as sort a column in the
output, or run a "find duplicates" query against the results - I get the
follwoing error:

--- "The specified field '[Wins-all].[IP Address]' could refer to more than
one table listed in the FROM clause of your SQL statement."

Note that I can sort on all output columns successfully except these three
(generates the error):
[Wins-all].RecordNm, [Wins-all].Type, [Wins-all].[IP Address]



Thanks in advance for your help!
Marcus

 

[Ken Sheridan]

Marcus:

Why not just base the second query on the table:

SELECT *
FROM [Wins-all]
WHERE State = "Active"
AND Type LIKE "*1Eh*"
AND Type LIKE "*workstation*";

Ken Sheridan
Stafford, England