Update table

I

Igor G.

I have two tables with same fields (ID, Date, Time, Field1, Field2, ...).
Table1 is with all data, and Table2 with only newest data.
How to copy just missed rows from Table2 to Table1 (missed ID).
Thenks!
 
J

John W. Vinson

I have two tables with same fields (ID, Date, Time, Field1, Field2, ...).
Table1 is with all data, and Table2 with only newest data.

Then you almost certainly have a misdesigned set of tables. Storing the same
data redundantly in two tables Is A Bad Idea.
How to copy just missed rows from Table2 to Table1 (missed ID).
Thenks!

INSERT INTO Table1 (ID, [Date], [Time], Field1, Field2, ...)
SELECT Table2.ID, Table2.[Date], Table2.[Time], Table2.Field1,
Table2.Field2...
FROM Table2 LEFT JOIN Table1
ON Table2.ID = Table1.ID
WHERE Table1.ID IS NULL;

This "frustrated outer join" query will select all records in Table2 which do
not have a match in Table1 (joining on ID), and append them into Table1.

Note that Date and Time are reserved words and should not be used as
fieldnames, and that an Access Date/Time value stores both date and time data
in a single value; it's usually best to take advantage of that fact and
combine your date and time fields into one appropriately-formatted date/time
field.

John W. Vinson [MVP]
 
R

Ron

What do you ean by "Just Missed Rows"?? They just got missed by error when
you copied the data? They got missed when the data was copied because they
were missing a field?
Let me know
 
N

NetworkTrade

make a new 'Append Query'

create a normal Select Query; while in Design View up in the menu bar select
'Query' and select 'Append Query'....follow the wizard to complete set up
 
J

Jerry Whittle

If, big IF, the ID field in Table1 is a primary key field or has a unique
index, the following should query work to add only new records:

INSERT INTO Table1
SELECT Table2.*
FROM Table;

You should get an error message if there are any duplicates and Access will
not import the dupes - just the new records.

To update existing records in Table1 with data from Table2:

UPDATE Table1 INNER JOIN Table2
ON Table1.ID = Table2.ID
SET Table1.[Date] = [Table2].[Date],
Table1.[Time] = [Table2].[Time],
Table1.[Field1] = [Table2].[Field1],
Table1.[Field2] = [Table2].[Field2],
Table1.[Field3] = [Table2].[Field3],
And so on
;

Make a backup of the tables in question or the entire database before doing
either of the above.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top