Joining Tables Query Problem.

  • Thread starter Thread starter Barbara Schneier
  • Start date Start date
B

Barbara Schneier

I am new to Access and need to join 3 tables into one. The tables have
fields in common, but none of the fields in any of the tables contain
unique records so I cannot make any of them a Primary key. I have tried
to do a table query without creating a relationship, first by just
selecting the 3 tables and then selecting all fields in the table in the
Query Design View, but this did not work. Then I added a field called ID
in each table using the Auto Numbering type and created a Primary Key in
just one of the Databases on the ID Field. This allowed me to create a
one to many relationship between the tables, but when I try to do a
table query to join the 3 tables a message comes up stating there can be
only one ID Field with Auto Numbering in the database I am joining them
to. I am at a loss as to what to do next. Any help with this problem
would be gratly appreciated.
 
I think you are a little confused! I assume you want to merge the data
together from 3 tables to 1 table? Simply copy the data from two of the
tables into the third one... Then add an additional column, say AutoNum, and
you have unique data... If you need to apply a unique number before merging,
i.e. Table1 data between 1 and 500, and Table2 between 501 and 750, etc.,
you can either use an Update query, which I think may confuse you more, or
simpler is to add the numbers manually in a new column (perhaps using
Excel's replication facility?) then copy the data as mentioned above, sort
it in the numerical way you want and then add an AutoNum field ... should
take about 5 minutes to complete and check...

HTH.

Tom.
 
Barbara

If you set an Autonumber column in each table, each table will independently
create autonumbers ... i.e., they will NOT be related.

Jeff Boyce
<Access MVP>
 
I think you are a little confused! I assume you want to merge the data
together from 3 tables to 1 table? Simply copy the data from two of the
tables into the third one... Then add an additional column, say AutoNum, and
you have unique data... If you need to apply a unique number before merging,
i.e. Table1 data between 1 and 500, and Table2 between 501 and 750, etc.,
you can either use an Update query, which I think may confuse you more, or
simpler is to add the numbers manually in a new column (perhaps using
Excel's replication facility?) then copy the data as mentioned above, sort
it in the numerical way you want and then add an AutoNum field ... should
take about 5 minutes to complete and check...

HTH.

Tom.
Click to expand...

I guess I did not explain this very well. I want to merge the data and
structure, not just the data. Some of the fields are the same and some
not. these are large tables (over 24000 rows and the fields are from 16
to 100. I have not found a way as you suggested to copy both the
structure and data of one table into another. I use Alpha 5 and they
have an operation called join tables. All I need to do is define the 2
tables I want to join and the Primary key (which fields can have
duplicates) in each table and run the operation and they are joined into
one table. Is there a similar way to do this in Access?
 
Back
Top