limiting access to reports

In my employee database, supervisors will need the ability to run reports on their employees. This reporting can begin at any level of the organizational hierarchy. The Director will need to run reports on any of her supervisors and their employees; her supervisors will need to run reports on their employees; employees will need to run reports on themselves. Each employee record has a field which gives that person’s supervisor number.

I have the reports working for supervisors, but I haven’t figured out how to limit the supervisors report to only their employees. As it is right now, any db user can run a report on anyone. (This db has not been released and is still in the development stages.) Data and tables are on a SQL Server and users have the front end forms, reports, etc. We’re using SQL logon authentication for access to the tables.

Where can I find information about how to limit a user’s access to only their employee’s records? I know the answer lies in a select statement, but how do I identify the current logged on user?

tia,
--
JMorrell