Extra rows...

[robsimons]

Hello guys,

I'm looking for a solution for a small problem...

I have DB with 2 table... same structure in both tables... table1 is last month and table2 is this month. Currently I do not have either table setup with a PK; the "Row" below is just so you can see how the data is organized.

Tha data will basically looks like this...

TBL1 ...............TBL2
RW SSN Name.. SSN NAME
1 ...12 john .....12 john
2 ...12 john .....12 john
3 ...14 mary ....12 john
4 ...14 mary ....12 john
5 ...16 smith ....14 mary
6 ...................16 smith
7 ...................17 ronald

In my mind I am looking for a piece of code or SQL that will count SSN's (group by SSN's) from TBL1 and compare that to the count of SSN's (grouped by SSN's) from TBL2 if the TBL1.count.ssn != TBL2.count.ssn then copy(or move) the "Extra" rows to another table (or report)?

So for the above example TBL1.count.12(ssn) = 2 and TBL2.count.12 = 4 since the 2 != 4 (how do you like that??? my math is getting better:rotlaugh:), I would like RW=3&4 copied(moved) to a new table.

I already have piece of SQL to find the unique(New) rows in TBL2. This example SSN = 17.

Any questions?

Thanks

[OBP]

Yes I have a question, why have you got 2 tables, it is not good database design to repeat data like SSNs and Names & Addresses?

[robsimons]

Well it's really NOT a DB... Orignally the two table are two txt files that are generated each month. We created two tables because in actuality they want to know all the differences between the two file down to the cell level... However this is not practical with 3000+ rows and 10+ columns each month (and every cell can change each month).

I was trying to break it down into pieces...
1. Find the New People in TBL2
2. Find new Rows in TBL2.

The assumption here is that the rows that are equal between tables have not changed. Not great but it gets you from about 36000 changes down to maybe 1000 (managable).

Is that a little better background?