normalization issue & logging in

[Rob S]

Our current db does not use any form of user authentication. There are
several "activity" tables used that are populated by form entries. Each of
the activity tables contains several fields, but there are two fields that
are on each of the activity tables - ActivityDate and Staff.

I am trying to create a more streamlined db and I've been able to create a
user authentication system, realizing that it is not a secure
authentication. Once authenticated, the staff logging in has field that is
set to 1, where the remainder of the staff are set to 0.

There are a couple of questions in this and maybe I've just looked at the
old db too long and I can't see my way through "fixing" it in the new one.
First question deals with normalization. It seems like the db is not
normalized because staff names and activity date are entered into every
form/table. What would be the best way to normalize this?

This relates to my second question. Assuming there isn't really any way to
normalize the tables better, is there a way to automatically enter the name
of the user that is authenticated into the appropriate field for the new
record without typing it in? I've tried to ignore the first question for
the time being and look at the second. I can do a dlookup of the staff
member that is logged in based on whether the user is set to a 0 or to a 1.
But doing this only enters the staff name in the form. It doesn't carry the
staff member's name over into the corresponding "activity" table.

We want to be able to look for activity of an individual person, so I need
to link to the user. But most of the time we will do a search for each of
the different tables based on date and not be concerned about an individual
person. My guess is that there is an answer to the second question that
also addresses the normalization issue and I'm just not seeing it.
Suggestions?

 

[John W. Vinson]

Our current db does not use any form of user authentication. There are
several "activity" tables used that are populated by form entries. Each of
the activity tables contains several fields, but there are two fields that
are on each of the activity tables - ActivityDate and Staff.

I am trying to create a more streamlined db and I've been able to create a
user authentication system, realizing that it is not a secure
authentication. Once authenticated, the staff logging in has field that is
set to 1, where the remainder of the staff are set to 0.

There are a couple of questions in this and maybe I've just looked at the
old db too long and I can't see my way through "fixing" it in the new one.
First question deals with normalization. It seems like the db is not
normalized because staff names and activity date are entered into every
form/table. What would be the best way to normalize this?

This relates to my second question. Assuming there isn't really any way to
normalize the tables better, is there a way to automatically enter the name
of the user that is authenticated into the appropriate field for the new
record without typing it in? I've tried to ignore the first question for
the time being and look at the second. I can do a dlookup of the staff
member that is logged in based on whether the user is set to a 0 or to a 1.
But doing this only enters the staff name in the form. It doesn't carry the
staff member's name over into the corresponding "activity" table.

We want to be able to look for activity of an individual person, so I need
to link to the user. But most of the time we will do a search for each of
the different tables based on date and not be concerned about an individual
person. My guess is that there is an answer to the second question that
also addresses the normalization issue and I'm just not seeing it.
Suggestions?

I hope that your Staff table contains a (probably numeric, though it could be
text) StaffID; if so you should be storing THAT field (not the staff name,
which may change and may not be unique) in your tables. There's nothing
denormalized about doing so; if one of the business attributes of an entity is
"who updated this entity's data and when" then it's perfectly appropriate.

No DLookup is needed. Simply use a Combo Box based on a query selecting only
the records with authentication = 1, displaying the name, and storing the
StaffID.

As for searching... just because you're storing a StaffID (or for that matter
a staff name), you're not obligated to search it. You can search on the date
with or without searching on the individual.

 

[Rob S]

OK, that makes sense. I got it to show the staff member who is logged in,
but it won't store the staff id in the table for the activity.

 

[Rob S]

I don't know if I solved this in the appropriate way or not, but I finally
got it done. I have an update query that is based on my log in info. This
query sets a numeric value to 1 for the staff logging in. Then I created a
combo box that looks at that numeric value and shows only the name of the
staff person logged in. Then I chose another field and then entered the
following code for a "lost focus" command:

me.cmbStaff = me.cmbStaff.ItemData(0)

This then selects the one name in that is present in the combo box. I can
now hide that field. This seems to work, but I wonder if there was a better
way of accomplishing my goal.

OK, that makes sense. I got it to show the staff member who is logged in,
but it won't store the staff id in the table for the activity.