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.