Forms Design

[Zorkmid]

I've got a question about forms design.

I have a database that keeps track of isolated patients (infection purposes) and that gives the location, name, ID# of the patient etc. I'm brainstorming ways that I can design the forms.

There is a master and child table, the master table (tblPatient) keeps track of the patient names, and the child table (tblCase) keeps track of their location (a new record for each movement of change in infection)

For example, when and infection control practioner gets wind of a new infection, I want them to be able to search by name, or patient number, or a combination of both, to see if that patient already has been at, or is still currently at the facility. I would then like to have a subform that brings up the most recent case if that patient is currently at the facility, or a fresh subform if that patient has a tblPatient record, or if the patient is brand new, a form that will allow the user to create a master and child record.

Is this a good idea? Is there a better way? How do I go about doiung this.

One hirdle that I see in this idea is what happens if multiple master records are returned?

-Z

 

[wazz]

sounds like you to need to look for people first so start with a simple search form; search by name or id. use list boxes (not multi-select) or combo boxes.

for each person:
Q. do they exist (are they on the list?)
no:
-> go to patient entry form
yes:
-> look up most recent check-out date.
--->Are they still here?
----->yes: show patient form with current record on subform.
----->no: show patient form with new record on subform.

 

[Zorkmid]

I understand how to search for patients to see if they are in the database. I'm not sure how to set it up so that I can determine which patients are currently present, any ideas?

-Z

 

[wazz]

how do you want to do it, theoretically? forgetting about the database for a sec, how do you want to determine if someone is there? how are you currently doing it? a check-out date? by checking to see if someone is in a room or not??

 

[Zorkmid]

how do you want to do it, theoretically? forgetting about the database for a sec, how do you want to determine if someone is there? how are you currently doing it? a check-out date? by checking to see if someone is in a room or not??

Well, I want to have a button for "closing the case" whenever a patient is moved, changes precautions, or leaves the hospital or expires. This button will insert the "now()" date into an EndDate field that I'll use for some total days calculations. I want to leave the EndDate field blank (or better yet have it display the current time and date so it will automatically update total days in my queries)

So in my original search form, lets say a patient's name exists in the database, I want to somehow check the EndDate field of the latest case to determine if the patient is present or not. I hope that makes sense.

-Z

 

[wazz]

Well, I want to have a button for "closing the case" whenever a patient is moved, changes precautions, or leaves the hospital or expires. This button will insert the "now()" date into an EndDate field that I'll use for some total days calculations. I want to leave the EndDate field blank (or better yet have it display the current time and date so it will automatically update total days in my queries)

i think it would be best to leave the EndDate blank until one of the circumstances you described actually happens. you can still calculate how many days have passed for the current...phase?... by Now() or Date() - theStartDate. leaving the EndDate blank might be clearer. (so, including a StartDate for each phase would be very useful, if not mandatory. when a patient enters, a startdate would be entered. when a patient is moved or changes precautions an EndDate would be entered for that phase and a StartDate entered for the next. i think this is what you are suggesting.)

So in my original search form, lets say a patient's name exists in the database, I want to somehow check the EndDate field of the latest case to determine if the patient is present or not. I hope that makes sense.

you can then check to see if the end date is Null; that will tell you if they are still there or not.

if there's some chance that you don't have all of the EndDate data available for every phase of treatment then you would have to try a different approach, but then there would be several questions/possibilities.

 

[Zorkmid]

i think it would be best to leave the EndDate blank until one of the circumstances you described actually happens. you can still calculate how many days have passed for the current...phase?... by Now() or Date() - theStartDate. leaving the EndDate blank might be clearer. (so, including a StartDate for each phase would be very useful, if not mandatory. when a patient enters, a startdate would be entered. when a patient is moved or changes precautions an EndDate would be entered for that phase and a StartDate entered for the next. i think this is what you are suggesting.)
you can then check to see if the end date is Null; that will tell you if they are still there or not.

Yes, although I am trying to brainstorm a good way to "close the case" that will be easy to do and create as little confusion as possible.

-Z

 

[wazz]

i understand. it sounds tricky. and it sounds fairly crucial to the point at hand. toss some ideas around with relevant people, considering real world scenarios. how does one know if a case is closed, currently? what different/extra info would help? if necessary, a yes/no field might be your only/best option. experiment with some test data/forms and get some feedback if possible; it might help.

 

[Zorkmid]

Any thoughts on how I have a form display the most recent Case (child record) when I search for a patient name or ID# (master record)?

 

[wazz]

i think all approaches would be unreliable if you don't have something to identify the most recent case like a date or a checkbox.

one thing you could do is create a query with the fields you want, sort the ID field DESCENDING then in the 'Top Values' box, enter 1 (or type SELECT TOP 1 ... in Sql view). that will give you the last entry in the table. but it won't necessarily give you the current case. if someone went in to the table and entered the most recent case over top of the first case and saved it, then retrieving the last row of the table would be useless. and ID numbers (AutoNumbers) are not necessarily stored the way you might expect. this might work for most entries though.

 

[Zorkmid]

Well, I'll have the patient name, and I want to use the EndDate field. I'm just wondering how I can get that Record to pop-up, the technicality of it. I'm not good at access, but am starting to understand DB design better

 

[wazz]

see if this sample helps at all.

 

[Zorkmid]

Thanks,

SO you build a query and then pull that into the search form? Any idea how I can limit the results to the most recent "problem".

-Z

 

[wazz]

right. change the 'final' query. add criteria to the end date field: Is Null.

 

[Zorkmid]

Hmm, strange, when I do that the form fails to yield any results for Jenny, Forrest or Bubba

 

[wazz]

just to be sure, it's qryPatientCases; enddate field: criteria: Is Null.