combining tables and linking records with queries

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;

-----Original Message-----
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


.

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

"Bob" wrote in message
...
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;

-----Original Message-----
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


.