Transpose Data into Buckets

[Omar Awan]

Hi,

I have user data in a two column table that relates roles that users have in a enterprise database. They can choose up to 20 roles out of 150 or so roles. The table conceptually looks like the following (i apologize for the format):

User.....Role.. ..
John.....Role 1
John.....Role 45
Ron......Role 1
Ron......Role 25
Ron......Role 30
Shawn....Role 41
Shawn....Role 42
Shawn....Role 43
Shawn....Role 44
Shawn....Role 45
............... .
(Shawn continues up to Role 60)
............... .
Shawn....Role 60

I would like to create a table that creates 20 buckets that fill with each role that the person has.

User.....Bucket 1..Bucket 2..Bucket 3.......Bucket 20
John.....Role 1....Role 45
Ron......Role 1....Role 25...Role 30
Shawn....Role 41...Role 42...Role 43........Role 60

I know relationship-wise databases don't like this, but i have a good reason as to why i need to do this (at least i hope i do). any help would be greatly appreciated. A simple crosstab won't do because of the 150 or so headers and all the blank fields. i need to "bucketize" it like KFC...

Thanks,
Omar

[Rabbit]

If you join the table to itself, you can set up a peudo-rank that you can then use to pivot.

Assuming that the combination of User and Role is unique, you could do something like this.

SELECT x.User, MAX(x.Role) AS RoleMod, COUNT(*) AS PseudoRank
FROM tableName AS x, tblName AS y
WHERE x.User = y.User AND x.Role <= y.Role
GROUP BY x.User

Then you could crosstab it like this.

TRANSFORM MAX(RoleMod) AS Role
SELECT User
FROM queryName
GROUP BY User
PIVOT "Bucket " & PseudoRank

[Omar Awan]

I've created the following SQL statement:

SELECT [CC Security].User, Max([CC Security]![CC Security Rule]) AS RuleMod, Count(*) AS PseudoRank
FROM [CC Security] AS x, [CC Security] AS y
WHERE (x.User = y.User) AND (x.[CC Security Rule]<= y.[CC Security Rule]);

I get two dialogue boxes asking to Enter Parameter Values, one for CC Security.User, and another for CC Security!CC Security Rule

After leaving the blank and clicking ok, i get the result:

Columns: User, RuleMod, PseudoRank and the only value is a number in PseudoRank (824).

I think I understand where you're going with this, but i can't figure out how to make the query work.

I'll keep trying in the meantime.

Thanks so much for your help.

[ck9663]

Are you using SQL Server or MS Access?

~~ CK

[Omar Awan]

Microsoft Access, the new version with the ribbon toolbar.

[Rabbit]

You gave the alias x and y to your tables, you can no longer refer to the fields in the table using the original table name, you have to refer to the fields using the table alias.

I made some fixes to the original query in my post above.

[Omar Awan]

Wow. You rock. I'm absolutely awestruck as to the clever solution you came up with. It works perfectly!

I think i'm going to add an IN statement for the crosstab to fix the bucket numbers from 1 to x in order...

Thanks so much for your help!
Omar

[Rabbit]

Not a problem, glad you got what you needed.

[Omar Awan]

I looked at the data again and noticed that the max function caused the results to only display one role per user in the crosstab. After thinking about it, I'm now trying to go about it a different way.

I created a query that groups users and counts the number of roles they have. Can I take this and create some kind of loop function that increments a number from 1 until the increment equals the count in the query? i could take this number, add a string like "Role " & [the increment] then run the crosstab on that.

again, thanks for helping...

[Rabbit]

Take out the Max but leave in the field. Group by that field as well and you should have what you need.

[Omar Awan]

You, sir, are a genius! Thanks for everything...