Link/query values from multiple fields into a single field on reference table

[HGCanada]

I have been pouring over this for some time, and keep thinking I have found the solution.

In my main Patients table, I have:
- a single field for patient status code (9 numerical options, entered via a form option group)
- 9 separate fields for status date - one for each status (example, ReferralDate, TriageCompletedDate, etc). Each of the date fields is automatically filled when that status is selected, and users can manually update them if needed. This is all working perfectly, and I really need to have these 9 separate fields so that they can all be displayed and manually updated on the form.

In my linked reference table, I have:
- patient status code (#1-9)
- patient status name (text version of the matching patient status- to appear on reports)
- status date

How do I link the 9 status dates, which are now in 9 separate fields, to the matching status date row on my reference table?

So, I want it to read in as follows in the date field on my reference table:
- Status date for status #1 = the value entered in the ReferralDate field for that patient
- Status date for status #2 = the value entered in the TriageCompletedDate field for that patient
- Etc. All the way up to status option #9.

Do I put a query right in the date field on my reference table to pull the data in from the other 9 fields on my main table? Or do I trash the reference table date field altogether, and make a query to pull the 9 separate status date fields into the appropriate columns for a report?

I am not a programmer. It would help if I could see examples, or at least specific names of functions, and then I can look up the appropriate code. Thanks in advance for any help you can offer. I've attached a picture.

 

[MarkK]

In the real world, I think you have a many-to-many relationship there, and you should add another table, named something like tPatientStatus...

tPatient
PatientID (Primary Key)
FirstName
LastName
etc...

tStatus
StatusID (PK)
Status

tPatientStatus
PatientStatusID (PK)
PatientID (Foreign Key)
StatusID (FK)
StatusDate <- this is where the rubber hits the road

Database tools work on rows. To find the latest status you do not want to search thru a bunch of fields to find a field name. Rather, you want to search a bunch of rows, and return the record that describes the object you are searching for.

Hope this helps,

 

[HGCanada]

Thanks so much. Just to clarify - I don't really want to find the latest status only. I need to store all 9 status dates. They each need to be saved, and displayed on the form, and the time spans between each status date will be reported. So, for each patient, I need to be able to create a report that shows when they reached each status.

I think I understand what you mean though about needing the data in rows, rather than fields. I guess I am trying to convert the date collected in separate fields, into a row on a table.

If I set up the extra table, is it possible to read in the values entered into the separate status date fields, or am I missing something?

 

[MarkK]

If I set up the extra table, is it possible to read in the values entered into the separate status date fields, or am I missing something?

I don't understand the question. What do you mean, 'read in the values?' Data has a structure in the real world. Your database needs to model that structure, and you are concerned with three types of objects here...

1) the patient, which exists in fact regardless of date or status
2) the status, which exists in fact (as an abstraction) apart from any date or patient
3) the status change event, which exists in time, and is directly related to a patient AND a status

Each of those things is a discrete type of object with multiple dimensions, and so each one needs to be described by a table. There is no way, in a database, to model that problem accurately without using three tables, and once you have an accurate data model, working with the data will suddenly seem much easier.

 

[HGCanada]

Thanks very much. Ultimately, I need to be able to do the following:

• on the data entry form, beside each of the 9 status options, display the date (date stamped) when the user clicks on that status.
• allow the users to modify the date stamped date for each status, right on the data entry form, because sometimes they won't update the database in real-time.
• save the date of *each* status, for each patient, for wait time reporting.

I think I understand, but just want to check and make sure. I've attached an image with my new table relationships (my naming is a bit different). Do I have it right? And some questions below about what is stored in each field.

tPatient
PatientID (Primary Key)
FirstName
LastName
etc...

tStatus <-- This table will only have 9 rows, which I enter, correct?
StatusID (PK) <-- These are status #1-9
Status <-- These are status names that I specify - #1 = referral received, #2 = triage completed, etc

tPatientStatus <-- This table creates a log, where each status for each patient is stored, correct? So, if I have 10 patients who have all moved through each status, there will be 90 rows?
PatientStatusID (PK) <-- This is an autonumbered field?
PatientID (Foreign Key) <--This is a foreign key to my main patients table?
StatusID (FK) <--This is a foreign key to my tStatus table?
StatusDate<- this is where the rubber hits the road <-- I program an after event procedure, and when the status is checked off on the form, a date stamp is inserted in this field?

I thought this was a new question, but it similar to one I asked in a different forum previously. However, I have now changed my database, and the table format you are suggesting is new to me as well.

The way I have it set up now, my status log only records the most recent status date, and Status code (1-9) is not recorded in my table at all.

 

[MarkK]

Yes to everything your wrote in blue, I think, but no to the image. Here's a quick and dirty db that demonstrates what I am talking about. See if it makes sense to you, and let me know if and how it fails to meet your purpose.
Cheers,

 

[jlcastrejon]

Thanks very much. Ultimately, I need to be able to do the following:

• on the data entry form, beside each of the 9 status options, display the date (date stamped) when the user clicks on that status.
• allow the users to modify the date stamped date for each status, right on the data entry form, because sometimes they won't update the database in real-time.
• save the date of *each* status, for each patient, for wait time reporting.

I think I understand, but just want to check and make sure. I've attached an image with my new table relationships (my naming is a bit different). Do I have it right? And some questions below about what is stored in each field.

tPatient
PatientID (Primary Key)
FirstName
LastName
etc...

tStatus <-- This table will only have 9 rows, which I enter, correct?
StatusID (PK) <-- These are status #1-9
Status <-- These are status names that I specify - #1 = referral received, #2 = triage completed, etc

tPatientStatus <-- This table creates a log, where each status for each patient is stored, correct? So, if I have 10 patients who have all moved through each status, there will be 90 rows?
PatientStatusID (PK) <-- This is an autonumbered field?
PatientID (Foreign Key) <--This is a foreign key to my main patients table?
StatusID (FK) <--This is a foreign key to my tStatus table?
StatusDate<- this is where the rubber hits the road <-- I program an after event procedure, and when the status is checked off on the form, a date stamp is inserted in this field?

I thought this was a new question, but it similar to one I asked in a different forum previously. However, I have now changed my database, and the table format you are suggesting is new to me as well.

The way I have it set up now, my status log only records the most recent status date, and Status code (1-9) is not recorded in my table at all.

Hi Canada
I agreed with Mark, from your picture is notorious that you have too many relations and some fields could be recorded in their own table as for instance names or lastnames, there is a rule for create a table, if something appears on your table more than once then the best is to have a table just for that information, now regarding dates that should be the case and in your table with the codes from 1 to 9 a 3th field linked to the ID field for "Dates" (ID_Dates) they have to be linked in order to use it.
Seems to me as well that you are not using a form which would help you a lot in all this.
Please if you havent solve your problem let me know may be i could help you with that as i have been working lately with dates and codes in one of my projects
Regards
JLC

 

[HGCanada]

Thank you both so much. Yes, I need to change it.

Mark, I looked at the database. That was really helpful. Now I understand what it is supposed to look like. In terms of the data returned, it gives us what we want, once I figure out the queries. So, two things:

1) In terms of data entry, the team wants a single and very easy to use form. There are many other fields, but in terms of patient status and status date, is there any way that I can make the data entry form look like the picture attached? Where:

- the 9 status options are in sequential and fixed locations that, and they just check off each status as it is reached (not every status will be checked for each patient)
- the status dates are beside their respective status check boxes, and are automatically updated, and can be manually updated (this part is already working)
- the subform blends in visually with the rest of the form

2) Also, I made a totals query to find the most recent status of each patient, and it works. But when I try to nest that query within another query to find the status associated with that most recent date for each patient, it returns every single patient status record. It doesn't filter by the previous query results. I've tried different join types. Any idea why? I've included a picture of that as well.

Thank you.

 

[MarkK]

Technically, the FormAppearance image describes a different status data structure than 9 sibling rows. It looks like there's a kind of ongoing 'service status' (Triage, Streamed, and Follow Up) which may have--in time--a 1) start, any number of 2) holds (a) applied and b) released), and a 3) completion, as 'sub-events'. By contrast, the Referral Received, Visit Completed, and Discharged events are inherently discrete and indivisible, so they cannot have sub-events. In this respect your status list contains objects that in the real-world have different functions and different structures, and this severely complicates your representing them in a data system as the same type of thing.

I think you have, in fact, two types of status, a "case status", and a "service status," and I think you should separate them, because tracking the duration of your application of service is going to be a very important part of a data system like this. Tracking the discharge status, by contrast, is very easy.

One other thing occurs to me, is that you might define a single "Intake" status that has sub-events like "1) Referred, 2) Visit-Completed, and 3) Discharged, and in this way unify those three as functioning with the same structure as the other "service events." So then you have these "service status" categories...

• Intake
• Triage
• Streamed At Rounds
• Follow Up

...and each of those can then define dated sub-events. But you just can't have apples and oranges in the same table.

Anyway, hope that gives you some ideas,

 

[HGCanada]

Thank you Mark. I see your point. I am mapping out some tweaks to the table structure.

I'm going to tinker with this, preferring the second option you suggested. If I set up the "oranges" categories correctly, and then all the dates end up in the "apples" categories, I think I can produce good reports for the team, and be able to represent which patients are in which categories, and the lag times to get from one to the other.

I'll report back! Many thanks. Have a great weekend.

 

[MarkK]

If I set up the "oranges" categories correctly, and then all the dates end up in the "apples" categories, I think I can produce good reports for the team

Yes, exactly!

Yeah, keep me posted, looks like an interesting project.

 

[HGCanada]

OK, so I have worked on it, and the biggest challenge is that that any which way I look at it, the 9 status options, including all the holds, don't fall into nice separate categories that make sense in the real world. I summarized in a way that I think makes the most sense, given the types of reports the team specified they need:

- Table 1 - subform with status hold details - each hold (triage, stream or followup) is its own record, with start and end date in separate fields. It was easy to calculate time difference to see how long they were on hold.

- Table 2 - subform with full status changes - time stamped, in a log. Status dates are all in the same date field.

I've done most of the queries, and I think the reports show pretty much what they need. What's left now is:

- how do I query how long it takes to get from one discrete status to the next for each patient - referral to triage, triage to initial visit, etc - when the status dates are all for different records in the same field? if you have any pointers, I would appreciate it.

- how can I constrain the status options in my subforms, so that they must enter the stati sequentially, and that for any one patient, they cannot select the same status twice?

 

[MarkK]

Well, if the statuses (stati?) are sequential, then you can define that in the status table by adding a SortOrder field, or Ordinal, or Index or something like that. That gives you the sequence.

To disallow duplicates, I would probably add an index in the table with the unique flag set to true. Looking back a sec, yeah, so in the tPatientStatus table you have a PatientID and a StatusID. In that table you can create an index with both those fields in it, and set the unique flag for that index to true. Then the table won't allow dupes, and a trappable error will occur for any process that tries to make that happen.

Time distance from one discrete status to the next, well, I'm not exactly sure how the data is structured, but you could write an aggregate query that finds the Min() and/or Max() date/time for each status type, but it raises the question too, does one status end with the beginning of the next? Or does each status have a Start and End defined? What then does it mean if there is time between the end of one status and the start of the next? Should the creation of the start of the second status automatically cause the end of the first status? These are questions about how your real-world system works that only you can answer really.

But if you want specific details about how to make a specific thing work, show the structure of the tables, and some sample data, and we can get down to brass tacks on that.

Hope this helps,

 

[HGCanada]

Hello again. It's been a while since I posted. The project was on hold for a while, and it's now up and running.

The biggest challenge now is getting current patient status. The "tPtStatusDates" table is great. We log changing stati and their associated dates, for multiple patients. I can get the most recent status for each patient, with this query:

SELECT tPtStatusDates.PtCaseID, Max(tPtStatusDates.StatusDate) AS MaxOfStatusDate
FROM tPtStatusDates
GROUP BY tPtStatusDates.PtCaseID;

What I can't figure out is the syntax for using the above in a subquery, which will return the associated Status (StatusID), for the most recent status date for each patient. I've attached a picture of my table and MaxDate query. All 3 of my variables are on the same form, so I figure it shouldn't be so hard, but I've tried many variations with no success. This query will form the basis of all the reports I need to create.

Thanks in advance.

 

[MarkK]

What you can do is just open a recordset of all rows sorted by date DESC, and in that case the first row will be the max date. Then just read the other values out of that row, so consider SQL like...

SELECT * 
FROM tPtStatusDate
WHERE ptCaseID = 123
ORDER BY StatusDate DESC

Then you could write a function like...

Function GetMaxStatusForCase(CaseID as long) as Long
   const SQL as string = _
      "SELECT * " & _
      "FROM tPtStatusDate " & _
      "WHERE ptCaseID = p0 " & _ 
      "ORDER BY StatusDate DESC;"

   with currentdb.createquerydef("", SQL)
      .parameters(0) = CaseID
      with .OpenRecordset
[COLOR="Green"]         'here we open a recordset and grab the value from the first row, if any exist[/COLOR]
         if not .eof then GetMaxStatusForCase = .Fields("StatusID")
         .close
      end with
      .close
   end with
end function

See how that returns the latest StatusID for the given CaseID?
Does that answer your question?