Field supercedes another field

[Thompyt]

TRANSFORM Count([CR_ID])-1 AS R_ID
SELECT [Change Request].Level, Count([Level])-3 AS [HB Totals]
FROM [Change Request]
WHERE ((([Change Request].Sub_No)=0) AND (([Change Request].Date_Closed) Between Date()-7 And Now()) AND (([Change Request].AO_Vote)="Hold" Or ([Change Request].AO_Vote)="Defer" Or ([Change Request].AO_Vote)="Pending"))
GROUP BY [Change Request].Level
PIVOT [Change Request].AO_Vote;

What if another field such as O6_Vote is updated and what I need reported is not in the AO_Vote, but in the O6_Vote field. How can I present this under the AO_Vote field?



Normal case O6_Vote = Null

IIf(Not isNull(O6_Vote), [O6_Vote], [AO_Vote]) in the Criteria?

AO_Vote also has a normal criteria of "Hold" or "Deferred" or "Pending" (They all mean the same to me, but the user wants this diferentiated for some reason)

Do I need to put in a switch statement somehow?

[John_G]

It might be easier just to run an update query on the [Change Request] table first:

Currentdb.execute "Update [Change Request] set AO_Vote = O6_Vote where AO_Vote is Null and OA_Vote is not null"

What happens if there is a value in both of them?

[Thompyt]

AO_Vote will never be null if O6_Vote is fileld in. You can't change the AO_Vote field contents to mirror the O6_Vote. The vote may be "Approved" at the AO_Vote level, but the O6_Vote level could Approve, Deny, Defer or Withdraw, wich doesn't always reflect the AO_Vote.

[John_G]

Understood.

What you can do then is create a query which has all the fields of [Change Request], plus one which you calculate in based on all those criteria, and base your Crosstab query on that.

[Thompyt]

In other words I need to make an If then statement per se on the field I want changed? I put an expression in the field title? Outcome: IIf(Not isNull(O6_Vote), O6_Vote, AO_Vote). Why couldn't I have that in the AO_Vote field as an expression? I'll give it a try, and see how it goes.

Gracias, Danke