Combining two tables in Access 2000

[tonywilliams]

I want to combine two tables into one. I have a table (tblmaintabs)with all the field definitions from two other tables (tblmain and tblturnover). 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

 

[Crowley16]

try joining the 2 tables together on some common key...

INSERT INTO tblMain SELECT *.tbl1, *.tbl2 FROM tbl1 Join tbl2 On tbl1.key = tbl2.key

--------------------
Procrastinate Now!

 

[tonywilliams]

Thanks Crowley16
First off I am a complete newbie!!!
1.In your example does tbl1 and tbl2 refer to my tables tblmain and tblturnover?
2. Should your reference to tblmain in your code be tblmaintabs
3. How do I execute the INSERT statement?
Sorry if these are obvious questions but I've spent 3 days trying to do this with append, update and make table queries and I'm getting nowhere.
Thanks for your help
Tony

 

[tonywilliams]

Hi Crawley16
I tried answering my own questions and produced this code
INSERT INTO tblMaintabs SELECT *.tblmain, *.tblturnover FROM tblmain Join tblturnover On tblmain.txtcompany = tblturnover.txtcompany

But when I tried to run it I got error message
Syntax error(missing operator)in query expression '*.tblmain'

What does that mean?
Thanks again

 

[Crowley16]

oops...

sorry, it should of course be tblmain.* and tblturnover.*...

what you can do is to use the querybuilder to set up a select query that shows you all the data you want, and edit that till you are happy with all the data, and then goto the sql statement, and then just add in the "insert into table" bit in front of the select...

once you save the query, the icon for the query will change, and if you exit it, and then run it again, it'll automatically run (with warnings about changing data and stuff of course)

--------------------
Procrastinate Now!

 

[tonywilliams]

Thanks I've changed that but now get Synatx error in FROM Clause and it highlights the word Join
getting close??
Thanks

 

[Crowley16]

use the querybuilder, like I suggested...

the actual join could be inner/outer/left/right and depends on how your data should be joined... It's much easier to use the querybuilder to sort it out...

--------------------
Procrastinate Now!

 

[tonywilliams]

Thanks Crowley, I'll do it that way and let you know.
Cheers
Tony

 

[tonywilliams]

Everything fine thatnks for your help
Tony