Logical Functions in Queries

[Mschneider331]

Using a products table that will show product ID, description and I need to create field that will show a message "time to reorder" if the qty in stock is less than one, or "in stock" is not. And also build a reminder field that handles both conditions (time to reorder, or in stock).


I'm lost how to do this and not finding much online to figure it out. Ive tried playing with Iif functions but I'm not getting it, any help I will be really grateful.

[orange]

Try
msg: iif([quantity in Stock]<1,"Time to reOrder","")

It would be helpful if you showed us the query you are using.

There is considerable info on Access functions at the link in my signature.

[Micron]

I don't think your product status/reminder field should be in your Products table. It should be in an inventory table.

[orange]

I agree with Micron regarding the table involved, but we know nothing of your database.

In Inventory control, the concept you are describing is called ReOrderPoint, and it is related to ReOrderLevel - how much of this Product should be ordered - for each Product in Inventory.

The reorder point (ROP) is the level of inventory which triggers an action to replenish that particular inventory stock. It is a minimum amount of an item which a firm holds in stock, such that, when stock falls to this amount, the item must be reordered.

If you look at the attached data model of a Plant Nursery Inventory from Barry Williams' site you'll see a table called Inventory_Levels, that is where ReOrderPoint would be evaluated.

[Micron]

This might work for something like a nursery, but for complicated stock keeping, I don't think so. An example of where not would be a manufacturing plant stores system that has parts or assemblies out for repair. They are still part of the inventory, but they are not in stock. The nursery example is one where a calculated value is being stored, which you and I know is to be avoided whenever possible. Whether or not as a means of further normalization this warrants a comparison (at the form level) of all receipts minus all disbursements as an indicator of stock level would be up to the designer and or db user, I suppose. Not saying I couldn't live with a stored inventory number in any case - just that I would consider not doing so.

[orange]

Micron,
Agreed. There are various scenarios that could make the original post more complex. But more details and the data structure being used are necessary to advise on details.
I think the model offered by Barry is a generic model. He does show the Inventory_Levels as a table including Stock_Taking_Date which, to me, is aligned with Allen Browne's stock taking approach. This is a physical count as of a specific day. You would use a query and the latest stock taking date to get current Inventory (count).
I don't think the OP is dealing with this level of complexity ( components in repair/maintenance, on loan to another organization etc).