Using Max Date in Query

[jondavidf]

Hey all!



I'm a pharmacy student trying to develop a database for a research project. I have SOME database experience but am hitting a road block and hope you can help!!

So Patients routinely come in and have their Blood Pressure and Cholesterol levels checked. This rarely happens on the same day, and Blood Pressure is checked more frequently. Patient ID, Blood Pressure Dates, and Cholesterol Dates are all stored in linked tables (key = Patient ID). I would like to know what the latest cholesterol levels are each time I check Blood Pressure. End result would be a select query with the following fields:

Patient ID
Blood Pressure Date
Latest Cholesterol Date

I tried using Max Date by modifying the following SQL I found elsewhere on the net - but I couldn't adjust it properly for my needs. I think it may be a good starting point, though, so I included it below. I've also included sample tables and the query I'm trying to make.

SELECT [Equipment ID], MAX([Transaction Date]) AS LatestDate
FROM [Database Table]
WHERE [Transaction Date] < #2016-30-06#
GROUP BY [Equipment ID];

Just FYI, each table and query will contain much more info but was removed to simplify the example below.

Thanks in advance!!!

Below is my example, with trying to create a query like Query 1 as the end state:

[ArviLaanemets]

Create an User Defined Function (UDF) in standard Module

Public Function ValidValue(parTable As String, IdField As String, IdCond As String, IdIsString As Boolean, RetField As String, DateField As String, parDate As Date)
    Dim dbs As Database
    Dim rs As Recordset
    Dim varQstr As String
    ' The function returns the value of RetField from table parTable
    ' where IdField equals IdCond and DateField is nearest past or equal to parDate.
    ' The parameter IdIsString must be True, when IdField has Text format
    
    On Error GoTo Err_ValidValue
    Set dbs = CurrentDb
    varQstr = "SELECT [" & RetField & "] FROM " & parTable & _
            " WHERE [" & DateField & "] <= " & Format(parDate, "\#mm\/dd\/yyyy\#") & _
            " And [" & IdField & "] = " & IIf(IdIsString, "'" & IdCond & "'", IdCond) & _
            " ORDER BY [" & DateField & "] DESC"
    Set rs = dbs.OpenRecordset(varQstr)
    rs.AbsolutePosition = 0
    ValidValue = rs.Fields(0).Value
    
Err_ValidValue:
        rs.Close
        dbs.Close
        Set rs = Nothing
        Set dbs = Nothing
End Function

Now

LastCholesterolValue = ValidValue("DatabaseTable", "Pt_ID", "0001", True, "Chol_Value", "Chol_Date", Date())

[jondavidf]

Won't that only include Pt_ID 0001, though?

There will be approximately 500 unique Pt_ID values. I would like to get most recent Chol_Date for each BP_Date (and for each Pt_ID).

Also, I'm less familiar with VB Code than I am with Access. I was hoping SQL would be able to handle the request. If not, I'll try to work through the module but may have to ask for additional help

[ArviLaanemets]

You use same function in calculated field in form/report. Or in query, like (on fly)

SELECT t2.Pt_ID, t2.BP_Date, ValidValue("Table3","Pt_ID", t2.Pt_ID, True, "Chol_Date", "Chol_date", t2.BP_Date) AS [Chol_Date], ValidValue("Table3","Pt_ID", t2.Pt_ID, True, "Chol_Value", "Chol_date", t2.BP_Date) AS Chol_Value FROM Table2 t2

To create a module , click Module button in Create menu pane. A new empty module is created, and opened in VBA Editor. Copy the function code I sent into module. It's all!

[jondavidf]

Thanks! I am out for the day now, but I’ll give it a try this evening.

[orange]

I see you are using dates, but here do you store the related levels and pressure?

Do not use names with embedded spaces---will lead to syntax errors.

[rdougherty]

reading the OP, I do not see why that example SQL wouldn't work, so long as the ID being grouped by, is the ID associated with the person. SO, for each person, what is their latest BP date... A group by determines the max for each grouping. This should work. Please, someone explain why a more involved solution of writing actual code is necessary here when a novice user simply needs to do a very basic query function.

[jondavidf]

Below is some additional info if it helps.

Underscores are used instead of spaces for field names. Also, there are related levels to the screenings. The actual table names and values include:

tblPtMain (Table 1 in example)
Fields:
Study_ID (linked as Pt_ID in other tables)
Also includes other variables

tblPtBP (Table 2)
Fields:
Pt_ID
SBP (for systolic blood pressure)
DBP (for diastolic blood pressure)
BP_Date

tblPtChol (Table 3)
Fields:
Pt_ID
LDL_Lvl
HDL_Lvl
Total_Chol
Chol_Date

Items in parenthesis are for explanation only and are not part of table.

Additionally the query will calculate age during screening, based on DOB. This field is already done so I didn’t include it in my original question.

Thanks again for the help!

[ArviLaanemets]

Please, someone explain why a more involved solution of writing actual code is necessary here when a novice user simply needs to do a very basic query function.

The query OP has there is a weird one, as it looks for latest equipment transactions (probably it is the one OP found in net and is trying to modify). Rest of explanations, and table descriptions, are about patients and various analyzes. OP has a table where something dubbed BP with several dates BP_Date for every patient is registered, and has one (or several in worse case) analyze tables with analyze results and dates.

1. OP needs not latest analyze date for patient, but latest analyze date before or equal with BP_Date - for every patient and BP Date (It means usually for patient will be several latest analyze dates!).
2. Dates are really byproduct - I assume OP needs analyze values from those analyze dates.

[jondavidf]

Thanks Arvil!! This seems to be working great. I'm going to expand the query a little and will let you know if I run into any other road blocks. Thanks again!!

[jondavidf]

Thanks to all for the help thus far. I'm sure I'll be back soon for more recommendations and collaboration. You are all awesome!! Thanks so much!!