Link to home
Start Free TrialLog in
Avatar of dianna Felton
dianna FeltonFlag for United States of America

asked on

MS Access 2007 Count Unique Values in a Group

How would I count each unique value when the values in a group change.  I would need to do a unique count based on the name / qty combination below.
Name             Qty          Example Count
Louis                  1             1
Louis                 12            2
Louis                 18            3
Brianna             1              1
Cynde                1              1
Cynde                6              2
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try this query

Select [name], Qty, (select count(*) From TableX as T where TableX.[Name]=T.[Name] and TableX.Qty >= T.Qty) as ItemCount
from TableX
order by [name]
Or perhaps:

SELECT Name, Qty, Count(*) AS ItemCount FROM YourTable GROUP BY Name, Qty
SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial