Help With Stock Database!

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