I am using vb express and MsAccess as database.
I am trying to write a query for the calculation of Stock.
My tables are as under:
PId PDate ItemId Description Price Quantity Amount
1 28/8/2009 1 Coca Cola Normal 1,00 10 10,00
2 28/8/2009 2 Coca Cola Zero 1,00 5 5,00
3 29/8/2009 1 Coca Cola Normal 1,00 5 5,00
4 29/8/2009 2 Coca Cola Zero 1,00 10 10,00
Data in Sales Table is as under:
SId SDate ItemId Description Price Quantity Amount
1 30/8/2009 1 Coca Cola Normal 2,70 2 5,40
2 30/8/2009 2 Coca Cola Zero 2,70 3 7,10
3 31/8/2009 1 Coca Cola Normal 2,70 1 2,70
4 31/8/2009 2 Coca Cola Zero 2,70 2 2,70
I tried something like this to have balance of stock at any date or interval between two dates
- Dim cmdText As String = "SELECT pt.ItemId, pt.Description, SUM(pt.Quantity)AS QuantityPurchased, SUM(st.Quantity) AS QuantitySold, (SUM(pt.Quantity) - SUM(st.Quantity)) AS Balance FROM PurchaseTable pt INNER JOIN SalesTable st ON pt.ItemId=st.ItemId WHERE pt.PDate Between @START and @END GROUP BY pt.ItemId, pt.Description"
-
-
If con.State = ConnectionState.Closed Then con.Open()
-
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(cmdText, con)
-
cmd.CommandType = CommandType.Text
-
-
cmd.Parameters.AddWithValue("@START", OleDb.OleDbType.Date).Value = TextBox1.Text
-
cmd.Parameters.AddWithValue("@END", OleDb.OleDbType.Date).Value = TextBox2.Text
-
-
Dim dr As OleDb.OleDbDataReader
-
-
If con.State = ConnectionState.Closed Then con.Open()
-
dr = cmd.ExecuteReader
-
If Not dr.HasRows Then
-
MessageBox.Show("No Records Found for Date: " & TextBox1.Text)
-
Else
-
MessageBox.Show("Record found for Date: " & TextBox1.Text)
-
ListView1.Items.Clear()
-
ListView1.ForeColor = Color.DarkRed
-
ListView1.GridLines = True
-
-
While dr.Read
-
Dim ls As New ListViewItem(dr.Item("ItemId").ToString())
-
ls.SubItems.Add(dr.Item("Description").ToString())
-
ls.SubItems.Add(dr.Item("QuantityPurchased").ToString())
-
ls.SubItems.Add(dr.Item("QuantitySold").ToString())
-
ls.SubItems.Add(dr.Item("Balance").ToString())
-
ListView1.Items.Add(ls)
-
End While
-
End If
But i could not get the desired results. Please advise what i am doing wrong with this.
Thanks
