access/excel question: merging changes

Factual Questions
1

I have a large database that gets replaced with an updated one every month or so. However, for personal use, I like to modify certain entries. I don’t want to have to do this every time: I’d like to automatically update the fields I want changed. I figure Access could handle it pretty easily, but I cant figure out how.

I already have the changes separated out, using one of the Query wizards in access. I thought about using an update query, but I am unsure how to use them in this manner (I’ve only used them for search-replace.)

I would appreciate any help you can provide. I’m in a hurry, so if you need more info, I might be able to provide it later.

2

I’m unsure how to help since I don’t know what type of criteria causes a field to be one that you update manually.

With Access, you’d almost certainly be running an update query.

With Excel, you might need to use a Macro or create a new sheet with “=IF” cells that mimick the original report but replace specific cells if criteria or met.

3

Sorry. I knew my quick explanation wouldn’t be sufficient. All I mean is that I have a table which has all the changes I want to apply, but I don’t know how to apply them. Let me simplify further.

I have a table that has over 7000 records. Now I have another table that has about 300 records. The 300 records are all updated versions of the 7000. I want to merge the 300 back into the 7000 without creating any duplicates. Only one field from each of the 300 records should be updated into the 7000-record table.

Now for the whole story. Perhaps there’s a better way to go about this. Here’s the background:

My dad works inventory for a resale company. He often pulls orders and things like that. There’s a large book that tells him where everything is located. He didn’t want to have to carry this book around with him all the time, and he wanted to be able to search through them more quickly. He asked me if there was some electronic gizmo that could do this for him.

I came up with the idea of buying a used old model Blackberry, and then running some sort of database program on it. We went ahead and bought the Blackberry for $50, but could not find database software that would run successfully on the old model.

Then I had another idea: the address book on a Blackberry is nothing more than a simple database program. All I needed to do is take the Excel file that comprises the database, and convert it to the address book format. But doing so is not as simple as you would think.

My dad gets a new copy of the database once a month. I open the file in Excel and add a title row (putting the item under First Name, Description under Last Name, etc.). Then I save it as a .CSV file. I open up Outlook, delete the old contacts, and import the new .CSV file into the contacts. I then use the Blackberry Desktop program to synchronise the data with the Blackberry, making sure to have wiped the Blackberry before I start. With 7000+ records, I usually have to run the synchronization twice. Now, when he needs to look up something. Dad just opens the Addresses program on the Blackberry and types in what he’s looking for (usually a part of the description).

Anyways, he noticed that some of the descriptions aren’t very good for searching, and so he updated the Excel file before going through the process I wrote out for him. The problem is, he’s not allowed to change the original Excel file, so every time he gets the updated one, he has to fix those descriptions. I’m trying to figure out a way to automate this process.

My current idea was to use an Unmatched query (after importing the table into Access, of course) to find the differences between the modified and unmodified Excel files from the previous month (which I’ve already done), and use some sort of query to merge that data into the new table, before convert/copying it to the Blackberry. Everything in the new table should remain the same, other than the descriptions of some of the items.

It is frustrating the heck out of me that I can’t think of a way to do this. It took a long time (and a lot of trial and error) to discover the conversion process I mentioned above, but at least I figured something workable out. I should be able to do the same here.

4

If I’m reading you correctly, what you’ll want to do is link the two tables together on a shared, unique primary key (like inventory number or some such), then run an update query on the 7k table, i.e., replace 7000.descrption with 300.description for 7000.inventorynum = 300.inventorynum.

Sorry–I don’t work in Access too often. If this were Foxpro or SQL Server 2008, I’d have it licked.

5

Here’s what I did earlier today:

Set up an Update query. This puts a line in the query that says “Update to…” Set up a criteria that says something like “[table1].[field1]=[table2].[field1]”. Make the field some identifier for the records. A primary key, if you have one. Then, in another box on the query-building grid thingy, put the to-be-updated field (the “destination field”, if you will*) as the top box, which I forget the name of at the moment. In the “Update to” box, put the origin field, using brackets like I did above.

In human, this says “Where a record on the small sheet matches a record on the big sheet, update the big sheet’s field (to be changed) to whatever the small sheet says.”

Make sense?

If I may hijack this thread a little, does anyone know a way to do this for multiple fields? I have a similar problem in that I have 65,000 blank-ish, placeholding, skeleton records and in a separate table, I have 600 of those completely filled out with 20-something fields of data. I want to plug the 600 into the skeleton records by either overwriting the blank records or by adding the complete ones, then deleting the skeleton ones that are now duplicates. The only thing the records have in common is field 3, ID number.

6

:smack: I feel stupid now! To think I actually took two courses in Access and couldn’t think of that. Gah!

7

Oh, Chessic Sense, no slight meant to you. You just got here late. Your answer is the one I will be saving just in case I forget again.