Second Post; Eliminating Related Records in a One-Many Table Pair

  • Thread starter Thread starter Kahuna
  • Start date Start date
K

Kahuna

I hope this makes more sense than my last post but its still seems like a
difficult one to get my head around in terms of logic!



I have two tables with a 'one to many' relationship and a form.



The 'one' Table is tblFilter and has an autonumber field.



The 'many' table is tblAnomaly, with a LongInteger as the link field and
fldRepNo and fldRefNo.



The form is frmAddAnomaly, with two fields fldRepNo and fldRefNo



My task is to create a query that lists only those tblFilter records which
do not have an associated tblAnomaly record.



So. if tblFilter has 10 tblAnomaly records associated, and any one of those
10 has fldRepNo and fldRefNo as shown on the frmAddAnomaly form then those
tblFilter records should NOT be listed.



Should be easy but my efforts so far have raised every record in tblFilter
where there is a tblAnomaly record that does not have fldRepNo and fldRefNo.
So I end up adding multiple records.



Any help with this logic would be gratefully appreciated.
 
Kahuna,

You didn't give us the name of the autonum field in tblFilter or
identify which field in tblAnomoly which corresponds to that field, so
I'll assume it is FilterID and fldRefNo.

To get all the records from tblFilter that don't have matching records
in tblAnomoly:

SELECT *
FROM tblFilter F
LEFT JOIN tblAnomoly A
ON F.FilterID = A.fldRefNo
WHERE A.fldRefNo IS NULL

You could also use the following, but I've found that the first method
is generally faster.

SELECT *
FROM tblFilter F
WHERE F.FilterID NOT IN (SELECT DISTINCT fldRefNo FROM tblAnomoly)


--
HTH

Dale Fye


I hope this makes more sense than my last post but its still seems
like a
difficult one to get my head around in terms of logic!



I have two tables with a 'one to many' relationship and a form.



The 'one' Table is tblFilter and has an autonumber field.



The 'many' table is tblAnomaly, with a LongInteger as the link field
and
fldRepNo and fldRefNo.



The form is frmAddAnomaly, with two fields fldRepNo and fldRefNo



My task is to create a query that lists only those tblFilter records
which
do not have an associated tblAnomaly record.



So. if tblFilter has 10 tblAnomaly records associated, and any one of
those
10 has fldRepNo and fldRefNo as shown on the frmAddAnomaly form then
those
tblFilter records should NOT be listed.



Should be easy but my efforts so far have raised every record in
tblFilter
where there is a tblAnomaly record that does not have fldRepNo and
fldRefNo.
So I end up adding multiple records.



Any help with this logic would be gratefully appreciated.
 
Dale the tblFilter Autonumber is fldID

and in tblAnomaly its fldIDLinkNo

Thanks for the feedback - ill try both those asap.

Cheers
 
Back
Top