listbox to select products and calculate the sum in query

[mseo]

hi,
I have two listboxes on for viewing all the products from products table
the second one to select specific products from the first one
can I get the sum using query for all the products in the selected products listbox
thank you very much

[TheSmileyCoder]

You could loop through all the items selected in your listbox, making a filter string, then using that along with DSUM, to give you the desired result. Atleast, that is the approach I would use.

If you need code samples let me know.

[NeoPa]

If the second one has a filter that reflects which records are included in your form, then you could use that filter in your SQL to work out the sum (of whatever you want to sum).

[mseo]

You could loop through all the items selected in your listbox, making a filter string, then using that along with DSUM, to give you the desired result. Atleast, that is the approach I would use.

If you need code samples let me know.

hi,
thank you
the selected items in the second listbox
I cannot calculate the sum of quantity of this items within query specifically if I limited the period of the production between two dates
please provide me with the code sample
I really appreciate your help

[mseo]

hi,

You could loop through all the items selected in your listbox, making a filter string, then using that along with DSUM, to give you the desired result. Atleast, that is the approach I would use.

If you need code samples let me know.

can you provide me more explaination of how to do so
thank you for your dedication

[NeoPa]

Presumably this code would run in the OnClick event procedure of a Command Button.

Private Sub cmdRun_Click()
    Dim strWhere As String
    Dim varIx As Variant

    With Me.lstProd
        For Each varIx in .ItemsSelected
            strWhere = strWhere & ",'" & .ItemData(varIx) & "'"
        Next varIx
    End With
    If strWhere > "" Then _
        strWhere = "WHERE [YourField] In(" & Mid(strWhere, 2) & ")"
    'The filter is now set up in strWhere.
    'Other code to apply the filter where you require it.
    ...
End Sub

PS. Sorry for late reply. I must have missed it somehow. I only saw it in response to your latest post.

[mseo]

Presumably this code would run in the OnClick event procedure of a Command Button.

Private Sub cmdRun_Click()
    Dim strWhere As String
    Dim varIx As Variant

    With Me.lstProd
        For Each varIx in .ItemsSelected
            strWhere = strWhere & ",'" & .ItemData(varIx) & "'"
        Next varIx
    End With
    If strWhere > "" Then _
        strWhere = "WHERE [YourField] In(" & Mid(strWhere, 2) & ")"
    'The filter is now set up in strWhere.
    'Other code to apply the filter where you require it.
    ...
End Sub

PS. Sorry for late reply. I must have missed it somehow. I only saw it in response to your latest post.

Hi, Neopa

Private Sub cmdrun_Click()
 Dim strWhere As String 
    Dim varIx As Variant 
For Each varIx In Me.list1.ItemsSelected
   strWhere = strWhere & Me.list1.Column(0, varIx ) & ","
Next varIx 
strWhere = "PID in (" & Left(strWhere, Len(strWhere) - 1) & ")"
DoCmd.OpenForm "form1", , , strWhere
End Sub

and it works great
I really appreciate you help

[mseo]

hi,
form1 based on query, I use sum function to get the sum of each item
now I need to limit the sum of the items between two dates, so if the item has more than one record and different date for each I would get the sum for the same date but not for different dates
I need to show the sum of the item in one record and limit it between two date in the same time
thank you

[NeoPa]

I'm struggling to understand what this is about. I get that dates come into it somewhere, but which fields or controls and where these are I have no idea.

[mseo]

hi, Neopa
the problem is I need to use aggregated function within a query and I want to get the sum of the production between start date and end date
the record in the table would be like this:
productid product quantity date
1 1 1000 1/1/2010
1 1 1000 1/2/2010
in the query if i insert the start date and end date
the query will be the same as in the table
i need to get one record for each product and limit the sum of the production using start date and end date
so if the start date is 1/1/2010 and end date is 1/2/2010
i need the query to view 2000 for the quantity
thank you very mcuh

[NeoPa]

So, I guess you want something like :

SELECT   [ProductID]
        ,[Product]
        ,Sum([quantity]) AS SumQty

FROM     [YourTable]

WHERE    [Date] Between Forms!YourForm.[Start Date]
                    And Forms!YourForm.[End Date]

GROUP BY [ProductID]
        ,[Product]

I admit, I still don't really understand what you're saying, but I hope this is something close to what you want.

[mseo]

ok, Neopa
I attached a DB that illustrates the problem
what I want to do is calculate sum for each product, and in this case I would get one record for each product
but if the same product produced in different dates
in this case I will get more than one record for the same product
the problem stems out from the dateproduced field

[NeoPa]

I think I understand your problem, but I had to hunt around a fair bit to work out what the problem was. Telling me the object name to look at would have been a lot easier than just dropping a database in and expecting me to work out what your problem was associated with.

Your SQL for the query [Sumquantity] is currently :

SELECT   tbl_ProductionDetail.Product_ID
        ,Sum(tbl_ProductionDetail.Quantity) AS SumOfQuantity
        ,Sum(tbl_ProductionDetail.OvertimeQuantity) AS SumOfOvertimeQuantity

FROM     tbl_Production INNER JOIN 
         (tbl_Products INNER JOIN
         tbl_ProductionDetail
  ON     tbl_Products.ProductID = tbl_ProductionDetail.Product_ID)
  ON     tbl_Production.Batch_No = tbl_ProductionDetail.Batch_No

GROUP BY tbl_ProductionDetail.Product_ID
        ,tbl_ProductionDetail.Dateproduced

HAVING   ((tbl_ProductionDetail.Dateproduced) Between [SD] And [ED])

This is because you set the criteria for the date without changing the Total from Group By to Where. This is a default that Access chooses which is frankly not clever. WHERE is what it should default to (sensibly), but when Group By is selected it adds it in as both GROUP BY AND HAVING, thus causing your problem.

What it should look like is :

SELECT   tbl_ProductionDetail.Product_ID
        ,Sum(tbl_ProductionDetail.Quantity) AS SumOfQuantity
        ,Sum(tbl_ProductionDetail.OvertimeQuantity) AS SumOfOvertimeQuantity

FROM     tbl_Production INNER JOIN 
         (tbl_Products INNER JOIN
         tbl_ProductionDetail
  ON     tbl_Products.ProductID = tbl_ProductionDetail.Product_ID)
  ON     tbl_Production.Batch_No = tbl_ProductionDetail.Batch_No

WHERE    ((tbl_ProductionDetail.Dateproduced) Between [SD] And [ED])

GROUP BY tbl_ProductionDetail.Product_ID

[mseo]

I think I understand your problem, but I had to hunt around a fair bit to work out what the problem was. Telling me the object name to look at would have been a lot easier than just dropping a database in and expecting me to work out what your problem was associated with.

Your SQL for the query [Sumquantity] is currently :

SELECT   tbl_ProductionDetail.Product_ID
        ,Sum(tbl_ProductionDetail.Quantity) AS SumOfQuantity
        ,Sum(tbl_ProductionDetail.OvertimeQuantity) AS SumOfOvertimeQuantity

FROM     tbl_Production INNER JOIN 
         (tbl_Products INNER JOIN
         tbl_ProductionDetail
  ON     tbl_Products.ProductID = tbl_ProductionDetail.Product_ID)
  ON     tbl_Production.Batch_No = tbl_ProductionDetail.Batch_No

GROUP BY tbl_ProductionDetail.Product_ID
        ,tbl_ProductionDetail.Dateproduced

HAVING   ((tbl_ProductionDetail.Dateproduced) Between [SD] And [ED])

This is because you set the criteria for the date without changing the Total from Group By to Where. This is a default that Access chooses which is frankly not clever. WHERE is what it should default to (sensibly), but when Group By is selected it adds it in as both GROUP BY AND HAVING, thus causing your problem.

What it should look like is :

SELECT   tbl_ProductionDetail.Product_ID
        ,Sum(tbl_ProductionDetail.Quantity) AS SumOfQuantity
        ,Sum(tbl_ProductionDetail.OvertimeQuantity) AS SumOfOvertimeQuantity

FROM     tbl_Production INNER JOIN 
         (tbl_Products INNER JOIN
         tbl_ProductionDetail
  ON     tbl_Products.ProductID = tbl_ProductionDetail.Product_ID)
  ON     tbl_Production.Batch_No = tbl_ProductionDetail.Batch_No

WHERE    ((tbl_ProductionDetail.Dateproduced) Between [SD] And [ED])

GROUP BY tbl_ProductionDetail.Product_ID

thank very much Neopa
It works fine for me
I thought, I had to use HAVING instead of WHERE because I used an aggregated function within the statement.
but the truth is: I can gain a lot of knowledge here at bytes everyday.
I am sorry I can't choose the best answer twice
thank you again and over again

[NeoPa]

This is a frequent misunderstandin g. Largely, I think, due to the default behaviour Access uses when changing a query from a standard display query to a GROUP BY query.

Let's have a quick look at the difference.
WHERE is a clause that determines which records from the incoming recordsets are even processed.
HAVING is a clause which determines which records are output after they've been processed.

Records that fail the WHERE test don't even get GROUPED BY. Records, that is resultant GROUPED BY records, will not be included in the outgoing recordset if they fail the HAVING test. They have obviously already been processed by the GROUP BY clause though.

Table=[tblTest]

 [I]ID  Colour  Size[/I]
  1  Red       1
  2  Red       2
  3  Red       3
  4  Red       4
  5  Red       5
  6  Blue      2
  7  Blue      4
  8  Blue      6
  9  Blue      8
 10  Blue     10

Assume that we want the average size shown for each colour, but we would like to ignore any size less than 4 from our calculations.

If we now look at using HAVING to filter out those where the size is less than 4, we can see the flaw in our reasoning.

SELECT   [Colour]
        ,Count(*) AS NumItems
        ,Avg([Size]) AS AvgSize

FROM     [tblTest]

GROUP BY [Colour]

HAVING   (Avg([Size])<4)

[I]Colour  NumItems  AvgSize[/I]
 Blue      5         6

Both colours are processed. The AvgSize for Red is 3 and for Blue is 6. Now the HAVING clause kicks in and drops Red from the output. You're left with Blue, but the AvgSize includes a weighting for [ID]=6, which is incorrect.

Now let's look at how this would make more sense with the WHERE clause :

SELECT   [Colour]
        ,Count(*) AS NumItems
        ,Avg([Size]) AS AvgSize

FROM     [tblTest]

WHERE    ([Size]<4)

GROUP BY [Colour]

[I]Colour  NumItems  AvgSize[/I]
 Red       2         4.5 
 Blue      4         7

The items we don't want are ignored by the aggregation processing and we get exactly the figures we're after.