We help IT Professionals succeed at work.

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
Comment
Watch Question

CERTIFIED EXPERT

Commented:

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

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:

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

All other entries are doubled.

Author

Commented:
I've attached the expected results



qrySumAgedCase1.xlsx
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:

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.

Author

Commented:
Why not update 0 to both records in that case
CERTIFIED EXPERT

Commented:

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

Author

Commented:
I can add one
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

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

Author

Commented:
John - I get an error on line         firstValue = .Fields("Goal") -variable not defined
Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:

Download and run the attachment as it is...i have changed the imported table name...for easiness...just checked and is working.

The big button on the form

Author

Commented:
Thanks