Max and Min function (1 Viewer)

[majordoc]

Hello all and thanks again for any help.
I'm using this dummy simple table to try and figure out how to performs some queries that I need to use for a large patient database.
I need a query to get the max, min and most recent blood result for each patient. There is apparent table where HandC is a unique patient. I just cannot get this to work at all!

 

[MajP]

Do two aggregate (group by) queries. One for max and one for min. Then do a union query to show both max and min.
Or you can do this in one query using Dmax, Dmin in calculated fields. The first is more efficient. The second may be easier.

 

[majordoc]

Hi. Thank you very much for this. I have hundreds of such queries for various tests etc. I have tried using DMax - apparently can be used in a calculated control but not a calculated field?

 

[majordoc]

I have created a calculated control in a form with following:
=DMax("Nt-ProBNP","PH Investigations") but all I get is a flickering error message #Error on the form in Form View.
Any ideas?

 

[jdraw]

Post your sample database.

 

[majordoc]

Thank you - have attached

 

[MajP]

See this

 

[MajP]

Using yours.

 

[majordoc]

That's great!
In total there are about 110 different numerical test result fields in the actual patient database. Even if less efficient, I think using a DMax and DMin function may be easier but I can't get that to work either - see above. And I need the dates associated with the max and min value, plus the most recent test result with date. - ie Date of Max result and result value, date of most recent result and value.
Really appreciate this help - it will make a big difference to the care of these patients actually

 

[plog]

In total there are about 110 different numerical test result fields in the actual patient database.

That's a problem you need to fix. You should not have a different field to hold each of your tests, you accomplish this with 2 diferent fields in the same table. That table should be structured as such:

tblTestResults
tr_ID, autonumber, primary key
tr_HandC, number, this will hold patient test is for (Currently HandC)
tr_Date, date, this will hold date test occured (Currently Date_Test)
tr_Type, short text, this will hold all the values you currently have as field names (e.g. "Nt-ProBnp" is a value in this field now)
tr_Results, number, this will hold the numerical value of the tests

That's it those 5 fields will now accomodate every test result you have. Better yet, if you ever add more it doesn't require a structural change, you simply add the new type to [tr_Type] and it will work.

Then you use MajP's method to get Min, Max and latest, being sure to add [tr_Type] to the GROUP BY clause and you get the results you want for every test type without having to hard code it into a DMax(). That's the method you should pursue and not this 100+ Dmax method you seem set on.

 

[MajP]

Bringing in the date makes the approach harder. I had to use a subquery. Look and MinMax2 and the helper queries.
qryMinMax2 qryMinMax2

ID	HandC	Date_Test	Nt-ProBNP	Type
4​	111​	12/5/1974​	6354​	Max BP
5​	111​	4/5/2020​	54​	Min BP
6​	222​	12/5/2007​	654​	Min BP
8​	222​	7/5/1968​	654​	Min BP
9​	222​	1/1/2020​	6546​	Max BP
14​	333​	12/11/2014​	21478​	Max BP
15​	333​	3/1/2015​	11​	Min BP

 

[majordoc]

Thank you both MajP and plog for your advice - I'm absorbing as you go along. I can assure you I'm not set on any particular method here. Plog what you say makes eminent sense. In this new test database I have replaced NT-ProBNP field with Ix_Type (for investigation type) and put in a second test with sample data 6MWT.

I am lost where to go from here. MajP in your qryMax you entered a field BP:Nt-ProBNP and also Type:Max. This is bamboozling me and I can't see (even if I got this) how I am going to query the most recent result with date. i am trying to do this in my spare time between patients by the way so excuse my ignorance

 

[majordoc]

Also - I though it might be useful to show the table of investigations that i had drafted so far for the actual database - as you can see there quite a few with more to come. Could I conceivably use Ix_Type for all of these?

 

[MajP]

I am lost where to go from here. MajP in your qryMax you entered a field BP:Nt-ProBNP and also Type:Max. This is bamboozling me and I can't see (even if I got this) how I am going to query the most recent result with date. i am trying to do this in my spare time between patients by the way so excuse my ignorance

With a union query I can join the rows from 2 different queries as long as the number of columns match and the data types match. In the first query I returned the Max BP for each HandC. In the second query I returned the Min. In the first query I added a field which is nothing but a label "MAX". So each record is labled "Max". I did the same in the second query and added a label "Min". I called this field "Type". Without it you would not know which is which. I did not see you wanted last. That would be a third query to union.

To understand, build any query. Then add a calculated field. Type
MyLabel: "Some Value"
Every record will have a field with "Some Value"

What Plog is suggesting is called an Entity Attribute Value model

Entity–attribute–value model - Wikipedia

en.wikipedia.org

Ironically the example they use is a physician and symptoms/readings

Doing this way is IMO much harder to build, but makes it much easier to mine the data. Once built this way you have much more flexibility.
I can write code to turn what you have into EVA so you do not have to recopy all of what you have. Do you have data already. Can write the code to transform the data.

Unfortunately IMO this is not black and white. There are pros and cons for each approach and has to be considered. The EVA has a learning curve, since it is not as "traditional" of a design. However doing queries will be much easier. Instead of very many queries you can do the same in fewer.

If you want to go EVA, we can suggest the table structure. Like I said I can help build it in code. There is more involved then Plog suggested. The challenge is that all data has to be entered in text and you then need a table to determine the real data type. So you will need a few additional tables.

 

[MajP]

I built the eva. I put attachments in their own table. An long memo fields as Notes.
I ran code to build the table

tblAttributes tblAttributes

AttributeID	AttributeDescription	AttributeDataType	AttributeUnits
4​	TR_Vmax	6​
5​	Echo_Findings_FreeText	10​
7​	FEV1	6​
8​	FEV1%	6​
9​	FVC	6​
10​	FVC%	6​
11​	DLCO	6​
12​	DLCO%	6​
13​	KCO	6​
14​	KCO%	6​
15​	ECG_Findings	10​
16​	PaO2	6​
17​	PaCO2	6​
18​	stdBE	6​
19​	stdHCO3	6​
20​	Lactate	6​
21​	ONO_PSG_Findings	10​
22​	VQ_Findings	10​
23​	HRCT_Findings	10​
24​	Abdominal_USS_Result	10​
25​	Antinuclear_Antibodies	10​
26​	HIV	10​
27​	Hepatitis_B	10​
28​	Hepatitis_C	10​
29​	Free T4	6​
30​	TSH	6​
31​	Total Bilirubin	6​
32​	AST	6​
33​	ALT	6​
34​	ALP	6​
35​	gGT	6​
36​	Albumin	6​
37​	NT-ProBNP	6​
38​	VO2_peak	3​
39​	VO2_peak_%	3​
40​	VE/VCO2_slope	3​
43​	6MWT_Distance	3​
44​	6MWT_Nadir_SpO2	3​
45​	RHC_Performed_By	10​
46​	RHC_Vasoreactivity_Test_Performed	1​
47​	Deemed_Vasoreactive	10​
48​	mean_RAP	3​
49​	RV_systolic	3​
50​	RV_diastolic	3​
51​	RVEDP	3​
52​	PA_systolic	3​
53​	PA_diastolic	3​
54​	mean_PA	3​
55​	PCWP	3​
56​	LVEDP	3​
57​	PCWP_after_fluid	3​
58​	CO_Fick	3​
59​	CI_Fick	3​
60​	CO_Thermo	3​
61​	CI_Thermo	3​
62​	PVR_by_PCWP	3​
63​	PVR_by_LVEDP	3​
64​	TPG_by_PCWP	3​
65​	DPG_by_PCWP	3​
66​	TPG_by_LVEDP	3​
67​	DPG_by_LVEDP	3​

When you create a new Investigation, you run an insert query to add each attribute to that investigation. This goes into the junction table tblInvestigationAttributes.

In this design I can run a single query to find max, min, and last value for each metric. Instead of very very many individual queries.

 

[plog]

Maybe I'm underthinking it, but I think I was able to get the results you wanted using your new table--no UNION, no other tables.

I used a sub query to get the Min Result, Max Result and latest date of each HandC/lx_Type permutation. Then I linked that query back to the main table to get the actual result for the latest date.

Attached is the database with those queries (sub1 & MainQuery).

 

[MajP]

I interpreted this different as Min value, min date, max value, max date, last value, last date.

Query2 Query2

ID	HandC	Date_Test	Nt-ProBNP	Type
5​	111​	4/5/2020​	54​	Last BP
4​	111​	12/5/1974​	6354​	Max BP
5​	111​	4/5/2020​	54​	Min BP
9​	222​	1/1/2020​	6546​	Last BP
9​	222​	1/1/2020​	6546​	Max BP
8​	222​	7/5/1968​	654​	Min BP
6​	222​	12/5/2007​	654​	Min BP
11​	333​	5/1/2019​	4478​	Last BP
14​	333​	12/11/2014​	21478​	Max BP
15​	333​	3/1/2015​	11​	Min BP

 

[arnelgp]

see Query1 and the form, Query1.

 

[majordoc]

First thank you all so much for your time and help here - am in awe at the willingness for people to help each other.
I can see immediately why an EVA model is so powerful and probably, in the hands of yourselves, the right way to go. I agree with plog that a bit of underthinking might be more appropriate for me. I am a jobbing physician trying to develop this database to look after a cohort of patients with a rare set of disorders - there is no money for help from a database professional or anything like that. It is important that going forward I have the ability to fix problems and changes without having to go back to someone who basically wrote it for me! In total there will only be ~ 6 physicians and ~ 4 nurses using this on day to day basis. Basically what I;, saying is this needs to be dumbed down to my level!

I think I can figure out using either those recent methods from plog and arnelgp. I know this will result in a lot of queries to make but don't mind grafting. On a different note, once this is done is there a way to "update" the queries in one go after a tranche of new investigation results is input? If it's OK with you kind folk, I will send you a copy of the entire database over the next few days (minus any patient data of course) so you can point out the mistakes?

Many thanks again
E

 

[MajP]

Maybe I am missing something, but you did say you want not just the max,min,and last values but the dates for each.
As far as I can tell only the query I did provides the date for each in #17. This is a more complicated query.

And I need the dates associated with the max and min value, plus the most recent test result with date. - ie Date of Max result and result value, date of most recent result and value.