Forms: Count & SUM in VBA Access

[weirdguy]

Hello all,

Before you read my question, I would advise you to visit the Image-Link.

The image-link shows 4Tables - its field, Primary, Foreign Keys, Relationships. The bottom of the Image, it is a Query (TransQ).

My question,

I am using a listbox to preview the following Count Code:

Me.listDetailsBox.RowSource = "SELECT ItemID, Count(TransQty) AS QtySold FROM TransQ GROUP BY ItemID ORDER BY Count(TransQty) DESC"

Data Example:

ItemID          Qty     Unit$      Total$
Item200         2          $1            $2
Item200         1          $1            $1
Item200         5          $1            $5

Output using the code above:

ItemID          Count(TransQty)
Item200                        3

The code's output only refer to 3 transaction, rather than the total quantity for all transaction which belongs to the ItemID. Is there anyway to make it that way?

The output should be

ItemID          Count(TransQty)
Item200                        8

[Delerna]

Me.listDetailsBox.RowSource = "SELECT ItemID, Sum(TransQty) AS QtySold FROM TransQ GROUP BY ItemID ORDER BY Count(TransQty) DESC"
 

count counts the number of rows in that group.
You have 3 rows

sum totals all the values in a field in that group
The sum of those three rows is 8

[weirdguy]

Thanks a lot. Really thanks a lot.

I have a further question. For this question, I am not really sure where to start to build the code. I want to have a Top 10 List. For an example, Top 10 Best-Selling Item. How can i limit the result to only display 10 or less? What type of Control-Box should i use - listbox?

if listbox, this is my RowSource code.

dateSource = "SELECT ItemID, Sum(TransQty) As QtySold FROM TransQ WHERE "
dateSource = dateSource & ((dateSQL))
dateSource = dateSource & " GROUP BY ItemID ORDER BY Sum(TransQty)DESC"
 
Me.listDetailsBox.RowSource = dateSource

In case, you are wondering what is dateSQL

dateSQL = "TransDate>=" & Format(txtSalesStartDate, conJetDate) & " And TransDate<=" & Format(txtSalesEndDate, conJetDate) & ""
datesSQL = dateSQL

Any other recommendation is welcome. Thanks again

[NeoPa]

Check out the TOP predicate in the Help system for the SQL SELECT clause.

Finding Jet SQL Help may help.