I desperately need this concept to work! I have multiple test tables (tbl_Test1, tbl_Test2, tbl_Test3...) the primary key in all is an autonumber. The foreign key in each table is FK_Sample_ID_Code (which is not unique). What I have to do is to query each and every table and return ONE datasheet of all FK_Sample_ID_Code specific matches like "KP-05" AND the other fields composing the various tables. The first table tbl_Test1 may have many FK_Sample_ID_Code entries of "KP-05", but other tables could have a different number of "KP-05" codes foreign keys. For those elite who can make sense of my question -- is this possible? Is it the recommended and approved way?
What it could look like is below:
FK_Sample_ID_Code, Test1 , Units, Test2 , Units, Test3 , Units
KP-05 , 0.004 , % , 0.01 , % , 25 , ppm
KP-05 , 0.002 , % , 0.02 , % , 10 , ppm
Should I take another approach? Much gratitude for anyone who wishes to tackle this one.