Query a Lookup Table in Excel or Access

Question

I've got a mental block about what I'm sure is a common scenario:

I have some data in a csv file that I need to do some very basic reporting from.

The data is essentially a table with Resources as column headings and People as row headings, the rest of the table consists of Y/N flag, "Y" if the person has access to the resource, "N" if they don't. Both the resources and the people have unique names.

Sample data:

 
        Res1  Res2  Res3
Bob       Y     Y     N
Tom       N     N     N
Jim       Y     N     Y

The table is too large to simply view it as whole in Excel(say 300 resources and 600 people), so I need a way to easily query and display (A simple list would be ok) what resources a person has access to, given the person's name.

The person that will need to use this has MS Office, and not much else on their PC.

So, the question is: What is the best way to manipulate this data to get the report I need? My gut says MS Access would be the best, but I can't figure out to automatically import data like this into a normal relational database. If not Access, perhaps there are some functions in Excel that could help me out?

Answer 1

You should normalize your data. This will make it easier to query against. For example:

table users:
UserID UserName
1      Bob
2      Tim
3      Jim

table resources:
ResourceID ResourceDesc
1          Printer #1
2          Fax Machine
3          Bowling Ball Wax

table users_resources:
LinkID UserID ResourceID
1      1      1
2      1      2
3      3      1
4      3      3

SELECT ResourceID
FROM users_resources, users
WHERE users.UserName="Bob"