Create table3 by merging table1 and table2

  • Thread starter Thread starter Mathew
  • Start date Start date
M

Mathew

Looking at a job that requires the answer to this question. I took a datadase
class that utilized Access 2002 years ago so I'm not up to speed. If you
could walk me through it baby steps I would be most gratefull.
Thanks,
Mathew
 
Temporary "merge" would be to use a UNION query to combine the two tables into
one. Union queries can only be built in SQL view. IF table1 and table2 have
the same exact structure. It would be as simple as

SELECT *
FROM [Table1]
UNION ALL
SELECT *
FROM [Table2]

If you want to permanently merge the records into one table then you can
choose one table as the master table and use an append query to add all the
records from one table into the other table.

== Open a new query
== Select Table2
== Select all the fields in table2 and put them in the grid
== Select Query: Append from the menu
== Select table1 as the destination table
== In the new row (Append To:) select the field in table1 that should be the
data for the field in table2

If you want to join records in table1 to records in table2 then you are not
merging the records. You are joining them together in one common row based on
some field or fields shared between the two tables.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
The first question is whether this is necessary or not. Redundant data is a
normalization no-no. It is likely all you need is a query to combine the
data.

If, on the other hand, you are redesigning your application, it is possible,
but there is not enough information about the data in the two tables, what
fields you need to use to link the data.

If you can describe WHAT you want to do, in detail, we can probably help
with the HOW To.
 
Create a query
Change to a Make Table query
Add Table 1 and T3 to the query
Link them by the desired field. (Click & drag from table to table using the
appropriate field.)
Add fields to grid
Add other criteria & sort order
 
Dave, the add reads "the boss dosent need an expert but someone that can at
least merge table1 and table2 into one table"
 
I don't know how the tables are structured but I assume they have a common
field and not the same structure

John Spencer said:
Temporary "merge" would be to use a UNION query to combine the two tables into
one. Union queries can only be built in SQL view. IF table1 and table2 have
the same exact structure. It would be as simple as

SELECT *
FROM [Table1]
UNION ALL
SELECT *
FROM [Table2]

If you want to permanently merge the records into one table then you can
choose one table as the master table and use an append query to add all the
records from one table into the other table.

== Open a new query
== Select Table2
== Select all the fields in table2 and put them in the grid
== Select Query: Append from the menu
== Select table1 as the destination table
== In the new row (Append To:) select the field in table1 that should be the
data for the field in table2

If you want to join records in table1 to records in table2 then you are not
merging the records. You are joining them together in one common row based on
some field or fields shared between the two tables.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Looking at a job that requires the answer to this question. I took a datadase
class that utilized Access 2002 years ago so I'm not up to speed. If you
could walk me through it baby steps I would be most gratefull.
Thanks,
Mathew
Click to expand...
Click to expand...
 
Mathew said:
Dave, the add reads "the boss dosent need an expert but someone that can at
least merge table1 and table2 into one table"
Click to expand...

When they ask you that question, ask them what they mean by merge.
 
I am pretty much self-taught in Access 2003. That said, I would probably
export the data from both tables to Excel then sort the data on the common
field, combine the 2 tables in Excel. Next I would go back to Access, make a
copy of the table with the most fields, and then add (type in) the names of
the added fields. Next I would copy the data from Excel to Access. It's not
the easiest method but it would work - especially for combining tables with
minimal fields.
 
Well, until you know how the tables are structured and how they are related,
it is doubtful that I can help you.

Good Luck.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top