asked on Access Udate Query
I have a Access table (tblExcelProductivity) that includes FullName, FieldOffice, Mnth , and Goal. I would like to use an update query to update the Maximum Goal number with 0 for each like record based on FullName, FieldOffice and Mnth. For example below, Mary has 2 records in Arlington in the month of DEC with Goals of 45 and 67.2. In this case 0 would be updated 67.2 the max for the 2 records.
![Image]()
qrySumAgedCase1.xlsx
qrySumAgedCase1.xlsx
Microsoft AccessSQL
Last Comment
shieldsco
Why shouldn't Mary's 67.2 not be updated to 45?
All other entries are doubled.
ASKER
This is not possible. Sometimes it is the last record that should be set to 0, sometimes the (two) first:
| Phillip | MIAMI | Oct | 70,4 | 0 |
| Phillip | MIAMI | Oct | 70,4 | 0 |
| Phillip | MIAMI | Oct | 70,4 |
That can only be done manually.
ASKER
Why not update 0 to both records in that case
Do you have any key field (record number) in your table? Can we add it?
ASKER
I can add one
Normally this a somewhat easy case except of the one with the "PHILIP" entry as mentioned by Gustav...which kind breaks the rules..
I prepared a small application that takes care of it..and it functions in all cases besides the order in "PHILIP"
take a look (the results are put in Goal2 field)
ASKER
John - I get an error on line firstValue = .Fields("Goal") -variable not defined
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks
Your sample file has zero lengh. Please, uplad it again an show there expected result