Locating records that DONT match!

[Kahuna]

Hi Folks, strange logic problem this that I cant seem to quite grasp. If my
explanation is a bit weird that's why!

My intent is to append records to a tblAnomaly table where there is not a
current record listed.

I have a table tblFilter with an autonumber field fld_id. This table links
on this field to another table tblAnomaly with a one (tblFilter) to many
(tblAnomaly) join.

tblFilter (one)
fld_id

tblAnomaly (many)
fld_id
fld_rep_no
fld_ref_no

I need to list all the records in the tblFilter that does not have a
corresponding tblAnomaly entry with a specified fld_rep_no and fld_ref_no.

My problem to date has been that I can filter out the tblAnomaly records
with the specified entries but that still leaves the entries with the same
fld_id but with dissimilar fld_rep_no and fld_ref_no entries.

I am finding it difficult to even express this linguistically! Any help
appreciated - or maybe some pertinent questions can help to unravel the
logic here.

Cheers

 

[Tom Ellison]

Dear Kahuna:

A direct and reasonably obvious way to write a query that does this
is:

SELECT F.*
FROM tblFilter F
WHERE NOT EXISTS(SELECT * FROM tblAnomaly A
WHERE A.fld_id = F.fld_id
AND A.fld_rep_no <> F.fld_rep_no
AND A.fld_ref_no <> F.fld_ref_no)

If you're running the Jet database, this may be a bit slow. An
alternative that would probably peform better:

SELECT F.*
FROM tblFilter F
LEFT JOIN (SELECT * FROM tblAnomaly A1
WHERE A1.fld_rep_no <> F.fld_rep_no
AND A1.fld_ref_no <> F.Fld_ref_no) A ON A.fld_id = F.fld_id
WHERE A.fld_id IS NULL

The above assumes I've understood your requirement, of which you
didn't seem so sure yourself.

Hi Folks, strange logic problem this that I cant seem to quite grasp. If my
explanation is a bit weird that's why!

My intent is to append records to a tblAnomaly table where there is not a
current record listed.

I have a table tblFilter with an autonumber field fld_id. This table links
on this field to another table tblAnomaly with a one (tblFilter) to many
(tblAnomaly) join.

tblFilter (one)
fld_id

tblAnomaly (many)
fld_id
fld_rep_no
fld_ref_no

I need to list all the records in the tblFilter that does not have a
corresponding tblAnomaly entry with a specified fld_rep_no and fld_ref_no.

My problem to date has been that I can filter out the tblAnomaly records
with the specified entries but that still leaves the entries with the same
fld_id but with dissimilar fld_rep_no and fld_ref_no entries.

I am finding it difficult to even express this linguistically! Any help
appreciated - or maybe some pertinent questions can help to unravel the
logic here.

Cheers

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Kahuna]

Let me try and explain again - perhaps I'll use English this time <lol>

tblFilter has an autonumber field

tblAnomaly has several fields one of which is a link longinterger on the
tblFilter autonumber.

For tblFilter there is one record fld_id = 10265

in tblAnomaly there are 33 records with the link field = 10265

One of these linked records may contain the fld_rep_no = AN123 and the
remainder may not.

my efforts so far have resulted in discounting the record with fld_rep_no =
AN123 but includes all other tblAnomaly records = 10265.

I believe your sql will result in the same??? I'll try it asap though.

Does this make more sense / clearer?

 

[Tom Ellison]

Dear Kahuna:

I cannot tell from your discription what you really WANT as far as
filtering fld_rep_no. Do you want to see one or more values of this
field? Do you want to exclude certain of them? Can you describe how
different value of fld_rep_no are to influence which rows are included
or excluded?

Let me try and explain again - perhaps I'll use English this time <lol>

tblFilter has an autonumber field

tblAnomaly has several fields one of which is a link longinterger on the
tblFilter autonumber.

For tblFilter there is one record fld_id = 10265

in tblAnomaly there are 33 records with the link field = 10265

One of these linked records may contain the fld_rep_no = AN123 and the
remainder may not.

my efforts so far have resulted in discounting the record with fld_rep_no =
AN123 but includes all other tblAnomaly records = 10265.

I believe your sql will result in the same??? I'll try it asap though.

Does this make more sense / clearer?

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Kahuna]

Tom, thanks for the response - I have posted again as your post just
arrived. I believe my explanation is a (little) more succinct in that second
post shown below
=========================================================================

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
does not have an associated tblAnomaly record.



So. if tblFilter has 10 tblAnomaly records associated, and any one of them
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 add multiple records.



Any help with this logic would be gratefully appreciated.