determine records between Dbs

[bknight]

I have two Dbs that are supposed to be equal in data, but not equal in results. In otherwords in both Dbs
Field0 is a Primary Key.
Field1 is a date.
Field2-5 are numbers.
Ifound last night that the number of records were not the same, but should be.
What is the best method to identify which records are missing from the smaller of the two? Secondly how would one determine if records in the larger one might be duplicates in Field1-5? There are almost 10K records in each.

[EileenStifone]

You must first say what the two Tables are called and what are the fields within them and which is the larger of the two.

[HansV]

Do you really mean two separate databases, or two tables in the same database?

[bknight]

2 separate Dbs.

[HansV]

I'd create a link in one of the databases to the table in the other one.
Then use the Find Unmatched Query Wizard to return the records in the larger of the two tables that don't have a matching Field0 in the smaller one.

[bknight]

I've never created a link between Dbs before tables, yes. The tables are identically name "tblMcClellan", the Dbs are named "McClell07" and "RAMcClell07". I suspect the second one is sort as the primary key is less than the firsts primary key. That being said I would link the second one to the first.

Got a link now how to design the unmatched records?

[bknight]

OK this is what I have and I get no records. The tblMcClellan1 is the bigger of the two, I believe.

Code: Select all

SELECT tblMcClellan.TradeDay, tblMcClellan.UStk, tblMcClellan.DStk, tblMcClellan.UVol, tblMcClellan.DVol
FROM tblMcClellan1 RIGHT JOIN tblMcClellan ON tblMcClellan1.TradeDay = tblMcClellan.TradeDay
WHERE (([tblMcClellan].[TradeDay]<>[tblMcClellan].[TradeDay])) OR (([tblMcClellan].[UStk]<>[tblMcClellan].[UStk])) OR (([tblMcClellan].[DStk]<>[tblMcClellan].[DStk])) OR (([tblMcClellan].[UVol]<>[tblMcClellan].[UVol])) OR (([tblMcClellan].[DVol]<>[tblMcClellan].[DVol]))
ORDER BY tblMcClellan.TradeDay;

[HansV]

If tblMcLellan1 is the larger one, try this for starters

SELECT tblMcClellan1.*
FROM tblMcClellan1 LEFT JOIN tblMcClellan ON tblMcClellan1.TradeDay = tblMcClellan.TradeDay
WHERE tblMcClellan.TradeDay Is Null
ORDER BY tblMcClellan1.TradeDay;

[bknight]

No records for that one either, perhaps I entered data in the larger one that was not accepted incrementing the ID, where as no errors in the "smaller".

Interesting, I deleted TradeDay is null and reran the query and come up with only ~7700 of the nearly 10K records.

[xps35]

Try

Code: Select all

SELECT Large.ID, Large.TradeDay, Large.UStk, Large.UVol, Large.DVol
FROM Large LEFT JOIN Small ON Large.ID = Small.ID
WHERE (((Small.ID) Is Null));

[xps35]

For your second question try

Code: Select all

SELECT Large.TradeDay, Large.UStk, Large.DStk, Large.UVol, Large.DVol, Count(Large.ID) AS Count
FROM Large
GROUP BY Large.TradeDay, Large.UStk, Large.DStk, Large.UVol, Large.DVol
HAVING (((Count(Large.ID))>1));

Just one more remark on the first question. The fact that one table is larger does not necessarily mean that all records from the small table are in the large one. Better check both ways.

[bknight]

For your second question try

Code: Select all

SELECT Large.TradeDay, Large.UStk, Large.DStk, Large.UVol, Large.DVol, Count(Large.ID) AS Count
FROM Large
GROUP BY Large.TradeDay, Large.UStk, Large.DStk, Large.UVol, Large.DVol
HAVING (((Count(Large.ID))>1));

Just one more remark on the first question. The fact that one table is larger does not necessarily mean that all records from the small table are in the large one. Better check both ways.

That is true and I should have remarked that I'm defining, at this point, the larger of the IDs since they increment with each record/error.

ETA: I get a syntax error on join operation

[xps35]

ETA: I get a syntax error on join operation

What is the error message?

[bknight]

ETA: I get a syntax error on join operation

What is the error message?

I forgot to image the error, sorry. But it was an error on the join operation with a couple extra words that I don't remember.

[bknight]

Oh man there was one duplicate in the larger Db, after deletion there were 9243 records. the smaller had no duplicates with 9241 records. When I did just a match to the two I came up with 9239. The problem is more nefarious than I originally believed. So what I'm going to do is to build a new table in the bigger Db using the earliest back up I have 4/20/15, then add records from the current Db>4/20/15. After this I will copy all the records o the smaller Db, which won't be smaller anymore.
Thanks to both of you for your suggestions.