MAX Function In Acess

[northenalias]

=MAX(0,OH!B18-SUMIF(orders!B:B,B22,orders!C:C))

I need the above Excel formula to work in Access but I do not se the same functions available, any suggestions.

[split_atom18]

http://www.techonthenet.com/access/f...umeric/max.php

If you have conditions you want it to meet to max, then you can put them in the criteria or via formula in the (expression) part.

Hope this helps,

Dan

P.S. If you want more info we need more info. I can't see you excel sheet or access sheet so i don't know what that is looking for really.

[northenalias]

Thanks Dan,

Actually I am looking for a solution to allocating Inventory at the item level. The task is to create query that will look at qty OH and subtract the qty needed and return the available qty. The trick is when there is muliple orders with the same item ex Item 1234 has 100 pcs availble and I have 6 orders for 20 ea. How can I show in table form the allocation of this inventory?

[split_atom18]

Have you looked at the Northwind Database that is provided with Microsoft access? There are a lot of good inventory examples in that database. From allocating to on hand to ordered etc. Inventory is a hard subject to help people with being that it can be extremely complex. I will gladly help you if I can.

Hope that helps you,

Dan

[northenalias]

Thanks Dan,

I guess my major issue is the fact that any table I create that decrements inventory shows like this

item OH sales available
11223 20 10 10
11223 20 15 5
11223 20 12 8

Actual -17 available
any ideas?
You see

[split_atom18]

Did you look at the Northwind Database?

I would have a inventory transactions table. Every time I sell or buy an item it would add a line in that table with part number, quantity,type(add or subtract), date created, date modified, comments, maybe an order and invoice # fields also. Then I would use a query to determine quanity available OH etc.