I am working on Stock calculations in Vb Express with MsAccess as database. I ha

Question

I am working on Stock calculations in Vb Express with MsAccess as database. I have following tables. ItemsTable ITId ItemId Description 1 1 Coca Cola Normal 2 2 Coca Cola Zero PurchaseTable PId PDate ItemId Price Quantity Amount 1 28/8/2009 1 1,00 10 10,00 2 28/8/2009 2 1,00 5 5,00 3 29/8/2009 1 1,00 5 5,00 4 29/8/2009 2 1,00 10 10,00 5 30/8/2009 1 1,00 10 10,00 6 30/8/2009 2 1,00 5 5,00 SalesTable SId SDate ItemId Price Quantity Amount 1 30/8/2009 1 2,70 2 5,40 2 30/8/2009 2 2,70 3 7,10 3 31/8/2009 1 2,70 1 2,70 4 31/8/2009 2 2,70 2 2,70 I tried something like as under but it did not give me the desired results. 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

Explanation / Answer

i have read your question, but the solution seems to be more complex than you think in advance. first of all, you will need to make clear what is understood as a balance "between two dates". i mean logically. to my knowledge a balance always expresses an amount at a certain point of time like an account balance or an inventory balance. it might be that you are trying to express two different things as the same thing (balance).

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.