How do I merge records from 2 tables into one ?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My problem is that I want to apend the records from one table to another.
Both tables have the same structure and definitions.

I read some of the past postings and I am having trouble using the INSET
INTO command; here is what a member posted in the past:

I declared SQLS as a string

INSERT INTO tblJuly
SELECT tblAugust.* FROM tblAugust

I assume that this must be put into a SQL statement and I created a string
like this:
SQLS = "INSERT INTO LASTAVERUNLINES" & _
"SELECT RUNLINE.* From RUNLINE"

The field structure of both tables are identical.
then I use the SQL execution comand with gthe string.
DoCmd.RunSQL SQL

and it gives me an error related to the Insert command....I'm Lost here, Am
I missing something?

I would appreciate the help.

Thanks, Henry G.
 
An easier way may be to create a query, open a query in design view, add the
runline table, change the query type to an append queary, the database will
prompt you for the table to append to, and select the fields you want to
append. You will see if the structure is the same it will select the field
to append to, or you can change it as required. Run the queary, and save it
if you will need it at a later date.
 
In SQLS, you need a space after LASTAVERUNLINES. Also you Docmd line
should be
DoCmd.RunSQL SQLS, not SQL.
 
Thanks Schasteen, tried that and works but I need to automate the process,
its done more than once and every day, a little time consuming for the user.

ProfeG
 
Thanks Jeff:

I made the correction to the string and the SQLS was already correct (added
the space), now I have a problem with the FROM statment; gives a sintax error
when I run the command; any ideas?
 
You can automate by:
Docmd.openquery "QueryName"

profG said:
Thanks Schasteen, tried that and works but I need to automate the process,
its done more than once and every day, a little time consuming for the user.

ProfeG
Click to expand...
 
You can create a macro that automatically runs for the user using the
/x macroName when you run Access. In the Macro, you can create a Union
query that select both tables. Then the macro can do a TransferDatabase
command which outputs the Union query to a table of your choice.
 
Thanks Roger for the input. This is a function that is performed more than
once and it requires that it runs within a module that i have put together.

I really want to write code for it because it will save me time with the
users in the long run. I think I'm in the right path, I am just having a
little bit of trouble using the Insert into and From commands....Any help
here. I made the sintax corrections and I'm still getting errors

Can you help me with the Insert and From Commands?

ProfeG.
 
Hi again:

I want to thank everyone on the board for all the help and the messages
posted on this issue.

My solution in fact was very simple as one member suggested.

I created a new Union query using 2 queries I had created. I created the new
Query using the Union Query option toi create a new query.

Need to remind people that sintax is very critical (spent lots of time
trying to figure it out), second the 2 queries must have the same number of
fields and they have to have the same structure; in other words they must be
identical.

Last, there is a limit on the number of fields to use if you use all fields
option( must be close to 128) and it's better to remove the autonumber ID
from them.


here is the code I included when I opened Union query:

here, QAVEDANDSRUNLINE represents my first query with actual data and
QLASTRACERUNLINE represents the second query with more data that needs to be
merged. One thing I did also was get rid off the autonumber field on both
queries.
The asterick represents all fields.

Don't forget the ";" semicolons, very critical.

code:
SELECT QAVEDANDSRUNLINE.*
FROM QAVEDANDSRUNLINE;
UNION SELECT QLASTRACERUNLINE.*
FROM QLASTRACERUNLINE;


The query executed when certain report that I had created for it is called.

It has been a learning experience and I thank everyone who contributed.
 
Back
Top