Design Question, location change

[Zorkmid]

I am building a database to keep track of isolated patients. Can anyone think of a way to keep track of the number of days that a patient stays in a room, and be able to change the location of the patient without creating a whole new record, while keeping a running tally of the number of days a patient has spend at one or more locations?


-Z

 

[HiTechCoach]

I am building a database to keep track of isolated patients. Can anyone think of a way to keep track of the number of days that a patient stays in a room, and be able to change the location of the patient without creating a whole new record, while keeping a running tally of the number of days a patient has spend at one or more locations?


-Z

I would use a child table related to the patient called something like tblPatientLocation. This table will use a separate record to track the date from and to at each location. It will be easy to sum up the number of days at each location.

 

[Zorkmid]

Thanks HItech, can someone tell me a little bit more about how a child table works ?

 

[HiTechCoach]

A child table is the many side of a one-to-many relationship between tables.

So you woudl have something like this:

tblPatients
- PaitentKey - autonumber primary key
- PatientlLstName
- PatientFirstname
...

tblPatientLocations
- PaitentLocationsKey - autonumber primary key
- PaitentLocation_PaitentKey -long - foreign key - link to the table tblPatients
- PaitentLocation_DateFrom
- PaitentLocation_DateTo
- PaitentLocation_ RoomKey

To get the number of days, you can calculate the days between the From and To dates and sum the value for the desired records.

 

[HiTechCoach]

This may also help:

Access Basics

 

[Zorkmid]

A child table is the many side of a one-to-many relationship between tables.

So you woudl have something like this:

tblPatients
- PaitentKey - autonumber primary key
- PatientlLstName
- PatientFirstname
...

tblPatientLocations
- PaitentLocationsKey - autonumber primary key
- PaitentLocation_PaitentKey -long - foreign key - link to the table tblPatients
- PaitentLocation_DateFrom
- PaitentLocation_DateTo
- PaitentLocation_ RoomKey

To get the number of days, you can calculate the days between the From and To dates and sum the value for the desired records.

In terms of having a form that will allow users to change the location of a patient, what you think a good way to set that up would be?

-Z

 

[HiTechCoach]

You might want to be able to do it more than one way depending on the workflow.

One method would be to use a form for Patients that has a sub form control for the locations.

 

[Zorkmid]

So that would mean pulling a form into a form (making it a subform) and having the controls that record locations inside the subform?

-Z

 

[Zorkmid]

I think I see what you mean, but I still have a few questions about how it would all work.

For example:

1. How could a user change the location of a patient and still have records that keep track of total days for specific locations?

2. How could a user bring up a summary of a patients history in the database?



Can I use a single form to populate several tables?

I have a good idea about what to do, just unsure about how to start.

-Z

 

[HiTechCoach]

1. How could a user change the location of a patient and still have records that keep track of total days for specific locations?

You would NOT change the location. The user would add every location as a separate record.

2. How could a user bring up a summary of a patients history in the database?

You could use a form with sub forms. The same for that could be used to enter the patient info and the locations.

Can I use a single form to populate several tables?

Normally a form "populates" (edits) a single table. You can use a sub form to "populate" (edit) additional table. YOu can add multiple sub forms for additional tables as needed.


Have you looked at any of the sample databases or templates from Microsoft? You might want to look at the sample Northwind database.

 

[HiTechCoach]

So that would mean pulling a form into a form (making it a subform) and having the controls that record locations inside the subform?

-Z

That is correct.

 

[Zorkmid]

Im still a bit confused here. How can a diesign this so that a user can open a patient's record and then change the location? How can I make it so that the number of days that a patient was on a certain location is measured even when the location can change helfway through a hospital stay?

Sorry, I'm just really stuck

 

[gemma-the-husky]

but in general you need to have multiple records per patient

SO
i think the easiest way is just to store the current location, and changes history for each patient

so you get

patient a, in room 123 at 6/2/09 12.02pm

when he moves you get
patient a, in room 23B at 6/12/09 8.40am

when he discharges you get
patient a, discharged at 6/16/09 2.30pm

so if he doesnt discharge you know where he should be, and its easy to build up a history of his movements from when he first came in

 

[Zorkmid]

but in general you need to have multiple records per patient

SO
i think the easiest way is just to store the current location, and changes history for each patient

so you get

patient a, in room 123 at 6/2/09 12.02pm

when he moves you get
patient a, in room 23B at 6/12/09 8.40am

when he discharges you get
patient a, discharged at 6/16/09 2.30pm

so if he doesnt discharge you know where he should be, and its easy to build up a history of his movements from when he first came in

I see, but how can I go about altering the location without losing the original location permanently?

 

[gemma-the-husky]

but in general you need to have multiple records per patient

i said at the outset, you need to store a patient location history

its just a matter of finding the most efficient way

i just thought oyu dont actually need a startdate/end date

a patient is just wherever he is put, until he is put somewhere else - if you see what i mean

 

[Zorkmid]

i said at the outset, you need to store a patient location history

its just a matter of finding the most efficient way

i just thought oyu dont actually need a startdate/end date

a patient is just wherever he is put, until he is put somewhere else - if you see what i mean

Anyone have ideas on how this might be accomplished???

 

[khawar]

I think you have been told the solution already
You have to use main table for patient information and child table for locations

enter date transferred to and transferred from room for each record except for the last location where transferred from will be null from which you can show the current room number of patient
and show the history and days of stay (calculated field) in the sub form

 

[Zorkmid]

Im still not really grasping the concept of how MS access is going to keep track of when the location is changed without opening a separate record for each stay.

 

[Zorkmid]

I understand how I could calculate the number of days spent in a certain location, but how are these linked back to the patient?

 

[Zorkmid]

I also have other factors that can change for each patient, like the type of isolation precautions they are on, or the type of infection they have, will one child table work for all of them? Or do I need three? I cant seem to find any info online about setting up a master-child table relationship, beyond just the general stuff. Im really stuck, sorry.