Combine two tables

[Tony Williams]

I have two tables but have decided my application would run better with one
table. How do I combine the field definitions and data into one new table
without having to rekey all the information.
TIA
Tony Williams

 

[Lynn Trapp]

Can you post the table structure for the two tables and the fields you would
like to have in the single table?

 

[John Ortt]

Open the two tables in design view and copy and paste the additional fields
into the original table.

Then use an Update Query to update the relevant fields.

Hope that helps,

John

 

[Joseph Meehan]

I have two tables but have decided my application would run better
with one table. How do I combine the field definitions and data into
one new table without having to rekey all the information.
TIA
Tony Williams

Follow Lynn's suggestion and tell us what fields you want to end up
with. In fact tell us which database they are in now.

You may be doing yourself a disfavor by combining them.

 

[Tony Williams]

Thanks John. I've got my new table with the data from 1 table. I now want to
import the data from the second table but get the data to match on a certain
field. How do I do this in an update query?
Thanks
Tony

 

[Tony Williams]

Lynn the two tables have about 20 fields each, some text some number,some
date.
They both have fields called txtmonthlabel and txtcompany. I want to combine
the tables so that the data matches where txtcompany and txtmonthlabel
matches and if there is no match then add the data for unmatched txtcompany
and txtmonthlabel fields and their corresponding data. So if I have data for
company A in month 2 in both tables I want to combine the data for that
company and that month. If there is a company Z and a month 2 in one table I
want to add the data for company Z for that month. If there is data for
company Y and month 10 but there is no data for month 10 but there is data
for Company Y in another month in the first table then I want to add that as
well.

Hope that's clear
Thanks for your time
Tony Williams

 

[Lynn Trapp]

Well, that's a little closer to what we need to know, but not completely. If
both tables have the same fields then you would need to simply do an update
statement to make the fields in one table match the fields in the other.
However, if they have different fields, then you will need to add
appropriate fields to one of the tables first and then do your update
statement. So, again, it depends on your table structures.

 

[Tony Williams]

I have a table now with all the fields from both tables that I need. I now
need to update this new table with the data from the other two tables.
However both the existing tables have 2 fields that are common ie
txtmonthlabel and txtcompany. I tried running an append query from each
table but I got duplicate records from the two tables eg if 1 table had data
for Company A and the other table also had data for Company A I got two
records whereas I only want one record with the combined data. The same
thing happens with txtmonthlabel.

Can you help here, I think I'm getting close?
Thanks
Tony

 

[Lynn Trapp]

First, insert all the records from one table1 into the new table. THEN, run
an insert query from table 2 but only for those records in table2 that are
NOT in the new table. Finally, run an update query to update the records in
the new table with the information in table2 that hasn't already been pulled
in.

 

[Douglas J. Steele]

Actually, you can do steps 2 and 3 in one query, Lynn.

See my November 2003 column in Pinnacle Publication's Smart Access. You can
download the column (and the accompanying database) at
http://members.rogers.com/douglas.j.steele/SmartAccess.html

 

[Lynn Trapp]

Cool Doug. I'm gonna have to check that out.