[Query] Information about a list of names from Excel

[Usernamee]

I am a relative newbie when it comes to Access. For my job I am supposed to "data-mine" a 5000+ name list of medical records to find information on ~2500 of the people on this list. To clarify, I have an excel file with around 2500 names and information fields which need to be populated by numbers from corresponding fields in Access. To manually do this, I would have to sit at my computer for weeks on end doing mind-numbing copy/paste .



So basically, spreadsheet 1 (access) has all of the information I need to query, just filtered through the names on spreadsheet 2 (excel).

Does anyone know the best route to tackling this problem?

(sorry if this has already been explained somewhere else on the forum, I searched but don't exactly have a good Access "vocabulary" yet)

[orange]

You seem to have some familiaity with Excel and not too much with Access.
General approach:
If you plan on working with Access;
--Import your excel list into an Access table
see http://office.microsoft.com/en-ca/ac...010272247.aspx
-using the Access table and your "new Table", create one or more queries to find/match records.
If you are dealing with Names from Excel, and trying to match with numbers from Access (assuming these numbers are unique identifiers for people), then you will need other info to ensure John Smith from your List is the very same John Smith in Access with the unique identifier. You will need additional info , such as address, phone number, DOB... somethings to ensure you really are matching the same person.

Good luck with your project.

[cb9604]

To add to orange's comment, the easiest way to create a query that accomplishes your purposes is to choose "Query Design" from the CREATE subset of tools. Choose your two tables that you imported into Access, they will appear with your column names in two separate boxes. Choose which of the two tables that has all of the information you want by double clicking the *, or you can also customize which fields you want to appear by choosing them from the boxes.

Now find the field from both tables that matches. This MUST be a unique record, such as a patient number, or your query will subject to errors. I would not recommend matching on First/Last name, as orange noted, there may be multiple John Smiths in your table that are not the same person.

Click and hold down on the field name in the box that you have chosen, and drag it over to the matching field from the other table. A black line will be drawn connecting the two fields.

In the top left you will see two buttons. Click "View" and choose "Datasheet View" or "Run" with the large red "!". Either will work. Your query will now display only the records where the two fields you connected match. You can then export this query back to Excel if you want, or use it to create a table in Access, or simply save the query itself to recover it later.