none
Creating a vlookup function in Access 2010 RRS feed

  • Question

  • Hello,

    I have experience in Excel, but am trying to teach myself in Access. My boss has asked me to start processing reports using Access rather than in Excel. What I need to do is create a macro (or query) which will run a vlookup the way you can between two Excel files.

    Multiple reports are merged into one associate productivity report, but I need to add associate information to the each of the original/raw files. I setup multiple tables, one is labeled Main_Table and will have large amounts of data appended to it each month. I've also setup separate tables for each of the raw files, and a Names table which has the associate information I need to pull in. FYI the data on the Names table that I need to pull over is all text.

    Question 1 - would it be better to create a macro to pull in the associate info on the individual raw tables, or should I append all of the raw tables onto the Main_Table then run a macro to add the assoc info?

    Question 2- how do I accomplish pulling the associate information into the table(s)?

    To make things more complicated, the raw reports I'm merging are from different systems and my company has slightly different versions of some associate names between the systems, which I need to cleanup before I do the vlookup. I have a unified name column on the Main_Table (and Names table) which is where I want the correct name to go, then based on that column I need to pullover their departement name, manager name and employee level.


    Lorac1969

    Wednesday, July 27, 2016 1:09 PM

Answers

  • One major thing to get your head around is that Access is not Excel on steroids. It's a RDBMS and the sooner you work within its framework (leaving behind everything you've learned about Excel), the better.

    Working in Access starts with a correctly designed database. Creating reports cannot be your concern right now, only designing a correct database, with all the quality markers that entails: normalization, PK, relationships, unique indexex, required fields. As a rookie this is the hardest thing to get right, so post your questions and attempts here and have us critique them. Only after that part is done can you start thinking about how to import data, how to write queries, and create reports. This is not the work of a lazy afternoon.


    -Tom. Microsoft Access MVP

    • Proposed as answer by David_JunFeng Friday, August 5, 2016 8:51 AM
    • Marked as answer by David_JunFeng Friday, August 5, 2016 9:37 AM
    Wednesday, July 27, 2016 1:47 PM
  • follow the wise advice of TvS' post.  In general your description of your use of vLookUp in excel sounds like a relationship join in an RDBS.  For instance I have an ID in the parent table.  My child has his ID in the children table and his record includes a field for my ID and it is the parent ID field that links the two tables together.

    This RDBS join between table is infinitely more efficient than firing vLookUps constantly.

    having said all that; in vba there is a DLookUp and much of the vba is common or at least very similar between Access and Excel - but you're starting point really is splitting those excel files into proper 1:Many tables.

    • Proposed as answer by David_JunFeng Friday, August 5, 2016 8:51 AM
    • Marked as answer by David_JunFeng Friday, August 5, 2016 9:37 AM
    Wednesday, July 27, 2016 2:09 PM
  • Hi Lorac1969,

    According to your description, if you store your data in Access and connect to it from Excel, you gain the benefits of both. Here are ten reasons why a marriage between Excel and Access makes lots of sense. Excel users, don't be afraid to let go of your data. Think of it this way.

    So I suggest that you could use Access with Excel.

    For more information, click here to refer about Top 10 reasons to use Access with Excel

    • Proposed as answer by David_JunFeng Friday, August 5, 2016 8:51 AM
    • Marked as answer by David_JunFeng Friday, August 5, 2016 9:37 AM
    Thursday, July 28, 2016 7:43 AM

All replies

  • One major thing to get your head around is that Access is not Excel on steroids. It's a RDBMS and the sooner you work within its framework (leaving behind everything you've learned about Excel), the better.

    Working in Access starts with a correctly designed database. Creating reports cannot be your concern right now, only designing a correct database, with all the quality markers that entails: normalization, PK, relationships, unique indexex, required fields. As a rookie this is the hardest thing to get right, so post your questions and attempts here and have us critique them. Only after that part is done can you start thinking about how to import data, how to write queries, and create reports. This is not the work of a lazy afternoon.


    -Tom. Microsoft Access MVP

    • Proposed as answer by David_JunFeng Friday, August 5, 2016 8:51 AM
    • Marked as answer by David_JunFeng Friday, August 5, 2016 9:37 AM
    Wednesday, July 27, 2016 1:47 PM
  • follow the wise advice of TvS' post.  In general your description of your use of vLookUp in excel sounds like a relationship join in an RDBS.  For instance I have an ID in the parent table.  My child has his ID in the children table and his record includes a field for my ID and it is the parent ID field that links the two tables together.

    This RDBS join between table is infinitely more efficient than firing vLookUps constantly.

    having said all that; in vba there is a DLookUp and much of the vba is common or at least very similar between Access and Excel - but you're starting point really is splitting those excel files into proper 1:Many tables.

    • Proposed as answer by David_JunFeng Friday, August 5, 2016 8:51 AM
    • Marked as answer by David_JunFeng Friday, August 5, 2016 9:37 AM
    Wednesday, July 27, 2016 2:09 PM
  • Hi Lorac1969,

    According to your description, if you store your data in Access and connect to it from Excel, you gain the benefits of both. Here are ten reasons why a marriage between Excel and Access makes lots of sense. Excel users, don't be afraid to let go of your data. Think of it this way.

    So I suggest that you could use Access with Excel.

    For more information, click here to refer about Top 10 reasons to use Access with Excel

    • Proposed as answer by David_JunFeng Friday, August 5, 2016 8:51 AM
    • Marked as answer by David_JunFeng Friday, August 5, 2016 9:37 AM
    Thursday, July 28, 2016 7:43 AM