Count Unique Field Values

[Katherine]

I'm working with this database for a theater company and I'm trying to
count the number of seasons someone has been a season ticket holder.
Problem is, when one person buys 4 tickets for one season, they current
show up in the database as having been a sesaon ticket holder for 4
years even though they're all for the same year. I'd like to, instead,
count by unique field values rather than total field values.

For example...

UserID Season

1 03-04
1 03-04
1 04-05
1 05-06
1 05-06
1 05-06
1 05-06
2 03-04
2 04-05
2 04-05
2 05-06

Both UserID 1 and 2 should return a count of 3 for number of seasons
they've been season ticket holders. Right now 1 returns a count of 7
while 2 returns a count of 4. I've tried playing with the Unique
Values and Unique Records properties, but these don't have any effect.

Can someone please offer suggestions?

Thanks for the help!

-Katherine

 

[John Spencer]

Easiest way is to user two queries. First Query
SELECT DISTINCT UserID, Season
FROM YourTable

Save that as qUniqueUserSeason and use it in a second query

SELECT UserId, Count(UserID) as SeasonCount
FROM qUniqueUserSeason
GROUP BY UserID

Post back if you absolutely have to do it in one query.