Avatar of shieldsco
shieldsco
Flag for United States of America 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
Microsoft AccessSQL

Avatar of undefined
Last Comment
shieldsco

8/22/2022 - Mon
als315

Your sample file has zero lengh. Please, uplad it again an show there expected result

Gustav Brock

Why shouldn't Mary's 67.2 not be updated to 45?

All other entries are doubled.

shieldsco

ASKER
I've attached the expected results



qrySumAgedCase1.xlsx
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Gustav Brock

This is not possible. Sometimes it is the last record that should be set to 0, sometimes the (two) first:

                                       

 PhillipMIAMIOct70,40
 PhillipMIAMIOct70,40
 PhillipMIAMIOct70,4


That can only be done manually.

shieldsco

ASKER
Why not update 0 to both records in that case
als315

Do you have any key field (record number) in your table? Can we add it?

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
shieldsco

ASKER
I can add one
John Tsioumpris

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)

Database14.accdb

shieldsco

ASKER
John - I get an error on line         firstValue = .Fields("Goal") -variable not defined
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
John Tsioumpris

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.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
shieldsco

ASKER
Thanks