Table "A" is a required list of items with a field called "WUC" and fields with each type of equipment that has how many are required. Table "B" is list of equipment with how many WUCs are actually installed. I can get a returen of items with greater than or less than required but I am having trouble getting a list of missing items. I have tried the Unmatched query and does not work for this. Is there a way to return the list if table B doesn't have a wuc from table A?

Sounds like you want records from table B where the table B "wuc" is not found in table A ?

SELECT tblB.WUC, tblA.WUC
FROM tblB LEFT JOIN tblA ON tblB.WUC = tblA.WUC
WHERE (((tblA.WUC) Is Null));

shoud do it.

RichM


[This message has been edited by RichMorrison (edited 05-01-2002).]

Thanks RichM, but I've been there. It does not return what I'm looking for. I will try to explain better.

Table A
wuc................required
yyyyy..................2
zzzzz..................3
wwwww..................1

Table B
wuc......installed.......enditem
yyyyy.........2............aaaaa
zzzzz.........3............aaaaa

I am trying to get this return:

enditem.....wuc.....required.....installed
aaaaa......wwwww.......1.............0


Anyone??

Thanks


[This message has been edited by RichB (edited 05-02-2002).]

I had a similar problem

see my post and the solution


good luck

my post
Hello, Please can anyone help


I am trying to compare data from 2 tables that should have the same information, and filter out the differences.
After running a “Unmatched Query” I am only left with unmatched results from the first fields i.e. (DJKL) from Table1, (DU1G) from Table 2,and(DHJ2)from both tables is eliminated because all fields are equal.

The problem is that each example has 4 fields that have to be equal and it is not picking up entries that has the same first Field but different amounts in field 2,3 and 4.

e.g.

TABLE 1
Job No B B1 B2
DHJ2 2 6 13
DJKL 5 1 6
DLE4 2 8 9

TABLE 2
Job No B B1 B2
DHJ2 2 6 13
DU1G 12 3 18
DLE4 7 5 4

The result that I am looking for after running the query is:

TABLE 1
Job No B B1 B2
DJKL 5 1 6
DLE4 2 8 9

TABLE 2
Job No B B1 B2
DU1G 12 3 18
DLE4 7 5 4


Any Ideas?

Thank you
Groundrush
Junior Member

[This message has been edited by Groundsrush (edited 04-09-2002).]

[This message has been edited by Groundsrush (edited 04-09-2002).]


RV
Member posted 04-10-2002 11:47 PM
--------------------------------------------------------------------------------
Try this:
SELECT DISTINCTROW "Table1", tblJob1.Job, tblJob1.B, tblJob1.B1, tblJob1.B2
FROM tblJob1 LEFT JOIN tblJob2 ON (tblJob1.B2 = tblJob2.B2) AND (tblJob1.B1 = tblJob2.B1) AND (tblJob1.B = tblJob2.B) AND (tblJob1.Job = tblJob2.Job)
WHERE (((tblJob2.Job) Is Null))
UNION
SELECT DISTINCTROW "Table2", tblJob2.Job, tblJob2.B, tblJob2.B1, tblJob2.B2
FROM tblJob2 LEFT JOIN tblJob1 ON (tblJob2.B2 = tblJob1.B2) AND (tblJob2.B1 = tblJob1.B1) AND (tblJob2.B = tblJob1.B) AND (tblJob2.Job = tblJob1.Job)
WHERE (((tblJob1.Job) Is Null));

Suc6,

Thanks,

I will give it a try.