Mismatch count between two tables (1 Viewer)

Joy83

Member
Local time
Today, 01:19
Joined
Jan 9, 2020
Messages
116
Hi
I need your help in building a query
I have (table1) that consists of Product number and status
Another table (table2)
That has the same information
I want to find the count of product ID that are status (open) from each table
And show the open product ID in table1 that are not matching the count in tabel2
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:19
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure I understand what "count" has to do with your question; but in the end, I believe you could use the Find Unmatched Query Wizard to get the final result you're after.
 

Joy83

Member
Local time
Today, 01:19
Joined
Jan 9, 2020
Messages
116
If I have product ID 222
222
222
Then I have a count of 3
I am expecting to find the same count in the other table
But I am just counting specific status
 

theDBguy

I’m here to help
Staff member
Local time
Today, 01:19
Joined
Oct 29, 2018
Messages
21,358
If I have product ID 222
222
222
Then I have a count of 3
I am expecting to find the same count in the other table
But I am just counting specific status
Okay, you can create two separate queries showing the count, one for each table. And then, use the query wizard I mentioned to find the no matches. For example,
SQL:
SELECT ID, Count(*) AS Total FROM TableA WHERE Status="something" GROUP BY ID

Edit: Typo.
 
Last edited:

plog

Banishment Pending
Local time
Today, 03:19
Joined
May 11, 2011
Messages
11,613
I want to find the count of product ID that are status (open) from each table
And show the open product ID in table1 that are not matching the count in tabel2

2 queries--one for each table

Code:
SELECT ID, COUNT(ID) AS OpenCount FROM Table1 WHERE Status="Open"

SELECT ID, COUNT(ID) AS OpenCount FROM Table2 WHERE Status="Open"

Name them sub1 and sub2 respectively, then make a third query to compare:

Code:
SELECT sub1.ID, sub1.OpenCount AS Count1, sub2.OpenCount AS Count2
FROM sub1
INNER JOIN sub2 on sub1.ID=sub2.ID
WHERE sub1.OpenCount<>sub2.OpenCount

That just finds count differences, it does not account for no matching records at all in Table2. I would make a 4th query to find those:

Code:
SELECT sub1.ID, sub1.OpenCount AS Count1, sub2.OpenCount AS Count2
FROM sub1
LEFT JOIN sub2 on sub1.ID=sub2.ID
WHERE sub2.OpenCount IS NULL

Then possibly a 5th query based on the SQL of the above to find records in Table2 without records in Table1
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:19
Joined
Feb 19, 2002
Messages
42,981
Code:
qry1:
SELECT ID, STATUS, COUNT(ID) AS OpenCount FROM Table1
qry2:
SELECT ID, STATUS, COUNT(ID) AS OpenCount FROM Table2
qry3:
Select *
From qry1 Left Join qry2 on qry1.ID = qry2.ID AND qry1.Status = qry2.Status
qry4:
Select *
From qry1 Right Join qry2 on qry1.ID = qry2.ID AND qry1.Status = qry2.Status
qry5:
Select * from qry4
Union Select * from qry5

You never want to hard code things like Status values. That means you need to run a query for each status. The qry1 and qry2 give you counts for all status'. qry3 and qry4 do left and right joins so you can find all the rows that match PLUS the rows from each side that don't match. The final query is qry5 (and that is the one you run) which gives you one set of records with a,b, a,null, null,b.
I do it this way because it is much easier to test. I'm sure you can mush it all together but keep in mind that Jet/ACE do not optimize subqueries efficiently and so if you have large tables a query with subqueries could be slower than just running qry5.
 

Joy83

Member
Local time
Today, 01:19
Joined
Jan 9, 2020
Messages
116
For some reason it’s not working

However I managed to put the count from eqch table in the one query
I just need to add one field and creat if statements of the difference maybe
Is it possible
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:19
Joined
Feb 19, 2002
Messages
42,981
We can't see what you've done. We don't know what "not working" means. You need to post the query and tell us what "not working" means. Are you getting an error? Are you getting extra records? Are you missing records?
 

Joy83

Member
Local time
Today, 01:19
Joined
Jan 9, 2020
Messages
116
We can't see what you've done. We don't know what "not working" means. You need to post the query and tell us what "not working" means. Are you getting an error? Are you getting extra records? Are you missing records?
I tried yours
And its working fine
Thanks all
 

Users who are viewing this thread

Top Bottom