Update Column from Another Column - Plz check attachment

[ishchopra]

Hello Experts,



I have a table with a column called Z_Status: the values of this column are DUP and Survivor, which means one of the record is Duplicate and another is survivor.

Now i have to update last Column called New_Account from Column C (EV870_ACCT_CODE), the New_Account Column should only be updated by the Max(EV870_ACCT_CODE) when grouping the information by Key2 Column.

I have shown the output and highlited the result in Green background (please check the attachment).

Apologize if i have not explained it well.

Waiting for the suggestions

Cheers

[June7]

Try:

UPDATE tablename SET New_ACCOUNT = DMax("EV870_ACCT_CODE","tablename","Key2='" & [Key2] & "'");

Is this real names and emails you have posted? Might want to remove the attachment.

[ishchopra]

Hello,

Thanks for looking into this, i have tried it but it doesnt work

can you please explain what this part is doing : DMax("EV870_ACCT_CODE","tablename","Key2='" & [Key2] & "'");


Please help

[June7]

I tested the sql on the sample data you show. It worked for me. What happens when you try - error message, wrong results, nothing? Show your exact sql statement.

DMax is a domain aggregate function. In this case it is retrieving the maximum EV870_ACCT_CODE value for each KEY2 value.