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
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