Unanswered: How to Import Spreadsheet and filter data into a query
Hi guys, I'm new to the forum and just looking for a bit of help. I'm using Access 2007. My boss wants me to create a database to simplify his new tasking.
He gets a monthly spreadsheet of 800 individuals. The spreadsheet has fields such as name, doctor, medications, medication date, etc.
What he has to do is first determine which individuals are cancer patients and those who signed a waiver. He'll also mark each one off as he reviews them and not require another review. This is a manual process, but this will eliminate a large % of individuals he has to review on the list. The problem is, he'll get a new list with all the same patients and more next month and so on.
I'm trying to figure out a solution for him and was wondering if maybe I could create a query with cancer patients, waivered and already cleared patients, then possibly import the spreadsheet each month and spit out a query of all the uncleared individuals. I'm trying to give him a workable list.
Do any of you have any suggestions or recommendations? My knowledge with Access is intermediate and I don't have any VBA knowledge.
Brian, I have worked with client lists (Access 2003) and I suggest that you determine that the Excel data can be used in a database. If your only identification of a client is a name, such as "John Smith", then you will not know with confidence if last month's John Smith is the same person as this month's John Smith.
If you do have a unique patient id and the spreadsheet columns are in good order, it should be easy to either link it to your access database, or learn how to use Access VBA code to import the spreadsheet into a table each month. If this were my task, I would ensure that the spreadsheet was created with the intention of using it in an Access database, and make sure the column headings and data were always in the same format.
Assuming that patients can be identified uniquely, you will be storing historical data in a table, and with a query or queries, comparing the new data with the old to identify uncleared records. During development you will want to verify that the columns of data in Access match the data in the spreadsheet, and that you have the same number of rows in Access.
My method would be to import the Excel spreadsheet into a table, run a query to put uncleared ids with all the fields into a separate table, then export that data into Excel. If you want to automate this task and are not prepared to learn VBA coding, you could work out the steps with Access macros.
Learn how automate with Access to make the boss's job easier, and you will become more indispensable than you are now. Remember to check your work for accuracy and reasonableness.
Jerry, thanks so much for the response. I forgot to mention that I do have unique ids for each patient. I'm going to take your advice and try to implement this using macros. Thanks for all the great information.