Managing Stock

[Allen Browne]

That is probably a bigger question than you realize.

As a starting point, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello

I have creat a database about a General Store. I have made 4 tables:
Products:
ProductID AutoNumber
ProductTag Number
ProductName Text
UnitPrice Currency
Stock Number

Customers:
CustomerID
CustomerName
CustomerPhone
CustomerAddress
Order:
OrderID
OrderDate
CustomerID
ProductID Lookup Wizard (Products)
UnitPrice
Quantity
Payments:
PaymentID
PaymentDate
CustomerID
PaymentAmount
Then I created a form from Customers Table>>>subform of Orders>>>subform
of Payments>>>in orders subform i.e. (sbfOrders)"=[ProductID].[column](2) and then i make a field "UnitPrice" and on
ProductID's ComboBox in "afterupdate" i entered the code>>>
me![ProductName]=me![ProductID].[column](3) >>>> Now i want to make a
field "Stock" which should manage the remaining stock when ever i sell any
thing.

Note: sbfOrders >>> is a continuous form and every item has a total stock
of 20. now how can i manage the stock. for example i have sold one
keyboard and two mouses now next time for the new entry it shoud show in
stock 19 keybaords and 18 mouses.

if i have not explain in good way, plz try to understand my problem and
guide me.

Best Regards

Shakeel Ahmad

(e-mail address removed)
www.groups.yahoo.com/group/islam-the-final-truth
www.geocities.com/shakeelahmad_79

 

[Todos Menos [MSFT]]

USE SQL Server

you can do things like triggers in SQL Server that would take a
mountain of code in VBA

Hello

I have creat a database about a General Store. I have made 4 tables:
Products:
ProductID AutoNumber
ProductTag Number
ProductName Text
UnitPrice Currency
Stock Number

Customers:
CustomerID
CustomerName
CustomerPhone
CustomerAddress
Order:
OrderID
OrderDate
CustomerID
ProductID Lookup Wizard (Products)
UnitPrice
Quantity
Payments:
PaymentID
PaymentDate
CustomerID
PaymentAmount
Then I created a form from Customers Table>>>subform of Orders>>>subform of
Payments>>>in orders subform i.e. (sbfOrders)>>>I created a field Date(from orderDate)>>>Then ProductID(from
"=[ProductID].[column](2) and then i make a field "UnitPrice" and on
ProductID's ComboBox in "afterupdate" i entered the code>>>
me![ProductName]=me![ProductID].[column](3) >>>> Now i want to make a field
"Stock" which should manage the remaining stock when ever i sell any thing.

Note: sbfOrders >>> is a continuous form and every item has a total stock of
20. now how can i manage the stock. for example i have sold one keyboard and
two mouses now next time for the new entry it shoud show in stock 19
keybaords and 18 mouses.

if i have not explain in good way, plz try to understand my problem and
guide me.

Best Regards

Shakeel Ahmad

(e-mail address removed)/group/islam-the-final-truthwww.geocities.com/shakeelahmad_79

 

[Scottgem]

Hi Shakeel,

Yes, definitely check out the article Allen linked you to. Its
considered the definitive explanation for your question.

I just wanted to add one point here. I doubt if your customers will
always order ONE product item with each order. Generally, you need to
provide a way to order multiple items. Do you need an Orders table and
an Orders detail table. The Detail table will be the trasnaction table
discussed in Allen's article.

HTH
Scott<>
Microsoft Access MVP 2007

Hello

I have creat a database about a General Store. I have made 4 tables:
Products:
ProductID AutoNumber
ProductTag Number
ProductName Text
UnitPrice Currency
Stock Number

Customers:
CustomerID
CustomerName
CustomerPhone
CustomerAddress
Order:
OrderID
OrderDate
CustomerID
ProductID Lookup Wizard (Products)
UnitPrice
Quantity
Payments:
PaymentID
PaymentDate
CustomerID
PaymentAmount
Then I created a form from Customers Table>>>subform of Orders>>>subform of
Payments>>>in orders subform i.e. (sbfOrders)"=[ProductID].[column](2) and then i make a field "UnitPrice" and on
ProductID's ComboBox in "afterupdate" i entered the code>>>
me![ProductName]=me![ProductID].[column](3) >>>> Now i want to make a field
"Stock" which should manage the remaining stock when ever i sell any thing.

Note: sbfOrders >>> is a continuous form and every item has a total stock of
20. now how can i manage the stock. for example i have sold one keyboard and
two mouses now next time for the new entry it shoud show in stock 19
keybaords and 18 mouses.

if i have not explain in good way, plz try to understand my problem and
guide me.

Best Regards

Shakeel Ahmad

(e-mail address removed)
www.groups.yahoo.com/group/islam-the-final-truth
www.geocities.com/shakeelahmad_79

 

[Shakeel Ahmad]

Hello

I have creat a database about a General Store. I have made 4 tables:
Products:
ProductID AutoNumber
ProductTag Number
ProductName Text
UnitPrice Currency
Stock Number

Customers:
CustomerID
CustomerName
CustomerPhone
CustomerAddress
Order:
OrderID
OrderDate
CustomerID
ProductID Lookup Wizard (Products)
UnitPrice
Quantity
Payments:
PaymentID
PaymentDate
CustomerID
PaymentAmount
Then I created a form from Customers Table>>>subform of Orders>>>subform of
Payments>>>in orders subform i.e. (sbfOrders)"=[ProductID].[column](2) and then i make a field "UnitPrice" and on
ProductID's ComboBox in "afterupdate" i entered the code>>>
me![ProductName]=me![ProductID].[column](3) >>>> Now i want to make a field
"Stock" which should manage the remaining stock when ever i sell any thing.

Note: sbfOrders >>> is a continuous form and every item has a total stock of
20. now how can i manage the stock. for example i have sold one keyboard and
two mouses now next time for the new entry it shoud show in stock 19
keybaords and 18 mouses.

if i have not explain in good way, plz try to understand my problem and
guide me.

Best Regards

Shakeel Ahmad

(e-mail address removed)
www.groups.yahoo.com/group/islam-the-final-truth
www.geocities.com/shakeelahmad_79