How to Filter Lookup List?

[Bishrul]

I have a Form Where i Need to see Values from a another table's field so i have created a lookup field, that works perfectly

but i wish to filter that lookup list with values which are not yet Added??

Table -- TblSup Field -- SCID

Table -- TblRec Field -- LPSCID

i have a lookup list from TblRec!LPSCID field so i can view values from TblSup!SCID Field
But Wish to Filter so that it shows only the values which are not already present in TblRec!LPSCID Field???

[twinnyfo]

I think this is what you are getting at:

SELECT tblRec.LPSCID 
FROM tblRec 
LEFT JOIN tblSup 
ON tblRec.LPSCID = tblSup.SCID 
WHERE tblSub.SCID IS Null
GROUP BY tblRec.LPSCID 
ORDER BY tblRec.LPSCID;

This creates a join between the two tables, and lists all records in tblRec, whether or not they are listed in tblSup. However, by adding the WHERE Clause, it limits the list to only those records that are not found in tblSup.

I think I got the syntax right for this. Haven't tested it - just freehanding this here.

Hope this hepps!

[isladogs]

The simplest way to do this is by using the unmatched query wizard
The result will probably be something like

SELECT tblRec.LPSCID 
FROM tblRec 
LEFT JOIN tblSup 
ON tblRec.LPSCID = tblSup.SCID 
WHERE tblSub.SCID IS Null;

In other words, a simplified version of twinnyfo's code without the GROUP BY & ORDER BY clauses

OR possibly the other way round

SELECT tblSub.SCID
FROM tblSub 
LEFT JOIN tblRec
ON tblRec.LPSCID = tblSup.SCID 
WHERE tblRec.LPSCID IS Null;