Hello all,
I feel this should be an easy task, but I have yet to figure out how
to do it.

I have 4 tables each containing the same fields but different data. I
want to make a 'master' table that contains all the data in the other
4 tables and is updated whenever I add data to any of the other 4
tables. I need my master table to have a unique 'ID' field so I can
log my samples by number.

Any help would be great,
Tim

Your intended plan leaves you with tables holding identical duplicate
data, which is a permanent pain to maintain, and a good way to get your
database into trouble.

I'd strongly suggest converting the four tables to one as you plan, and
then use that one table to run your database. To make it work, you will
need to add one field to the table design, to indicate which original
table the data came from. This gets you out of maintaining the extra
table as you asked.

You'll probably have to change some queries to specify which records in
the master table they should pick up. But this is much better than
managing what will five separate tables.

[[ If a database has 3 tables of Students, Graduates, and Alumni, with
the same fields for all, then the extra field would just have a 1-letter
code of S, G, or A to indicate what kind of person this is. And when
you want to track information on Benefactors, you only need to start
using a "B" to indicate those.]]

A relatively easy way, if you're going to use only the point-and-click
tools in Access:

1) Add a field, maybe call it "Type" (use a text data type) to each of
the original four tables. For each table, fill it with a single
distinct code letter to show which of the four original tables the data
is from.
2) Copy one of the 4 tables, and paste it into the databse BUT when the
"Paste Table As" dialog opens, select "Structure Only". This gives a
table with the same fields, but no data.
3) Now in query design view for a new query, include the first of your 4
existing tables, and drop all fields onto the query grid.
In menu bar, click on Query - Append Query, and in the dialog box, set
"Append To" to the new table (so far it's empty).
The design grid will show an "Append To" row, which I hope is
self-explanatory. Verify that it's going where it should. Run the
query via the red exclamaion mark icon, or Query - Run! (expand the
Query menu to see this vital item). Open the new table to see if you
like what's there. If not right, edit the query and run it again.
4) Once the Append query is working properly, do the same for all four
tables, being sure to import each one into the table once only.

If you have skills writing SQL, you can use a union query and an insert
query to do this in one or two steps, but then you probably wouldn't
have asked in the first place.

Bruce Pick
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++
To send mail, remove all < > and [ ] and the junk ~ between:
[brucepick1]
< a~t >
[comcast.net]



Tim wrote: