I have two tables, which both have a field called FixtureID in it. One table is called tbl_fixtures and the other tbl_reports.

I want to return a list of FixtureID's that are in tbl_fixtures, that are not in tbl_reports.
Simple? Sounds it!

I tried to use the Not Exists structure as follows but it returns No rows when it should return about a dozen or more.
SELECT fix.FixtureID
FROM tbl_Fixtures AS fix
WHERE NOT EXISTS (SELECT Rep.FixtureID FROM tbl_Reports AS Rep)

Then Access has reformatted it to the following:
SELECT fix.FixtureID
FROM tbl_Fixtures AS fix
WHERE (((Exists (SELECT Rep.FixtureID FROM tbl_Reports AS Rep))=False));

Which also makes sense logically to me, but again it is not bringing any rows back.

Lost and confused as to why it isnt working,
BK

Erm, After posting this i can see straight away what is wrong. I had the two queries back to front :(


SELECT Rep.FixtureID
FROM tbl_Reports AS Rep
WHERE (Exists (SELECT fix.FixtureID FROM tbl_Fixtures AS fix)=true);


Works

Oh well!

SELECT Rep.FixtureID
FROM tbl_Reports AS Rep
WHERE (Exists (SELECT fix.FixtureID FROM tbl_Fixtures AS fix)=true);

I believe your query returned every record from the tbl_Reports table because the subquery will always be true when there are records in the tbl_Fixtures table.



I want to return a list of FixtureID's that are in tbl_fixtures, that are not in tbl_reports.

Try the Find Unmatched Query Wizard, which can build a more efficient query than Not Exists. (To use Not Exists correctly, you need to use it in a correlated way, which is inefficient.)

^