none
Finding Identical Records and Related Records in Access RRS feed

  • Question

  • I have 2 similiar table structures Rolodex and Specifications. Each structure has 3 tables - General, Press, and Sheet. General is the parent table, Press is a child of General, and Sheet is a child of Press.

    I need to find all records in the Rolodex!General table and related child tables that match a selected record Specifications!General table and related child tables. So the record count and data for each related table needs to match. There is 1 - 3 'Sheet' records for each 'Press' record and 1 - 2 'Press' records for each 'General' record.

    I was trying to do nested queries for the related records and 'WHERE [field] = value' for each field value that needs to match. Which I can get the first related record, but not the subsequent related records.

    I'm hoping someone has done something similar finding a parent record with matching related child records that would be willing to share how they did it.

    For example, the table below, the yellow cells indicate the fields that need to match. I would need to find matching records in the Rolodex!General table, that has the matching Press and Sheet records.

    General Table

    Press

    Sheet

    SpecID

    Binding

    FoldSize

    FoldType

    HoleSize

    NumHoles

    NumStaples

    SpecID

    TabNo

    Press

    SpecID

    TabNo

    Side1InkColors

    NumberSheets

    Side2InkColors

    PaperColor

    PaperStock2

    FinishedSheets

    FinishSize

    51145

    Staple

     

     

    5/16

    3

    2

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    51145

    1

    TR System

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    51145

    1

    Black

    1

     

    White

    Springhill Index

    1

    8.5 x 11

     

     

     

     

     

     

     

     

     

     

    51145

    1

     

    1

     

    White

    Springhill Index

    1

    8.5 x 11

     

     

     

     

     

     

     

    51145

    2

    TR System

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    51145

    2

    Black

    26

    Black

    White

    Docusource Bond

    26

    8.5 x 11

    Thanks,
    Trent

    Wednesday, May 15, 2019 8:05 PM

All replies

  • I have not done anything similar, so I'm only speculating.  But it seems to me that you may find it easier to work with the result sets from de-normalizing queries.  That is, have a query that joins the General, Press, and Sheet tables for Rolodex, and another that does the same for Specifications.  Then you could either have a query that joins those two queries on the comparison fields to find matching/not matching records, or else use a VBA procedure that opens recordsets on the two queries and loops through them for comparison.  There're a lot of details in this process that I've left out, because I'm only speculating so far, but it seems like one of these methods might work.

    It's not clear to me how Null fields are supposed to be compared; whether they are to be treated as values for comparison (such that Null = Null and Null <> non-Null, which is not normally the case) or as wild-cards (such that Null = any non-null).  If the former, you could deal with it by converting Nulls to zero-length text fields in your denormalizing queries.  If the latter, you might have to do that and then use extra comparison logic in the later step to match values with zero-length strings.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Thursday, May 16, 2019 12:21 PM