Need Ideas Please

[Guest]

I am the "Security Facilitator" for a bank. We have standard computer access
profiles set up by job code. In other words, tellers get these applications,
branch managers get those, etc. However, when we have a new hire, the
supervisor has to complete a form I created in Excel and send it to me. Then
I have to compare this Excel file to the standard profiles I've setup in
Access, and to a web-based form created by our holding company for Security
Facilitators to use when requesting the creation of a computer access profile
for new employees.

Currently I'm comparing all this stuff visually. Is there a better way to
have supervisors submit a request? Or is there a better way to compare an
Excel file to its original, or to an Access table?

I'm just looking for some general ideas on how to do this better.

Many thanks!
GwenH

 

[pietlinden]

there's no reason you can't link to the Excel file from within Access
and compare the values in a query. You could do something really
simple like creating an expression that did something like

ExactMatch: (xlsTable.Field1=accTable.Field1 AND
xlsTable.Field2=accTable.Field2...)

 

[KML]

This sounds like more of an issue of process than a computer related
issue. Since, in essence, it doesn't sound like the supervisors have
the option to create a "custom" set of security, could you not just
have the form show them the different levels of access and choose
between "tellers", "branch managers", etc. instead of choosing each
specific security item?

Maybe this won't work for whatever reason, but I thought I might as
well throw it out there....

 

[Guest]

I wish my life could be that simple! We often have situations like this: A
new employee is hired as a teller, but the assistant branch manager wants to
train them *eventually" to be a personal banker. So the Asst. Mgr. wants to
give them all the standard teller applications, plus one or two things that
the personal bankers get.

In other words, not every teller is strictly a teller, and their additional
job duties tend to vary from one branch to another. While we have the
standard profiles, the supervisors need the flexibility of adding other
applications to meet the team member's actual job duties.

 

[Guest]

Currently my standard profiles table in Access only has about 5 fields. The
Excel form has many more. That's because the fields in the Access table are
all text fields. One field lists all the company Intranet applications a
particular job function needs, another field lists all the other applications
they need that are not Intranet-based.

I know there's gotta be a better way to structure this table, but the
problem is we have probably 50+ Intranet applications and about 25+ other
applications that aren't Intranet-based. I tried a table that was just a
bunch of checkboxes, but I also need to have the applications in a paragraph,
separated by commas, for copying and pasting into the holding company's
web-based computer access setup request form. That means the checkboxes
wouldn't work.

Any other ideas how to structure the standard profiles table? And I'm hoping
all this makes sense to you.

Thanks!

 

[John Vinson]

Currently my standard profiles table in Access only has about 5 fields. The
Excel form has many more. That's because the fields in the Access table are
all text fields. One field lists all the company Intranet applications a
particular job function needs, another field lists all the other applications
they need that are not Intranet-based.

I know there's gotta be a better way to structure this table, but the
problem is we have probably 50+ Intranet applications and about 25+ other
applications that aren't Intranet-based. I tried a table that was just a
bunch of checkboxes, but I also need to have the applications in a paragraph,
separated by commas, for copying and pasting into the holding company's
web-based computer access setup request form. That means the checkboxes
wouldn't work.

Any other ideas how to structure the standard profiles table? And I'm hoping
all this makes sense to you.

You really would benefit from normalizing this table. If you're
storing multiple internet applications in one field, and multiple
non-Internet in another - you're violating the very basic principle
that fields should be "atomic", having only one value. You should
consider a three-table structure:

Employees
EmployeeID
<all the usual people fields>

Applications
ApplicationID
InternetRelated <yes/no>
ApplicationName
<other info about the application if needed>

EmployeeApplications
EmployeeID
ApplicationID
<any info about this employee/this application>

You can CONSTRUCT a text string separated by commas from the records
in these tables; that text string should certainly NOT be stored in
any table!


John W. Vinson[MVP]

 

[KML]

Gwen,

It sounds like what you really need is something that can compare the
form that is sent to you to a list of standard applications that
certain job codes get. There are definately ways to do this, but the
only ones I can think of involve some moderately complex coding. As
was suggested previously, you could link the Excel spreadsheet and then
write code to compare the form to a table or tables that contain the
standard templates. An experienced programmer should be able to do
this in a matter of hours with no problem, but if you're a novice it
could take significantly longer.

If you would like to pursue this avenue further, I can help you with
some tips and guidance, but you would need to be able to do the
majority of the work on your own.

 

[Guest]

Three things:
(1) Applications aren't linked to a particular employee, but to a particular
job title.
(2) One job title has access to multiple applications including Intranet
apps. In the EmployeeApplications table you suggest, how would you link one
job title to multiple Application IDs?
(3) How would you construct a text string separated by commas, given the
fact that each job title will have multiple applications?

Thanks!

 

[John Vinson]

Three things:
(1) Applications aren't linked to a particular employee, but to a particular
job title.

Ok... use a table of job titles instead of a table of employees. The
same principle applies.

(2) One job title has access to multiple applications including Intranet
apps. In the EmployeeApplications table you suggest, how would you link one
job title to multiple Application IDs?

By adding *as many records to EmployeeApplications* (or as it appears,
to JobtitleApplications) as there are ApplicationID's, each with its
own ID value. Again... "fields are expensive, records are cheap".

(3) How would you construct a text string separated by commas, given the
fact that each job title will have multiple applications?

See sample code at
http://www.mvps.org/access/modules/mdl0004.htm

John W. Vinson[MVP]

 

[KML]

Three things:
(1) Applications aren't linked to a particular employee, but to a particular
job title.
(2) One job title has access to multiple applications including Intranet
apps. In the EmployeeApplications table you suggest, how would you link one
job title to multiple Application IDs?
(3) How would you construct a text string separated by commas, given the
fact that each job title will have multiple applications?

Thanks!

Sorry for jumping in here Jon.

(1) Then instead of any employees table you would have a Job Title
table. However, in order for this design to work, every employee with
the same Job Title MUST ALWAYS have the same security. If that
statement is not true, then security is not dependant on job title, it
is dependent on something else (probably the employee).
(2) Each Job Title would have a unique ID and the EmployeeApplications
table would have an entry for EVERY application that Job Title has
access to. Each entry would included the Job Title ID and Application
ID (and possibly other data, but it's not necessary). So, if the Job
Title has access to 50 different applications, this table would have 50
different entries for that Job Title (If it turns out that security is
dependent on employee just replace Job Title with Employee in the above
paragraph).
(3) There are probably MANY different ways to do this. I would
probably use a form with a blank field called Job Title (or Employee)
ID, a button titled "Construct Application String", and a picture box.
When the "Construct Application String" button was clicked, I would use
ADO code to gather all of the records from the EmployeeApplications
table that match the Job Title (or Employee) ID in the field and then
loop through them collecting all of the applications that Job Title (or
Employee) has access to, which I would concatenate to a string and send
to the picture box. You could then copy and paste from the picture
box.

Hope this helps to clear up what Jon is talking about...

 

[Guest]

Thanks for the clarification! It helped me understand things better. Now I
need to clarify some things. Our holding company has around 300 banks. At our
bank alone, there are 150+ employees. However, while some may have the job
title "Teller I" and others the job title "Teller II," all Tellers (for
example) basically have the same access with some variations. As a result,
there are only 30 basic job titles with different security access. Now, I'm
not going to try to track these applications by employee - just by job title.
What I'm trying to do is setup standard access profiles so we'll know what to
request when an employee first starts work here. The holding company charges
each bank more than $50 for each computer access project request. So, if we
have standard profiles, we greatly reduce the number of project requests
submitted for a new employee and save ourselves money. With that in mind, I
don't care if Jane Doe, a Teller who has been with us for 2 years, has one
application that none of the other Tellers at our 11 branches have.

Now let's take Intranet Applications as an example. When an employee logs
into the company Intranet, there are up to 75 buttons that employee can see.
Each button leads to a different application. But of course, no employee has
all 75 buttons. Given the fact that we have 75 buttons or applications to
choose from, what I envision having is a table with the 30 basic job titles,
and 75 columns - one for each Intranet application. Under these columns,
should I have a checkbox or simply enter a Y or X if a particular job title
gets that particular Intranet application? And could such a table be used to
construct the application string you guys are talking about, since the
Intranet application names will be the column headings?

I should also mention that once a quarter, we receive an Excel spreadsheet
from the information security department of the holding company that shows,
by employee, which Intranet applications that employee has access to. It sure
would be nice if there was an automated way to compare my standard profiles
for Intranet Apps to this quarterly report in Excel.

Sorry to be so long-winded, but I want to make sure I'm headed in the right
direction before I do a lot of work on this.

Many thanks!

 

[Guest]

Yes, but will that show me what has changed if the fields aren't identical?
For example, Field 5 in the worksheet might have these contents:

Check Image, Synergy, Deluxe

Field 5 in the Access table might have these contents:

Check Image, Business Objects, Cardbase Management, Deluxe Check Ordering

Note the last item in both fields is identical; the names are just slightly
different.

Thanks!

 

[KML]

Gwen,

I haven't forgot about you... I just got too busy today to answer this
one... I'll try to get to it tomorrow!

Kris

 

[John Vinson]

Yes, but will that show me what has changed if the fields aren't identical?
For example, Field 5 in the worksheet might have these contents:

Check Image, Synergy, Deluxe

Field 5 in the Access table might have these contents:

Check Image, Business Objects, Cardbase Management, Deluxe Check Ordering

Note the last item in both fields is identical; the names are just slightly
different.

Well, you have two problems, both rather severe.

For one thing, you have non-atomic fields. As far as Access (or Excel)
is concerned, the text strings

Check Image, Business Objects, Cardbase Management, Deluxe Check
Ordering

and

Business Objects, Check Image, Cardbase Management, Deluxe Check
Ordering

are totally different text strings, and they are neither identical nor
even similar.

And worse, you're attributing human intelligence to a poor idiotic
computer - it has NO way of knowing that "Deluxe" implies "Deluxe
Check Ordering" (as opposed to, say, "Deluxe Suite Accommodation".

If you don't have a controlled vocabulary with defined terms, then
matching will be difficult or impossible. Suppose one user chooses to
enter "Cheque Picture"? Is that identical to "Check Image"? Or suppose
someone chooses to enter "Enterprise Standards" - can a computer, or
even a human who doesn't know that particular jargon term - know that
this is just another way of referring to "Business Objects"?

I'm afraid you have a classic case of GIGO, unless I'm
misunderstanding!

John W. Vinson[MVP]

 

[KML]

Gwen,

It sounds like we're getting into some very basic database design
issues here. Yes, it would probably "work" to have the table set up as
you are describing. However, most experienced database developers
would frown upon that type of setup. The reason is that it would not
be what we database nerds like to call "normalized". The reason for
this is that you are creating unnecessary columns with null (blank, non
existant) data. If you are interested in learning more, there are a
lot of articles related to basic database design at the following link:

http://databases.about.com/od/specificproducts/

Now to answer some of your more specific questions...

Thanks for the clarification! It helped me understand things better. Now I
need to clarify some things. Our holding company has around 300 banks. At our
bank alone, there are 150+ employees. However, while some may have the job
title "Teller I" and others the job title "Teller II," all Tellers (for
example) basically have the same access with some variations. As a result,
there are only 30 basic job titles with different security access. Now, I'm
not going to try to track these applications by employee - just by job title.
What I'm trying to do is setup standard access profiles so we'll know what to
request when an employee first starts work here. The holding company charges
each bank more than $50 for each computer access project request. So, if we
have standard profiles, we greatly reduce the number of project requests
submitted for a new employee and save ourselves money. With that in mind, I
don't care if Jane Doe, a Teller who has been with us for 2 years, has one
application that none of the other Tellers at our 11 branches have.

Now let's take Intranet Applications as an example. When an employee logs
into the company Intranet, there are up to 75 buttons that employee can see.
Each button leads to a different application. But of course, no employee has
all 75 buttons. Given the fact that we have 75 buttons or applications to
choose from, what I envision having is a table with the 30 basic job titles,
and 75 columns - one for each Intranet application. Under these columns,
should I have a checkbox or simply enter a Y or X if a particular job title
gets that particular Intranet application?

If you go with this type of setup you should have a Yes/No field to
signify if the user has access or not.

And could such a table be used to
construct the application string you guys are talking about, since the
Intranet application names will be the column headings?
Yes.


I should also mention that once a quarter, we receive an Excel spreadsheet
from the information security department of the holding company that shows,
by employee, which Intranet applications that employee has access to. It sure
would be nice if there was an automated way to compare my standard profiles
for Intranet Apps to this quarterly report in Excel.

The answer (such as it is with many things) is almost invariably yes,
an automated way to compare them could be created. However, the level
of difficulty in doing this will depend on a few factors, the most
important being the format of the Excel Report.

Sorry to be so long-winded, but I want to make sure I'm headed in the right
direction before I do a lot of work on this.

Measure twice, cut once. Always a good philosophy.

 

[Guest]

GIGO ... tell me about it! The problem is that I have end users completing
the request form, then I have to compare that to my list that uses the proper
terminology.

It sounds like there's really not a way to streamline this any further than
I already have. But thanks anyway for helping me hash this out in my mind and
make sure there wasn't a better way!

 

[John Vinson]

GIGO ... tell me about it! The problem is that I have end users completing
the request form, then I have to compare that to my list that uses the proper
terminology.

Is this a paper form? If so, could you make it a multiple choice with
checkboxes, rather than blanks to fill in by hand? Or, better, wean
the users onto an Access (or Excel, or Web, whatever works best) Form
where you can control the content?

It sounds like there's really not a way to streamline this any further than
I already have. But thanks anyway for helping me hash this out in my mind and
make sure there wasn't a better way!

Best of luck - you'll need it!

John W. Vinson[MVP]