Combine or Merge Tables

[jon_mc99]

Hi,

I have two Access tables which I need to combine in to just one.

Log1 is the main table which has had the relevant collumns added to correspond to the same collumns in Log2 so the import should be fairly easy to map the data from and too.

I keep being referred to an Update Query but I'm not so sure this is the correct function to use when copying from one table to another.

Any thoughts?

 

[KeithG]

You would need to use an append query. An update query will update existing records, and append query will create new records.

 

[jon_mc99]

I don't want new records to be created though. I want fields within current records to be updated using the 'ID' which is available in both sets of tables as the common Primary Key.

 

[neileg]

Then an update query is what you need.

 

[jon_mc99]

Can you give me a breakdown of how I go about entering the code for creating the update query.

I simply want to update the very same field from 1 table to the other where fields are named exactly the same in both tables, using the Primary Key field as the common denominator which is available in both tables.

If I haven't explained it very well, I could post a dummy database from Access 2003.

Thanks in advance for any assitance.

 

[The_Doc_Man]

You can do all you need for this query from the query grid. Add the two tables to the list of tables participating in the query. If you predefined the relationship between the two fields that are being used to form the JOIN, it should show up above the grid. If you didn't pre-define it (and you didn't have to), then establish that relationship now.

The area above the grid is the relation area. It acts just like the general Relationships window except that it is temporary for the duration of this query only. Establish a one/one relationship NOW between the two prime key fields. If you establish this in the design screen, Access will automagically create a JOIN of the proper flavor for you in the SQL equivalent for that query.

On the icon bar, there is a button that allows you to select the kind of query you want to build. One of which is UPDATE. Select that.

Now, you will have in the grid the ability to select (via drop-down) the names of the fields you want from each table. There is a row for what is being updated and another row for the source of the update data. BOTH are drop-downs. BOTH should automagically be "qualified" by the table names since two tables are involved.

When you are finished, you can switch to SQL design view to look at it to verify that it makes sense. DON'T actually OPEN the query until you are ready to execute it, because opening an action query executes the action.

Don't bother with SQL syntax for this case. The query grid will do everything you want much faster, more accurately, and far simpler. AND you can switch to SQL view to LEARN the syntax any time. (Though the grid likes parentheses a little too much...)