Need help with SQL query

[sazd1]

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: 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 I want a query for stockcalculation to show data as under: PDate,ItemId, QuantityPurchased, QuantitySold, Balance(QuantityPurchased - QuantitySold) Please advise how I can get the above information through a query on the above two tables to be displayed in a Listview.

[Johan Hakkesteegt]

You are in the wrong forum, but try this link: http://www.w3schools.com/sql/default.asp[^] There you will find a free tutorial that will teach you how to do this.

My advice is free, and you may get what you paid for.

[dan sh]

General Database would have been a better forum.

sazd1 wrote:

I want a query for stockcalculation

What have you tried?

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

[sazd1]

thanks for your reply I tried something like this: 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" But it did not give the desired results. it is not making the correct calculations.

[Pasan148]

To do this you have to create another table with following fields for store remaining stock. ItemID Description Price Quantity When you make a transaction update the Stock table. When generating the listview ignore PDate field. You can get QuantityPurchased and QuantitySold fields from Purchase table and Sales table. You can get Balance field from Stock table

[sazd1]

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. 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 b