Unanswered: Comparing two tables and finding the number of matching records
I have 2 lists (really screwd up db set up), 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 long, 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 trying to work with the Select query in Access, but i guess thats not going to give it and must write some SQL statement. 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...
I am thinking first I have to select the matching records and then do a count on the matching records in the child table.....
I am thinking that it might not be a query that will accomplish this but a VB script to create and execute a query for each record in parent table and update the parent table's column... FML!
Can anyone please tell me how to create one query statement for one particular record? So for e.g. ID 1 in parent table, create query to find all matching enteries for that ID in child table, Count it and store that in some variable and then loop through the whole parent table...
But a more important question is why you want to store the information. Generally, you don't want to store derived information in a table (as long as it is easy/fast/cost-effective to derive it again).
If you ever need the number of rows a certain key value has in the child table a simple:
SELECT COUNT(*) AS CNT
WHERE KEYCOL1 = value
AND KEYCOL2 = value
AND KEYCOL3 = value
If you have to store (and maintain the correct values as rows are added/deleted), then:
UPDATE parent-table PT
SET count-col = (SELECT COUNT(*)
FROM child-table CT
WHERE PT.KEYCOL1 = CT.KEYCOL1
AND PT.KEYCOL2 = CT.KEYCOL2
AND PT.KEYCOL3 = CT.KEYCOL3
works in the databases I deal with.
If it doesn't can you post the errors you are getting?