If you care that John Doe was working for Agency A while working on Project
17, Task 3 in 2008, but was working for Agency B while working on Project
23, Task 11 in 2009, then you'll need to keep track of Staff/Agency
connections.

If you want to see EVERYTHING John Doe ever touched, but don't care who he
was working for at the time, you don't need the Staff/Agency connection.

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 pseudocode 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.

"RAK" wrote in message
...


"Jeff Boyce" wrote:

The way you handle many-many relationships in Access is via a junction
table.

It sounds like you are saying that:

One Project can have many Tasks.
One Task can be assigned to (any-of-many) Staff
One Staff can be working for (any-of-many) Agencies

Sounds like:
[Project] table - info specific to a project
[Task] table - Task-specific info, including ProjectID (to which
Project
does this task belong?)
[Agency] table - list of agencies and agency-specific info
[Staff] table - AKA [Employee] table
[AgencyStaff] table - a junction table, showing who worked for which
agency during which time frame
[TaskAssignment] table - a junction table, showing which AgencyStaffID
worked on which TaskID

But you're closer to your situation. Does the above make sense?

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 pseudocode 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.

"RAK" wrote in message
...
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.



.
Yes, thanks. This is the direction I was heading but got a little lost
when I started working on relational links and defining the foreign keys.
The AgencyStaff junction table appears to take care of the relationships
for staff & agency. But I'm not sure what relationships are defined in
the TaskAssignment junction table. Can I impose upon you for a little
more detail and perhaps a light will shine for me on how to handle the
accounting data.
To clarify your conclusions:
One Project can have many Tasks. Yes
One Task can be assigned to (any-of-many) Staff Yes
One Staff can be working for (any-of-many) Agencies No, not in same
year