to subform or not to subform

[Katy88]

The purpose of my database is to track patients who are on a waitlist for surgical procedures (a very timely problem here in Canada). I have a table which has several fields for basic patient demographic information, then a field for the procedure the patient is listed for (selected from a list of procedures in a separate table), then fields for information pertaining to this patient's situation vis-a-vis the procedure (i.e. date entered on the list, estimated length of hospital stay and est. length of time for procedure, a comment field). Instead of using the patient's unique chart # for the primary key, I used an autonumber, because it is conceivable that a patient who is already on the list for one operation, might be listed later for a totally different operation before they've had the first one. That would be a separate record, with the patient's demographics being entered twice.
Are you still with me...?
I have a query which, in addition to the above info, also calculates the persons current age, and the length of time they have been on the waiting list.
I started with one form, with all the query fields on it, but I'm realizing that this is too cumbersome in the case of a pt waitlisted twice.
Is a subform the answer? Should I have one form for the patient's demographics and a subform for the procedure and possibly an additional procedure if they need it?
I will be needing to query info in both areas - patient demos, how many are waiting, for how long, etc.
Thanks for any help you can send my way.
I really apologize for being so longwinded!
Katy 88

 

[JCross]

Hi Katy 88 -

If you have one table with all of the patients demographic info, and then another table with the waitlist info (possibly more than one record per patient) - then store the PaitientID as FK in the Waitlist table.

Next Create a form/subform. The wizard is good just to see what it COULD look like. Then you can always change it. When creating a form with the wizard choose all the demo. info. from the patient table, and all the waitlist infor from the waitlist table. Then choose the form/subform option. What you will end up with is a form with the demo. stuff on top - then a datasheet (changeable) type subform with ALL waitlists for that patient. If you switch the subform view to 'form', it will show one at a time, 'continuous forms' will show them all again but as forms and not datasheet.

hope this helps.

Jennifer

[This message has been edited by JCross (edited 03-12-2002).]

 

[JCross]

ooops - just noticed you had a query as a recordsource...sorry! same concept, though. base the subform on the query with the main form being the patient demo. info.

Jennifer

 

[Alexandre]

Are you still with me...?

In fact you lost me exatly at this point. I believe your justification for using autonumber in not correct. you should keep using the unique identifier (chart #) you have, and change your structure:

tblPatients (NO info regarding processes: this is a different topic)
-Chart# Primary key
-First Name
-Last Name
-Sex
-Age

tblProcesses (your lookup table for processes)
-ProcessID Autonumber primary key
-ProcesName
...

tblPatientsProcesses
-PatientProcessID Autonumber primary key
-Chart# Foreign key
-ProcessID Foreign key
-EnteringDate
-EstimatedStayPeriod (Or Leave/EndOfProcess Date)
-Comment
...

If a patient can never be listed twice for the same process, you may want to make Chart#/ProcessID a no duplicate index (Or Chart#/ProcessID/EntereingDate)

If you use an estimated period of stay, instead of an actual leave date, you may need another field do indicate the status of the process (current/over...). Because you will need a mean to distinguish between ongoing and terminated processes.

Relations:
tblProcesses: 1___Many: tblpatients: 1___Many: tblPatientsProcesses

THEN, you will find that a subform is the logical way to go to display the various process for one patient (Main form for the data in tblPatients)

Alex



[This message has been edited by Alexandre (edited 03-12-2002).]

 

[Alexandre]

Once again beaten in the submission contest

Alex

 

[JCross]

Just trying to see where I can help! (and probably not doing a very good job of it)

Jennifer

 

[Alexandre]

No complex to have. Your contribution is relevant and more concise than mine.

Alex

[This message has been edited by Alexandre (edited 03-12-2002).]

 

[JCross]

Thanks Alex -

Posting really just gives me a (poor)excuse not to work on my own frustrating project. heh. and look! i made 'member' status! woohoo!

Jennifer

 

[David R]

Well, now you've done it. There's no escape now.

Come along, you belong...

David R (hiding his post count in shame)

 

[JCross]

Now I see what everone meant when they told me that the junior/member thing was a joke........and I thought i'd find myself suddenly enlightened. bah!

heh.

Jennifer

 

[David R]

I think I was just insulted, but I'm not sure.

 

[JCross]

no no no!!!! i'm self effacing and bad at it.
i'm so tentative about posting any ideas i come up with....not that i have many. and so many of you members are SO VERY HELPFUL. it's too bad i didn't hit the magic number and suddenly become better at all this stuff!

did you know that they have found that pregnancy actually diminishes a womans brain size temporarily? did i mention that i'm 7 months pregnant

Jennifer

[This message has been edited by JCross (edited 03-12-2002).]

 

[Alexandre]

At least, it gives us a chance to compete a few months in a lifetime.

Alex

BTW, when you find the magic number, tell me because so far I ve been obliged to learn.

[This message has been edited by Alexandre (edited 03-12-2002).]

 

[Katy88]

You guys are good!!
Thanks, will let you know how it works.
Alex-further to your suggestion, I will be needing to track outcomes and ACTUAL times etc. for each patient/procedure after their surgery, so I'm thinking that with the use of a 'ProcedureCompleted' toggle, they will become part of another table into which this post-procedure info will be entered. What do you think?
Thanks all, for your input,
Katy

 

[Alexandre]

I d be tempted to say yes, since a basic rule to identify the need for a new table is : is this a different topic?
Now, if you provide more info about what you mean by outcomes / actual times (of what?), etc. we may give a more accurate answer.

Alex

[Re reading, if you mean 'outocomes and actual times of processes for each patient, then the answer is more likley no, still according to the above mentionned tip. More details please]

[This message has been edited by Alexandre (edited 03-12-2002).]