Help with Query

[Carey27]

Im trying to construct a query for my clinic that lists the patients who's HIV medication is failing. Once upon a time I was serviceable at programing but I have no clue how to do this... Any help would be very much appreciated

The two WHO criteria we need to apply to the query are:
1) The patients most recent CD4 count has dropped below the CD4 count at the start of therapy.
2) There has been > 50% reduction in CD4 count from their peak post therapy level

Our Lab Table captures Patient ID, Lab Type (CD4 Count), Date of Lab and Result. And the patient info table captures the day they started Anti-Retroviral Therapy. So for....

The First Criteria
What i need to do is compare the most recent CD4 lab for all of our patients against the closest lab date to the start of therapy (either before or after). To complicate it even more some patients do not have start dates so i will have to exclude them...

The Second Criteria
I must figure out what the patients highest CD4 count since starting therapy is. Then compare it with their most recent lab.

Again, Im not really sure were to begin here. Any help would be great

 

[MSAccessRookie]

Im trying to construct a query for my clinic that lists the patients who's HIV medication is failing. Once upon a time I was serviceable at programing but I have no clue how to do this... Any help would be very much appreciated

The two WHO criteria we need to apply to the query are:
1) The patients most recent CD4 count has dropped below the CD4 count at the start of therapy.
2) There has been > 50% reduction in CD4 count from their peak post therapy level

Our Lab Table captures Patient ID, Lab Type (CD4 Count), Date of Lab and Result. And the patient info table captures the day they started Anti-Retroviral Therapy. So for....

The First Criteria
What i need to do is compare the most recent CD4 lab for all of our patients against the closest lab date to the start of therapy (either before or after). To complicate it even more some patients do not have start dates so i will have to exclude them...

The Second Criteria
I must figure out what the patients highest CD4 count since starting therapy is. Then compare it with their most recent lab.

Again, Im not really sure were to begin here. Any help would be great

The First Criteria

To select the earliest Lab Testing Date for Patients, the SQL code would look something like the following:

qryGetMinLabDate:

Select PatientID, Min(LabTestingDate)
From LabTests
Group By PatientID
Order by PatientID
​

To select the most recent Lab Testing Date for Patients, the SQL code would look something like the following:

qryGetMaxLabDate:

Select PatientID, Max(LabTestingDate)
From LabTests
Group By PatientID
Order by PatientID
​

To get the most recent CD4 lab result for Patients, the SQL code would look something like the following:

qryGetLatestCD4:

Select LabTests.PatientID, LabTests.CD4Result
From LabTests Inner Join qryGetMaxLabDate On LabTests.LabTestingDate = qryGetMaxLabDate.LabTestingDate
Group By LabTests.PatientID
Order by LabTests.PatientID
​

The Second Criteria

To get the most Highest CD4 lab result for Patients since the start of therapy, the SQL code would look something like the following:

qryGetMaxCD4Result:

Select PatientID, Max(CD4Result)
From LabTests
Where TherapyStartDate Is Not Null
Group By PatientID
Order by PatientID
​

You must then combine the Query results into one final query to get the report that you are looking for.

There are probably other ways to do this, but this approach should be close to what you are looking for.

 

[Carey27]

Rookie thanks for the help but I think two of the queries would not give me what I need.

The First Criteria
qryGetMinLabDate:

Select PatientID, Min(LabTestingDate)
From LabTests
Group By PatientID
Order by PatientID
​

I think this would give me the oldest lab test and not just the one closest to the date they started therapy.

To get the most recent CD4 lab result for Patients, the SQL code would look something like the following:

qryGetMaxCD4Result:

Select PatientID, Max(CD4Result)
From LabTests
Where TherapyStartDate Is Not Null
Group By PatientID
Order by PatientID

​

I think a problem with this query would be that it would simply return the highest CD4 count the patient has had and not the highest SINCE starting therapy.​

 

[MSAccessRookie]

Rookie thanks for the help but I think two of the queries would not give me what I need.


I think this would give me the oldest lab test and not just the one closest to the date they started therapy.



I think a problem with this query would be that it would simply return the highest CD4 count the patient has had and not the highest SINCE starting therapy.

The reason that I indicated in my response that the Queries that you needed would be something like the ones that I posted, was because I assumed that you might need to modify them before you were able to use them.

The differences that you require can most likely be resolved by adding an appropriate Where clause that references the TherapyStartDate to any Query that needs one. Remember that when TherapyStartDate does not exist, it will most likely be NULL, giving you a method of excluding (or separating) these patients from the rest of the group.

Give it a try and get back to us with any further questions.

 

[Carey27]

Thanks will play around with it this afternoon