Return values from table that are not present in another table

[MilaK]

I’m trying to return values from table “tbl_Horizon200” that are not present in another larger table “tblVariants”. Instead of evaluating all of the records I would like to evaluate rows that meet additional criteria: tblVariants.Sample_Type = "HD200_QC"AND “tblVariants.Run_Name” matches the value of a text box on a form.Here is the query that I’ve tried, however, it’s not correct.

 SELECT tbl_Horizon200.Gene, tbl_Horizon200.AA_change, tbl_Horizon200.[Standard Value], *
 FROM tbl_Horizon200
 WHERE (((Exists (SELECT * FROM tblVariants     WHERE  tbl_Horizon200.AA_change = tblVariants.AA_change AND tblVariants.Sample_Type = "HD200_QC"  AND [Forms]![frmReview_One]![txtCurrentRun]))=False));

Please help to revise the query. Thanks

 

[Ranman256]

You can join the 2 tables together and do an OUTER join.

 

[MilaK]

Ok, but how should I change the query? I got the same results when I changed the Join type. Thanks

 

[MilaK]

I'm closer but still can't figure out how to indicate the name of the tblVariants.Run_Name in the query, since that field is only present in tblVariants and I'm displaying records from tbl_Horizon200 that are not in tblVariants. The name of the Run is stored in a textbox on a form and only need to return records that are not present tblVariants for that specific run.

Can some here please suggest how to make this work?

SELECT tbl_Horizon200.AA_change, tbl_Horizon200.Frequency, tbl_Horizon200.Sample_Type
FROM tbl_Horizon200 LEFT JOIN tblVariants ON (tbl_Horizon200.[Sample_Type] = tblVariants.[Sample_Type]) AND (tbl_Horizon200.[AA_change] = tblVariants.[AA_change])
WHERE (((tblVariants.AA_change) Is Null));

Thanks

 

[sneuberg]

I suggest creating a separate query to select the records in tblVariants that have the Run_Name in the textbox something like:

SELECT tblVariants.Sample_Type, tblVariants.AA_change
FROM tblVariants
WHERE (((tblVariants.Run_Name)=[forms]![FormNameWithRunName]![txtRunName]));

Where you would need to change FormNameWithRunName and txtRunName to the names of your form and textbox. Then use this query in place of tblVariants in the query you posted. The reason why I'm suggest to make this a separate query is that criteria in a left join can turn it in into a natural join.