none
New to Access, need help with mapping RRS feed

  • 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

    Friday, July 28, 2017 9:35 PM

All replies

  • 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...

    Friday, July 28, 2017 9:41 PM
  • Ok. And i can do this to just the unmapped accounts? i dont want to affect the accounts that are mapped already.
    Friday, July 28, 2017 9:45 PM
  • 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."
    Friday, July 28, 2017 9:47 PM
  • Gonna give it a try. Thank you for the quick response!
    Friday, July 28, 2017 9:56 PM
  • Hi,

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

    Friday, July 28, 2017 10:03 PM
  • 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
    Tuesday, August 1, 2017 7:39 AM
  • Hi, i was able to resolve it using Excel VBA. Couldn't figure out how to join the information as needed
    Thursday, August 3, 2017 1:38 PM
  • 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!

    Thursday, August 3, 2017 2:37 PM