how to add records to existing table using query?

[Nadalia]

I have tblAddress that contain over 4000 records. There are 70 more records in a seperate table that need to be added into tblAddress. Currently, these 70 records are in a seperate table called tblSAddress which has the same structure as tblAddress. I was trying to use Append query to append all records in tblSAddress to tblAddress, but it did not work. The append query seems work fine when you want to append records to an empty table, but how about the table that already have some records?

Any idea will be really appreciated!

Nadalia

 

[o1110010]

.. a seperate table called tblSAddress which has the same structure as tblAddress. I was trying to use Append query to append all records in tblSAddress to tblAddress, but it did not work. The append query seems work fine when you want to append records to an empty table, but how about the table that already have some records?

That's what I'd use: Append query. (Tables don't even have to be 100% alike when using the append query they just need to have matching fields.) Want to either attach the db and we can take a look? Or explain the design of the query?

 

[Nadalia]

That's what I'd use: Append query. (Tables don't even have to be 100% alike when using the append query they just need to have matching fields.) Want to either attach the db and we can take a look? Or explain the design of the query?

I know that in append query, you can have an option to choose which fields you want to append to from which fields. That is how I got my 4000 records in tblAddress. My question is that if there are some records in tblAddress, how can append more records to this table? I tried the append query, but it won't work.

I' be back after lunch

Hi, Thanks for your help anyway!
Nadalia

 

[Nadalia]

Not mean that I stop talking to you, pls keep helping me!

 

[o1110010]

Let's say we have these tables & fields:

tblNadalia-01
txtOne
txtTwo
numThree

tblNadalia-02
txtOne
txtTwo
numThree

If you want to append tblNadalia-02 to tblNadalia-01..
1) Query -> New -> Design view
2) add tblNadalia-02 to the query
3) change it to an append query
4) Specify you want to Append To tblNadalia-01
5) Since our tables are the exact structure, double-click the * (asterisk)
6) Run the query. (If you have the default warnings still allowed, you will be prompted with something that will say "you are about to append X rows".)

Notice, there are some limitations in these steps. If you have any indexed fields, any duplicate data will cause trouble. Also note that the data isn't "added to the end" of the table, if there is any sorting being done (default is ascending), then the new records just got mixed in with the old records. I can attach a .db example if you wish.

 

[Nadalia]

Hi, you're right! It works just fine! I wonder why it did not work first time I run it?!

Thank you very much!

Nadalia

 

[o1110010]

I wonder why it did not work first time I run it?!

Maybe it was locked (accidently or purposefully open)? Although just having a table open shouldn't stop an append query. I've had other instances where this has caused queries to halt but not with appends. Hmm.

Glad to hear you got it to work.