Clinic database - convert yes/no boxes to sortable field for report? (1 Viewer)

Hi everyone,

I am a novice-almost intermediate Access user, designing a database for a single clinic, to track their patient caseload - patient status, caregiver responsible, referral details, program accepted into, etc. It will be updated multiple times daily, by multiple people, from multiple computers (eventually possibly some off-site accessing through a browser, I believe). I have created forms, but am now working on reports (how many in each program, how many for each caregiver, etc), and the database is in testing. It needs to go live in 2 weeks.

The hardest part right now is this:

- There are 9 different patient status options - referral received, triage completed, initial visit completed, etc, right up until discharge.
- Each patient can only have 1 status at once, so it would make sense to have status as a single status field with 9 options, BUT...
- Each time a patient's status is updated, the database needs to have an auto-complete date stamp when the new status box is checked (and they need to be able to manually over-ride with a new date if needed) so that we can track their progression through the various status changes.
- using an option group on a form, with status as a single field with 9 options, I could not figure out how to get an auto-complete date field to save when updating a patient to a new status, and the date stamp also has to remain in the database, even when the next status is selected.
- Therefore, I made each status a Yes/No checkbox, so now I have 9 separate fields
- I need to create many reports, one of which reports by patient status.

So, am I missing something? It seems like such a convoluted way to do this - 9 separate yes/no boxes for status, just so that we can save date stamps each time a status is updated. How do I sort by status on my reports, if they are all yes/no checkboxes?

If I really have to keep it as 9 separate Yes/No box fields, do I somehow assign a numerical value to each status (1-9), and create a separate field that records the highest numbered status currently checked for that patient, and then use that to sort the report by?

Thanks in advance for any assistance you can provide. It would be greatly appreciated.

Time to take a few steps back and get the table structure right. The development process in Access should be this:

Tables then Reports then Forms.

I would use that approach going forward from here. Let's fix your tables, make sure we can generate the reports you need from those tables, then worry about input forms.

From a table perspective you need a whole new table to store patient stati. It would be structured like so:

PatientStatus
ps_ID, autonumber, primary key
ID_Patient, number, forieng key to Patient table
ps_Status, text, stores actual status patient is in
ps_Time, date/time, stores date/time patient entered that status

That's it. Those 4 fields can now hold every status of every patient. With data in it, it will look like this:

ps_ID, ID_Patient, ps_Status, ps_Time
1, 3, Referred, 1/1/2016 8:00 am
2, 4, Referred, 1/4/2016 9:00 am
3, 3, Initial Visit Complete, 1/7/2016 1:00 pm
4, 3, Discharged, 1/7/2016 2:30 pm

That data says you have 2 patients, one that has yet to visit (ID_Patient=4) and one that has been discharged (ID_Patient=3).

To find the current status of a patient you build a query to find the last ps_Date of each patient, then you build another query on top of that one to find out the status of the patient at their last ps_Date.

Just so we catch everything, could you post a screenshot of your relationship window?

Thank you so much, plog. I'm working on that. I have only one table right now, so no relationships to speak of. I'll figure it out and post, though it might be later from home. It looks like this will create more of a log with multiple records for each patient, which is a different structure for me, but one that makes sense. It will just take me a while to get fluent in working with this.

But will I still be able to display the status change date/time on the form, beside each status, so that they can see what time referral was checked, what time each other subsequent status was checked, or will this just show up in reports? I suppose I can link a report from the form, if people really want to see the progression of each patient.

I am good at creating single tables and beautiful forms, but less familiar with how to set up relationships, queries and reports. I foresee many tutorials in my future.

OK that actually was pretty quick. Here's the link.

It never actually says anywhere "foreign key" (I am working with Access 2007), but I think I did it right. I chose the 3rd join properties option.

HCN = PtID. There are many more fields in the Intake Form, obviously, but I don't really have clearance to share them, so I don't think I can at this time. I'll ask my managers after the holidays.

Thanks again.

Without all the fields in [Intake Form], I can't really help spot any other issues. You have set up the new table correctly though.

The only thing I can recommend is not using spaces (or any other non-alphanumeric characters) in table or field names. I would at least change [Intake Form] to [IntakeForm], if not change the name completely to hold what the data is (Patients) and not where its coming from (the intake form).

OK thank you. I think that is what I needed most urgently for now. I would like to share the rest, and I get that I need to show the rest of the fields in order to get additional help.

I'll work on queries in the meantime, and on figuring out how to get the time stamp on the page. I really do thing they need to see the time stamp, though, beside each different status, and be able to over-ride the date/time, as they may sometimes not update the status immediately (especially if off-site and unable to connect to the database).

Thanks again. I am sure there will be more questions, but it's helpful to at least be working with a structure that makes more sense now.

I got my database to time stamp my patient status changes, but it isn't creating a time-stamped log all of the changes - it just the most recent time that the field was updated. I need it to log the most recent time each of the 9 options for the status field was selected.

I did this:
- created an option group on my data entry form (Patients), with the patient status field.
- created a "PatientStatus" and "PtStatusDate" field on the form, made these fields invisible, and bound them to the PatientStatus table
- Created an "After Update Event" for the option group.

Private Sub PtStatusFormField_AfterUpdate()
If Me.PtStatusFormField Then
Me.PtStatusDate = Now
Else
Me.PtStatusDate = Null
End If
End Sub

At one point, I also tried a macro to save the date and time, based on some instructions I found online, but that wasn't working. I figured the code should work, though.

I thought at one point that maybe if I create an event to update the PtStatusDate for each of the 9 option group check boxes, rather than for the overall option group frame, that would do the trick. I just cannot actually figure out how to do that. Any tips would be appreciated.

When you implement a one to many table relationship as a form, you do it via a form/sub-form. Check this link out: https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b

You would create your patient form which would show one patient. Then you would create a continous form based on your PatientStatus table.

The subform would have 2 inputs per row:
A drop down to select the PtStatus field
An input for the Date/Time which you would default to =Now()

When you place the subform on the main form you would establish a child/parent relationship on the Patient ID field of both underlying tables. Read the link, it explains it in more detail.

That way, when viewing the patient form you can see every status of the patient and add new ones. VBA doesn't come into play with this.

Thanks so much. I'll give that a try.

I am stuck, probably on some little minor detail ... and another question.

#1

I created the continuous patient status subform with the 2 fields: an option group for collecting patient status, which has an After Update event procedure, to date stamp into the pt status date field. I still need it to log when all the status changes occur (so that we can later calculate wait times, etc).

When I update the patient status on the subform, both of the subform fields are automatically replicated on my subform, in the space below the original ones that I made, and this keeps going on and on - I think one patient's subform has 10 sets of fields on it. In the original option group at the top of the subform, I can click as many times as I want between all the various stati, but only the most recent selection is saved to my patient status table. However, if I update each of the extra automatically appearing option groups below, then my patient status table logs one status selection per option group updated. I attached a picture of my replicating option groups.

Any ideas why extra option groups keep appearing?

#2 - Also, regarding next steps, I will need to create a date field beside each status option on my patient form - a query or lookup field displaying the most recent date that this status was selected for this patient, right? (you had mentioned before a query on top of a query). If the status change actually happened on an earlier date but was not checked off on the database, can the user subsequently manually over-ride the status date via the form, if that field is technically a query/lookup output? And then how do I get subsequent queries on status dates to choose the manual over-ride date, if it is earlier than the one that was automatically date-stamped?

I really wish there were a way to program a separate event (such as date stamp) for *each* option within an option group. Failing that, I feel it might make more sense to just do away with time stamps altogether, and get the users to enter all dates manually.

1. Sounds like you have a continous form for your subform--which you should have. But you shouldn't be using an option group, instead a drop down. If the user can only select one status per update, then why give the option of selecting more? Make it a drop down.

2. I would include the timestamp in the form, no need for a lookup. There would be no need for "manual" update. You would instead just keep your data clean. The user would be able to see whenever a user entered each status and correct if it was wrong.

Thanks very much.

1) I thought that an option group is just another way of viewing a list - as check boxes that can be moved around the screen, rather than a drop-down list that appears in one small space. With the option box, I can still only select a single option. The team really prefers the option box appearance. Am I missing something in terms of why it is not ideal?

In any case, I did switch it to a list box on the form. As before, each time I select an option from the drop-down list, a new set of status/time fields appears, and the changes in status are only logged and time stamped if I use the newly replicated copies of these fields to enter the new stati. I want it to log the changes if I update just one single list box at the top of the form. Is there a way to log the changes without replicating the entire set of fields on the form?

2) Do you mean have a date stamp beside each of the 9 stati? That is what the team wants. I've added a picture of how they wanted their data entry forms.

Also, I am going to start a separate question in the coding forum, about coding with option groups, because I think it's more general, not just to this database. Thank you so much for all of your help. I appreciate it greatly.

1. I'm not really familiar with option groups, so I suggest you post a new thread in the Form section for help in making it work.

2. No, my method would have a drop down for the status and an input for the timestamp. The timestamp input would default to the current date. No overwriting involved, when they enter a new status record it receives its own timestamp so you know when that status was entered into..

Thank you so much.

I think I have found a way to do it that combined everything I`ve learned so far.

From what I can tell, an option group is really just a different visual representation of a list box. I can use select case to program a different event for each option.

I`ve scrapped the individual yes no boxes for each status as you suggested, and made them into a single field, represented as an option box, on the form. I think I will keep 9 separate status date fields - one for each status. When a status is updated, users will see the date stamp for that option, and they will be able to over-ride. I can use the 9 date fields to calculate differences, for queries for time lags between stati.

Many thanks! I think I am going to have a better database, and make it maximally usable for the clinic staff.