"Dorian" wrote:

Only you can determine how things relate to eachh other since you know your
business rules.
I can envisage an employee working for multiple agencies possible more than
one simultaneously. Whether this could happen during a project you would know.
An employee might work on more than one project simultaneously possibly for
different agencies.
Actually it seems like you are handling several distinct functions in one
database. You have an Employee system, a Project system and an Accounting
system. Will these all be used by the same people. Possibly you may need
multiple front-ends for different functions.
Of course, since I dont know your systems this may all be inaccurate.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"RAK" wrote:

I am trying to create an operational database. We hire temporary staff
through several different employment agencies to complete work on projects
for various clients. The term of employment is linked to individual projects
which are time limited and include different assignments within each project.
Many of the people we use have worked on many different projects at many
different times including several years. I started out thinking I needed 5
different tables and needed to use a many-many relationship. Here are the 5
tables I identified with some types of data:
Employee_Personal (usual type data)
Employment_History (includes Dates hired, Projects assigned at different
times, employment title per project, performance records; rehire status)
Agency (related to each employee, # provided for each project; performance
of employees provided)
Project (Clients, Dates, subjects, teams needed, staff needed)
Accounting [(Hours to complete project-administration pre training,
training, execution and administrative client reporting); (staff cost factors
computed for number of hours *title pay); (computer cost and needs per
project); space cost and needs per project)]

I started looking at the tables to try to see what relationships I needed
and if I needed junction tables. I didn’t know how to handle the different
dates of employment and different assignments for each employee and wondered
if I could use multi-valued fields or value lists for this data. Then I
started doubting if I was going in the right direction and thought I’d see if
I could get any help.


First to clarify: staff can only be hired thru one agency during a current
year but they can work on many different projects
You said "Possibly you may need
multiple front-ends for different functions." What do you mean by this? I thought one of the strengths of Microsoft Access was to contain all relational data in one data base. Are you thinking that I need three databases? I would appreciate your views.