Link to home
Create AccountLog in
Avatar of shieldsco
shieldscoFlag 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.




User generated image

qrySumAgedCase1.xlsx
Avatar of als315
als315
Flag of Russian Federation image

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

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

All other entries are doubled.

Avatar of shieldsco

ASKER

I've attached the expected results



qrySumAgedCase1.xlsx

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.

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?

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)

Database14.accdb

John - I get an error on line         firstValue = .Fields("Goal") -variable not defined
ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks