Excluding items from a linked table?

[tangcla]

[SOLVED] Excluding items from a linked table?

Hi,
So I'm trying to build a query here, and I know you can link fields in different tables to limit the results... but how do I link a field so that it is NOT in the list of results?

i.e. in TableA, there's a field "Requester name" with results a, b, c, d, ... x, y, z.
Then there's TableB, with a field "Name", that has b, d, z.

Can I link the two together, so that it excludes b, d and z, so my results will be a, c, e, f, g, ... w, x, y ?


I hope I make sense...

 

[Banana]

Something like this?

SELECT * 
FROM TableA 
WHERE ColA NOT EXISTS
                        (SELECT ColA 
                         FROM TableB);

 

[tangcla]

wow, that was quick I was hoping I could slack off work a bit more until I got an answer...! haha.

Is it possible to do it in the design view at all? Or is it only in code? I'm not very confident in creating code


If it helps, the query is like this at the moment. The query has the two fields joined, but will show me the results which IS in table B.

SELECT [TableA].Status, [TableA].[PSR ID], [TableA].[Requester name], [TableA].[Created on], [TableA].[Business area], [TableA].[Charge code], [TableA].[Agreed cost], [TableA].Summary
FROM [TableB] INNER JOIN [TableA] ON [TableB].Name = [TableA].[Requester name];

 

[tangcla]

First post updated, to make things slightly clearer - I hope.

 

[tangcla]

I found out that there's a wizard function which does exactly what I was looking for... LOL