unique patient record number and grouping

[dbertanjoli]

Hello,
On my main form I have a list box with our patient names and IDs. Some patients are entered a few times (but they have same patient record number). What I am trying to do is to have a list showing patient name only once and in report (which is accessible from this form) to show all records related to this patients by his patient record number). I would greatly appreciate any suggestions.
Many thanks,
Debbie

 

[georgedwilkinson]

Aggg! I can't believe anyone designs stuff like this.

My first and best advice is: do it right! Normalize it correctly.

My second and worst advice is:
In your UN-updateable main form, use a unique/distinct query (press the "group by" button in the designer), selecting only the patient ID (I'm sure you can't promise the patient name is the same in each record, right?).

In your first sub-form, select the patient name and link it to the ID from the main form. Don't show any of the navigation buttons and make it single form view.

In your second sub-form (linked to the main form), select your record stuff in datasheet or continuous view.

I have no further advice regarding the wrong way. If you need help with the right way, post back and I'll try to help.

 

[dbertanjoli]

Hi George,
Thank you for your reply and sorry for causing you frustration
This is a bit more complicated then it sound. This is a questionnaire on line and asp is used to enable administrators to make questions on line and then make them available to patients. So one of my tables is receiving question IDs and question descriptions. This database has over 10 tables. So one patient (unique patientrecord ID) is obligated to do a questionnaire a few times a year and each time he submits it is recorded under unique response id. So now on my desktop form there is a list with all patient names and their record numbers (as they are received on line) and when I select a patient name I can pull out report from that form, for one submitted questionnaire. What we need to have is when I select a patient to be able pull a report with all his/her responses (for example the person did a survey in January then again in June, I want to see both reports in one).
Is there a way to do it?

Many, many thanks,
Deb