Please help... complicated IIf Statement

[AccessTokyo]

I need to track the Delivery lot number being sold value of the below query. Please help, I am newbie to MS Access.



The first delivery of 1000 liters is tagged as Delivery lot number ”1”, the second as “2”, the third as “3”, and so on. The column Delivery lot number being sold will track which delivery lot is currently being sold so the first four rows are tagged as “1” because they have not sold up the first delivery which is 1000 liter but it is tagged as “2” in the fifth row which means the second delivery is getting started to get sold and so on.

The logic should like:
Delivery lot number being sold = IIf ((Delivery of Delivery lot number (n) – Sold Running Total) < 0, (Delivery lot number(n) +1), Delivery lot number (n))

[rpeare]

This is a bad design if this is your table structure. IF you are not in control of the structure of this table or the content of the database you might be better off doing this in a report and not attempting to do this in a query. Or alternately exporting it to excel and doing what you want with a formula, this is not a normalized structure and there are tons of issues if this is the extent of your data.

[AccessTokyo]

Hi RPeare,
What do you mean not in control of the structure of table? the first four columns are as simple as you see it (the data or values are already there). only the fifth query column I need a logic to put 1 or 2 or 3 or 4 or so on.
In other forums too, people just dispose of my inquiry saying design is bad blah, blah, blah, and not really able to provide concreate solution. If you suggest to put this in report, can you be specific how to?

[rpeare]

In terms of receipt/disbursements of *anything* the structure you have shown is really bad for a variety of reasons.

1. There is no way to organize the data the same way (reliably) every time so, for instance it's possible for a query to run on your data and have the last record appear first which would throw off all of your formulas
2. You are storing a receipt and disbursement on the same record

These are the two big ones, what you actually want is a more normalized structure like

tblTransactionType
TT_ID  TT_Desc
1      Receipt
2      Disbursement

tblTransaction
T_ID  TT_ID  T_Qty  T_LotID  T_Date
1     1      1000   1       1/1/2015
2     2      20             1/1/2015
3     2      100            1/2/2015
4     2      850            1/3/2015
5     1      500    2       1/3/2015
6     2      20             1/4/2015
7     2      30             1/5/2015
8     1      2000   3       1/5/2015
9     2      400            1/6/2015

[NTC]

In terms of an IIF statement - it appears you pretty much nailed it and it is just a matter of using brackets around field names, and of course using the exactly correct field name:

Delivery lot number being sold = IIf ([Delivery lot number] – [Sold Running Total] < 0, [Delivery lot number] +1, [ Delivery lot number])

the comma separates the clauses - so you don't really need ( ) for simple math within a clause, although it is ok....