Calculated field for max value rollup (1 Viewer)

[Lb_User]

I have a database where for each person (ID), I need to calculate the most adverse score by weight (0 is worse than a 10) by section. See SectionRollup for expected value.

For each ID and SectionTitle, what score has the highest (max) weight?

ID	SectionTitle	Score	Weight	SectionRollup
1	1	3	3	8
1	1	8	8	8
1	2	0	11	0
1	3	3	3	3
2	1	2	2	10
2	1	10	10	10

 

[theDBguy]

Hi. Have you tried using a Totals query? If you want to show all the columns, you may have to create two or three queries. Another option is to use the DMin() function.

 

[Lb_User]

I can't figure out how to do a totals query or DMax formula that takes two columns into account and then to dump the result in a new column.
DMax(«expression», «domain», «criteria») ----- my interpretation: Dmax([CaseData.].[Weight]),[CaseData],???)

See attached - let me know if more info would help.

 

[theDBguy]

I can't figure out how to do a totals query or DMax formula that takes two columns into account and then to dump the result in a new column.
DMax(«expression», «domain», «criteria») ----- my interpretation: Dmax([CaseData.].[Weight]),[CaseData],???)

See attached - let me know if more info would help.

Hi. I hope I understood what you wanted. Please see the attached and open the query qryScoreRollup and let us know if it's correct or not.

 

[Lb_User]

Thank you theDBGuy, it works perfectly! You saved me so much time.

 

[theDBguy]

Thank you theDBGuy, it works perfectly! You saved me so much time.

Hi. Glad to hear it worked for you. Good luck with your project.