DLookup Question

[cheiboy]

In my database I have a table with 726 Providers that are updated each week. The changes come to me in an excel spreadsheet. Each week, I skim through the list and I'm having to do a update query to make any changes to the Provider ID numbers if I see anything. So, one week I'm working on Provider ID numbers for physician, then the next week I'd be checking on Hospitals and so on. The providers that are listed in my table is only a fraction of the list that I receive. Is there any way to simplify this process.

Thanks much.

SJB

 

[cghoga]

if you are strictly going off the list in Excel, and when you find discrepancies in Excel, updating your table, I can see how this would get tough.

this may sound weird, but it may save you a lot of time and improve quality and efficiency.

try creating an ODBC connection to the Excel file. When you get the file each week, save it to a standard location so that the link remains.

i'm assuming that their is only one Excel file. if you had various Excel files coming in that had nothing in common as far as data was concerned creating a link would not work.

but if you are going off the same Excel file each week, just different/updated data, i would look into creating an ODBC link to the Excel file.

this way your data remains up to date and exact. and you can run queries, forms, and reports just as if the table was in Access.

and if you want the table in Access, there are ways to create a table based off the linked table on the fly via a VBA proc.

if you decide to try this, and you need help creating the ODBC link let me know.

hope this helps you.