Calculated Status Field

[moore020]

Can anyone tell me how to create a calculated field that does the following:

I have a table that has about 4000 records in it. An asset can be listed in the table multiple times but I need to change the a status to "Dead" if any record with the same asset id has an action of "Kill".

Any ideas?

 

[BlueIshDan]

Need more information. Show us some structure along with your description.

- [FieldName]
- [FieldName]

Some Examples of records.

Example of required outcome.

Thanks!

 

[moore020]

Table: [Details]
- Field Name [ProjectNumber]
- Field Name [Action]

ProjectNumber Status Action 100002.0004.AC.ABCEFGHIJKLMN Dead NEW 100002.0004.AC.ABCEFGHIJKLMN Dead INCREASE 100002.0004.AC.ABCEFGHIJKLMN Dead DEOB

In cases where the project number is the same, if there is a DEOB action, the status would be dead.

 

[BlueIshDan]

I cannot follow the format of the example of records, could you space each individual record on new line?

 

[moore020]

ProjectNumber Status Action
100002.0004.AC.ABCEFGHIJKLMN Dead NEW
100002.0004.AC.ABCEFGHIJKLMN Dead INCREASE
100002.0004.AC.ABCEFGHIJKLMN Dead DEOB

 

[BlueIshDan]

Ok now what are some examples of desired outcome and why?

 

[moore020]

The status field where each one of them is showing as dead is the desired outcome and it is dead because one of the actions was deob. Basically, if an asset get a deob action then it needs to show as dead.

 

[smig]

Create an update query.
Put "kill" as the criteria of the Action field and update the Status field to "dead".
Run the query (!) to update.

I suggest you to create a Status table and in the original field to put the status key, not a text.

 

[moore020]

Thanks Smig, I think this will work for me.