Comparing individual entries in Access tables

[Guest]

I have 2 tables (tblA abd tblB)
tblA has 3 fields (a1, a2, and a3). All are text fields.
tblB has one field (b1). Text field.
Both are tables listing vaious part numbers for an assembly (a collection of
parts that belong together)
tblA contains every part number and is segragated by something called "part
level" and hence the 3 fields.
tblB is supposed to contain all the part in tblA in a single field. But some
of the parts in tblB may be missing.
I need to find what parts are missing from tblB
The SQL statement I used was:
SELECT tblA.a1, tblA.a2, tblA.a3
FROM tblA
LEFT JOIN tblB ON
tblA.a1=tblB.b1
OR tblA.a2=tblB.b1
OR tblA.a3=tblB.b1
WHERE tblB.b1 Is Null;

This gives me expected results only where every part number in a row in tblA
is not in tblB.

If there is a single part number in tblA that is not in tblB, I do not get
the results I need (e.g., the part in row 3 for field a2 is not in tblB but
the parts in fields a1 and a3 for row 3 are in tblB)

Is there a way to compared individual entries in tblA to tblB?

 

[John Vinson]

On Tue, 9 Nov 2004 11:24:10 -0800, GreggK

Answers inline.

I have 2 tables (tblA abd tblB)
tblA has 3 fields (a1, a2, and a3). All are text fields.
tblB has one field (b1). Text field.

ONLY the one field? No information about b1 at all? Ok, just a bit
odd!

Both are tables listing vaious part numbers for an assembly (a collection of
parts that belong together)
tblA contains every part number and is segragated by something called "part
level" and hence the 3 fields.

What do you mean by "segregated"? Are you storing the "part level"
value in the field name? I.e. is A1 the level-1 part, A2 the level-2?
This could be a real problem.

tblB is supposed to contain all the part in tblA in a single field. But some
of the parts in tblB may be missing.
I need to find what parts are missing from tblB
The SQL statement I used was:
SELECT tblA.a1, tblA.a2, tblA.a3
FROM tblA
LEFT JOIN tblB ON
tblA.a1=tblB.b1
OR tblA.a2=tblB.b1
OR tblA.a3=tblB.b1
WHERE tblB.b1 Is Null;

If there is a single part number in tblA that is not in tblB, I do not get
the results I need (e.g., the part in row 3 for field a2 is not in tblB but
the parts in fields a1 and a3 for row 3 are in tblB)

Is there a way to compared individual entries in tblA to tblB?

You'll need to create a query joining TblB to tblA *THREE TIMES*, one
to each field:

SELECT tblA.a1, tblA.a2, tblA.a3
FROM ((tblA LEFT JOIN tblB AS X ON tblA.a1 = X.b1) LEFT JOIN tblB AS Y
ON tblA.a2 = Y.b1) LEFT JOIN tblB AS Z ON tblA.a3 = Z.b1)
WHERE X.B1 IS NULL OR Y.B1 IS NULL OR Z.B1 IS NULL;

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps