Crosstab queries are confusing me! 1

[snoopy75]

I've done the search thing and read all I can on crosstab queries, and I just don't get it! I need to make a permissions table (or query, probably) with usernames in the row headers, and accounts in the column headers (or vice versa, I don't care)... like this:

      | Acct1 | Acct2 | Acct3 | Acct4
------|-------|-------|-------|-------
User1 |   X   |       |       |   X
User2 |       |   X   |       |   X
User3 |   X   |   X   |   X   | 
User4 |       |   X   |   X   |

I already have a table listing all of my users, and a table listing all of my accounts. I know which users need X's in which accounts, but I don't know where to put that information, and I'm not sure how to generate the above once the information is in place. Please forgive my confusion on what should probably be a basic concept. How do I get started on this, please?

--Ryan

 

[lespaul]

do you have a table that lists which people have which account?

Leslie

 

[snoopy75]

Kind of.... In my Users table now, I have a field called Accounts that contains a comma-delimited string of the accounts that each user is authorized to access. Is there a better way to store that information?

 

[lespaul]

Yes, you should have a

tblPersonAccounts
PersonID
AccountID

Then your crosstab will be very easy.

Read up on third normal form:
'The Fundamentals of Relational Database Design'

Leslie

 

[snoopy75]

Ah... I knew there was some basic concept that I'd missed. This reading will probably help me in more areas than this one. Thanks!!!

 

[lespaul]

Glad to help and thanks for the star!

les