Inventory control

[joaoclb]

Hi everyone,


I am working in a database to control the stock of tools. I have a table called tools which has all the tools in the system and 3 tables regarding shippments, shipping to clients (shipping bills), shipping from clients to maintenance and shipping from maintenance to the stock again.



The table shipping to clients (shipping bills) I turned into a form, and the part to select the tools to be shipped I would like to make it a multivalued field, but my problem is that as soon as I checked the tools, i would like them to be unavailable in the list until they are back in the stock again.


And I would like to create a query with a input paramenter (date) to know where the tools is.


Could anyone give me any advice how to do it? I would be extremely grateful.



Best regards

 

[Galaxiom]

Have you considered the Shipping all being in one table? A Source or Destination field would be enough to differentiate the movements.

Much easier to interact with and track a sequence of events and locations in one table.

 

[vba_php]

but my problem is that as soon as I checked the tools, i would like them to be unavailable in the list until they are back in the stock again.

joa,

you would not want to attempt to do that by manipulating the listbox or combo box, whatever you're using. that sort of thing is info that's supposed to be stored in a table. Galxiom is saying almost the same thing. you could possibly store all your transactions (shipping bills, shipping TO clients, shipping TO maintence) in one table and indicate what's happening in a single field. as in, indicate where exactly the tools are in their journey from party to party. then, you can list available tools your form control by querying out all tools that don't have status of "in transit" somewhere assigned to them. the attached sample may help to some degree. the "sales" table is similar to what you would have. "sales" for you would be something like "tools checkout" or "tool transactions". the rest of the attached sample is irrelevant, cuz the purpose is to illustrate an accounting concept. I apologize if the attachment is a little too advanced. that's why I singled out the only relevant table to look at. I'm also pretty sure microsoft's northwind database sample illustrates what inventory control looks like using a single table. you might want to download that sample and take a look.