Help for an SQL query

[sazd1]

I have been guided to put my question to this forum. 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").ToStr ing()) 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

[David Skelly]

The first thing that I notice from your query is that you are using an inner join. This will only return information for items that have an entry in both the Purchase table and the Sales table. If you have any items that are only in one table (e.g a Purchase but no Sale for the same item), they will not show up in your results. The second thing I notice is that you are selecting on the basis of the date in the Purchase table, but you are ignoring the date in the Sales table. So, you will pick up a subset of the Purchase data but all of the Sales data, which seems a bit wrong to me. When you say that you are not getting the desired results, what exactly is going wrong? That would help us to narrow down the problem.

[sazd1]

The problem i am having is as under: Data in PurchaseTable 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 The result of query with date range of 28-08-2009 to 29-08-2009 and also with the date range of 28-08-2009 to 31-08-2009 is displayed as under: Coca Cola Normal 30 6 24 Coca Cola Zero 30 10 20 whereas actually for date range of 28-08-2009 to 29-08-2009 the result should be as under: Coca Cola Normal 15 0 15 Coca Cola Zero 15 0 15 And with the date range of 28-08-2009 to 31-08-2009 the result should be as under: Coca Cola Normal 15 3 12 Coca Cola Zero 15 5 10 Pleae advise what i am doing wrong with the query. Thanks again for your guidance.

[David Skelly]

I have already told you what the problem is. Re-read my previous post. You are not including the date on the Sales table in your query.