User's computer login to filter in query

[Jason Lopez]

I asked a question a long time ago and received a response that involved
retrieving the userid from a user's login to filter the results of a
database. Essentially, I am looking for the creation of a report that is
generic in design but specific in results based on who is looking.

Essentially, I only want employees looking at projects that are their own.
Each project has the employee userid associated with it. Is there a way
that I can code the report or the query that only those projects owned by
the viewer are displayed?

Jason Lopez

 

[NetworkTrade]

an easy way is to begin the db with a start up form that prompts their identity

leaving this form open you can then use this identity value as a criteria in
the query that sources the reports

 

[MGFoster]

I asked a question a long time ago and received a response that involved
retrieving the userid from a user's login to filter the results of a
database. Essentially, I am looking for the creation of a report that is
generic in design but specific in results based on who is looking.

Essentially, I only want employees looking at projects that are their own.
Each project has the employee userid associated with it. Is there a way
that I can code the report or the query that only those projects owned by
the viewer are displayed?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, but you'll have to use Access' user-security. This will allow you
to use the CurrentUser() function to get the "userID." You'll also have
to assign the userID to all project rows (records).

The security FAQ is available here:

http://download.microsoft.com/download/access97/faq1/1/win98/en
us/secfaq.exe

I would use a table that has the userID associated with projects, e.g.:

CREATE TABLE UserProjects (
user_id TEXT(50) NOT NULL
REFERENCES Employees (user_id),
project_id INTEGER NOT NULL
REFERENCES Projects (project_id)
CONSTRAINT PK_UserProjects PRIMARY KEY (user_id, project_id)
)

You could have other columns - like a date range to limit the time a
employee is on a project. Then you could use that range in a query to
limit the data to just a period in the projects time cycle.

Then I'd use the UserProjects table in queries to limit the data to just
that assigned to the employee (user):

SELECT <column names>

FROM table1 AS T1 INNER JOIN UserProjects AS UPS ON T1.project_id =
UPS.project_id

WHERE UPS.user_id = CurrentUser()
... etc. ...

The WHERE clause cause the query to select only projects associated with
the current user. The INNER JOIN links those selected projects to the
table you want to get data out of.

HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR9co94echKqOuFEgEQKipACdE6yX1M0NuEaIRWJUukDiK7tT2X0AnR+e
3tCBtKmqp7GmUiuOI2vn2OQ0
=emwJ
-----END PGP SIGNATURE-----

 

[Ron2006]

Although I do use the loginID to determine what computers are logged
in to some of my applications, I DO NOT simply use the loginID for
criteria in queries as such.

The strongest reason for this is that if Jan's computer goes down
there is NO way for Jan to do her work on some other person's computer
without loging in to the network as herself. This usually causes
problems with email and other items.

Using JUST the network login also would not allow someone else to do
Jan's work without signing on as Jan and that would curtail that
person doing their own work right after that or even in the same
session.

In most of the applications, I do however require them to login to the
app as a specific person. In our particular environment, that worked
the best for us. This also allows us to selectively re-assign Jan's
work to other people or finishe off Jan's work when Jan leaves the
company and her userID is no longer valid on the network or she gets
promoted or moved to another team.

Ron

 

[Jason Lopez]

Thank you all for your input on this. I thought it would be a good idea to
use individual daily logins as the criteria for access to certain queries
and reports. As each of you has pointed out, there are some drawbacks and
benefits. But, on this endeavour, the drawbacks overpower the benefits.
Thanks again for all of your input.

Jason