Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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....
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •