Allow user to only see their data in pivot table

[jaimekash]

I have a pivot table that houses data for the entire corporation and it is pulling data from one large access table.

We have users that will need to only see the data for themselves and/or their department and they will need to see it in the same pivot table format.

I can do it through access based on the workgroup and assigning a particular value to each record that will link to the users name.

Is there anyway to do it in excel without creating seperate pivot tables for each user? The goal is to make it so that each user has to sign into the excel spreadsheet and based on the username, it will only show what they are supposed to see.

Thank you in advance.

 

[johnny45]

Not sure this is the best way but :
Use an input box for the user to put there username or unique secret ID etc... and use that in the SQL query(WHERE Clause)
and exit the sub if it is left blank !

 

[Rofeu]

Hi,

I would set up another table in your Access db with the usernames and their department. Then set up an ADO connection in your Excel workbook and query for the data you want retrieved adding a criterion on the name of the current user (either through a logon protocol or even ENVIRON("USERNAME")) to limit the number of records returned. On top of that you can create your pivottable.

Of course there are more ways than one to skin a cat. I curious to see other solutions.

Cheers,

Roel

 

[xlbo]

I would go with Roel's solution - environ("username") to get the userid in excel and use that as a criteria in a query to restrict the records returned from the access table...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[johnny45]

Depending on the sensitivity of the information, and accessability of the workstations....I could walk by someone elses pc and run the spreadsheet...

IMHO controlling the accessability to the query results through access gives you better SECURITY control as you can edit the user list rather than worry about unauthorized use of a rogue spreadsheet....

 

[jaimekash]

Wow, that is a lot of options and thank you all for such quick responses.

The information is extremely sensitive and is only to be viewed by that particular person so passwords will have to be included.

Thank you again.

 

[johnny45]

That said, I would create a user access table and inclute it in your query that way you are sure that ONLY the right person can access there info....

 

[xlbo]

If its THAT sensitive, why on earth are you using Access and Excel ?????????????? neither are particularly hard to break in terms of their security....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[EBGreen]

I would go so far as to say that both are trivial to break.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]