Field in query formula question

[jcaptchaos2]

I have pulled my query data from access into excel because I know what formula I would use in excel, the issue is I don't want to use excel I want to keep iy in access. My formula to get the result in excel is:

=IF(B2=B1,F1-F2,0)

I want to put something like this in my access query, the Field name in access is for column B - "ItemNumber" and for column F - "OpNumber" how would I do that or can I do that?



Thanks

[ssanfu]

Use the IIF() function (immediate if)... Try:

=IIF(ItemNumber=OpNumber,F1-F2,0)

Replace F1 and F2 with field names.

[jcaptchaos2]

Thanks ssanfu I will give that a try

JC

[jcaptchaos2]

Ok after looking at this it is not quite what I need as in your advice F1 and F2 and the same with colum B need to be the same field name for me, in an excel spread sheet it would be the row above minus the row below. I would have to be something like,

=IIF(row2 (shopordernumber)=row 1 (shopordernumber), row1(WIP)-row2(WIP),0)

So if in my query result the (shopordernumber) from row1 result = the (shopordernumber) from row2 it would take the (WIP) result from row 2 and subtract it from row 1.

results from query

---------shopordernumber --- WIP --- Formula needed
Row 1 -------- 12345 -------- 2000 -------- 0
Row 2 ---------12345 -------- 500 ------- 1500

[ssanfu]

Sorry, I totally misread your question.

This is like calculating vehicle mileage (or miles per gallon) when each record has only the odometer reading at fill-up. In this case you can use odometer reading or fill-up date to order the records in order to get the previous odometer reading to calculate the miles driven between readings. I use a function to get the previous reading.

---------shopordernumber --- WIP --- Formula needed
Row 1 -------- 12345 -------- 2000 -------- 0
Row 2 ---------12345 -------- 500 ------- 1500

Are the numbers from a table or query?
How is the row order determined?
Why is (WIP) 2000 in Row 1 and (WIP) 500 in row 2? Does the order matter?

[jcaptchaos2]

Sorry I also kind of mislead you, the data is from a query and the result from the query should look like this in a table as the WIP should be the formula,


Order # ---- OP# ---- Parts complete ---- WIP(need formula)
12345 ------- 10 -------- 2000 ----------------0
12345 --------20 ---------500 ---------------1500
12345 --------30 ---------200 ---------------300
55555 --------10 --------1000 ----------------0
55555 --------20 ---------900 ---------------100
55555 --------30 ---------750 ---------------150

and so on. Everytime the order number changes the result of the formula would be 0 because the order number from the previous row was different.

Thanks

[ssanfu]

Order # ---- OP# ---- Parts complete ---- WIP

Are these the actual field names?

[jcaptchaos2]

No the field names in the query are

Itemnumber, Item description, shopordernumber, opnumber, goodpcs and scrappcs and then I'm attempting to add that formula (WIP) next