Combining two tables

[Tony Williams]

I want to combine two tables into one. I have a table with all the field
definitions from two other tables. 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 anyone help me here?
TIA
Tony Williams

[Dave Bradshaw]

I think that what you need to do is copy one of the original tables,
and then add the extra fields to the copy. Then run an append query to
add in the records from the other table.

On Thu, 16 Dec 2004 11:14:26 +0000 (UTC), "Tony Williams"
<[e-mail]> wrote:

I want to combine two tables into one. I have a table with all the field
definitions from two other tables. 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 anyone help me here?
TIA
Tony Williams

[Tony Williams]

Thanks Dave I've tried that but I get duplicates. Eg One table has say 1500
records the other has 500. When I combine them I should still have 1500 but
I get 2000
So the data is not being "merged" just added as new records. I've been at
this for three days now and I didn't think something that sounded
simplecould be so difficult!
Thanks
Tony
"Dave Bradshaw" <"dave"&"."&"bradshaw"&"@"&"bowbros"&"."&"co"&"."& "uk">
wrote in message news:7f********************************@4ax.com...

I think that what you need to do is copy one of the original tables,
and then add the extra fields to the copy. Then run an append query to
add in the records from the other table.

On Thu, 16 Dec 2004 11:14:26 +0000 (UTC), "Tony Williams"
<[e-mail]> wrote:

I want to combine two tables into one. I have a table with all the field
definitions from two other tables. I now need to update this new table withthe data from the other two tables.However both the existing tables have 2fields that are common ie txtmonthlabel and txtcompany. I tried running anappend query from each table but I got duplicate records from the two tableseg if 1 table had data for Company A and the other table also had data forCompany A I got two records whereas I only want one record with the combineddata. The same thing happens with txtmonthlabel.
Can anyone help me here?
TIA
Tony Williams

[Darryl Kerkeslager]

table1 is the table you want to append to
table2 is the table with some but not all duplicate data.
company is the field that is the same in both tables.

INSERT INTO table1
(
SELECT table2.*
FROM table2 LEFT JOIN table1 ON table1.company = table2.company
WHERE table2.company Is Null
)

Your situation may be a little different. If this does not work as planned,
try this:

In the query design part of Access, visually construct a SELECT query to get
the results you want. Then you can change it to a "Make Table" query if you
want. (Not sure if you want a new table3, or just to insert the
non-duplicated records into table1) . Then you can view the SQL, and copy it
to VBA, or run it as a stored query from code.

Darryl Kerkeslager

"Tony Williams" <[e-mail]> wrote in message
news:cp**********@titan.btinternet.com...

Thanks Dave I've tried that but I get duplicates. Eg One table has say 1500 records the other has 500. When I combine them I should still have 1500 but I get 2000
So the data is not being "merged" just added as new records. I've been at
this for three days now and I didn't think something that sounded
simplecould be so difficult!
Thanks
Tony
"Dave Bradshaw" <"dave"&"."&"bradshaw"&"@"&"bowbros"&"."&"co"&"."& "uk">
wrote in message news:7f********************************@4ax.com...

I think that what you need to do is copy one of the original tables,
and then add the extra fields to the copy. Then run an append query to
add in the records from the other table.

On Thu, 16 Dec 2004 11:14:26 +0000 (UTC), "Tony Williams"
<[e-mail]> wrote:

I want to combine two tables into one. I have a table with all the fielddefinitions from two other tables. I now need to update this new table withthe data from the other two tables.However both the existing tables

have

2fields that are common ie txtmonthlabel and txtcompany. I tried running anappend query from each table but I got duplicate records from the two tableseg if 1 table had data for Company A and the other table also had data forCompany A I got two records whereas I only want one record with the combineddata. The same thing happens with txtmonthlabel.
Can anyone help me here?
TIA
Tony Williams

[Tony Williams]

Thanks Darryl first suggestion worked OK
Tony
"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:Rr********************@comcast.com...

table1 is the table you want to append to
table2 is the table with some but not all duplicate data.
company is the field that is the same in both tables.

INSERT INTO table1
(
SELECT table2.*
FROM table2 LEFT JOIN table1 ON table1.company = table2.company
WHERE table2.company Is Null
)

Your situation may be a little different. If this does not work as planned, try this:

In the query design part of Access, visually construct a SELECT query to get the results you want. Then you can change it to a "Make Table" query if you want. (Not sure if you want a new table3, or just to insert the
non-duplicated records into table1) . Then you can view the SQL, and copy it to VBA, or run it as a stored query from code.

Darryl Kerkeslager

"Tony Williams" <[e-mail]> wrote in message
news:cp**********@titan.btinternet.com...

Thanks Dave I've tried that but I get duplicates. Eg One table has say

1500

records the other has 500. When I combine them I should still have 1500

but

I get 2000
So the data is not being "merged" just added as new records. I've been at

this for three days now and I didn't think something that sounded
simplecould be so difficult!
Thanks
Tony
"Dave Bradshaw" <"dave"&"."&"bradshaw"&"@"&"bowbros"&"."&"co"&"."& "uk">
wrote in message news:7f********************************@4ax.com...

I think that what you need to do is copy one of the original tables,
and then add the extra fields to the copy. Then run an append query to
add in the records from the other table.

On Thu, 16 Dec 2004 11:14:26 +0000 (UTC), "Tony Williams"
<[e-mail]> wrote:

>I want to combine two tables into one. I have a table with all the field >definitions from two other tables. 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 anyone help me here?
>TIA
>Tony Williams
>