Camparing 2 Spreadsheets PLEASE HELP

[MarkNie]

Hi

I was wondering if anybody can point me in the right direction I have been messing around with this for a couple of days now and not getting it right. Maybe just need a fresh eye.

Well What I am trying to do is compare 2 excel spreadsheets which get imported into my db. So there are 2 tables as follows:

1. tblBBM (the fields are as follows)

(ACCT NO.)(ACCOUNT TITLE)(LEGAL ENTITY)(REPORT)(#COPIES)
130434 National Tr Management reporting 2
130434 National Tr Management reporting 1
130563 Japan Port Management reporting 2
130717 International Management reporting 3
130723 London Life Management reporting 1

2. tblBNY (the fields are as follows)

(ActCd) (ActNm) (LglEnt) (RptNm) (WebRptNm) (Copies)
130434 NTEF/B Managem Report Package 1
130434 NTEF/B Managem Report Package 1
130563 UEB/JP Managem Report Package 1
130563 UEB/JP Managem Report Package 2
130607 CFORTE Managem Report Package 2
130715 FAMIL Managem Report Package 1
130717 EQINT Managem Report Package 1
130717 EQINT Managem Report Package 3

What I want to accomplish is to show all the reports that macth and the ones that don't match.

The following fields are the ones that have to be matched:

(ACCT NO.) & (ActCd)
(#COPIES) & (Copies)

The end result must be a spreadsheet which has all the accounts that match and 2 others which don't match one for the first table and one for the second.

The problem I am coming across is that in tblBBM the first entry 130434 is in twice but the copies are 2.in one and 1 in the other In the tblBNY there are 2 130434’s and each one has 1 copy. What I want to show if this accures is.


Combined:

130434 National Tr Management reporting 1
130434 National Tr Management reporting 1

tblBBM unmatched:

130434 National Tr Management reporting 1

tblBNY unmatched:

130434 NTEF/B Managem Report Package 1

I hope this makes sense.

Been playing around with duplicate counting and then trying to combine it but nothing has worked so far. If anybody has any ideas on this please could you lend a hand.

Thanks
Regards
Mark

 

[IT4EVR]

I think you have the wrong idea of what an unmatched query is. It will show those records that are in table a but not in table b. The 130434 record with one copy is in both tables, so they would not be unmatched.

These are the three queries I ran:
Combined

SELECT a.AccountNo, a.AccountTitle, a.LegalEntity, a.Report, a.Copies
FROM tblBBM AS a INNER JOIN tblBNY AS b ON (a.Copies = b.Copies) AND (a.AccountNo = b.ActCd);

the tblBBM Unmatched query

SELECT a.AccountNo, a.AccountTitle, a.LegalEntity, a.Report, a.Copies
FROM tblBBM AS a LEFT JOIN tblBNY AS b ON (a.AccountNo = b.ActCd) AND (a.Copies = b.Copies)
WHERE (((b.ActCd) Is Null));

the tblBNY Unmatched

SELECT a.ActCd, a.ActNum, a.LglEnt, a.RptNum, a.WebRptNum, a.Copies
FROM tblBNY AS a LEFT JOIN tblBBM AS b ON (a.Copies = b.Copies) AND (a.ActCd = b.AccountNo)
WHERE (((b.AccountNo) Is Null));