Auto Calculation.

[mel9338]

sorry if i am asking a question previously asked in this forum because i try to search it yet doesn't seems to find any thread relating to my question.

here is my question..

i got two table

tblHQ
Inv_ID - autonumber
Brand - text
Article - text
Color - text
size - number
qty - number

tblsales
Brand - text
Article - text
Color - text
size - number
qty - number

is it possible that i enter the field in tblsales and the value in tblHQ will auto minus out the qty i enter in tblsales. anyone can show me how?

thanks in advance

 

[ecniv]

sorry if i am asking a question previously asked in this forum because i try to search it yet doesn't seems to find any thread relating to my question.

here is my question..

i got two table

tblHQ
Inv_ID - autonumber
Brand - text
Article - text
Color - text
size - number
qty - number

tblsales
Brand - text
Article - text
Color - text
size - number
qty - number

is it possible that i enter the field in tblsales and the value in tblHQ will auto minus out the qty i enter in tblsales. anyone can show me how?

thanks in advance

tblItem
ItemID - auto
Brand - text
Article - text
Color - text
size - number
qty - number

tblHQStock
HQstockID - auto
ItemID - number
qty - number

tblsales
SalesID - auto
ItemID - number
qty - number

Select tblItems.itemID,QtyHQS,QtySales,QtyHQS-QtySales as TotalStock
From (tblItems left join
(select itemid,sum(tblHQStock.qty) as QtyHQS from tblHQStock) as qryHQStock on tblItems.ItemID=qryHQStock.ItemID) left join
(Select itemid,sum(tblsales.qty) as QtySales from tblsales) as qrySales) on tblItems.ItemID=qrySales.ItemID

I think that will work - wrote straight outta my head, but should give you an idea?


Vince

 

[The_Doc_Man]

Mel, search this forum for the topic "Balance" or "Inventory Balance"

We have had a lengthy thread on this subject within the last month.

I don't want to repeat the discussion. It was pages and pages of stuff. Find the thread and read it. The idea of subtracting from inventory betrays an un-normalized table structure. Down the road, this junkyard dog will bite your butt.