Help with stock database!

[emshim]

Hi,

I am creating a database which keeps control of stock. Each item is scanned in with a barcode scanner and given a unique stock number. The Manufacturer, System, Subsystem, Location and Classification is then added.

When a customer phones, and orders a product i.e. a camera, a customer order number can be added to the item in stock. The problem with this is that if the customer is ordering a number of items, and other items are not in stock yet, this item needs to be seen as unavailable. Due to large amounts of stock, i do not want to allocate the customer a specific item with stock number, but just allocate one of the many items of that sort. I do not want to allocate a specific item as when you go to collect the item from the stock room, every item of this type would have to be checked to find the specific stock number, which would be timely and difficult.

I tried using a make table query, which copies the information from the stock table, except the stock number, and so with this list, i will have a list of every item. I can then produce a table which shows me how many of each type of item is available. The problem is, this needs to update every time more stock is added, but i also need to be able to allocate a number of the items, and this becomes unavailable stock. At the moment, each time the query is run, the information is overwritten.

Please can someone help me if they can think of a better idea of how to do this! Im not that hot on programming, and so a in depth description of what to do would be great!

Emily

 

[Guus2005]

Deja vu???

 

[emshim]

Deja vu???

you've had this problem before?

 

[neileg]

If you search in these forums, you'll find lots on stock control and inventory. There are also Access templates that will create applications for inventory and order tracking. That will give you some background.

When a customer phones, and orders a product i.e. a camera, a customer order number can be added to the item in stock. The problem with this is that if the customer is ordering a number of items, and other items are not in stock yet, this item needs to be seen as unavailable. Due to large amounts of stock, i do not want to allocate the customer a specific item with stock number, but just allocate one of the many items of that sort. I do not want to allocate a specific item as when you go to collect the item from the stock room, every item of this type would have to be checked to find the specific stock number, which would be timely and difficult.

I think you need to capture the serial number after you have retrieved the item (assuming you want to capture it at all).

I tried using a make table query, which copies the information from the stock table, except the stock number, and so with this list, i will have a list of every item. I can then produce a table which shows me how many of each type of item is available.

You don't make tables to do this, you need a query. Plus you don't copy all the data from one table to another in most cases. If each stock line has a unique number (the primary key in the table) then you only need hold this number to retrieve the data when you need it.

The problem is, this needs to update every time more stock is added, but i also need to be able to allocate a number of the items, and this becomes unavailable stock. At the moment, each time the query is run, the information is overwritten.

You need to store transactions, not balances on hand. If you store stock purchases, stock sales and allocations, then the unallocated stock is the sum of these transactions (stored as +ve and -ve values).