Stock Control Database

I’m running a small shop and I need a database to help me keep track of the
sales and most important, the stock. I need to enter all the stock in the
database(stock table), so that it reduces as I make sales.

At the moment, I have two tables, sales and stock and they have the
following fields.
Sales: sale#(autonumber)
Date
Item bought(text)
Quantity(number) [this is quantity purchased by a customer]
Unit price (number)
Total (number)

Stock: Item(text)
Quantity available(number) [ this is quantity available in stock]
Unit Price

The sales are carried out using the following form:
On the form, I have all the following:
Sale number (autonumber)
Date
Item bought : this selected from the combo box
Quantity : this selected from the combo box
Unit Price : this is entered manually
Total : this comes automatically when you enter unit price.

The problem is that I don’t know how I can link the two tables so that the
stock
table(Quantity field) automatically reduces when I make a sale on the form.

Further, I also want a situation where, when an item is selected from the
combo
box on the form, the associated unit price should be displayed
automatically in
the unit price field of the form.

Use the AfterUpdate event of the Item combo to lookup the price. There is an
example of how to do that in the Northwind sample database, Order Details
form, ProductID combo.

For an introduction to the idea of stock control, see:
Inventory Control - Quantity On Hand
at:
http://members.iinet.net.au/~allenbr...Inventory.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Wantula" wrote in message
...
I'm running a small shop and I need a database to help me keep track of
the
sales and most important, the stock. I need to enter all the stock in the
database(stock table), so that it reduces as I make sales.

At the moment, I have two tables, sales and stock and they have the
following fields.
Sales: sale#(autonumber)
Date
Item bought(text)
Quantity(number) [this is quantity purchased by a customer]
Unit price (number)
Total (number)

Stock: Item(text)
Quantity available(number) [ this is quantity available in
stock]
Unit Price

The sales are carried out using the following form:
On the form, I have all the following:
Sale number (autonumber)
Date
Item bought : this selected from the combo box
Quantity : this selected from the combo box
Unit Price : this is entered manually
Total : this comes automatically when you enter unit price.

The problem is that I don't know how I can link the two tables so that the
stock
table(Quantity field) automatically reduces when I make a sale on the
form.

Further, I also want a situation where, when an item is selected from the
combo
box on the form, the associated unit price should be displayed
automatically in
the unit price field of the form.