combining tables and linking records with queries

  • Thread starter Thread starter Dale Peart
  • Start date Start date
D

Dale Peart

I have a main table (Table 1) that I use for login of MOST of our laboratory
samples. I have a couple of other login tables (Table 2; Table 3) that keep
track of certain other kinds of samples that are "children" of items logged
in the main table with additional unique columns in each. I have queries
(Query 1 & 2)set up to link the location, date & time info in Table 1 with
the unique information in Table 2 or 3 that deal with sample processing. My
main problem is that one of my primary users insists that all samples be in
one table which makes for a very inefficient database design and LOTS of
empty fields in most cases. It also means re-entering all the
location/date/time/etc. information that is in the original record. His
proficiency is Excel so he copies all of the main table to an Excel
spreadsheet and does his searches there.

I can accomplish what he wants by creating a new table (Table 4) with all
the fields of all three tables, copying Table 1 into it entirely; using an
Append query to append Query 1 to Table 4; using an Append query to append
Query 2 to Table 4 and then exporting the result to Excel.

Is there a way to use a query or ??? to create a dynaset that would include
all the records of Table1, the output of Query 1 (all the records of Table 2
linked with the identification information in Table 1) and the output of
Query 2 (all the records of Table 3 linked with the identification
information info in Table 1) and not have to create a new Table (thus
avoiding duplicating the size of my database)?

Thanks for your help in advance!

Dale Peart
 
You could use a UNION query. The sql would look something
like this.

select field1, field2, fieldn from table1 UNION select
field1, field2, fieldn from query1 UNION select field1,
field2, fieldn from query2;
 
Thanks Bob,

That worked great until I got to the unique columns in Tables 2 & 3.
Because a UNION query requires the same number of columns in each table and
the missing columns in Tables 2 & 3 are padded with "NULL," I get a type
mismatch error when trying to put a "NULL" in the first select statement.

Say field3 does not exist in Query1 and field4 does not exist in Table 1.
'SELECT field1, field2, field3 from Table1 UNION SELECT field1, field2, NULL
from Query1' works fine.
'SELECT field1, field2, field3, field4 from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' generates a pop-up box requesting input
for field4.
'SELECT field1, field2, field3, NULL from Table1 UNION SELECT field1,
field2, NULL, field4 from Query1' runs for a few seconds and then generates
a "type mismatch" error.

I know very little about SQL. Is there a solution to this problem?

Dale
 
Back
Top