Complex Relationship Setup help

[Leonidsg]

Hello,



I have another question if you all don't mind. I have a few relationships setup as follows:


Using this setup I have a FirstVisit form with several subforms including PSH = past surgical history form and medications form.This works great because I have the subform setup in continuous therefor I can enter a patients PSH and Medications on my first visit for. What I would like to do is make another form called FollowUp form and have the Medications and PSH subforms also part of that form, that way when a patient has a follow up appointment I can continue to update the Medications and PSH table. The way I have it setup right now is a form where the main form form belongs to FirstVisit, I am just using the a few fields from the FirstVisit table and then 3 subforms called PSh, Medications and FollowUp. Everything is united via the MRN = medical record number which is the Primary key in FirstVisit and the Foreign key in MEdications, PSH, anf FollowUp.

Hope that makes sense, thank you again for everyones help.

Lenny

[June7]

Think I would do different structure.

Instead of separate tables for type of visit (first, followup) suggest a table of PatientInfo and a table of VisitsInfo (date, reason, recommendation) and maybe associate medications with the visit record.

[orange]

I agree with June7 re the tables. Your jpg doesn't show all tables fully open/showing, so we can't comment on some of the fields in the tables. I could see Date being important in the tables.
Wouldn't subsequent Visits be FollowUps??

[Leonidsg]

Here is a better screen capture:
I kind of understand what June7 and Orange are saying. Essentially my FirstVisit is my patient information, and any demographics about the patient. The FollowUp, will as Orange said be every subsequent visit. I could link Medications and PSH to the FollowUpID. I would probably change to name to VisitID. I think I will still have a couple of problems. 1: For the first visit table wouldn't I need a form where the Patients being the parent form with a subform being the Visits and that subform having its own subforms for PSH and medications; or would I want to input patient info into one form and save that info and then open another form with Visits being the parent and the PSH and Medications being the subforms making it only a 2 tier hierarchy, I think the latter makes more sense correct me if I am wrong. 2: after a patient has their first visit I will fill out the patients table and the child tables including: Visits, Medications, and PSH. When the patient visits the offiice the next time I want to open the visits form and in the subforms see all medications and all PSH not just the one from the current visit. I hope that makes sense. Thanks again for everyones help

Update: here is what I changed it to:

Thank you
Lenny

[Leonidsg]

Hey June7 and Orange, so I setup everything but I would like to be able to see Medications and PSH that correlated to the previous visit. I think because I have it setup that they relate to a particular visit I am unable to see them when I entering a new visit. This is why I was thinking linking them the the patient table and not the visit table would be best. Any suggestions.

Thank you,

[June7]

Is it important to know which visit (complaint and diagnosis) initiated the prescription?

I am sure there would be a way to show all prescriptions on the form independently, perhaps in a listbox that has RowSource query joining the 3 tables. This would be for informational display, not for data entry/edit.

[Leonidsg]

It is not important to know what medications go along with each visit. I just want to see a running list of medications. The reason I want to see the medications from the previous visit is because on the next visit I may want to put an end date for an older medication that they were on previously.

[June7]

Then have a go with a Medications table associated with PatientInfo.

[Leonidsg]

Okay perfect. thank you

[orange]

I agree, and would suggest you set it up as a test.Once you get your table "designed", I would set up a question along--
What medications/prescription did Patient X get on their previous visit?

Work through a query or 2 to attempt to get an answer. You will soon see if there "is something missing" (can't get that date from here; not enough info for Patient... or whatever)

Bottom line is do some testing and refine the logic using a query before getting too hung up on forms etc.

KISS-- make it work, then make it pretty.