Access Query Help!!

[wheels979]

I have 2 tables. 1 table is a linked tabled from an oracle database. All three fields in both tables must be unique, I think you call it a "composite" key?

Anyways, my goal is to write a query that compares the two tables and shows any different rows between them eliminating the same rows.

Eventually, I want to update the data in Table B with new records I find in Table A. I've written the query at the bottom but it returns no records and I can not figure out why as I know there are differences. Any help would be greatly appreciated!

Table A
----------
field1
field2
field3

Table B - Linked table
----------
field1
field2
field3


SELECT tableA.field1, tableA.field2, tableA.field3
FROM tableA
LEFT JOIN
tableB ON tableA.field1=tableB.field1
AND tableA.field2=tableB.field2
AND tableA.field3=tableB.field3
WHERE
(((tableB.field1) Is Null))
OR (((tableB.field2) Is Null))
OR (((tableB.field3) Is Null));

 

[AlexCuse]

Maybe your missing records are actually in TableA?

Also, you can remove this part:

OR (((tableB.field2) Is Null)) 
OR (((tableB.field3) Is Null));

The join is only performed if all conditions are true (because you are specifying AND), so if there is not a match anywhere Field1 in TableB will always be null.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[wheels979]

Alex,

Are you proposing that I JOIN on Table A instead of Table B?

I'm confused as to why I wouldn't need all three OR clauses in the join as it's the combination of the three fields that indicate a different row. If I just kept (((tableB.field1) Is Null)) would that account for this scenario?

i.e. -

Table A
--------
A
B
C

Table B
---------
A
C
B

 

[PHV]

Have a look here:

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OhioSteve]

Let's use a specific example to work through this. Consider these two tables-

table1
MODEL/YEAR/COLOR
Corvette/1963/red
Mustang/1965/red
MGB/1970/green

table2
MODEL/YEAR/COLOR
Corvette/1963/red
Mustang/1965/blue
RX-7/1985/white

Let's say that table1 is your Oracle table and table2 is the other data. What output would you want? I suspect that you would want this output:

MODEL/YEAR/COLOR
Mustang/1965/red
MGB/1970/green
Mustang/1965/blue
RX-7/1985/white

If that's what you want, I suspect that you need three queries:

*Name= In1NotIn2*
SELECT table1.model,
table1.year,
table1.color
FROM table1
LEFT JOIN table2 ON
(table1.color = table2.color) AND
(table1.year = table2.year) AND
(table1.model = table2.model)
WHERE (((table2.model) Is Null));


*Name= In2NotIn1*
SELECT table2.model AS modelb,
table2.year AS yearb,
table2.color AS colorb
FROM table2 LEFT JOIN table1 ON (table2.color = table1.color)
AND (table2.year = table1.year)
AND (table2.model = table1.model)
WHERE (((table1.model) Is Null));


*Name= finalProduct*
select model, year, color from In1NotIn2
UNION ALL select modelb, yearb, colorb from In2NotIn1;