merging records – 2003

Topic

New Reply

I have an excel sheet that contains patient information. Such as

patient ID, First, Last, test score

A patient could have more than one record because of various test. I want to merge all the records for that particular patient and make one record.

So, I would have HDL score, LDL score, CBC score…etc.

How can I do this?

Reply | Quote

Replies

I presume you want to create that single record for the patient in Access. If so, link to the Excel worksheet, and then using that as your data source, run a series of update queries, one for each score, that take the selected score and update that data in the Access record. Of course step 1 is to create the record in Access to begin with. And there needs to be some sort of unique identifier that you can join on to get the right data linked to the right Access record. What you are doing is essentially denormalizing the data to get a single record. Is it possible that a patient might have two or more of the same test records in the Excel worksheet? If so, that may complicate things – my wife has been having blood tests every 4 hours for that past several days….

Reply | Quote

I think a union query will work for me but I am not sure how to create it.

I have a table of patients name and id number. 1 record.
I have a table with patient name, type of test and score. So, a patient could have 3 records or more.

I want to create a table with patient name, test score 1, test score 2, etc…..one record for the patient.

Reply | Quote

You can create a crosstab query based on the tests table. Use the patient name as row header (with Group By in the Total row), the test type as column header (also with Group By in the Total row) and the score as data field (with First in the Total row, or Average if there could be multiple scores for the same test).

Reply | Quote

I don’t think a union query will help you. That gives you the same effect as just selecting all the test score records. First you need to create a record that has a field for each kind of test score, and also includes the Patient’s name and ID number. Then run a query that selects all of the LDL scores (for example), and join that to the record that contains each type of test score using the Patient ID. Now turn that into an update query and update the test score in the LDL field with the LDL test score from the test scores table. Repeat the query for each of the other test score types.

But if you have multiple results for a patient for a specific test score, you will have an issue.

Reply | Quote