How to select top three values from a crosstab

[celtnots]

HI all,
I have a crosstab query that counts the number of times each of a list of activities has been entered for every user, like this:

User act-1 act-2 act-3 etc

Dana----3
John ---------1 -------4
Fox -----------4
etc

Currently there are 16 activities (and hundreds of users), but the list keeps growing. What I'd like to be able to do is to list, in a report, the top three activities for each user and the count of those activities. Is there an easy way to do this? Or a ridiculously complicated way, for that matter!

Thanks,

James

 

[DCrake]

Simple Software Solutions

No matter how you cut the cake you are still going to need 16 columns in your crosstab, because different users will do different activities. so trying to select the top 3 will not reduce the output. What happens if the user only does 2 activities?

You could reduce it by only displaying the top 3 activities across all users. To do this you would need a further query that grouped by activity and counted the number of users, sorted descending and filtering the TOP 3 records.

This would then be added to your crosstab query and joined to the matching activity in your crosstab.

CodeMaster:

 

[celtnots]

Thanks for the quick reply. Actually, it's not the top 3 activities overall that I'm after; it's each person's top three, independent of the others.

I can figure out how to do this (visually, at least) in Excel, but there must be a way to do it programmatically or with SQL...

Many thanks again,

James

No matter how you cut the cake you are still going to need 16 columns in your crosstab, because different users will do different activities. so trying to select the top 3 will not reduce the output. What happens if the user only does 2 activities?

You could reduce it by only displaying the top 3 activities across all users. To do this you would need a further query that grouped by activity and counted the number of users, sorted descending and filtering the TOP 3 records.

This would then be added to your crosstab query and joined to the matching activity in your crosstab.

CodeMaster:

 

[DCrake]

Simple Software Solutions

So if you can work it out in Excel or at least write it down then you should be able to replicate it in Access.