New to Access, need help with mapping

Question

Hello,

I have been working with excel for a while now and the data is now too heavy to run an efficient vba macro. 

I have a data table with the following fields, many of which have duplicate entries: Policy Holder Name, Policy Holder ID, Policy Number and Account

The account field has some entries that are classified as UNMAPPED

I have a mapping table with the following fields: Policy Holder Name, Policy Holder ID, Policy Number and Account description

What i was able to achieve in VBA was updating the unmapped accounts by mapping them first with a policy number. If there is not match for policy number in the mapping table then match the account by policy holder ID. Any entries in the data table without matching numbers or IDs are left as UNMAPPED.

However the data table is now over 200,000 lines and the code : (a) takes too long to run, (b) causes excel to hang

I was curious to know if theres a way to port over the code into access or any similar procedure that can be done. I am vaguely familiar with Left Joins and attempted a solution but didnt achieve the results I wanted. The resulting table was duplicating several rows. I would appreciate any help or indication as to how to begin tackling this in Access. Thanks in advance

Answer 1

Hi,

If you perform this operation in Access, you might not even need any code. Importing both Excel table into Access tables, you should be able to use an UPDATE query to map the accounts.

You could probably do it in one pass, but probably safer to do it in two passes.

The first pass is to map using the policy number, and the second pass is to use the policy holder.

Hope it helps...

Answer 2

Ok. And i can do this to just the unmapped accounts? i dont want to affect the accounts that are mapped already.

Answer 3

Ok. And i can do this to just the unmapped accounts? i dont want to affect the accounts that are mapped already.

Yes, you can. When you create a query in Access, you can include a criteria to only update the records matching your specified condition, which in this case, I suppose, would be something like "WHERE [Account] Is Null."

Answer 4

Gonna give it a try. Thank you for the quick response!

Answer 5

Hi,

You're welcome. Let us know if you get stuck. Good luck!

Answer 6

Hi SantiagoA,
Has your original issue been resolved? If it has, I would suggest you mark the helpful reply as answer.
If not, please feel free to let us know your current issue.
Best Regards,
Terry

Answer 7

Hi, i was able to resolve it using Excel VBA. Couldn't figure out how to join the information as needed

Answer 8

Hi, i was able to resolve it using Excel VBA. Couldn't figure out how to join the information as needed

Hi,

If you used "+" in Excel to join the parts together, you could do the same using "&" in Access.

Actually, + would work too in Access, but & is preferred.

Cheers!