Reworking an old database - need some help

[Larry Kahm]

I am working on a database that was originally designed and built in 2000 by
someone who was just learning Access. One issue that I am faced with is how
to handle an oversight and correct it without endangering the contents of
the database.

The Staff table has a primary key of a person's Initials and includes the
following essential fields: FirstName, LastName, and Title. The Title field
contains the individual's current position in the firm, and the table
contains duplicate text values. I want to change this field to a numeric
and link it to a table that contains the staffing titles.

However, various queries and forms use these three fields in combo boxes and
display fields. The combo boxes all use the Initials as the bound field.
The display fields are the troublesome aspect, because they have the
following format: [Staff].[FirstName]+" "+[Staff].[LastName]+",
"+[Staff].[Title] AS WholeName

Now, that's just great for pulling in the current value of someone's title.
But this doesn't reflect the fact that Bob Jones was an Entry Level Engineer
in 2001 when the record was created, and is a Senior Engineer in 2009. The
record, if displayed or printed, will show Senior Engineer - and that is
incorrect.

At this point, I believe I have to store the value of the Title (from the
Staff table) in each of the affected tables, which would change the display
field to [Staff].[FirstName]+" "+[Staff].[LastName]+",
"+[currentTable].[PastTitle] AS WholeName. But, given the existing design,
there is no way I can determine what any of the previous values were - I can
only do this on a "go forward" basis.

So either I'm making more work for myself than I need to, or I am missing
something simple.

I'm open to any, and all, suggestions.

Thanks!

Larry

 

[Larry Kahm]

Thank you Ken, that helps!

Larry

Larry:

As an employee's Title appears only in the Staff table there is, as you
say,
no way to determine from the data what an employee's previous titles were.
If you are going to include these the values would have to be entered from
other personnel records.

As far as the model for this is concerned you have entity types Staff and
Titles so there would be a table for each of these. The relationship
type
between them is many-to-many so you'd have a table StaffTitles say, to
model
this with columns StaffInitials and TitleID (if you are using a surrogate
key
rather than a natural Title key for Titles). A common way of handling
employees' job history is by the date at which they were appointed or
promoted to the job in question so you could include a column
DateEffective
of Date/Time data type in the StaffTitles table.

This would rely on the context in which you want to show an employee's
previous title including a date value by which the relevant title can be
determined. Say for instance you have a form of Projects based on a table
or
query which includes a ProjectDate column, then the RowSource property for
a
combo box to list employees by their title at the time of the project
would
be along these lines:

PARAMETERS Form!ProjectDate DATETIME;
SELECT StaffInitials, (FirstName+" ") & (LastName +", ") & Title
FROM Staff, StaffTitles, Titles
WHERE StaffTitles.StaffInitials = Staff.StaffInitials
AND StaffTitles.TitleID = Titles.TitleID
GROUP BY StaffInitials, (FirstName+" ") & (LastName +", ") & Title
HAVING MAX(DateEffective)<=Form!ProjectDate;

The combo box would be requeried in the ProjectDate control's AfterUpdate
event procedure and the form's Current event procedure.

This would (a) limit the list to those staff appointed at the time of the
project, and (b) show each employee's title effective at the project date.

Ken Sheridan
Stafford, England

I am working on a database that was originally designed and built in 2000
by
someone who was just learning Access. One issue that I am faced with is
how
to handle an oversight and correct it without endangering the contents of
the database.

The Staff table has a primary key of a person's Initials and includes the
following essential fields: FirstName, LastName, and Title. The Title
field
contains the individual's current position in the firm, and the table
contains duplicate text values. I want to change this field to a numeric
and link it to a table that contains the staffing titles.

However, various queries and forms use these three fields in combo boxes
and
display fields. The combo boxes all use the Initials as the bound field.
The display fields are the troublesome aspect, because they have the
following format: [Staff].[FirstName]+" "+[Staff].[LastName]+",
"+[Staff].[Title] AS WholeName

Now, that's just great for pulling in the current value of someone's
title.
But this doesn't reflect the fact that Bob Jones was an Entry Level
Engineer
in 2001 when the record was created, and is a Senior Engineer in 2009.
The
record, if displayed or printed, will show Senior Engineer - and that is
incorrect.

At this point, I believe I have to store the value of the Title (from the
Staff table) in each of the affected tables, which would change the
display
field to [Staff].[FirstName]+" "+[Staff].[LastName]+",
"+[currentTable].[PastTitle] AS WholeName. But, given the existing
design,
there is no way I can determine what any of the previous values were - I
can
only do this on a "go forward" basis.

So either I'm making more work for myself than I need to, or I am missing
something simple.

I'm open to any, and all, suggestions.

Thanks!

Larry