Add field data to specific records in table

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

Guest

I have a database that gets data from two sources. One source is direct data
entry by me. The other is from Excel spreadsheets. On the spreadsheets, there
are only four fields out of eighteen. (The others are either reptitious or
not needed).

What I do is export from the database the records that need to be updated. I
include the Keynumber fields from the database. I then rename the fields from
the outside spreadsheet to match the Access field names. I then cut and paste
the needed data from the outside spreadsheet to the Access spreadsheet.

I want to take the data from fields 14, 15, 16, & 17 and insert them into
the corresponding fields in Access, using the Keynumbers to keep the data
synchronized. The other fields in Access table already have data in them, so
I don't want to lose any of it.

I found one solution to this but I'm not sure it will do what I want. Any
advice will greatly appreciated.
http://www.microsoft.com/office/com...cbf0a0-7365-4080-aadf-60a0715719cf&sloc=en-us
 
Link to the Excel spreadsheet from within Access. Create a query against
that linked "table" (of Excel data). Select only the columns you wish to
use (remember the ID field). Run this query ... do you see the data you
wish to use to update?

Create a new query. Use your "to be updated" table. Use the query you just
built above. Link on the ID fields. Add the "to be updated" table fields
and their counterparts from the linked Excel table. Run it ... are you
getting the "old" values and the "new" values for the matching IDs?

Make a backup of your db file ... update queries ... update things (and
sometimes the wrong things). Open the second query and convert it to an
Update query. In the "update to" row under each of your "old" fields, enter
the corresponding name of the field (from the Excel) that holds the "new"
value. You won't put anything under the "new" fields (you can drop them,
you only want to update the old fields, right?).

When you run this Update query, your permanent Access table should be
updated to the values held in the linked Excel table's corresponding (ID)
columns.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Back
Top