how to update records in database with cells recently modified

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My name is DIOUF, from senegal but working in Johannesburg.

I am managing drill holes data for our gold mining junior company.

Is there a way that I can re-import records (>2500) that are already in the
database and update the cells which have been modified since their creation?

I dont want to delete those records in my parent table, because all the data
in my "child" table will also be deleted. I want to save on that time.

Thanks for your help
 
Barbara,

Cheer up, quite easy! The idea is to link your external data, and use an
Update query to update your existing records in your parent table,
without deleting them.

Start by linking your external data: go File > Get External Data > Link
Tables, select the appropriate file type (Excel or Access, i would
expect) depending on the file type of your external data, and follow the
directions of the wizard. At the end of the process, you should have a
new linked table in your database window, with your external data.

Next, start making a new query (design view); add the existing table
that you wish to update, and the linked table with the external data.
You need to link the two, so you match corresponding records; there must
be field in each table (DrillHoleID or something) which uniquely
identifies each record, and this is the field on which you join the two
tables on, by dragging the field from one table and dropping it on the
matching field on the other (you should get a libne joining the two).
Now drag the ID field and the field(s) that you want to update down to
the grid (so they are displayed in datasheet view), as well as the
corresponding fields from the external table (the latter is not required
for the actual updating, just for viewing before you proceed!). Switch
to datasheet view; you should see the "old" and "new" fields side by
side. If you are happy that this has worked as expected so far, return
to design view, and go to menu Query > Update; you should now have a new
row in the grid, titled "Update To". In this row, you must specify the
field in the external table to whose value each field in the original
table will be updated to. So, if field ABC in the existing table is to
be updated to the value of field XYZ in th external table, you should
type [XYZ] in the Update To row under field ABC (including the square
brackets). You need to do this only for those fielfdss in the existing
table which are to be updated. Note: if the fields in the external table
have the same names as the ones in the existing table, then you need to
include the external table name (as it appears in the database window),
so Access can tell which table's field to "read". So, the field
reference should be something like:
[My External Table].[FieldName]
again, include the square brackets.
Finally, go Query > Execute, confirm and the job is done!

CAUTION: Always backup before you try anything of which you are not 100%
sure!

HTH,
Nikos
 
Back
Top