Filtering records based on user

  • Thread starter Thread starter Secret Squirrel
  • Start date Start date
S

Secret Squirrel

I want to set up a filter where a specific user can only see certain records
within my tblEmployees. I have a table (tblDepartments) that is linked to my
tblEmployees. I also have a table (tblUsers) that is linked to tblDepartments
as well. Each user has a specific department they are responsible for so I
only want them to see the records in the tblEmployees that are within their
department. How would I design a query to link all this together so it
filters correctly based on the department? I assume I'll also have to link my
tblUsers to the ULS so it knows which user is logged in, right? Any ideas?
 
You don't mention how these are 'linked', so I'll assume you mean your
tables have foreign key fields that point back at their "parents" records.

By the way, I'll also assume you don't have any "mesh" or "shared"
assignments, in which a single Employee answers to more than one Department.

You are asking a "how" question ... how depends on what ... it all starts
with the data. And I'm guessing you'll need so code/procedures also, not
just "a query to link all this together".

FYI, you can use a dynamically-constructed SQL statement in a procedure to
serve as a query. I'd probably take an approach like this to first identify
the user logged in, then find the records in tblEmployees who are in that
department.

You could try your hand at creating a query that lists all Employees for
each Department, for each User. Then the only other piece of the puzzle
would be creating the WHERE statement for which User.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
That should have read

"... you'll need code/procedures also,..."

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
FYI, you can use a dynamically-constructed SQL statement in a procedure to
serve as a query. I'd probably take an approach like this to first identify
the user logged in, then find the records in tblEmployees who are in that
department.

You could try your hand at creating a query that lists all Employees for
each Department, for each User. Then the only other piece of the puzzle
would be creating the WHERE statement for which User.
Click to expand...

Jeff's right.

But remember that if the back-end database is in Access, you won't be
able to ensure that someone can't see records outside their
department. There are plenty of ways to circumvent the application
and get to the underlying data directly. So make sure that you're
doing this for convenience, not for sensitive or personal data.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Hi Jeff,

Thanks for the help. What I'm thinking is this...

I create a case select on my form's load event to lookup the CurrentUser()
and match it to the username in my tblUsers then have it take the value from
the field "dept" also in my tblUsers and whichever the value is use a case
select to run a SQL function to load the correct query. How does that sound?
 
Actually this is to filter records so that managers can only see their
employees. So it's kind of both for convenience and sensitivity purposes. The
database will be secure and users won't be able to get to the BE. This is
just an internal security measure on top of having ULS set up.
 
I took another approach but didn't realize that this type of query isn't
updateable. Or is it and I just have it written wrong? I still want to be
able to add new records but I have a feeling I can't if I'm filtering the
records.

SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.LastName & ", " & tblEmployees.FirstName AS LastFirst,
tblEmployees.FirstName & " " & tblEmployees.LastName AS FirstLast,
tblEmployees.EEID, tblEmployees.Dept, tblEmployees.StartDate,
tblEmployees.EndDate, tblEmployees.Address1, tblEmployees.Address2,
tblEmployees.City, tblEmployees.State, tblEmployees.ZipCode,
tblEmployees.HomePhone, tblEmployees.SSN, tblEmployees.Birthdate,
tblEmployees.Status, tblEmployees.Position, tblEmployees.Notes,
DateDiff("m",[StartDate],Date()) AS Accrual,
tblEmployees.memProperyPhotoLink, tblEmployees.VacationOverrides,
tblEmployees.VacationOverrideReason, tblEmployees.BadgeNo,
tblEmployees.Shift, tblEmployees.VacationCarryover,
tblEmployees.VacationCarryoverReason, tblEmployees.NextReviewDate,
tblEmployees.PersonalTimeOverride, tblEmployees.PersonalTimeOverrideReason
FROM tblEmployees, tblUsers
WHERE (((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE
CurrentUser() = tblUsers.UserName)) AND ((tblEmployees.Status)="Active")) OR
((([Forms]![frmEmployeeMain]![chkInactive])=-1));
 
Ok I've got it working now but I have one question. In my tblUsers in the
field Dept I want to put "All" and have this query show all records for that
specific user that has "all" in the dept field. How do I write that into my
query?


SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.LastName & ", " & tblEmployees.FirstName AS LastFirst,
tblEmployees.FirstName & " " & tblEmployees.LastName AS FirstLast,
tblEmployees.EEID, tblEmployees.Dept, tblEmployees.StartDate,
tblEmployees.EndDate, tblEmployees.Address1, tblEmployees.Address2,
tblEmployees.City, tblEmployees.State, tblEmployees.ZipCode,
tblEmployees.HomePhone, tblEmployees.SSN, tblEmployees.Birthdate,
tblEmployees.Status, tblEmployees.Position, tblEmployees.Notes,
DateDiff("m",[StartDate],Date()) AS Accrual,
tblEmployees.memProperyPhotoLink, tblEmployees.VacationOverrides,
tblEmployees.VacationOverrideReason, tblEmployees.BadgeNo,
tblEmployees.Shift, tblEmployees.VacationCarryover,
tblEmployees.VacationCarryoverReason, tblEmployees.NextReviewDate,
tblEmployees.PersonalTimeOverride, tblEmployees.PersonalTimeOverrideReason
FROM tblEmployees
WHERE (((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE
CurrentUser() = tblUsers.UserName)) AND ((tblEmployees.Status)="Active")) OR
(((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE CurrentUser() =
tblUsers.UserName)) AND (([Forms]![frmEmployeeMain]![chkInactive])=-1));
 
Code. Consider creating a procedure that dynamically sets the WHERE clause
of your SQL statement. If the <ALL> choice is made, don't use ANY clause
for WHERE!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Secret Squirrel said:
Ok I've got it working now but I have one question. In my tblUsers in the
field Dept I want to put "All" and have this query show all records for
that
specific user that has "all" in the dept field. How do I write that into
my
query?


SELECT tblEmployees.ID, tblEmployees.LastName, tblEmployees.FirstName,
tblEmployees.LastName & ", " & tblEmployees.FirstName AS LastFirst,
tblEmployees.FirstName & " " & tblEmployees.LastName AS FirstLast,
tblEmployees.EEID, tblEmployees.Dept, tblEmployees.StartDate,
tblEmployees.EndDate, tblEmployees.Address1, tblEmployees.Address2,
tblEmployees.City, tblEmployees.State, tblEmployees.ZipCode,
tblEmployees.HomePhone, tblEmployees.SSN, tblEmployees.Birthdate,
tblEmployees.Status, tblEmployees.Position, tblEmployees.Notes,
DateDiff("m",[StartDate],Date()) AS Accrual,
tblEmployees.memProperyPhotoLink, tblEmployees.VacationOverrides,
tblEmployees.VacationOverrideReason, tblEmployees.BadgeNo,
tblEmployees.Shift, tblEmployees.VacationCarryover,
tblEmployees.VacationCarryoverReason, tblEmployees.NextReviewDate,
tblEmployees.PersonalTimeOverride, tblEmployees.PersonalTimeOverrideReason
FROM tblEmployees
WHERE (((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE
CurrentUser() = tblUsers.UserName)) AND ((tblEmployees.Status)="Active"))
OR
(((tblEmployees.Dept)=(Select([Dept]) FROM tblUsers WHERE CurrentUser() =
tblUsers.UserName)) AND (([Forms]![frmEmployeeMain]![chkInactive])=-1));


Jeff Boyce said:
You don't mention how these are 'linked', so I'll assume you mean your
tables have foreign key fields that point back at their "parents"
records.

By the way, I'll also assume you don't have any "mesh" or "shared"
assignments, in which a single Employee answers to more than one
Department.

You are asking a "how" question ... how depends on what ... it all starts
with the data. And I'm guessing you'll need so code/procedures also, not
just "a query to link all this together".

FYI, you can use a dynamically-constructed SQL statement in a procedure
to
serve as a query. I'd probably take an approach like this to first
identify
the user logged in, then find the records in tblEmployees who are in that
department.

You could try your hand at creating a query that lists all Employees for
each Department, for each User. Then the only other piece of the puzzle
would be creating the WHERE statement for which User.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
Click to expand...
 
Actually this is to filter records so that managers can only see their
employees. So it's kind of both for convenience and sensitivity purposes. The
database will be secure and users won't be able to get to the BE. This is
just an internal security measure on top of having ULS set up.
Click to expand...

Okay, you're using ULS and the database will be secure.

Are you using OwnerAccess queries, with users having no permissions to
the BE tables themselves? If so, how do you ensure that the
OwnerAccess queries will never access records outside the user's
department?

Is the BE database encrypted? How do you mean that the users won't be
able to "get to the BE" - they'll need read/write permission to the
folder, so they'll be able to take the BE home on a USB drive if they
want to.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Back
Top