I am considering converting a business customer database to Access as it has gotten quite large and unwieldy otherwise. One concern that has cropped up is accessing the data from Excel. For example, say I have a Totals sheet in Excel that can auto-fill customer information when a valid account number is typed by using VLOOKUP to draw the data from another Excel sheet. I am having difficulty finding a similar function for Excel to pull specific data from Access. Is there a way to do this?
31 Answer
Have you tried using queries in Access for both sets of data? Can you import the files to Access to perform the work? The query in Access will perform the same function as a vlookup formula and can be linked on more than one field to ensure more accurate answers. You can then use another query to extract the errors fund such as the N#A and other errors found in Excel and include them in another table (this is like a file in excel).
Using the Excel vlookup function searches for data in two worksheets or files of two sets of data. Access is better in that as long as you have unique fields, you can build these to add and extract data from any number of tables of data.
You can also then create queries that will extract whatever details for customers you want by listing those customers in another table which can be imported from excel to extract such detail. I have spent 13 years writing very complex queries between multiple tables the writing macros to include the queries in one procedure for a certain process.
Would like to see what data you have and what you want as the outcome. One last suggestion is that although Access is great for analysing data you do not have the functionality of Excel for many actions taken. The analysing can be done in Access in checking between reports and extracting data then exported to excel for refining and formatting the report .
Hope this helps
