NEED SIMPLE UPDATE QUERY METHOD

[AnimalMagic]

Hey Guys,

I am pretty good with Excel, and I used to develop databases in Paradox
back in the 286 days, but I am not taking to the development paradigm for
access all that well so far.


I have a table with two fields... a unique key field and a data field.

I download updated versions of a larger table that carries additional
information occasionally and I want to write an update query that changes
and adds records to my table using the "new" table data.

A couple speed enhancers come to mind...

A query that adds all records with a unique key field number higher
than the highest value in my current table. Followed by a query that
replaces any changed data in the existing records.

As far as I know, there are never any records that get deleted so all
the original table data will remain, but the data itself may change at
some positions.

 

[John W. Vinson/MVP]

A query that adds all records with a unique key field number higher
than the highest value in my current table. Followed by a query that
replaces any changed data in the existing records.

You can actually do both operations in one query if you wish. Back up
your database first, just in case something goes wrong!

To do it as separate queries, use an Append query to add the records,
and an UPDATE query to fix the existing ones. Let's do the second
first:

Create a new Query by adding both tables to the query grid. Join the
two tables by the unique ID, if Access doesn't do so for you
automatically. Change the query to an Update query using the Query
menu option, or the query type tool in the toolbar. On the "Update To"
line under the data field in the current table, type

[OtherTable].[Fieldname]

Run the query by clicking the ! icon.

To add new records, change the query back to a Select query; then
change the Join type to a left outer join by selecting the join line.
Select the option "Show all records in <source table> and matching
records in <target table>". Select all the fields from the source
table, and only the primary key record from the target table; put a
criterion on that field of

IS NULL

Change the query to an Append query, and select the appropriate fields
in the target table; leave the Append To line blank for the field on
which you have the criterion.

 

[AnimalMagic]

A query that adds all records with a unique key field number higher
than the highest value in my current table. Followed by a query that
replaces any changed data in the existing records.

You can actually do both operations in one query if you wish. Back up
your database first, just in case something goes wrong!

To do it as separate queries, use an Append query to add the records,
and an UPDATE query to fix the existing ones. Let's do the second
first:

Create a new Query by adding both tables to the query grid. Join the
two tables by the unique ID, if Access doesn't do so for you
automatically. Change the query to an Update query using the Query
menu option, or the query type tool in the toolbar. On the "Update To"
line under the data field in the current table, type

[OtherTable].[Fieldname]

Run the query by clicking the ! icon.

To add new records, change the query back to a Select query; then
change the Join type to a left outer join by selecting the join line.
Select the option "Show all records in <source table> and matching
records in <target table>". Select all the fields from the source
table, and only the primary key record from the target table; put a
criterion on that field of

IS NULL

Change the query to an Append query, and select the appropriate fields
in the target table; leave the Append To line blank for the field on
which you have the criterion.

Thank you. I just thought that it would be quicker to query my table
to be updated for its highest key value, and then query the update table
for all records above that number, then add that result.

For some reason I thought that would result in less reading of the
update table, but it likely doesn't unless the table is also sorted on
that field (which it would naturally be, were it a key field, right?).

I thought I would do my share to reduce cycles needed. :-]

 

[John W. Vinson/MVP]

Thank you. I just thought that it would be quicker to query my table
to be updated for its highest key value, and then query the update table
for all records above that number, then add that result.

For some reason I thought that would result in less reading of the
update table, but it likely doesn't unless the table is also sorted on
that field (which it would naturally be, were it a key field, right?).

I thought I would do my share to reduce cycles needed. :-]

You would be able to do so using a subquery - but that basically hits
the table indexes twice. It might be interesting to try both and see
which is more efficient.

The disadvantage of finding the largest value is that doing so would
miss any "interior" gaps.