Calculation in Code Builder for Sum & Subtraction

[braveali]

Hello Lads!



I have created a small stock entry database in Access (2010), Please refer to the attached Relationship (.jpg) for quick understanding of my work.

I need your help in some calculation stuff:

As you can see the (from the attached reference): When every we receive stock, it goes to StockIN with Name & Qty received,

on the other hand: when ever we give stock to someone in the organization, we make an entry in StockOut table with Name & QtyRequired and this quantity subtract from StockIN Qty.

Now I have created an other Table tblStockReturn: this table will hold the information of the stock return (fix Assets) if any employee leave the organization and submit his fix asset back to admin department.

in this case: the Qty in StockIN tbl should be add (+) with the returned stock so that we can know how much current stock balance we have, and should be Subtract (-) from StockOut, so that we know that Mr. xyz has no assets due on him.

i have to create a form (for Stock Return) and need to put some VB lines so that when ever i give any Qty in here and save it, it will automatically add this value in StockIN Qty and subtract with value from StockOut Qty.


i am very new in access you may guide me some other way of doing the same too!

i will appreciate your help in this regards,



Thanks

[recyan]

Not the answer, but was wondering :

1) Can we not combine the 3 tables in to 1, StockIn, StockOut & tblStockReturn in to tblStockInOutReurn with an additional field showing Type i.e. In, Out or Return as values.

2) Can we not incorporate DeadStock in to the above table with the Type field value displayed as DeadStock or the ItemLog table displaying ItemType as "DeadStock" (Am assuming, ItemLog table is a Master Items table).

3) tblStockReturn - Why is EmployeeName stored & not EmpID and ItemName is stored and not ItemID. Why is Designation stored in tblStockReturn

4) Table StockOut - What is AvailabelQty? Is it a calculated Quantity? If yes, if not essential, we should avoid storing calculated values in tables.

5)

in this case: the Qty in StockIN tbl should be add (+) with the returned stock so that we can know how much current stock balance we have, and should be Subtract (-) from StockOut, so that we know that Mr. xyz has no assets due on him.

I think there is no need to ( we should not ) do this at table level. It can be handled at Query & Report Level. The tables should ideally store only raw data.

Thanks

[braveali]

Thanks for your time in my questions, here are some answers.

1) Can we not combine the 3 tables in to 1, StockIn, StockOut & tblStockReturn in to tblStockInOutReurn with an additional field showing Type i.e. In, Out or Return as values.
Yes we can, but different table have different fields if we accommodate all the fields into single table, with an extra drop down field for IN, OUT or Return I'm afraid there would be very difficult to manage alot of other fields which are not associate with the other Items.

2) Can we not incorporate DeadStock in to the above table with the Type field value displayed as DeadStock or the ItemLog table displaying ItemType as "DeadStock" (Am assuming, ItemLog table is a Master Items table).

if we put it into tblItemLog how could we make an entry that xyz stock is received today (date of submission) in admin department for dead stock?


3) tblStockReturn - Why is EmployeeName stored & not EmpID and ItemName is stored and not ItemID. Why is Designation stored in tblStockReturn

its a drop down menu a list of employees name should be chosen from this list imported from tblEmployee and same as Itemname and designation so that we can know who has return this item and on what post he was working.


4) Table StockOut - What is AvailabelQty? Is it a calculated Quantity? If yes, if not essential, we should avoid storing calculated values in tables.

as we select any stock to deliver it, its balance will automatically come in this, its not a calculated field, on form, i have created a VB code to calculate the values and show in his field.

yes you are right we can do this on query level!

would you able to help me to create some quereis for this please?


such as this is my 1st database, so im learning it

Thanks

[recyan]

Okay, now moving away from the design part to what you want :

1) In our StockOut Table, I assume there are multiple records for a particular Item say ItemID - M9011.
How is the AvailableQuantity stored against a particular ItemID?

2)
Let us say :

Existing Data in your StockOUT table

EmpID    DateOfIssue    ItemID    QtyIssued
E1011    1/13/2012    M9011    2
E1011    1/13/2012    M9022    5
E1011    1/21/2012    M9033    1

Data of the Stock being returned which is entered in a Form :

EmpID    DateOfReturn    ItemID    QtyReturned
E1011    1/30/2012    M9011    1
E1011    1/30/2012    M9022    4

How do you want this to be handled ?

If I have understood correctly, you want the

ItemID    QtyReturned
M9011    1
M9022    4

to be added to the StockIN table.
Ok, but we need to have something in the StockIN table which relates to the ReturnID, bcos the StockIn table is specific to Supplier input.


At the same time, you want it to be subtracted from the StockOut table, from where, against the Date on which it was issued?

3) If we need to have a seperate table for Stock Return, then why update the StockIn & StockOut tables. We can just add a few more fields like DateOfReturn,etc to tblStockReturn.

4) If we want to get status of the StockIssued to & Returned by an Employee, we can join StockOut & tblStockReturn on EmpID & ItemID.

5) If we want the Available Stock for a particular ItemID, perhaps something like :
Sum(StockIN.ItemID_Qty) - Sum(StockOUT.ItemID_Qty) + Sum(tblStockReturn.ItemID_Qty)

Thanks

[braveali]

Hi,

continue to our yesterday's conversation, i would like to say thanks for taking interest in my problem and helping me out in it,

Ok, but we need to have something in the StockIN table which relates to the ReturnID, bcos the StockIn table is specific to Supplier input.

as you asked, Should I add an additional field into StockIN with the name of ReturnID and make its relation with tblStockReturn ReturnID?

3) If we need to have a seperate table for Stock Return, then why update the StockIn & StockOut tables. We can just add a few more fields like DateOfReturn,etc to tblStockReturn.

If we will not add the return stock in tbl.StockIN how could we able to make an entry to reissue that stock in tbl.StockOut to some other employees?
please refer to my 2 attached forms "StockIN" & StockOut..

in stockout you can see the available Qty automatically appears when ever we select any ItemID, so that we can know how much stock we have before deliver it, and if you have no stock in hand, i can't deliver it to someone and call admin to buy that.

if i remove the available Qty field from StockOut, so don't you think it would be difficult for us while issuing any item to check its available balance from the an other report and then come into stockout entry table to enter the quantity accordingly? but on the other hand; this could make our work easy, to make different quires for what stock was get IN and Out and what we have available or returned and convert them into reports,

4) If we want to get status of the StockIssued to & Returned by an Employee, we can join StockOut & tblStockReturn on EmpID & ItemID.

5) If we want the Available Stock for a particular ItemID, perhaps something like :
Sum(StockIN.ItemID_Qty) - Sum(StockOUT.ItemID_Qty) + Sum(tblStockReturn.ItemID_Qty)

I agreed with your 4 & 5 advices.

Please guide me how could I proceed?

Thanks

[braveali]

Some Progress:

I have created a field in StockIn as ItemType (dropdown for NewStock or ReturnStock) in the case stock return we'll make an itemtype as ReturnStock

and a separate table for StockReturn,

we'll sumup the Qty in Stock with Qty in StockReturn,

how should i write a query so that i till sumup Qty.StockIn + Qty.stockreturn - Qty.StockOut?


SELECT Stin.itemid, Stin.itemName, Stin.sumofqty, tblStockReturn.qtyreturn Nz ( [outqty],0) AS outquantity, [sumofqty] +[qtyreturn] - Nz ([outqty],0) AS balance
FROM stin LEFT JOIN stout ON stin.itemID = stout.itemID;


please guide!

thanks

[recyan]

Just check if below gives some guidelines ( The StockIn & StockOut tables are not touched in the below scenario, if Items are returned by an Employee & also there is no StockAvailable Field in the StockOut table) :

Tables
tblItems

ItemID    ItemName    ReorderLevel
1    A    50
2    B    100
3    C    200 

StockIN

ReceiptID    ItemID    DateOfReceipt    QtyIn    SupplierID
1    1    2/26/2012    10    1
2    1    2/27/2012    20    1
3    1    2/29/2012    10    1
4    1    3/2/2012    5    1
5    2    2/25/2012    20    2
6    2    2/26/2012    30    2
7    2    2/27/2012    40    2
8    2    2/28/2012    50    2
9    2    2/29/2012    60    3
10    2    3/1/2012    70    3
11    3    2/27/2012    80    3
12    3    2/28/2012    90    3
13    3    2/29/2012    100    4
14    3    3/1/2012    110    4
15    3    3/2/2012    120    4 

StockOUT

StockOutID    ItemID    DateOfIssue    QtyIssued    EmpIDIssuedTo
1    1    2/27/2012    5    1
2    2    2/28/2012    5    2
3    3    2/29/2012    5    3
4    1    3/1/2012    5    1
5    2    3/2/2012    5    2
6    3    2/27/2012    5    3
7    1    2/28/2012    5    1
8    2    2/29/2012    5    2
9    3    3/1/2012    5    3
10    1    3/2/2012    5    1
11    2    2/27/2012    5    2
12    3    2/28/2012    5    3
13    1    2/29/2012    5    1
14    2    3/1/2012    10    2
15    3    3/2/2012    5    3
16    1    3/2/2012    5    1 

tblReturns

ReturnID    ItemID    DateOfReturn    QtyReturned    EmpIDReturnedBy
1    1    2/26/2012    5    1
2    1    2/28/2012    5    1
3    2    2/27/2012    10    1
4    2    2/29/2012    10    2
7    1    3/2/2012    5    3
8    2    3/2/2012    10    3 

____________________________
The sub-queries for Available Balance :

qryTotalStockIn

SELECT 
    StockIN.ItemID, 
    Sum(StockIN.QtyIn) AS SumOfQtyIn
FROM 
    StockIN
GROUP BY 
    StockIN.ItemID;

The result :

ItemID    SumOfQtyIn
1    45
2    270
3    500 

qryTotalStockOut

SELECT 
    StockOUT.ItemID, 
    Sum(StockOUT.QtyIssued) AS SumOfQtyIssued
FROM 
    StockOUT
GROUP BY 
    StockOUT.ItemID;

The result :

ItemID    SumOfQtyIssued
1    30
2    30
3    25 

qryTotalReturns

SELECT 
    tblReturns.ItemID, 
    Sum(tblReturns.QtyReturned) AS SumOfQtyReturned
FROM 
    tblReturns
GROUP BY 
    tblReturns.ItemID;

The result :

ItemID    SumOfQtyReturned
1    15
2    30 

The final query for Available balance to be run :

qryAvailableBalance

SELECT 
    tblItems.ItemID, 
    tblItems.ItemName, 
    tblItems.ReorderLevel, 
    Nz([SumOfQtyIn],0) AS TotalQtyIn, 
    Nz([SumOfQtyReturned],0) AS TotalQtyReturned, 
    Nz([SumOfQtyIssued],0) AS TotalQtyIssued, 
    Nz([SumOfQtyIn],0)+Nz([SumOfQtyReturned],0)-Nz([SumOfQtyIssued],0) AS CurrentBalance, IIf([CurrentBalance]<=[ReorderLevel],"PlaceOrder","StockLevelOk") AS ReorderStatus
FROM 
    ((tblItems LEFT JOIN qryTotalStockIn ON tblItems.ItemID = qryTotalStockIn.ItemID) 
    LEFT JOIN 
    qryTotalReturns ON tblItems.ItemID = qryTotalReturns.ItemID) 
    LEFT JOIN 
    qryTotalStockOut ON tblItems.ItemID = qryTotalStockOut.ItemID;

The result :

ItemID    ItemName    ReorderLevel    TotalQtyIn    TotalQtyReturned    TotalQtyIssued    CurrentBalance    ReorderStatus
1    A    50    45    15    30    30    PlaceOrder
2    B    100    270    30    30    270    StockLevelOk
3    C    200    500    0    25    475    StockLevelOk 

____________________________
The sub-queries for Employee Balance :

qryEmpItemOut

SELECT 
    StockOUT.EmpIDIssuedTo, 
    StockOUT.ItemID, 
    Sum(StockOUT.QtyIssued) AS SumOfQtyIssued
FROM 
    StockOUT
GROUP BY 
    StockOUT.EmpIDIssuedTo, 
    StockOUT.ItemID;

The result

EmpIDIssuedTo    ItemID    SumOfQtyIssued
1    1    30
2    2    30
3    3    25 

qryEmpItemReturned

SELECT 
    tblReturns.EmpIDReturnedBy, 
    tblReturns.ItemID, 
    Sum(tblReturns.QtyReturned) AS SumOfQtyReturned
FROM 
    tblReturns
GROUP BY 
    tblReturns.EmpIDReturnedBy, 
    tblReturns.ItemID;

The result :

EmpIDReturnedBy    ItemID    SumOfQtyReturned
1    1    10
1    2    10
2    2    10
3    1    5
3    2    10 

The final query to be run for Employee Items Balance to be returned :

qryEmpItemIssuedReturned

SELECT 
    qryEmpItemOut.EmpIDIssuedTo, 
    qryEmpItemOut.ItemID, 
    qryEmpItemOut.SumOfQtyIssued, 
    qryEmpItemReturned.EmpIDReturnedBy, 
    qryEmpItemReturned.ItemID, 
    qryEmpItemReturned.SumOfQtyReturned, 
    [SumOfQtyIssued]-Nz([SumOfQtyReturned],0) AS BalanceWithEmp
FROM 
    qryEmpItemOut 
    LEFT JOIN 
    qryEmpItemReturned 
    ON 
    (qryEmpItemOut.EmpIDIssuedTo = qryEmpItemReturned.EmpIDReturnedBy) 
    AND 
    (qryEmpItemOut.ItemID = qryEmpItemReturned.ItemID);

The result :

EmpIDIssuedTo    qryEmpItemOut.ItemID    SumOfQtyIssued    EmpIDReturnedBy    qryEmpItemReturned.ItemID    SumOfQtyReturned    BalanceWithEmp
1    1    30    1    1    10    20
2    2    30    2    2    10    20
3    3    25                25 

Thanks

[braveali]

Thanks a million for your time and for the codes, give me some time to try all of these...

[braveali]

Hi,

just a quick questions in your last post,

ItemID ItemName ReorderLevel
1 A 50
2 B 100
3 C 200

what is the ReorderLevel? do we need to enter it manually ? because ItemTable just stores Items Name and its category like is it fix asset of non fix asset

Thanks

[recyan]

Hi,

just a quick questions in your last post,

ItemID ItemName ReorderLevel
1 A 50
2 B 100
3 C 200

what is the ReorderLevel? do we need to enter it manually ?

I just put it in as an additional feature. You can remove it if you don't need it.

Reorder Level - The minimum Quantity of an Item that should be held in stock, at which point, an purchase order should be placed for that item. This is different for different Items & is arrived at after analysing the consumption pattern, delivery time from a Vendor, etc of an Item. It can be entered manually in our case, or can be arrived at with a little bit of complicated calculations (automated), the explaination of which is currently beyond my scope.
If you see the result of qryAvailableBalance, the CurrentBalance has gone below the Reorder Level, hence it generates a PlaceOrder status in the Query result, thus alerting that an order has to be placed for that item A.
Thanks

[braveali]

Excellent Work! Let me try out this as well

[braveali]

Hi,

Everything works perfectly great! and I'm so Glade & Thanks full to you!

Just one last thing, we did not accommodate the Dead Stock?

How if someone return a fix asset (i.e. old Laptop) to be submitted as a dead stock?

There should be a separate table where we could store the dead stock stuff.



Thx

[braveali]

One more quick question:-


SELECT

qryEmpItemOut.EmployeeID,
qryEmpItemOut.ItemID,
qryEmpItemOut.SumOfQtyIssued,
qryEmpItemReturned.EmployeeID,
qryEmpItemReturned.ItemID,
qryEmpItemReturned.SumOfQtyReturned,
[SumOfQtyIssued]-Nz([SumOfQtyReturned],0) AS BalanceWithEmp

FROM
qryEmpItemOut

LEFT JOIN
qryEmpItemReturned

ON (qryEmpItemOut.EmployeeID = qryEmpItemReturned.EmployeeID) AND (qryEmpItemOut.ItemID = qryEmpItemReturned.ItemID);

The output of the above qryEmpItemIssuedReturned, EmployeeID & ItemIssed & Return have the same information which are repeating please refer to the attached .jpg, can't we fix it so that Emp Name should not be repeat in the same row and same ItemID? Thanks

[recyan]

Just one last thing, we did not accommodate the Dead Stock?

How if someone return a fix asset (i.e. old Laptop) to be submitted as a dead stock?

There should be a separate table where we could store the dead stock stuff.

By DeadStock, if I have understood correctly, some Item that has been returned by an Employee which is no longer usable due to damage or some other reason & hence cannot be issued again & hence will not reflect in the Available Stock again.

If above assumption is correct, it can perhaps be handled in 2 ways :
1) Have a seperate table as you have suggested. Only thing that we will have to consider in what I have suugested earlier is, we will have to link up this table also in our Employee Balance Queries, to reflect the correct status of all items returned by an employee.

2) We can include this in tblReturns, which in turn can have an additional field (ReturnType) stating that the Item is DeadStock. In this case, we will have to modify the query qryTotalReturns by adding a where clause, something like " Where ReturnType <> 'DeadStock' ", to reflect the correct Available Balance for Issue.

Edit :

The output of the above qryEmpItemIssuedReturned, EmployeeID & ItemIssed & Return have the same information which are repeating please refer to the attached .jpg, can't we fix it so that Emp Name should not be repeat in the same row and same ItemID?

Go to the Query in the Design Grid & fiddle around.
You can remove those fields from the Query, if you find things are working OK.
Play around with Queries in Design Grid. It's quite an useful tool.
Always remember to make a copy or take a backup, before you start fiddling around.

Thanks

[braveali]

Hi

For Dead Stock: I have created a separate field with ReturnType: "Good" or "Dead"

Please see the attached out result:

ReturnID	EmployeeID	ItemID	DateofReturn	QtyReturned	ReturnType
1	Muhammad Ali Khan	Antiviurs	04-Mar-12	1	Good
2	Fayaz Thaheem	Desktop Computer	04-Mar-12	2	Dead
3	Moazzam Ali Marri	Antiviurs	05-Mar-12	1	Dead

Created a qry:

ItemID	QtySumDeadReturn
Antiviurs	1
Desktop Computer	2

now i want to show it in Qry Available Balance

here are commands:

SELECT

tblItems.ItemID,
tblItems.ItemName,
tblItems.ReOrderLevel,
tblItemReturn.QtyReturned,


Nz([QtySumDeadReturn],0) As TotalDeadReturn,

Nz([SumofQtyIn],0) AS TotalQtyIn,

Nz([SumQtyReturn],0) AS TotalQtyReturned,

Nz([SumQtyIssued],0) AS TotalQtyIssued,

Nz([SumofQtyIn],0)+Nz([SumQtyReturn])-Nz([SumQtyIssued],0) AS CurrentBalance,

IIf([CurrentBalance]<=[ReOrderLevel],"Place Order","Stock level is OK") AS ReOrderStatus

FROM ((tblItems LEFT JOIN qrySumofStockIN

ON tblItems.ItemID = qrySumofStockIN.ItemID)

LEFT JOIN
qryTotalReturn ON tblItems.ItemID = qryTotalReturn.ItemID)

LEFT JOIN
qrySumofStockOut ON tblItems.ItemID = qrySumofStockOut.ItemID

LEFT JOIN
qryDeadItemReturn on tblItems.ItemID= qryDeadItemReturn.ItemID;


but its giving me an Error: Syntax Error(missing Operator)in query Expresion 'tblItems.ItemID= qrySumofStockout.itemID

LEFT Join
qryDeadItemReturn on TblItems.ItemID = qryDeadItemReturn.ItemI'.

i think i did something wrong in query

please guide!

thanks