Query to Calculate field depending on Value of another field?

[crisenr]

hey guys . .

I have 3 fields: TEST, NETQTY, EXTENDED

NETQTY usually = EXTENDED depending on the TEST

BUT the Test PTCGCD has a EXTENDED value that is TWICE the value of NETQTY

How do i create a query which will update the EXTENDED field depending on the TEST value>?

For Example:

The general TESTS: AFP, ANAS, CYC etc . . EXTENDED = NETQTY(1)

PTCGCD: EXTENDED = NETQTY(2)

?

 

[ScottGem]

Extended should NOT be a field in a table. Since its calculated, it is not necessary to store it. the expression:

IIF([TEST] = "PTCGCD",[NETQTY]*2,[NETQTY])

will display the correct value for Extended wherever needed.

In a query use:

EXTENDED: IIF([TEST] = "PTCGCD",[NETQTY]*2,[NETQTY])