Using DMAX in query with String criteria (1 Viewer)

[caprice63]

Hello.
I've trawled the forum and thought I found the answer but it just gives me errors.
Scenario:
I have a table "RTr". It contains rows of names (NNN) and corresponding numerical data e.g. Score, Rating etc.
The names in the table may be repeated in different rows as the numerical data changes.

Problem:
I want to create a formula which selects the current row name (NNN) and checks all the other rows in the table for the same NNN and return the Maximum of the numerical data.

What I have done:
I created a query that pulls in most of the fields from the RTr table
In the builder I have tried this
Expr1: DMax([RTr]![Rating],"RTr","[RTr]![Name] = " & [RTr]![Name])
but this gives me #Error in the Expr1 field of all rows when I run the query.

When I click on one of the #Error items it presents me with a dialog box saying Syntax error (comma) in query expression '[RTr]![Name] = O'Neill, Mr TC'.

The names are fixed so I can't take out the commas etc. that separate the surname from the initials.

Any advice would be very much appreciated.
Thanks

PS I also need to do something similar but which will check the NAME and DATE fields and return the max value of all rows where the Name and Date are the same.
In Excel the formula is =MAXIFS([Rating],[Name],[@Name],[Date],[@Date])

If you also have any advice on doing this in Access that would also be good.
Thanks

 

[isladogs]

Your DMax syntax is wrong but there is a better way

Create an aggregate query by clicking the Totals button on the ribbon.
Group by ID field, name and any other fields you want.
Then add your Rating field and change Group By to Max in the Totals row

 

[caprice63]

Hi Colin,
Thanks for the tip. I've been testing it out and it looks to be the way forward.
Cheers
Chris

 

[isladogs]

Hi
Glad to have helped