Have more records when doing a join query then what is in the table! (1 Viewer)

[Pari]

I really need some help here please so would be grateful if anyone has an idea as to what is going on!

I have 2 tables. One of the tables has 191 records and the other 221 records. The tables contain the same data and I need to know how to run a query to show which records are missing from the 191 table.

To acheive this I have added both tables to my query and then choose the join properties option and asked for the differnace to be displayed. However upon doing this, for some reason we end up with more records!

Does anyone have a clue as to why this would be the case ?

Regards. Pari.

 

[DCrake]

Try using the unmatched query wizard to find out

 

[Pari]

Thank you as always David for the prompt reply.

I have run the query both from the table 221 and then from the table 191. On both occassions I ended up with different results, quite correctly but they do not equal the total differnace, i.e, 221 - 191.

If it helps I have attached both tables.

Thanks and kind regards. Pari.

 

[DCrake]

Without checking you may have

Records in A that do not exist in B
Records in B that do not exist in A
Records that exist in both A and B
Therefore it is quite conceavable that you get different results.


What answers do you get?

 

[Pari]

Hi Dave,

For the first run I get 3 and the second run I get 6, which does not tally up at all.

I personally think that all the records in 221 should be in 191 but with an extra 30.

Regards. Pari.

 

[wilpeter]

Some of the records are not formatted as Text, some not as Numbers. Make all Permits the same format. Try doing an alpha sort and you'll see.

 

[Pari]

Thanks Wilpeter,

Can you please advise on how to actualyl do an aplha sort as I have checke both tables in design view and they are showing as text.

Thanks. Pari.

 

[wilpeter]

On second look, I may be mistaken. 221 has 0.003 as first item after sort; 191 has a blank permit# first after sort. To sort, I used table datasheet view and used AZ to view the sorted result (by Permit No), then didn't save result.

 

[Pari]

Thank you,

If you do an unmatched query on the 2 tables, selecting 221 as the first table, then it should come up with 30 results, as this is the differance between the 2 tables.

As this is not the case please could you advise on the way to locate the total missing records from 191, i.e, 30 records.

Also it would be really helpful if anyone could advise why when we do a join query we end up with a lot more records than in both tables.

Thanks and kind regards. Pari.

 

[dkinley]

Just as an aside and to add some more confusion, you might have a 'black hole' going on.

For instance Table1 and Table 2 each have 100 records. However, matching from Table 1 -> Table 2 shows 20 unmatched and from Table 2 -> Table 1 shows 15 unmatched.

The problem is that each might have records totally unrelated to the other but yet the coincidence in the error shows each table to have the same amount of records.

I run into this from having indepedant orphaned records that should have a correlation (users delete from each table but not both --- not my system or I would have cascaded the deletes and can't access to change).

This will leave you with a 'black hole' of integrity since you can't verify data from table one or the other.

Good luck,

-dK

 

[Pari]

Thanks for the reply DK,

However I can confirm we have not deleted any data.

Regards. Pari.

 

[wilpeter]

1186200 1010124 (twice) = 3 in 191 that don't match.
1186281, 0.003, 1186268, 1119755, 1119755 (again), and 1185707 don't match in 221 table.
Method: First do a matched query of 191 and 221, joined by permit#...result is 118 records that are in both. Then use that query result to perform an Unmatched query against the 191 (3 are unmatched) then another Unmatched query against 221 (6 are unmatched). Determine if those 9 are legitimate (presume the duplicates are not permitted) and add them to the opposite table.

 

[wilpeter]

Sorry, forgot the important ending...
After fixing the 191 table with those found in 221; and the reverse; and eliminating the duplicates and the one with no permit number...
Do a Duplicates query on the 221 table. There are 4 #1107970 for example.