Stock/Inventory calculations with opening/closing balance

EDN Admin · Sep 16, 2009

I am a newbie working in VB Express 2008 with MsAccess as database. I got stuck up with one issue of inventory/stock calculations and put my problem in four threads and grasped some ideas and thoughts for that. But could not find SQL query for this issue in those threads. So with the help of these forums and some forums of other sites i am able to construct SQL query for stock/inventory calculations. Since I could not find any SQL query as an example on this issue inspite of spending a lot of time and search, deifinitely there might be so many examples but i was unable to reach them, so after constructing this query i wanted to share my humble effort with all. It may be a second thought for beginners like me having the same problem. The second purpose of placing this query here is to welcome improvement or alternate of this query. Thanks to all who gave me suggestions to solve my problem. My Tables are as under: 
<table border=1 cellspacing=0 bgcolor="#ffffff

<tr>
<th><th><th><th><th><th><th>
</tr>
</thead>
<tbody>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
</tbody>
<tfoot></tfoot><caption> PurchaseTable1 </caption>
</table>
 
<table border=1 cellspacing=0 bgcolor="#ffffff

<tr>
<th><th><th><th><th><th><th>
</tr>
</thead>
<tbody>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
<tr valign=top>
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
<td align=right bordercolor="#d0d7e5 
</tr>
</tbody>
<tfoot></tfoot><caption> SalesTable1 </caption>
</table>
 My complete code is as under: 
<div style="color:black;background-color:white
<pre><span style="color:blue Dim cmdText <span style="color:blue As <span style="color:blue String = <span style="color:#a31515 "Select a.ItemId,a.Description, SUM(PQuantityBefore) AS PQuantityBefore, " & _

<span style="color:#a31515 "SUM(SQuantityBefore) AS SQuantityBefore, " & _

<span style="color:#a31515 "(Sum(PQuantityBefore) - Sum(SQuantityBefore)) AS BalanceBefore, " & _

<span style="color:#a31515 "Sum(QuantityPurchased) AS QuantityPurchased, Sum(QuantitySold) AS QuantitySold, " & _

<span style="color:#a31515 "(Sum(PQuantityBefore) - Sum(SQuantityBefore) + Sum(QuantityPurchased) - Sum(QuantitySold)) AS Balance " & _

<span style="color:#a31515 "From " & _

<span style="color:#a31515 "(" & _

<span style="color:#a31515 "SELECT pt.ItemId, pt.Description, pt.Quantity AS PQuantityBefore, " & _

<span style="color:#a31515 "0 AS SQuantityBefore, 0 AS QuantityPurchased,0 AS QuantitySold FROM PurchaseTable1 pt " & _

<span style="color:#a31515 "WHERE pt.PDate < @START " & _

<span style="color:#a31515 "UNION ALL " & _

<span style="color:#a31515 "SELECT st.ItemId, st.Description, 0 AS PQuantityBefore, st.Quantity AS SQuantityBefore, " & _

<span style="color:#a31515 "0 AS QuantityPurchased,0 AS QuantitySold FROM SalesTable1 st " & _

<span style="color:#a31515 "WHERE st.SDate < @START " & _

<span style="color:#a31515 "UNION ALL " & _

<span style="color:#a31515 "SELECT pt.ItemId, pt.Description, 0 AS PQuantityBefore,0 AS SQuantityBefore, " & _

<span style="color:#a31515 "pt.Quantity AS QuantityPurchased, 0 AS QuantitySold FROM PurchaseTable1 pt " & _

<span style="color:#a31515 "WHERE pt.PDate Between @START and @END " & _

<span style="color:#a31515 "UNION ALL " & _

<span style="color:#a31515 "SELECT st.ItemId, st.Description, 0 AS PQuantityBefore,0 AS SQuantityBefore, " & _

<span style="color:#a31515 "0 AS QuantityPurchased, st.Quantity AS QuantitySold FROM SalesTable1 st " & _

<span style="color:#a31515 "WHERE st.SDate Between @START and @END " & _

<span style="color:#a31515 ") a " & _

<span style="color:#a31515 "GROUP BY a.ItemId, a.Description"

<span style="color:blue If con.State = ConnectionState.Closed <span style="color:blue Then con.Open()

<span style="color:blue Dim cmd <span style="color:blue As OleDb.OleDbCommand = <span style="color:blue New OleDb.OleDbCommand(cmdText, con)

cmd.CommandType = CommandType.Text

cmd.Parameters.AddWithValue(<span style="color:#a31515 "@START", OleDb.OleDbType.<span style="color:blue Date).Value = TextBox1.Text

cmd.Parameters.AddWithValue(<span style="color:#a31515 "@END", OleDb.OleDbType.<span style="color:blue Date).Value = TextBox2.Text

<span style="color:blue Dim dr <span style="color:blue As OleDb.OleDbDataReader

<span style="color:blue If con.State = ConnectionState.Closed <span style="color:blue Then con.Open()

dr = cmd.ExecuteReader

<span style="color:blue If <span style="color:blue Not dr.HasRows <span style="color:blue Then

MessageBox.Show(<span style="color:#a31515 "No Records Found for Date: " & TextBox1.Text)

<span style="color:blue Else

MessageBox.Show(<span style="color:#a31515 "Record found for Date: " & TextBox1.Text)

ListView1.Items.Clear()

ListView1.ForeColor = Color.DarkRed

ListView1.GridLines = <span style="color:blue True

<span style="color:blue While dr.Read

<span style="color:blue Dim ls <span style="color:blue As <span style="color:blue New ListViewItem(dr.Item(<span style="color:#a31515 "ItemId").ToString())

ls.SubItems.Add(dr.Item(<span style="color:#a31515 "Description").ToString())

ls.SubItems.Add(dr.Item(<span style="color:#a31515 "PQuantityBefore").ToString())

ls.SubItems.Add(dr.Item(<span style="color:#a31515 "SQuantityBefore").ToString())

ls.SubItems.Add(dr.Item(<span style="color:#a31515 "BalanceBefore").ToString())

ls.SubItems.Add(dr.Item(<span style="color:#a31515 "QuantityPurchased").ToString())

ls.SubItems.Add(dr.Item(<span style="color:#a31515 "QuantitySold").ToString())

ls.SubItems.Add(dr.Item(<span style="color:#a31515 "Balance").ToString())

ListView1.Items.Add(ls)

<span style="color:blue End <span style="color:blue While

<span style="color:blue End <span style="color:blue If

[/code]

View the full article

Stock/Inventory calculations with opening/closing balance

EDN Admin

Well-known member

Similar threads