How to Use Recorset and Loop

[bob bisquick]

I am trying to do the following:

I have a table with patients and medications they are on. One record per med per patient. I would like to make a table that has 1 record per patient, with a field for the patient key, and a single field that has a list of all the meds the patient is on.

I think I have to look at a patient, loop through each of his or her records and add each med to a field. I believe this involves creating a record set, and using a loop, both of which I have never done before. I am trying to hack something together using Access help but am having problems. Any advice?

 

[Mile-O]

I would like to make a table that has 1 record per patient, with a field for the patient key, and a single field that has a list of all the meds the patient is on.

But that would go against the rules of normalisation.

Since you have a many to many relationship here what you really want is one table for patients, one table for medications, and a third table (containing only the key of the aforementioned tables) to simulate the many-to-many join.

On a form you can use a form/subform combination to list all the medications for each patient.

 

[bob bisquick]

Ah. I have those things. A table for patients, a table for meds, and a table with meds prescribed, linked to patients and meds by Primary Key. I am trying to make a report that lists each patient and what meds they are on.

 

[bob bisquick]

Ultimately, I need a big table to export, that has one record for each patient, and a whole bunch of information about the client that could be imported into SPSS. I had thought of making one field per medication, which I guess would also work. You know med1, med2, etc... But I still am not sure how I would do that either...

 

[Rich]

Article ID: Q141624

 

[bob bisquick]

Bingo. Thanks a lot.