Solved Query advice please

[Gasman]

Hi everyone,
I have a DB to monitor my carb intake for my diabetes, and it also keeps track of my blood pressures.
I have just inherited another BP monitor. This one is the one you put on your upper arm, like the hospitals have. The one I have been using for nearly a year now is a wrist mounted one.
I would like to compare readings from both for a while.

Now if I had not normalised the table, then I would have two extra fields for Systolic and Diastolic, and the query would just be adding the extra two fields to the query.
The query is run from Excel as that has better graphing capabilities (to my view at least)

So, my question is, what is the best way to get what will be the next record in the table and bring in those two extra fields to the query?

I could just create yet more queries for that monitor, but thought I would ask first.

All I can think of atm, is a function that looks for the next record and returns either the Systolic or Diastolic each time. I am not aware of a function being able to return more that one value? Now it could be that there would not be a 'next' record, so need to take that into account. Certainly, apart from the last days records, none will exist, as I have just started using the arm one. I have a tblMonitor that holds the ID of each.

This function could be a DlookUp or a recordset with FindFirst?

At present I use the wrist monitor, then the arm monitor.

TIA

 

[CJ_London]

I am not aware of a function being able to return more that one value?

a function can return a recordset - might be one record, might be more.

Not clear to me what data is what - is the last column the monitorFK?

You could also perhaps use a crosstab query with column set to monitorFK and the two values combined as a value (Systolic & "/" & Diastolic) and then split it in excel

 

[Gasman]

I *think* I have solved it with DLookUp()

Systolic1: Nz(DLookUp("systolic","tblPressure","pressureID = " & [pressureID]+1 & " AND MonitorIDFK = 3"),0)
Diastolic1: Nz(DLookUp("diastolic","tblPressure","pressureID = " & [pressureID]+1 & " AND MonitorIDFK = 3"),0)
[code]
Not a lot of data being searched. I am limiting to this year.

[code]
SELECT tblPressure.PressureDateTime AS [DateTime], tblPressure.Systolic, tblPressure.Diastolic, tblPressure.Heart, tblPressure.Comment, tblMonitor.MonitorMake, Nz(DLookUp("systolic","tblPressure","pressureID = " & [pressureID]+1 & " AND MonitorIDFK = 3"),0) AS Systolic1, Nz(DLookUp("diastolic","tblPressure","pressureID = " & [pressureID]+1 & " AND MonitorIDFK = 3"),0) AS Diastolic1, tblPressure.PressureID
FROM tblMonitor INNER JOIN tblPressure ON tblMonitor.MonitorID = tblPressure.MonitorIDFK
WHERE (((tblPressure.PressureDateTime)>#1/1/2025#) AND ((tblMonitor.MonitorID)=1))
ORDER BY tblPressure.PressureDateTime;

 

[Gasman]

Well Excel did not like the NZ(), so I had to create a query for Lloyds records, subtract 1 from it's pressureID, so I could then join in the qryPressure.
Probably more efficient as well?
QryPressure

SELECT tblPressure.PressureDateTime AS [DateTime], tblPressure.Systolic, tblPressure.Diastolic, tblPressure.Heart, tblPressure.Comment, tblMonitor.MonitorMake, qryPressureLloyds.Systolic AS Systolic1, qryPressureLloyds.Diastolic AS Daistolic1
FROM (tblMonitor INNER JOIN tblPressure ON tblMonitor.MonitorID = tblPressure.MonitorIDFK) LEFT JOIN qryPressureLloyds ON tblPressure.PressureID = qryPressureLloyds.PressureID1
WHERE (((tblPressure.PressureDateTime)>#1/1/2025#) AND ((tblMonitor.MonitorID)=1))
ORDER BY tblPressure.PressureDateTime;

QryPressureLloyds

SELECT [PressureID]-1 AS PressureID1, tblPressure.Systolic, tblPressure.Diastolic
FROM tblMonitor INNER JOIN tblPressure ON tblMonitor.MonitorID = tblPressure.MonitorIDFK
WHERE (((tblMonitor.MonitorID)=3));

 

[CJ_London]

that is assuming they are always next to each other

Did you look at my suggestions?

 

[Gasman]

that is assuming they are always next to each other

Did you look at my suggestions?

Not yet. I will give that a try.
On the odd occasion they are not, I get that blank entry.

 

[CJ_London]

or you put them in the other way round

 

[Gasman]

I now have a basic crosstab working, and will play with that further.

Thank you @CJ_London