[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

We need to append records from one table to another table in same database

We have two Access DB tables that need to be combined together.  Table 1 has 20 fields and about 800 records. Table 2 has 10 fields and about 85 records.  The first 10 fields are the same on both tables.  They both have the same field names, the same size, type and are in the same order.  Information for the last 10 fields will be added manually after the two table are combined. The last 10 fields are not on Table 2.
The main table (Table 1) has an automatically assigned autonumber whenever a new record is entered.  Table 2 also has an autonumber for each record.  We will not need the autonumber from table 2 when they are combined.

What is an easy way to combine these two tables.  Once they are combined and tested, table 2 will be deleted. There are no duplicates.  If we must use a special procedure, plese tell us where to put the code.  We are using Access ver 7 in Windows.

Thanks for any assistance you can give.
0
sherman6789
Asked:
sherman6789
  • 3
  • 2
2 Solutions
 
Helen FeddemaCommented:
Write an append query to append records from Table 2 to Table 1.  Use specific field names, because the AutoNumber field would cause an error.  Here is some typical SQL for such a query
INSERT INTO tblContactAddresses ( AddressType, StreetAddress, POBox, City, StateOrProvince, PostalCode, Country, Notes, LastUpdated )
SELECT tblMoreContactAddresses.AddressType, tblMoreContactAddresses.StreetAddress, tblMoreContactAddresses.POBox, tblMoreContactAddresses.City, tblMoreContactAddresses.StateOrProvince, tblMoreContactAddresses.PostalCode, tblMoreContactAddresses.Country, tblMoreContactAddresses.Notes, tblMoreContactAddresses.LastUpdated
FROM tblMoreContactAddresses;

Open in new window

0
 
sherman6789Author Commented:
Thanks for your prompt response.  Where do I write the append query?  Should both of the tables be open?  Thanks.
0
 
MINDSUPERBCommented:
You can post a sample db containing those two tables. You may take out the data.
What we need are the fields names so that we can write the proper Append query statement.

Sincerely,
Ed
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MINDSUPERBCommented:
sherman6789,

You may use the attached sample DB as your guide.

The DB has 2 tables, tbl1 and tbl2. It contains the Append query that append all data from tbl2 which are not found in tbl1.

It would be a good start for you to achieve what you want.

Sincerely,
Ed
SampleDB.accdb
0
 
sherman6789Author Commented:
Thanks to Helen_Feddema and MINDSUPERB.

Both suloutions worked fine for me and I believe they may come in handy for others who track solutions through Experts-Exchange.  I've divided the points between both of you. Thanks again.

WRS
0
 
sherman6789Author Commented:
The screen views given by MindSuperb were excellent.

WRS
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now