Creating a form to manage stock levels

[CodLiverOil]

Hi

I've got a table that acts as a stock control for my company, it works fine but I wan't to make it work better.

At the moment, the table is displayed in table view, so the table is;

tblStock
Item
QtyIn
QtyOut
QtyRemaining



When the QtyRemainig get's below 50, I order some more of whatever it is, and manually change the QtyIn to the new vale and QtyOut to 0. QtyRemaining is a simple calculation of QtyIn - QtyOut.

I want to add a form for staff to use to pick an item from a drop-down and then enter the quantity they are after in a text box, and that fills in the QtyOut on the appropriate item, and also when I add stock I can enter that into a text box and it adds it to the QtyRemaining value and puts that into the QtyIn, then resets the QtyOut to 0

I have created the form that allows the staff to pick multiple items from a list, but how do I associate a text box to the QtyOut of the appropriate record?

Or is this a silly way of designing the database, is there a more elegant solution?

Thank you in advance.

[pbaldy]

I won't say silly, but it is novel.

The more typical approach is to store the in and out transactions, and calculate the quantity on hand from that. It doesn't sound like you have any history of what went in and out. What you ask is probably possible, though I am not picturing your form that lets users pick multiple items and input quantities.

[NTC]

as PB implies - you don't have a design for an inventory system; it would be:
tblStock
Item
Qty
Date

An IN would go into as a positive QTY, and OUT would go into as a negative QTY and the Qty Remaining is a calculation made on the fly via an Aggregate Query. The Date field is just that. Then you have history.

We are talking about the tables - the way things are presented to users in forms/reports can be controlled i.e. you can put an 'OUT' field where they enter a positive number and it gets changed to negative and goes into the same field as the IN field behind the scenes....

[orange]

See the info at this link for info on database and database design. There are some video tutorials included to help with concepts.

Good luck.

[CodLiverOil]

as PB implies - you don't have a design for an inventory system; it would be:
tblStock
Item
Qty
Date

An IN would go into as a positive QTY, and OUT would go into as a negative QTY and the Qty Remaining is a calculation made on the fly via an Aggregate Query. The Date field is just that. Then you have history.

We are talking about the tables - the way things are presented to users in forms/reports can be controlled i.e. you can put an 'OUT' field where they enter a positive number and it gets changed to negative and goes into the same field as the IN field behind the scenes....

Thanks all for your replies, I'll check out that videos as suggested too. As you can tell this is quite new to me!

With regard's the reply above from NTC, I can understand the suggestion, but I don't see how the history would work, other than it would show me the last time the stock level changed, either negative or positive. It's a feature I had not thought about (history of changes), but would be quite handy and it's something else I would learn on the way!

[orange]

For your own benefit and education, try to describe your "proposed system/database" in plain English. Identify the business processes involved. Create some data flow logic to see what is involved in which steps and to identify all the steps involved. Don't get hung up on Access or dbms jargon too early. The software won't do magic for you. You have to determine the problem and the solution and then transfer that into database/Access syntax as appropriate.
You can do a lot with pencil and paper. The key is a thorough understanding of WHAT the business processes are.

Good luck.