Here's What I Want to Do.

[Izzittdashooz]

I don't want to create and inventory that has so much to do with Sales Orders as Work Orders. I would say I have quite the unique situation here, so let me elaborate.



I have been asked to create a database that holds the following information:

• A number of items in storage.
• A number of items on a store shelf.
• A number of items in process.
• The ability to see the number of items available to ship (storage+store shelf).
• The ability to add the quantity of in process items to the quantity in storage via a form.
• The ability to add the quantity of storage items to the quantity on the store shelf via a form.
• The ability to subtract from the quantity InBins and InStore when they ship items.
• The ability to create work orders for workers for items to be produced (the quantity in process) via a form.

I have created a Products table.
I have created an Inventory Table with two columns, InBins and InStore.
I have created a report that adds those two numbers and creates a calculation called available to ship.

Here's their proposed process:

• The work order for the item to produce is created.
• The quantity to be produced is put a field I call "InProcess."
• When marked complete, the quantity in the InProcess field is added to the InBins field in the Inventory Table.
• If they move products from storage to the shelf they want to open a form, put in how many they're moving and have it automatically update the InBins field and the InStore field.
• If they ship products from storage or even have to pull from the shelf to ship they want to open a form, put in how many were shipped and have it automatically update the InBins and/or InStore field.
• Because I know how things work around there, we also need the ability to adjust that inventory via a form in the event someone yanks items out of production early to ship via a form.

Here's my dilemma. I know NOTHING about scripts. I have attempted to do this math they're asking for via queries, but I can't figure them out.

I have looked at dozens of templates. All of the ones that I have seen have sales order modules in them. I have not figured out a work around for those...they don't want to integrate their sales data...they simply want inventory tracking with forms.

I have made several attempts at queries and attempted to do math that way, but can't make them work. I have tried update queries to no avail...

I am also open to the idea I am forgetting about a query type or something...

Any ideas?

Ryan

[aytee111]

I couldn't figure out where the InProcess qty is being kept, it should be on the Inventory table.

You can use the form to do the calcs for you. There will be a combo box for them to select the product and then the three quatity fields will be displayed. They can be read only with unbound textboxes used for them to enter new qty's and then in the AfterUpdate event you can recalculate the values which would be simple a + b or a - c or whatever. Or the user can enter the qtys directly into the correct controls.

I think I am missing something, are different people updating these qtys in different places? Why is the process being made so complicated?

[Izzittdashooz]

I couldn't figure out where the InProcess qty is being kept, it should be on the Inventory table.

The InProcess quantity is being kept in a table for Work Orders. This is done so that the items being produced can have a status update so that workers know where to start if something is left overnight and there's another step. That is why we are hoping to create something that, once the work order is marked complete it would be added to the InBins quantity.

I think I am missing something, are different people updating these qtys in different places? Why is the process being made so complicated?

To a degree, yes. There are a few things that happen. First, as stated earlier, sometimes worker finish things others have started. Second, sometimes items in process in small quantities when needed to finish an order are expedited so that they can be included in a shipment. For example, if I need two of these items that just need labels to go out on today's order, I will slap the labels on an send them out.

Ryan

[aytee111]

When they mark a quantity as complete on the WO, in the AfterUpdate event run a simple update query to add that quantity to the InBins value.

The other updating can be done with a bound form (bound to the Inventory table) and calculations done on the form itself.

[orange]

Have you built a data model?
See the first few topics here. Entity relationship diagramming.
You have to get the tables and relationships structured before you start dealing with forms etc.