Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    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.....
    Last edited by warrior2009; 06-22-09 at 15:02.

  2. #2
    Join Date
    May 2009
    Posts
    258
    Hello warrior,

    The best thing to do would be to write this as a query, but not store the value as another field.

    With the parent table being ParentTable, the child table being ChildTable, and the matching fields being MatchCol[1,2,3], the following SQL may help you:
    Code:
    SELECT A.*, 
    (SELECT COUNT(*) FROM ChildTable
    WHERE MatchCol1 = A.MatchCol1
    AND MatchCol2 = A.MatchCol2
    AND MatchCol3 = A.MatchCol3) AS ChildCount
    FROM ParentTable A
    Regards,

    Ax

  3. #3
    Join Date
    Jun 2009
    Posts
    2
    Thanks, but it didnt help .

    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...

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Basically what Ax supplied works.

    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:
    Code:
    SELECT COUNT(*) AS CNT
    FROM child-table
    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:

    Code:
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •