determine records between Dbs
-
- BronzeLounger
- Posts: 1408
- Joined: 08 Jul 2016, 18:53
determine records between Dbs
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.
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.
-
- NewLounger
- Posts: 15
- Joined: 16 Aug 2022, 05:39
Re: determine records between Dbs
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.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: determine records between Dbs
Do you really mean two separate databases, or two tables in the same database?
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1408
- Joined: 08 Jul 2016, 18:53
Re: determine records between Dbs
2 separate Dbs.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: determine records between Dbs
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1408
- Joined: 08 Jul 2016, 18:53
Re: determine records between Dbs
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?
Got a link now how to design the unmatched records?
-
- BronzeLounger
- Posts: 1408
- Joined: 08 Jul 2016, 18:53
Re: determine records between Dbs
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;
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: determine records between Dbs
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;
SELECT tblMcClellan1.*
FROM tblMcClellan1 LEFT JOIN tblMcClellan ON tblMcClellan1.TradeDay = tblMcClellan.TradeDay
WHERE tblMcClellan.TradeDay Is Null
ORDER BY tblMcClellan1.TradeDay;
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1408
- Joined: 08 Jul 2016, 18:53
Re: determine records between Dbs
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.
Interesting, I deleted TradeDay is null and reran the query and come up with only ~7700 of the nearly 10K records.
-
- NewLounger
- Posts: 14
- Joined: 27 Sep 2022, 07:22
- Location: Schiedam, NL
Re: determine records between Dbs
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));
Groeten,
Peter
Peter
-
- NewLounger
- Posts: 14
- Joined: 27 Sep 2022, 07:22
- Location: Schiedam, NL
Re: determine records between Dbs
For your second question try
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.
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));
Groeten,
Peter
Peter
-
- BronzeLounger
- Posts: 1408
- Joined: 08 Jul 2016, 18:53
Re: determine records between Dbs
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.xps35 wrote: ↑02 Feb 2023, 15:39For your second question tryJust 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.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));
ETA: I get a syntax error on join operation
-
- NewLounger
- Posts: 14
- Joined: 27 Sep 2022, 07:22
- Location: Schiedam, NL
-
- BronzeLounger
- Posts: 1408
- Joined: 08 Jul 2016, 18:53
-
- BronzeLounger
- Posts: 1408
- Joined: 08 Jul 2016, 18:53
Re: determine records between Dbs
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.
Thanks to both of you for your suggestions.