Back to discussion

Need Help for SQL query

Last post was 05 Sep 2009 at 17:47
SQL , vb express , MsAccess Abu Dhabi, United Arab Emirates
  • 11 years ago

    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

    Please advise what i am doing wrong and how i can get correct balance of Stock at any date or interval of dates. Thanks.

    Report abuse
  • 11 years ago

    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

    Please advise what i am doing wrong and how i can get correct balance of Stock at any date or interval of dates. Thanks.

    Report abuse
  • 11 years ago

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

    Report abuse
  • 11 years ago

    ... a balance can not be a delta amount between two dates but is always a current state amount.

    Report abuse
  • 11 years ago

    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.

    Report abuse
  • 11 years ago

    hi sazd1,

    you will see the mistake instantly, if you ungroup your query. every entry in the purchase table with the same itemid (2 entries) is combined with every entry in the sales table (2 entries). Therefore, you have got an amount twice as high as you expected.

    you need to make three queries out of this query in order to calculate the figure as you want it. you need to group the purchase and the sales table and then join both tables.

    qryPurchaseGrouped: SELECT Purchase.ItemID, Sum(Purchase.Price) AS Price, Sum(Purchase.Quantity) AS Quantity, Sum(Purchase.Amount) AS Amount FROM Purchase WHERE (((Purchase.PDate) Between #8/28/2009# And #8/31/2009#)) GROUP BY Purchase.ItemID;

    qryPurchaseGrouped: SELECT Sales.ItemID, Sum(Sales.Price) AS Price, Sum(Sales.Quantity) AS Quantity, Sum(Sales.Amount) AS Amount FROM Sales WHERE (((Sales.SDate) Between #8/28/2009# And #8/31/2009#)) GROUP BY Sales.ItemID;

    qryBalance: SELECT qryPurchaseGrouped.ItemID AS PItemID, qryPurchaseGrouped.Price AS PPrice, **qryPurchaseGrouped.Quantity AS PQuantity, qryPurchaseGrouped.Amount AS PAmount, qrySalesGrouped.ItemID AS SItemID, qrySalesGrouped.Price AS SPrice, qrySalesGrouped.Quantity AS SQuantity, qrySalesGrouped.Amount AS SAmount, [PQuantity]-[SQuantity] AS QuantityDelta FROM qryPurchaseGrouped LEFT JOIN qrySalesGrouped ON qryPurchaseGrouped.ItemID = qrySales_Grouped.ItemID;

    The left join in this last query is quite important since you would not get any results if you did not sell an item.

    Hope this helps. But please note, that this is still not a balance since you would ignore every item in stock before the start date.

    Osman

    Report abuse
  • 11 years ago

    ... sorry. the second query should be called qrySalesGrouped

    Report abuse

Post a reply

Enter your message below

Sign in or Join us (it's free).

SQL forum discussion

SQL tutorials

Quick links

Recent activity

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Web Development

Developer Jobs

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

Cancel
“A computer is a stupid machine with the ability to do incredibly smart things, while computer programmers are smart people with the ability to do incredibly stupid things. They are, in short, a perfect match” - Bill Bryson