which join?

[Justin]

Hello,
I have a scenario where I have unique identifiers in about 25 tables,
each table has varying fields - eg.

Table #1 Table #2 Table #3 Table #4
--------- -------- -------- --------
sample_no sample_no sample_no sample_no
coords test1 test2 test3
desc desc desc notes
absorb diff

The 25 tables correspond to different chemical tests that are run on
each sample. Each test has its own "fields", and hence its own table.

Now, I want to query the tables where the sample number is 31601 and
let's say that sample_no exists in all but table #3. So essentially, I
wish to query every table and extract everything from those table(s)
which have data on sample_no 31601. It turns out that when I query
using a straight select statement, it returns nothing if one of the
tables doesn't have that sample_no(31601). If I remove the table which
is does not have sample_no = 31601, it works just fine. I have
surmised this is due to the type of JOIN that MS-Access uses in its
query builder.

Not sure where to go from here...
Is there any way can I get around this?
Thanks in advance!
jkm

[Allen Browne]

Hi Justin

The best structure might be to use one Test table that contains the basic
fields such as:
TestID AutoNumber
TestDate Date/Time
LabID Foreign key indicating which lab (or staff) did the test
and so on. Any field that is common to most tests could go into this table.

If a test has fields that are unique to it, such as a series of steps or a
series of results, it may be possible to use a TestDetail table, and create
one record for each step/result. In this case you end up with a very simple
and easy to query pair of tables.

If that is not practical, you may need several related tables as you
currently have, but each one will have the TestID as a foreign key. Or
perhaps your "sample_no" is actually a foreign key like that? If so, then
you need to use an outer join from the main Test/Sample table to each of the
other tables. In query design view, double-click the line joining the Test
table to your Table2. Access offers 3 choices. Choose the one that says,
"All records from Test table, and ...".

It is fairly easy to end up with "ambiguous outer join" problems. But if you
end up with the arrowheads all pointing away from the Test table, it should
work.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Justin" <jm***@bud.ca> wrote in message
news:34**************************@posting.google.c om...

I have a scenario where I have unique identifiers in about 25 tables,
each table has varying fields - eg.

Table #1 Table #2 Table #3 Table #4
--------- -------- -------- --------
sample_no sample_no sample_no sample_no
coords test1 test2 test3
desc desc desc notes
absorb diff

The 25 tables correspond to different chemical tests that are run on
each sample. Each test has its own "fields", and hence its own table.

Now, I want to query the tables where the sample number is 31601 and
let's say that sample_no exists in all but table #3. So essentially, I
wish to query every table and extract everything from those table(s)
which have data on sample_no 31601. It turns out that when I query
using a straight select statement, it returns nothing if one of the
tables doesn't have that sample_no(31601). If I remove the table which
is does not have sample_no = 31601, it works just fine. I have
surmised this is due to the type of JOIN that MS-Access uses in its
query builder.

Not sure where to go from here...
Is there any way can I get around this?
Thanks in advance!
jkm