Comparing two tables and finding the number of matching records

[warrior2009]

I have 2 lists (unlinked and unrelated), where one can be called the parent and the other the child with one to many relationship between parent-child. The child table is really big, over a million records. The link between the two, is a set of 3 fields that must be equal between the two. So when the two match, there can be a number of records in the child field, because it is related to the usage history of the parent record.

So here is my problem, I want to add a column in the parent field to correspond to the number of matching records in the child table for that particular record. I am guessing it should not too advanced... so but can anyone please help?

For. eg. i know ID 1 has 20 records matching in child table and so i would like to add 20 in the column for ID 1 in parent table. ID 2 has no records so 0 for that and so on...

[orangeCat]

@warrior2009
What have you tried so far?

[warrior2009]

Well anything and everything to do with design view of query building but so far no luck. I was hoping that Access would provide some functionality, afterall the idea doesnt sound too hard...

Now I am thinking maybe query may not do, and will need to create a VB script to loops through the entire parent table and create query condition for child table and store the total count of query in a variable and add it to that record. Unfortunately its been like 4 years since I did Access/VB stuff (infact any programming at all) :(

Any ideas?

[patjones]

Well, have you done anything in SQL view yet, in Query Design?

Suppose 'tblA' is the parent table, while 'tblB' is the child. As an intermediate step, you could create a table which lists the counts for all the id's:

SELECT * INTO tblTemp FROM (SELECT id, COUNT(*) AS id_count FROM tblB GROUP BY id);

The only thing is that this won't list id's that aren't in the table. For instance, if no entries have id = 5 then it won't appear in tblTemp. Next to update your new field in tblA which will hold the counts:

UPDATE tblA LEFT JOIN tblTemp ON tblA.id = tblTemp.id SET tblA.id_count = tblTemp.id_count;

Again, the entries having no match in the child table will be blank. This will fill those in:

UPDATE tblA SET id_count = 0 WHERE id_count Is Null;

This is a little roundabout, and I tried doing it in one statement using a correlated subquery, but Access didn't like it for some reason. But, try this and see if you get the results you expect.

[warrior2009]

Good idea zepphead80, this might work except that my tables are a bit more than going by one ID only. So here you showed me if I were to compare the ID between the two tables, but what if it is 3 fields that I need to compare between the tables? So e.g.

TblA has ID=1, Quality = X and Facility = A should correspond to the same record with the identical three fields? Sorry I should have mentioned that in my question. Because unfortunately same ID can have different other attributes which screws up the situation a little bit for me. So basically, need to find compare the two tables based on three fields instead of just one. Could you please tell me how to modify the queries in that case?

Thanks alot!

[orangeCat]

warrior2009,

I have mocked up your tables and suggest the following:
table_name field_name data_type length
ByteTbl Idx Long 4
ByteTbl ID Text 50
ByteTbl Quality Text 50
ByteTbl Facility Text 50
ByteTbl CntHistRecords Long 4

table_name field_name data_type length
ByteUseHist UsageId Long 4
ByteUseHist ID Text 50
ByteUseHist Quality Text 50
ByteUseHist Facility Text 50
ByteUseHist Usagedate Date 8
ByteUseHist Name Text 50

Create a new Table MyByte that contains the Counts for each ID+Quality+Faci lity

SELECT ByteTbl.ID, ByteTbl.Quality, ByteTbl.Facility, 
    Count(ByteUseHist.UsageId) AS CountOfUsageId  INTO MYByte 
FROM ByteTbl INNER JOIN ByteUseHist ON (ByteTbl.ID = ByteUseHist.ID)
    AND (ByteTbl.Facility = ByteUseHist.Facility) AND (ByteTbl.Quality = ByteUseHist.Quality)
    GROUP BY ByteTbl.ID, ByteTbl.Quality, ByteTbl.Facility 

Then, using your initial table and the new MyByte table, update the Count Field
using a query such as:

 UPDATE ByteTbl INNER JOIN MYByte ON (ByteTbl.ID = MYByte.ID) AND (ByteTbl.Quality = MYByte.Quality) AND (ByteTbl.Facility = MYByte.Facility) SET ByteTbl.CntHistRecords = MYByte!CountOfUsageId;

[orangeCat]

warrior2009,

Not sure what happened with my previous post. There was a final piece to place a 0 in the CntHistRecords field where no History usage records exist. To do this you would ruun an update query as follows.

UPDATE ByteTbl SET ByteTbl.CntHistRecords = 0
WHERE (((ByteTbl.CntHistRecords) Is Null));
 

Good luck.

[FishVal]

Hello gentlmen.

You should take care counting matching records via table join in a case when tables involved could themselves contain duplicate records.

e.g. Table1 which has 2 duplicate records and Table2 has 3 duplicate records which match those in Table1. Table join will give 6 combinations.

Regards,
Fish

[patjones]

Hi warrior -

Sorry about that, I think I was focusing too much on your example at the end of your initial post. I guess I would modify my initial statement (still keeping tblB as your child) as:

SELECT * INTO tblTemp FROM (SELECT id, quantity, facility, COUNT(*) AS usage_count FROM tblB GROUP BY id, quantity, facility);

Then to update the count column in the parent, tblA:

UPDATE tblA LEFT JOIN tblTemp ON (tblA.id = tblTemp.id AND tblA.quantity = tblTemp.quantity AND tblA.facility = tblTemp.facility) SET tblA.usage_count = tblTemp.usage_count;

And, as before, to capture the nulls:

UPDATE tblA SET usage_count = 0 WHERE usage_count Is Null;

Since, using this procedure, tblTemp will end up with one record each for each combination of id, quantity and facility that is in your child table, and also since your parent table has one record for each combination (based on your initial post), we should be able to avoid the situation that FishVal is referring to.

Notice that my UPDATE statement is similar to orangeCat's, although I'm trying to think about how it would turn out differently using an INNER as opposed to LEFT join. Nonetheless, our procedures are very similar; I believe you'll get the results you're looking for.

[warrior2009]

great!! Both works well! Thanks alot gents!