one to many, analyze each medical encounter

[Lifeseeker]

Hi,

I have a one to many database set up. Primary table contains patient demographics, and the encounter table contains each medical visit. When there are two medical encounters, there will be two records of that same patient.

One of the analyses is find out which encounter of the patient meets certain criteria and guidelines set out by this provincial authority. The analysis can be......of the total number of patients coming to this hospital, how many of them have evidence of a specific test in a given period. (say in the month of july and august)

So the calculation is patient-specific as you can see.

Now given that the database can actually store multiple encounters for each patient, how do I essentially base my calculation on each encounter rather than patient? I am told that each encounter needs to be looked at and we would like to find out if every encounter for that patient meets the criteria, etc.

How should I go about doing the analysis this way?


Any thoughts/comment is much appreciated.

 

[Alansidman]

You will need to set up a query with the two tables joined on the common patient identifier field. Depending on what you want to evaluate, you may want to set a date range in your criteria for the visit date and depending on the outcomes you may want to set a diagnosis in the criteria for the applicable field. Without knowing your fields and what you may be trying to specifically do, this is the best I can offer.

Alan

 

[Lifeseeker]

You will need to set up a query with the two tables joined on the common patient identifier field. Depending on what you want to evaluate, you may want to set a date range in your criteria for the visit date and depending on the outcomes you may want to set a diagnosis in the criteria for the applicable field. Without knowing your fields and what you may be trying to specifically do, this is the best I can offer.

Alan

Thanks.

Please see the attached for a sample database in Access 2003.

The query I wrote only extracts the latest encounter for the same patient. However, as I mentioned above, all encounters need to be looked up.

If you see in the encounter table, the PHN of "5234" is repeating 3 times, implying that there are 3 counters for this patient. So all 3 needs to be looked at.

So an example analysis is......find out how many encounters are there where the cbc test done is within 2 days of hospital arrival in the month of July.

As you can see in the sample database, only two encounters for 5234 would meet the criteria. (by looking at the query results, no formula used)

The question is....two encounters are NOT the same as two patients. In fact, this is one patient's data. So my concern is.....how to summarize the finding at a patient level rather encounter level? How can we easily break the two encounters up and be able to say....the first encounter of patient 5234 meets the criteria, and the 2nd encounter of the patient 5234 also meets the criteria. The third one wouldn't meet the query criteria, so it wouldn't have shown up in the query.

How do I get the total number of patients? From the patient demographics table instead of the encounter table?

Does this make sense?

 

[Alansidman]

Is the PHN a unique patient identifer? If so, this should be set to no duplicates and should probably be your Primary Key in the patient table.

How do we know when the patient arrived at the hospital. Is this date missing?

 

[plog]

First off, forget the Patient Demographics, it has nothing to do with this. Second, you don't have hospital arrival data in the database you posted, so I wasn't able to create the example you proposed. 3rd you are going to need a subquery to get encounters per patient which you will use to roll up into a query that gives you total encounters and total unique patients.

Since I didn't have the data necessary to create the query you wanted, to show you what you need to do I created a query that tells you total encounters and unique patients in a month. Below is the SQL for that query.

SELECT MonthTotals_sub.MonthCode, Sum(MonthTotals_sub.Encounters) AS TotalEncounters, Count(MonthTotals_sub.PHN) AS TotalPatients
FROM (SELECT visit.PHN, Format([cbc_test_done_date],"yyyymm") AS MonthCode, Count(visit.PHN) AS Encounters FROM visit GROUP BY visit.PHN, Format([cbc_test_done_date],"yyyymm"))  AS MonthTotals_sub
GROUP BY MonthTotals_sub.MonthCode;

I used the sample database you posted, so if you copy the above code into it you will be able to run the query and hopefully deconstruct it so that it will serve your exact purpose.

 

[Lifeseeker]

First off, forget the Patient Demographics, it has nothing to do with this. Second, you don't have hospital arrival data in the database you posted, so I wasn't able to create the example you proposed. 3rd you are going to need a subquery to get encounters per patient which you will use to roll up into a query that gives you total encounters and total unique patients.

Since I didn't have the data necessary to create the query you wanted, to show you what you need to do I created a query that tells you total encounters and unique patients in a month. Below is the SQL for that query.

SELECT MonthTotals_sub.MonthCode, Sum(MonthTotals_sub.Encounters) AS TotalEncounters, Count(MonthTotals_sub.PHN) AS TotalPatients
FROM (SELECT visit.PHN, Format([cbc_test_done_date],"yyyymm") AS MonthCode, Count(visit.PHN) AS Encounters FROM visit GROUP BY visit.PHN, Format([cbc_test_done_date],"yyyymm"))  AS MonthTotals_sub
GROUP BY MonthTotals_sub.MonthCode;

I used the sample database you posted, so if you copy the above code into it you will be able to run the query and hopefully deconstruct it so that it will serve your exact purpose.

Sorry I must have not sent you the saved database while I had it open on my computer, but I attached it here again.

Also....is there any way to do all of this using the query grid? I would rather like to see a list of patients as output, or essentially PHNs after the query is run.

So patient 5234 has 3 encounters. Of the 3, only 2 meets the criteria. (criteria is getting the result done within 2 days of hospital arrival).

For hospital date, I used the arrival_date in the encounter table. Reporting month is July. Meanwhile, I will try to make sense of the code you wrote.

Thank you

 

[Alansidman]

In the attached, I changed a couple of dates for the procedure to make sure that my formula delivered correct results.

In Query1, I re-created the query that plog gave you in SQL statement form.

In Query2, I added an iif statement that says if the procedure date is within two days of admittance then place a 1 in the calculated field otherwise a zero. I then did an aggregate query on the Patient for the number of procedures within two days.

Look this over and post back if you still have issues.

Alan