Importing data into existing record fields

[hairwormman]

I have a database containing ever changing records. To start with, I create individual records with several blank fields, which I fill in over a few weeks (as the data comes in). Is it possible to integrate batches of new information as it comes in, to fill in those blank fields? I have tried through the import function (as my data is in Excel) but Access always produces brand new records, rather than filling in existing ones.
Thanks!!

 

[JeremyNYC]

Why are you putting the data in Excel and then Access? If you're getting the data in that form from someone else, it becomes a little complex to just grab selected fields and overwrite those fields on specific records. It's certainly doable, but it will require some fancy form and query building.

I would think you would want a form that allows you to display the current record and the matched record from the excel spreadsheet, along witha check box to indicate whether or not you want to overwrite the old data with the new data. You could build rules for the initial state of the check box, so that it would be blank if there was no data in the spreadsheet and checked if there is data. If you wanted to make sure the user made a choice in confusing situations, you could use a combo box with Yes/No/Choose for each field, and make the initial state Choose, if there is data in both the spreadsheet and the database. Then a command button would execute this for the displayed record.

Of course, if you're dealing with more than a very small number of records, this is going to be a pain in the neck. Maybe there's some set of fields that always get overwritten? Describe what you're doing in more detail and suggestions posted here will better fit your scenario.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[BNPMike]

Your incoming data must contain a key. A key is one or several fields that identify a single record.

Then import your data into a table - call it Import. Go into the Query screen and create a query with two tables - your Import table and your main table. Join the two tables on the key field(s). Now change the query to Update and fill in the names of the incoming data for each main table field. When you run the query all the matching records will be updated from the imported data.

 

[hairwormman]

Thanks!
I tried the import function through the update query and it works like a charm. Do you think that this process could now be done thorough a macro?

Thanks again.

Ben

 

[BNPMike]

I'm pretty sure you can do it all with a macro. Make sure you delete (drop) the import table each time. Bringing in the excel data is done with the action TransferSpreadsheet (I think).