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

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).]

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

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).]

Once again beaten in the submission contest http://www.access-programmers.co.uk/ubb/wink.gif

Alex

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

Jennifer

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).]

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

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

Come along, you belong...

David R (hiding his post count in shame)

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

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

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 http://www.access-programmers.co.uk/ubb/smile.gif

Jennifer

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

At least, it gives us a chance to compete a few months in a lifetime. http://www.access-programmers.co.uk/ubb/smile.gif

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).]

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

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).]