append new data and update changed data

[Guest]

I have two tables. Table A has new data being entered daily and also some
data that is being updated. TABLE B is a master table that has data from
every month. I want to know how to add the new data from Table A into Table B
and update the data that has been changed in Table A into Table B. I used the
append, but that adds the updated data as a new record in Table B.
I just want to add new data to table B and update the data that just has a
field changed. How do I do this?

jac007

 

[Guest]

You'll need to run two queries to do this, firstly an update query to update
the current rows and secondly an append query to insert the new rows. For
the update query you join the tables on their primary key column(s) or any
other column or set of columns which uniquely identifies each row. Note that
if the tables use an autonumber column as the primary key you can't join on
this as they will not match row for row between the tables, so you'd need to
identify another column or set of columns which maps each row in A to the
correct row in B. For the sake of this example I'll assume a single column
non-autonumber key called MyID, so the query would go like this:

UPDATE [Table A] INNER JOIN [Table A]
ON [Table B].[MyID] = [[Table A].MyID]
SET [Table B].[Field 1] = [[Table A].Field1],
[Table B].[Field 2] = [[Table A].Field2],
[Table B].[Field 3] = [[Table A].Field3],
[Table B].[Field 4] = [[Table A].Field4];

To append new rows from table A to table B you need to identify those rows
in Table B which don't exist in Table B:

INSERT INTO [Table B]
([MyID], [Field1], [Field2], [Field 3],[Field 4])
SELECT [MyID], [Field1], [Field2], [Field 3],[Field 4]
FROM [Table A]
WHERE NOT EXISTS
(SELECT *
FROM [Table B]
WHERE [TableB].[MyID] = [TableA].[MyID];

Ken Sheridan
Stafford, England

 

[kingston via AccessMonster.com]

It sounds like you need two queries: an update query and an append query.
The update query will consist of both Table A and Table B joined on the
primary key (hopefully, there is one). Then, for the fields in Table B, the
Update To: setting will be the corresponding field in Table A:
Field: [Table B].[Field1]
Update To: [Table A].[Field1]
This will overwrite all data in Table B with data in Table A where the key
field matches.
To add new data from Table A, perform an append query where the key field
doesn't exist in Table B. This can be done with a subquery:
[Table A].[Key] NOT IN (SELECT [Table B].[Key] FROM [Table B]
This might not be necessary if the key is unique (no duplicates allowed) and
common to Table A and B. However, if there isn't a key (then you're in
really bad shape with the first problem), you might be able to do this by
filtering on dates.

 

[Guest]

Thanks, but is this "WHERE NOT EXISTS" regonized by Access or is it "WHERE
NOT IN"?

You'll need to run two queries to do this, firstly an update query to update
the current rows and secondly an append query to insert the new rows. For
the update query you join the tables on their primary key column(s) or any
other column or set of columns which uniquely identifies each row. Note that
if the tables use an autonumber column as the primary key you can't join on
this as they will not match row for row between the tables, so you'd need to
identify another column or set of columns which maps each row in A to the
correct row in B. For the sake of this example I'll assume a single column
non-autonumber key called MyID, so the query would go like this:

UPDATE [Table A] INNER JOIN [Table A]
ON [Table B].[MyID] = [[Table A].MyID]
SET [Table B].[Field 1] = [[Table A].Field1],
[Table B].[Field 2] = [[Table A].Field2],
[Table B].[Field 3] = [[Table A].Field3],
[Table B].[Field 4] = [[Table A].Field4];

To append new rows from table A to table B you need to identify those rows
in Table B which don't exist in Table B:

INSERT INTO [Table B]
([MyID], [Field1], [Field2], [Field 3],[Field 4])
SELECT [MyID], [Field1], [Field2], [Field 3],[Field 4]
FROM [Table A]
WHERE NOT EXISTS
(SELECT *
FROM [Table B]
WHERE [TableB].[MyID] = [TableA].[MyID];

Ken Sheridan
Stafford, England

I have two tables. Table A has new data being entered daily and also some
data that is being updated. TABLE B is a master table that has data from
every month. I want to know how to add the new data from Table A into Table B
and update the data that has been changed in Table A into Table B. I used the
append, but that adds the updated data as a new record in Table B.
I just want to add new data to table B and update the data that just has a
field changed. How do I do this?

jac007

 

[Guest]

Either can be used but the EXISTS predicate is generally preferred as it
allows the indexes to be exploited most efficiently. The IN operator is
really something of a left-over from the early days of the development of the
SQL language when it was based on relational algebra, whereas later
relational calculus came more into play.

Ken Sheridan
Stafford, England

Thanks, but is this "WHERE NOT EXISTS" regonized by Access or is it "WHERE
NOT IN"?

You'll need to run two queries to do this, firstly an update query to update
the current rows and secondly an append query to insert the new rows. For
the update query you join the tables on their primary key column(s) or any
other column or set of columns which uniquely identifies each row. Note that
if the tables use an autonumber column as the primary key you can't join on
this as they will not match row for row between the tables, so you'd need to
identify another column or set of columns which maps each row in A to the
correct row in B. For the sake of this example I'll assume a single column
non-autonumber key called MyID, so the query would go like this:

UPDATE [Table A] INNER JOIN [Table A]
ON [Table B].[MyID] = [[Table A].MyID]
SET [Table B].[Field 1] = [[Table A].Field1],
[Table B].[Field 2] = [[Table A].Field2],
[Table B].[Field 3] = [[Table A].Field3],
[Table B].[Field 4] = [[Table A].Field4];

To append new rows from table A to table B you need to identify those rows
in Table B which don't exist in Table B:

INSERT INTO [Table B]
([MyID], [Field1], [Field2], [Field 3],[Field 4])
SELECT [MyID], [Field1], [Field2], [Field 3],[Field 4]
FROM [Table A]
WHERE NOT EXISTS
(SELECT *
FROM [Table B]
WHERE [TableB].[MyID] = [TableA].[MyID];

Ken Sheridan
Stafford, England

I have two tables. Table A has new data being entered daily and also some
data that is being updated. TABLE B is a master table that has data from
every month. I want to know how to add the new data from Table A into Table B
and update the data that has been changed in Table A into Table B. I used the
append, but that adds the updated data as a new record in Table B.
I just want to add new data to table B and update the data that just has a
field changed. How do I do this?

jac007

 

[Guest]

Hi Ken

Thank you for your help - the query runs perfectly. Unfortunately in my
case, [MyID] in [TableA] is an autonumber, and I believe it is causing me to
get the error message:
"Data type mismatch in criteria expression".

None of my cells are Null. Is there something I can do to make the query
read MyID not as an autonumber?
Is this the issue here?

Thanks in advance
Will

You'll need to run two queries to do this, firstly an update query to update
the current rows and secondly an append query to insert the new rows. For
the update query you join the tables on their primary key column(s) or any
other column or set of columns which uniquely identifies each row. Note that
if the tables use an autonumber column as the primary key you can't join on
this as they will not match row for row between the tables, so you'd need to
identify another column or set of columns which maps each row in A to the
correct row in B. For the sake of this example I'll assume a single column
non-autonumber key called MyID, so the query would go like this:

UPDATE [Table A] INNER JOIN [Table A]
ON [Table B].[MyID] = [[Table A].MyID]
SET [Table B].[Field 1] = [[Table A].Field1],
[Table B].[Field 2] = [[Table A].Field2],
[Table B].[Field 3] = [[Table A].Field3],
[Table B].[Field 4] = [[Table A].Field4];

To append new rows from table A to table B you need to identify those rows
in Table B which don't exist in Table B:

INSERT INTO [Table B]
([MyID], [Field1], [Field2], [Field 3],[Field 4])
SELECT [MyID], [Field1], [Field2], [Field 3],[Field 4]
FROM [Table A]
WHERE NOT EXISTS
(SELECT *
FROM [Table B]
WHERE [TableB].[MyID] = [TableA].[MyID];

Ken Sheridan
Stafford, England

I have two tables. Table A has new data being entered daily and also some
data that is being updated. TABLE B is a master table that has data from
every month. I want to know how to add the new data from Table A into Table B
and update the data that has been changed in Table A into Table B. I used the
append, but that adds the updated data as a new record in Table B.
I just want to add new data to table B and update the data that just has a
field changed. How do I do this?

jac007