Show employees and others in combo box

[BruceM via AccessMonster.com]

I am trying to figure out a way to show employees and non-employees in a
combo box. The training database includes training records. Training may be
conducted by either employees or outside people.

It is easy enough to make a combo box showing employees, of course. The
EmployeeID number would be the bound column. I suppose I could make a table
for non-employees, and join that to the Employee table in a query, or
something like that. Unlike the Employee table, the non-employee information
would consist of just a first and last name, and maybe the company (and an
Inactive field?).

One problem with this approach is that most often an outside person conducts
a single training session. Their names would remain on the list unless an
Inactive field is checked, or something like that (similar to the Employee
table). Some people return fairly regularly, so some names should remain on
the list. Others return less often, maybe every year or two, but we don't
necessarily know at the time that somebody will be returning in the future.
Therefore in most cases a name would remain on the list until some
housekeeping is done and unneeded names are marked Inactive. This may mean a
lot of asking around to see if a name should remain. There are ways around
these difficulties (by having the user elect to include Inactive names in the
drop-down, for instance), but it seems there could be maintenance hassles and
an inconvenient user interface.

I am redesigning an old database. In that one I got around the difficulty by
storing the trainer's name, not a number. The combo box list is drawn from
the Employee table, and Limit To List is set to False. In this way a non-
employee trainer can be typed in directly. There has never been a need to
list training sessions conducted by an employee. Rather, inquiries about
past training tend to be about the subject of the training or a related
document. Once the record is located the trainer name may be of interest,
but in terms of searching the trainer name is rarely if ever used.

I am violating some normalization principles if I store the actual name, but
is there a more normalized approach that is as convenient as the current
system of typing in a non-employee name?

 

[Jeff Boyce]

Bruce

What about the idea of using a 'person' table to store everyone, and having
an [Employee] table for those folks who have additional information?

You could use an Autonumber primary key on the [Person] table, and use THAT
id to show who is doing the training.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[BruceM via AccessMonster.com]

Ah, I see what you're saying. I was thinking of it the other way around.
Makes sense. The thing I would still need to figure out is how to handle the
maintenance of entries that are typically "one and done" for non-employees,
while preserving as active the handful of non-employees who are expected back
to conduct more training.

Thanks for the idea.

Bruce

What about the idea of using a 'person' table to store everyone, and having
an [Employee] table for those folks who have additional information?

You could use an Autonumber primary key on the [Person] table, and use THAT
id to show who is doing the training.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

I am trying to figure out a way to show employees and non-employees in a
combo box. The training database includes training records. Training may

[quoted text clipped - 45 lines]

is there a more normalized approach that is as convenient as the current
system of typing in a non-employee name?

 

[Jeff Boyce]

You are welcome, and I like your idea of using an [Active] field. Here's a
variation ... if you care when the person became inactive, store a date
value and change the field to [Inactive].

You can use either the y/n field or the presence of a date to help with your
selection criteria for "active" folks to display.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Ah, I see what you're saying. I was thinking of it the other way around.
Makes sense. The thing I would still need to figure out is how to handle
the
maintenance of entries that are typically "one and done" for
non-employees,
while preserving as active the handful of non-employees who are expected
back
to conduct more training.

Thanks for the idea.

Bruce

What about the idea of using a 'person' table to store everyone, and
having
an [Employee] table for those folks who have additional information?

You could use an Autonumber primary key on the [Person] table, and use
THAT
id to show who is doing the training.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

I am trying to figure out a way to show employees and non-employees in a
combo box. The training database includes training records. Training
may

[quoted text clipped - 45 lines]

is there a more normalized approach that is as convenient as the current
system of typing in a non-employee name?