if else statement

[jk.]

I am trying to write an If then else statement in Access.

i have query mkePallet which is join from tbl_A and tbl_B using Material Code.

my statement needs to be something like this:
if TotalQty = OpenQty,New total = OpenQty
else if TotalQty < OpenQty, New total = TotalQty
else
New total = OpenQty

TotalQty is from tbl_A and Open Qty is from tbl_B. New total is the new field I am creating with that statement.

how can do this in query...anybody can help me....

 

[elliotgr]

New Total:iif([TotalQty]=[OpenQty],[OpenQty],iif([TotalQty]<[OpenQty],[TotalQty],[OpenQty]))

 

[jk.]

hi elliotgr,thanks for your rep and its work..

I have another question. i have a table tbl_P with 11 fields.

col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11
ab 123 eer 100 235 rerr reer eerr 50 50 eerr
ab 123 dfg 100 dfd dfdf fgfg fgfg 100 0 fgf
dfg ere rrr 500 eer erer erer rtr 150 350 rrt
dfg ere erf 500 ter fgfd fgf fgf 100 400 gfgf

what i want to do is the col4 in 2nd row will automatically change based on balance in col10 and item code in col2. col10 is (col4-col9).

is it posibble to do it in query...i really need help on this matter..please anybody help me...

thanks

 

[elliotgr]

No, If I understand Col 10 is Col4-Col9 and you want Col4 to be based on the value in Col10. You are creating a circular reference. Maybe you can explain a little clearer?

 

[jk.]

i have tbl_A that list out the open order detail. tbl_B contain the detail of a package. I have join this table using query,Old Item Code as the key and make tbl_C. 1 Old Item Code could have more than one open order.

Let say i have package 1, Old Item Code = acb, TotalQty = 300

when i join tbl_A and tbl_b, it will list all the open order with based on the Old Item code with different value of OpenQty.

can you please take a look at my attchment..

thank you so much for your help..

 

[elliotgr]

See if this is what you are looking for. I have tried to duplicate what you described, otherwise I will need snapshots of the tables and queries and some of the data