How do I make this super query work

[SemiAuto40]

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.

[June7]

Sounds like might be similar to my laboratory database structure. Depends why you have multiple test tables. Is the data captured different in each table? If the tables all have the same fields then should not be separate tables.

Do all the tables have all the Sample_ID_codes or is there a master table that has all possible Sample_ID_Codes as a primary key? This is the Login table in my database. It has general info about the sample. Then related test tables hold the results of each specific test. However, in my case the Sample_ID is also unique in each test table, meaning a one-to-one relationship between Login and each test table.

Do you want to provide project for analysis?

[SemiAuto40]

I would sure like to see your laboratory database project..... So to answer with details. I have multiple test tables because I have a hundred different tests. Any given sample only needs a few of the tests. If I tried to have all possible test results in only one test result table then there would be a whole lot of unused blanks in the database table. For this cause it made sense to me to segregate each test having it's own table and associated test result components. The Sample_ID_Code is unique in it's own table but can have many relative to the tests tables that it requires. What I am trying to do is gather from whichever tests tables that the Sample_ID_Code calls for and display all results on one form in a spreadsheet format.

[June7]

Link to old version of my project (keep meaning to replace but basic structure hasn't been changed) can be found in http://forums.aspfree.com/microsoft-...hs-445065.html

If you want to provide yours I will see what I can do with the query.

[SemiAuto40]

Until I make a link to my mini project let me restate the problem.

Master table has unique SampleIDNumber for each sample plus a non-unique SampleIDCode and other info. Table1 has certain test results and its own SampleIDNumber and SampleIDCode field with identical values to the Master table. More tables like Table2, Table3, Table4 also will have the SampleIDNumber and SampleIDCode fields in them.
PROBLEM: I need to be able to select a unique SampleIDNumber sample from the Master table, AND read it's SampleIDCode, THEN go out to all of the other tables (Table2, Table3, Table4 etc..) and get all of the matching SampleIDCode and result entries that may be in them. The final result would look like unique SampleIDNumbers left column, then right beside any and all of the sample results that may be found in Table2, Table3, Table4 etc.. where SampleIDNumber and SampleIDCode match the Master table.

Since Table1, Table2, Table3, Table4 etc... will have information for "other different" SampleIDCodes - I only want to see the info matching my SampleIDCode for the past week's entries. So I could have a "datasheet" format result of 20 unique samples all having the same SampleIDCode in common but there will be empty blanks in the datasheet for some tables.

I'm getting stomped on this.

[Jester0001]

SemiAuto40,

I'm not sure if this will help, but it may help in explaining your problem. It sounds to me like you are just looking for a query to pull information your tables onto a datasheet.

Based upon your description, I have created a simple database that may help us to arrive at a solution. Please take a look and let us know where things are right (what you are looking for), and where things are incorrect (what you are looking for).


DatabaseLabTest.zip

June 7,
Thank you for linking to your database. It took me a bit to figure out how to get it running (I think I had to open in developer mode), but it has given me some ideas to use in the database I am currently working on (minus the non-normalized storage of data, lol).