Help with query (Grouping, Calculations, etc)

[cooh23]

Hi All,

I have a very complicated (for me) query. I am working on a blood sugar calculations and have the following under Screening Type: FBS, PPBS, HgA1C. A patient can have any of the 3 screenings. Here is how my query is setup:

PatientID--ScreeningType--DateofVisit--Result--Outcome--MaxDOV--DayCount
1234--FBS--01/20/2011--100--Controlled--01/20/2011--179
1234--PPBS--02/20/2011--130--Uncontrolled--02/20/2011--148
1234--HgA1C--02/04/2011--7--Uncontrolled--02/04/2011--164

This is where it gets tricky, I want to get the latest outcome of the 3, in this case it's PPBS so outcome should be Uncontrolled. However, if HgA1C is within 3 months of the other readings, then get HgA1C outcome which is Uncontrolled. I was able to do this in Excel but am having a hard time in access.

I added 2 more columns:
MaxDOV - Patients will have several readings but I only wanted to get the latest from each ScreeningType. This is the latest date for the individual screening

DayCount - To try to get which has the latest date, I calculated todays date - MaxDOV.

So what I am trying to do is to get a query like below if HgA1c is "within 3 months of any of the readings:
PatientID--ScreeningType--DateofVisit--Result--Outcome--MaxDOV--DayCount
1234--HgA1C--02/04/2011--7--Uncontrolled--02/04/2011--164

If HgA1c is not within 3 months, let's say it's date is 09/04/2010, then get the latest outcome for the patient. Which is:

PatientID--ScreeningType--DateofVisit--Result--Outcome--MaxDOV--DayCount
1234--PPBS--02/20/2011--130--Uncontrolled--02/20/2011--148

I hope this is possible, I have been pulling my hair on this for a few days.

Thank you very much.

John

 

[PNGBill]

Some wizards can make a complicated sql to handle virtually anything.
Others need to resort to more then one query.

Create a query to return the lastest result for each of the three groups (3 queries)

Then a new query will use those three and maybe tables etc and select the result from whichever category suits the criteria.

Trust this assists

 

[cooh23]

Thank you! I knew I was on the right path since I had already created a query to show only the latest dates. But the other problem is how to address my other issue of finding out if HgA1c is within 3 months and giving the outcome of that?

 

[PNGBill]

You can use DateDiff in your design or sql query.
Here is an example from my Access Help

DateDiff ("yyyy", [BirthDate], Date()) > 30    This criterion applies to a Date/Time field, such as BirthDate. Only records where the number of years between a person's birth date and today's date is greater than 30 are included in the query result.

 

[cooh23]

You can use DateDiff in your design or sql query.
Here is an example from my Access Help

DateDiff ("yyyy", [BirthDate], Date()) > 30    This criterion applies to a Date/Time field, such as BirthDate. Only records where the number of years between a person's birth date and today's date is greater than 30 are included in the query result.

The problem I am having is trying to figure out how to do the following:

If FBS, PPBS or HgA1C exist, then find out which of the 3 has the latest date, If HgA1c has the latest date then show HgA1c Outcome. If HgA1c is not the latest, then find out from the two results if HgA1c is within 3 months, if it is then show HgA1c Outcome, if not then show the outcome of the latest date.

This is the best way I can describe it.I was able to find out how to get the latest date but I can't seem to figure out how to do what I just descibed.

 

[PNGBill]

Return your three options. You would want to allow for Null Records here. If there is no record, then say return a date 01/01/1901 which should not be a possible date.

You are saying that if HgA1C is within 3 months of today then that is your preferred option.

You then say if HgA1C is not within 3 months then you will use the most recent dated option of the three excluding 1/1/1901.
I assume, if more then one record matches the criteria (same date) and one of them is HgA1C then that is your choice.
What if FBS and PPBS are the two records (same date), which is your preferred choice ?

What if all your dates are 1/1/1901 ie, no records for any of the three options?

The select queries will return your three options and then one query with an extended IfThen statement will decide which of the three is your final choice.

Respond above and I will try and construct the IfThen expression.

 

[cooh23]

Return your three options. You would want to allow for Null Records here. If there is no record, then say return a date 01/01/1901 which should not be a possible date.

I am not sure what you mean by this one. I don't think there will be any null records here. A patient will always have a record all 3 screenings (FBS, PPBS, HgA1c) or just one.

You are saying that if HgA1C is within 3 months of today then that is your preferred option.

Not 3 months of today, within 3 months of the dates of the results. So if I look for one patient with ID 1234 and it shows 3 records.
PatientID--ScreeningType--DateofVisit--Result--Outcome
1234--FBS--01/01/2011--99--Controlled
1234--HgA1c--02/01/2011--7--Uncontrolled
1234--PPBS--04/28/2011--179--Controlled

So this shows 3 results with PPBS as the latest date. But since HgA1c is within 3 months of the results, it should show the Outcome for HgA1c. Uncontrolled.

You then say if HgA1C is not within 3 months then you will use the most recent dated option of the three excluding 1/1/1901.
I assume, if more then one record matches the criteria (same date) and one of them is HgA1C then that is your choice.
What if FBS and PPBS are the two records (same date), which is your preferred choice ?

What if all your dates are 1/1/1901 ie, no records for any of the three options?

The select queries will return your three options and then one query with an extended IfThen statement will decide which of the three is your final choice.

Respond above and I will try and construct the IfThen expression.

Correct, if HgA1c is not within 3 months, then use the latest date of the result.
A patient will not have same date readings so we can exclude this from our rule. A patient will always have different dates for their reading.

P.S. The patient will not always have 3 readings. Sometimes they'll have one, two, or all three.
They will NOT always have HgA1c so if HgA1c doesn't exist then just get the Outcome of the record with the latest date.

I hope I explained it clearly.

Thank you very much for helping me on this.

John

 

[PNGBill]

Maybe a solution is:

Select query all records for a given patient and sort desending order by date. This will return the most recent result.

If this is HgA1c then that is your result.

Else, (result is FBS or PBS) use the date of the most recent result (above) and look for any record within 3 months of that date.

If there is a HgA1c record within this result, then use HgA1c, else, use the latest dated FBS or PPBS result.

Is this the way to read your result ?

I will create sample database to reflect te above.

 

[cooh23]

Maybe a solution is:

Select query all records for a given patient and sort desending order by date. This will return the most recent result.

If this is HgA1c then that is your result.

This is how I currently have my query setup

Else, (result is FBS or PBS) use the date of the most recent result (above) and look for any record within 3 months of that date.

If there is a HgA1c record within this result, then use HgA1c, else, use the latest dated FBS or PPBS result.

Is this the way to read your result ?

I will create sample database to reflect te above.

That is correct! That is how I want the query to work.

I really appreciate your help on this. Thank you!

John

 

[PNGBill]

Sorry for delay but here is a sample database in 2010 mdb.
Let me know if you need it in an earlier version.

I used VBA to resolve the issue.
Open the database and open the one form. You can then select the Patient from a drop down list and the relevant data will be displayed.

Trust the result is what you are looking for.

The function is a Public Function and can be used anywhere withing your database, once edited to match your names etc.

You can expand this to allow for variable test results ie have a form that allows you to enter the tests you are looking for and have the sql and vba to still work.

Trust this assists.

 

[cooh23]

Sorry for delay but here is a sample database in 2010 mdb.
Let me know if you need it in an earlier version.

I used VBA to resolve the issue.
Open the database and open the one form. You can then select the Patient from a drop down list and the relevant data will be displayed.

Trust the result is what you are looking for.

The function is a Public Function and can be used anywhere withing your database, once edited to match your names etc.

You can expand this to allow for variable test results ie have a form that allows you to enter the tests you are looking for and have the sql and vba to still work.

Trust this assists.

OMG... Thank you very much!!! This is exactly what I am looking for! I will modify the code to work with my queries! I was pulling my hair out trying to figure out how to do this. Thanks again!

John

 

[PNGBill]

Glad to help. Should have been able to do this quicker but I have been renovating a beach house rather then working on our database for the last 8 months and got a little rusty
Be careful of the dates as Access can get confused between US and Normal dates.
eg 02/09/11 can be 2nd Sept or 9th Feb. test your data to check for this.
I used 90 days rather then 3 months - little difference.

DayCount and MaxDOV appear to be superfluous.

 

[cooh23]

Glad to help. Should have been able to do this quicker but I have been renovating a beach house rather then working on our database for the last 8 months and got a little rusty
Be careful of the dates as Access can get confused between US and Normal dates.
eg 02/09/11 can be 2nd Sept or 9th Feb. test your data to check for this.
I used 90 days rather then 3 months - little difference.

DayCount and MaxDOV appear to be superfluous.

Ah.. Enjoy your beach house!

Thanks for the heads up!. I'll double check it. Thanks again!

John

 

[cooh23]

I seem to be having problems editing the code

The outcome for my tables are created in queries by iif statements. Here's how I have my queries setup:

SELECT tblBloodTest.RecordID, tblBloodTest.PatientID, tblBloodTest.ScreeningType AS Bloodscreentype, tblBloodTest.DateofVisit, tblBloodTest.TestResult AS result, IIf([screeningtype]="hga1c" And [Testresult]<7,"controlled",IIf([screeningtype]="hga1c" And [testresult]>=7,"Uncontrolled",IIf([screeningtype]="fbs" And [testresult] Between 70 And 130,"Controlled",IIf([screeningtype]="fbs" And [testresult] Not Between 70 And 130,"uncontrolled",IIf([screeningtype]="ppbs" And [testresult]<180,"Controlled",IIf([screeningtype]="ppbs" And [testresult]>=180,"uncontrolled")))))) AS Outcome
FROM tblBloodTest
ORDER BY tblBloodTest.DateofVisit DESC;

In your sql select statement you have the following:

"HAVING (((tblBloodTest.PatientID)=" & txtPatientID & ")) " & _

I am not sure I understand this part. and also the part below:

Public Function BloodTest(txtPatientID As String) As String

I am really not too familiar with VBA so I got lost on this one.

I see you've used txtPatientID in form.

When I create a query based on the following:

SELECT tblBloodTest.RecordID, tblBloodTest.PatientID, tblBloodTest.ScreeningType, tblBloodTest.DateofVisit, tblBloodTest.TestResult, tblBloodTest.TestOutcome, bloodtest([patientid]) AS FinalOutcome
FROM tblBloodTest;

I get exactly what I want and I don't understand where I'm making a mistake.

Please advise.

Thank you,

John

 

[PNGBill]

May be best to think about this in two parts.

Firstly you have your normal Query where you use iif and collect the data you require. If this works, leave it as so.
You also need a form to interact with your raw data. I guess you have such a form. This most likely is also quite ok as is - just minor change.

In my sample database I have two tables. Patients and bloodtest.
You will see a query that collects data.
Because my Bloodtest table had all the info I needed that is all I used but you can use your query for the base data you need.

The 2nd thing to tink about is the Module - vba code.
This is a Public function and can be called from anywhere in your database to perform the task it is designed to do.
Module is named modBloodTest and if you open this (design view), you will see one Public Function - BloodTest(txtPatientID As String) As string

What this does is use the text box control on your form named txtPatientID and take the data in this as the data to use to run the code.
In this case the form has the PatientiD as it's data therefore the vba code (function) will run with that patientID as it's reference point.

Within the code, the sql you don't understand is taking that PatientID and collecting information for that patient as per the sql.
If you study the sql and ignore the ""'s it should appear like any normal sql query. The "" are needed because vba doesn't understand sql and reads it as string text.

You can edit the vba code sql so it looks to your query rather then tblBloodTest.
txtPatientID to whatever the control is on your form. best to use txt.... as the Name of the text box control becuase the default Same Name as the data can be confused in some code situations.
ie, if the Control Source is PatientID (Property Sheet Data), use txtPatientID as the Name (Property Sheet Other).

The text box control on the form, txtBloodResult has it's Data control Source simply the Name of the Public Function and as you see, =BloodTest([txtPatientID]) is telling the function to use the form control as it's data.

In the Public Function, I maybe should have named it BloodTest(SomeRef As string) As String but it doesn't change anything really as the function can only work on a Patient ID.

Some functions can do tasks on different data so it makes snece to use a common name but you could use SantaClause As String.
As String maens it is looking for Text rather then dates or numbers and it will also return a text result.

You will also not in the vba code I have joine the result to give test type, date etc as one line of text. Easly to amend this to read the result you require.

Trust the above assists.

 

[PNGBill]

Here is the SQL with the "" & _ 's removed.

You will need to enter a Patient ID when requested.

SELECT tblBloodTest.RecordID, tblBloodTest.PatientID, tblBloodTest.ScreeningType AS BloodScreenType, Max(tblBloodTest.DateofVisit) AS MaxOfDateofVisit, tblBloodTest.TestResult AS BloodTestResult, tblBloodTest.TestOutcome AS BloodTestOutcome
FROM tblBloodTest
GROUP BY tblBloodTest.RecordID, tblBloodTest.PatientID, tblBloodTest.ScreeningType, tblBloodTest.TestResult, tblBloodTest.TestOutcome
HAVING (((tblBloodTest.PatientID)= [Enter PatientID]))
ORDER BY Max(tblBloodTest.DateofVisit) DESC;

 

[cooh23]

Thanks again for your timely reply. I will try the codes again as soon as I can. I will be on meetings for the next few days and won't be able to get back to working on this til maybe this weekend. I hope this gives me a fresh mind when I do start working on this.

Thank you very much for the clarifications on your code. It makes sense, I guess I was kind of lost on why it was referring to a form since I actually don't use the form. I just use the database to analyze the data in a report format. I am getting the data dump from excel (this will change soon as I am creating a form for the data to be entered).

So the query that I have will show all patients that have had screenings. Would it be possible to remove the "Having" part so I don't set any criteria on a patient? So it will just show everyone instead of a particular one.

I am not looking at the results based on individual. I just wanted to find out how many were controlled and how many were uncontrolled. So that's why I have a query that just shows the list of patients with their results and outcome. Then I provide a summary of the result on a report with graphs and other breakdowns.

Thank you,

John