Hi friends

i have two Ms access database files with same structure and same data

Example

File 1

Row (Key) SSN Tax Salary Gross
1 3 6 7 9
2 4 6 8 10
Missing record---------------------


File2
Row (Key) SSN Tax Salary Gross
1 3 5 7 9
2 4 6 8 10
3 11 12 13 14

Note : In file1 first row the value of tax are different

My SQL query is
--------------------------------------------------------------------
SELECT
File2.row, File2.SSN, File2.QtrTxbl, File2.QtrTxbl, File2.YtdTxbl, File2.QtrAmt, File2.YtdAmt, File2.QtrGross, File2.YtdGross

FROM File2 LEFT JOIN File1 ON File2.SSN=File1.SSN

WHERE (((File1.SSN) Is Null));

--------------------------------------------------------------------

I want Qury to return only 3rd record in file2 as that one is Null in file1 (missing in file1 but present in file2)

The problem is its retrieving first row and the missing record too

Can anyone help me with this?


Thanks and Regards
Lawson, COBOL

__________________
Thanks and Regards
Lawson, COBOL

Hi lawsoncobol

Try using the 'Find unmatch query wizard'.

SQL
------------------------------------------------
SELECT [Copy Of Table1].id, [Copy Of Table1].Q1, [Copy Of Table1].Q1A, [Copy Of Table1].Q1B, [Copy Of Table1].Q2, [Copy Of Table1].Q3, [Copy Of Table1].Q4, [Copy Of Table1].Q5, [Copy Of Table1].Q6, [Copy Of Table1].Q7, [Copy Of Table1].Q7A, [Copy Of Table1].Q7B, [Copy Of Table1].Q8, [Copy Of Table1].Q8A, [Copy Of Table1].Q9, [Copy Of Table1].Q9A, [Copy Of Table1].Q9B, [Copy Of Table1].Q10
FROM [Copy Of Table1] LEFT JOIN Table1 ON [Copy Of Table1].id = Table1.id
WHERE (((Table1.id) Is Null));
-----------------------------------------------------
The sql code that I have pasted her was between 'Table1' and 'Copy Of Table1'. If this don't work please let me know then we can try something else.

Cheers
Hennie

Hello Hennie

My SQL code
SELECT
File2.row, File2.SSN, File2.QtrTxbl, File2.QtrTxbl, File2.YtdTxbl, File2.QtrAmt, File2.YtdAmt, File2.QtrGross, File2.YtdGross
FROM File2 LEFT JOIN File1 ON File2.SSN=File1.SSN
WHERE (((File1.SSN) Is Null));

AND your SQL code

SELECT [Copy Of Table1].id, [Copy Of Table1].Q1, [Copy Of Table1].Q1A, [Copy Of Table1].Q1B, [Copy Of Table1].Q2, [Copy Of Table1].Q3, [Copy Of Table1].Q4, [Copy Of Table1].Q5, [Copy Of Table1].Q6, [Copy Of Table1].Q7, [Copy Of Table1].Q7A, [Copy Of Table1].Q7B, [Copy Of Table1].Q8, [Copy Of Table1].Q8A, [Copy Of Table1].Q9, [Copy Of Table1].Q9A, [Copy Of Table1].Q9B, [Copy Of Table1].Q10
FROM [Copy Of Table1] LEFT JOIN Table1 ON [Copy Of Table1].id = Table1.id
WHERE (((Table1.id) Is Null));

BOTH ARE ACTUALLY SAME TRY RELACE ALL WORDS Copy Of Table1 with File2 and TABLE1 = file1 and ID with SSN
and it becomes my code

Anyway i tried putting [] as u did but nothing worked


Thanks and Regards
Lawson, COBOL

Hi Lawson

I create DB with your field name and data that you have supplied me with. I have picked up date you have a few other field name in your table, you can just add those ones to this code. I have use the sql below and it works. If this doesn't work, feel free to drop me a mail.

----------------------------------------
SELECT
TBL2.Row, TBL2.SSN, TBL2.Salary, TBL2.Gross
FROM TBL2 LEFT JOIN TBL1 ON TBL2.SSN = TBL1.SSN
WHERE (((TBL1.SSN) Is Null));

Just change your WHERE clause to:

WHERE (((File1.SSN) Is Null) OR (File1.Tax <> File2.Tax) OR (File1.Salary <> File2.Salary) OR (File1.Gross <> File2.Gross));


-Phil-

hi phil and hennie,
 thans for the effort it works fine now.

Thanks and Regards
Lawson, COBOL