By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,253 Members | 1,701 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,253 IT Pros & Developers. It's quick & easy.

listbox to select products and calculate the sum in query

mseo
100+
P: 181
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
Jul 1 '10 #1

✓ answered by NeoPa

Presumably this code would run in the OnClick event procedure of a Command Button.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRun_Click()
  2.     Dim strWhere As String
  3.     Dim varIx As Variant
  4.  
  5.     With Me.lstProd
  6.         For Each varIx in .ItemsSelected
  7.             strWhere = strWhere & ",'" & .ItemData(varIx) & "'"
  8.         Next varIx
  9.     End With
  10.     If strWhere > "" Then _
  11.         strWhere = "WHERE [YourField] In(" & Mid(strWhere, 2) & ")"
  12.     'The filter is now set up in strWhere.
  13.     'Other code to apply the filter where you require it.
  14.     ...
  15. End Sub
PS. Sorry for late reply. I must have missed it somehow. I only saw it in response to your latest post.

Share this Question
Share on Google+
14 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Jul 2 '10 #2

NeoPa
Expert Mod 15k+
P: 31,768
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).
Jul 2 '10 #3

mseo
100+
P: 181
@TheSmileyOne
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
Jul 4 '10 #4

mseo
100+
P: 181
hi,
@TheSmileyOne
can you provide me more explaination of how to do so
thank you for your dedication
Jul 7 '10 #5

NeoPa
Expert Mod 15k+
P: 31,768
Presumably this code would run in the OnClick event procedure of a Command Button.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdRun_Click()
  2.     Dim strWhere As String
  3.     Dim varIx As Variant
  4.  
  5.     With Me.lstProd
  6.         For Each varIx in .ItemsSelected
  7.             strWhere = strWhere & ",'" & .ItemData(varIx) & "'"
  8.         Next varIx
  9.     End With
  10.     If strWhere > "" Then _
  11.         strWhere = "WHERE [YourField] In(" & Mid(strWhere, 2) & ")"
  12.     'The filter is now set up in strWhere.
  13.     'Other code to apply the filter where you require it.
  14.     ...
  15. End Sub
PS. Sorry for late reply. I must have missed it somehow. I only saw it in response to your latest post.
Jul 7 '10 #6

mseo
100+
P: 181
@NeoPa
Hi, Neopa
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdrun_Click()
  2.  Dim strWhere As String 
  3.     Dim varIx As Variant 
  4. For Each varIx In Me.list1.ItemsSelected
  5.    strWhere = strWhere & Me.list1.Column(0, varIx ) & ","
  6. Next varIx 
  7. strWhere = "PID in (" & Left(strWhere, Len(strWhere) - 1) & ")"
  8. DoCmd.OpenForm "form1", , , strWhere
  9. End Sub
and it works great
I really appreciate you help
Jul 7 '10 #7

mseo
100+
P: 181
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
Jul 7 '10 #8

NeoPa
Expert Mod 15k+
P: 31,768
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.
Jul 8 '10 #9

mseo
100+
P: 181
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
Jul 8 '10 #10

NeoPa
Expert Mod 15k+
P: 31,768
So, I guess you want something like :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [ProductID]
  2.         ,[Product]
  3.         ,Sum([quantity]) AS SumQty
  4.  
  5. FROM     [YourTable]
  6.  
  7. WHERE    [Date] Between Forms!YourForm.[Start Date]
  8.                     And Forms!YourForm.[End Date]
  9.  
  10. GROUP BY [ProductID]
  11.         ,[Product]
I admit, I still don't really understand what you're saying, but I hope this is something close to what you want.
Jul 8 '10 #11

mseo
100+
P: 181
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
Jul 8 '10 #12

NeoPa
Expert Mod 15k+
P: 31,768
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tbl_ProductionDetail.Product_ID
  2.         ,Sum(tbl_ProductionDetail.Quantity) AS SumOfQuantity
  3.         ,Sum(tbl_ProductionDetail.OvertimeQuantity) AS SumOfOvertimeQuantity
  4.  
  5. FROM     tbl_Production INNER JOIN 
  6.          (tbl_Products INNER JOIN
  7.          tbl_ProductionDetail
  8.   ON     tbl_Products.ProductID = tbl_ProductionDetail.Product_ID)
  9.   ON     tbl_Production.Batch_No = tbl_ProductionDetail.Batch_No
  10.  
  11. GROUP BY tbl_ProductionDetail.Product_ID
  12.         ,tbl_ProductionDetail.Dateproduced
  13.  
  14. 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   tbl_ProductionDetail.Product_ID
  2.         ,Sum(tbl_ProductionDetail.Quantity) AS SumOfQuantity
  3.         ,Sum(tbl_ProductionDetail.OvertimeQuantity) AS SumOfOvertimeQuantity
  4.  
  5. FROM     tbl_Production INNER JOIN 
  6.          (tbl_Products INNER JOIN
  7.          tbl_ProductionDetail
  8.   ON     tbl_Products.ProductID = tbl_ProductionDetail.Product_ID)
  9.   ON     tbl_Production.Batch_No = tbl_ProductionDetail.Batch_No
  10.  
  11. WHERE    ((tbl_ProductionDetail.Dateproduced) Between [SD] And [ED])
  12.  
  13. GROUP BY tbl_ProductionDetail.Product_ID
Jul 9 '10 #13

mseo
100+
P: 181
@NeoPa
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
Jul 10 '10 #14

NeoPa
Expert Mod 15k+
P: 31,768
This is a frequent misunderstanding. 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]
Expand|Select|Wrap|Line Numbers
  1.  ID  Colour  Size
  2.   1  Red       1
  3.   2  Red       2
  4.   3  Red       3
  5.   4  Red       4
  6.   5  Red       5
  7.   6  Blue      2
  8.   7  Blue      4
  9.   8  Blue      6
  10.   9  Blue      8
  11.  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.
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Colour]
  2.         ,Count(*) AS NumItems
  3.         ,Avg([Size]) AS AvgSize
  4.  
  5. FROM     [tblTest]
  6.  
  7. GROUP BY [Colour]
  8.  
  9. HAVING   (Avg([Size])<4)
Expand|Select|Wrap|Line Numbers
  1. Colour  NumItems  AvgSize
  2.  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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [Colour]
  2.         ,Count(*) AS NumItems
  3.         ,Avg([Size]) AS AvgSize
  4.  
  5. FROM     [tblTest]
  6.  
  7. WHERE    ([Size]<4)
  8.  
  9. GROUP BY [Colour]
Expand|Select|Wrap|Line Numbers
  1. Colour  NumItems  AvgSize
  2.  Red       2         4.5 
  3.  Blue      4         7
The items we don't want are ignored by the aggregation processing and we get exactly the figures we're after.
Jul 10 '10 #15

Post your reply

Sign in to post your reply or Sign up for a free account.