-----Original Message-----
After creating several tables I have come to see that
there are a lot of fields now common to each and only a
couple of unique fields in each table.
So I now what to create one table with all the inforamtion.
Is there a way to merge one table with another?
If so, I guess that each field must have the same
name/properties, but does it need to be in the same place
within the table or will Access recognise the field names?
Regards
Duncan
.
If there is a one-to-one relationship between these tables
you can do a simple MakeTable query. Create a query in
design view, select the tables from which you want to
extract information, assign a temporary relationship
between the key fields, and load up your QBE pane with the
columns that you want. Common columns between these tables
need to be added only once, unique columns should be
pulled from the appropriate table. Set your field types
and formats using the right-click / properties.
Assuming that one of the multiple tables that you have is
a "main" table, your relationship should be set so that
you get all records from this "main" table and only
matching records from the subtables. Otherwise you might
get duplicate records in your new super-table.
Run this first as a select query to make sure you are
getting what you expected, then change it to a make-table
query and give your new table a name.
If there is a one-to-many or many-to-many relationship
between your tables you would be better off leaving them
as separate tables, just create replacement tables without
the unneeded common columns. You can create the tables in
table design (and run an append/update query) or as a
maketable query, your choice.
Either way you will need to reprogram your forms and such
to use the new tables.
