Relationship teaser

[Tom]

I need to add a table to the "many-to-may" structure (see
below).

Basically, the structure I currently have allows to have
multiple employees assigned to a job.

I now need to add two additional table (e.g. "location"
& "comments"). Obviously, there can be multiple
locations and comments in the structure.

I first tried to follow the existing structure
and "copied" the tblEmployees and then renameed it to
tblLocation.

I modified and added the 3rd primary key (LocID) in the "
tblJobAssignments". However, when querying for
information I now don't get any results in the query.

Quick structure recap:
- Multiple jobs/projects
- Multiple employees (some of them assigned to same job)
- Multipe locations (some locations are assigned to same
job/project)
- Multiple comments for each job


Here are now my questions:
1. How should I join the additional tables (location,
comments, etc.) into the existing structure.
2. What are their relationships (PKs, one-to-many, many-
to-many)?



This design has really become a challenge for me. I
appreciate all help I can get on this!




********** EXISTING STRUCTURE ********************

tblJobs
JobID JobTitle
(JobID is Autonumber Primary Key)

tblEmployees
EmpID EmpName
(EmpID is Autonumber Primary Key)

tblJobAssignments
JobID EmpID
(both fields Long Integer)
(Primary Key: JobID, EmpID)
(Relationship: JobID foreign key to tblJobs.JobID, one
to many)
(Relationship: EmpID foreign key to tblEmployees.EmpID,
one to many)



tblJobs Contents (JobID contents determined by Access):
JobID JobTitle
==================
51 Analyst
52 Engineer
53 Manager
54 Analyst II


tblEmployees Contents (EmpID contents determined by
Access):
EmpID EmpName
================
121 Jack
122 Ben
123 Sue
124 Carol
125 Mike
126 Tom


tblJobAssignments Contents:
JobID EmpID
===============
51 121
52 122
52 123
53 124
53 125
54 126

*******************************************

 

[Tom]

Jack:

Thank you so much for your help on this!!!

I (believe) that I have a better understanding of how the
relationship work. I think my problem
was the "junction table" which had 2 PKs in there.

Nevertheless, I am still left with a "problem".


COMPLETED WORK:
===============
- Modified the example and appropriate tables (see **
SUGGESTED STRUCTURE ** below)
- Created the relationship as you suggested
- In the relationship window, all relationships were
created successfully


Additional Actions:
===================
In the original thread, I used the "Employee, Jobs"
example since this many-to-many relationship
was a reply to me in an older threat. This allowed to
provide a quick idea of the underlying design.

However, my actual design follows a bit of a different
path (but I believe it uses the same idea).

Here's what I need to achieve:
1. Track CORRESPONDENCE (multiple documents =
multiple "EMPLOYEES ON PROJECT")
2. CORRESPONDENCE has multiple "CLASSIFICATIONS (=
multiple "EMPLOYEES")
3. CORRESPONDENCE will go into multiple LOCATIONS (= JOBS)
4. via JUNCTION table (= PROJECTS), CORRESPONDENCE is
linked to multiple ACTION OFFICERS (= PROJECT LOCATIONS)
5. ACTION OFFICERS will taken appropriate actions and add
multiple COMMENTS per CORRESPONDENCE case

Therefore, I have renamed the **SUGGESTED STRUCTURE** .
Please review the **STRUCTURE AFTER RENAMING** (see
below).
I recreated the relationships and they are now identical
to the original example.


NOW THE PROBLEM:
- I need to use forms w/ subforms in order to create cases
- I would like to create the form/subform relationship
via query
- Since all relationships are established properly (I
guess), I created a query and (for testing purposes)
clicked on the * (all fields) of ALL TABLES
- When executing the query, I expected to NOT find any
records since no data has been added to the tables yet.
- However, rather than seeing ONE BLANK record, ONLY THE
GRAY FIELD NAMES came up (I did not even see the first
white blank record)
- Also, when creating e.g. the first form/subform (i.e.
Correspondence), I wanted to add data in the SUBJECT
field (first field for testing purposes). Unfortunately,
I already was prompted with some error dialog boxes such
as:


ERRORS:
- "Parts of the link w/ missing pair fields will be
ignored" (when adding subform tblClassification)
- "You cannot add or change a record because a record is
required in the table tblClassification" (when attempting
to
add a subject to SUBJECT field in tblCorrespondence.



Jack, I hope I did not cause any confusion. Please let
me know if I did. I really need to get this to work... I
TRULY APPRECIATE if you could provide me more information
and pointers
1. did I violate some of the relationships by renaming
them? (I shouldn't... whether I call it "X" or "Y"
("Jobs/Projects/Employes"
vs. "Correspondence/ActionOfficers/Classification" etc.
should not have a difference for adding data)?
2. what am I missing in respect to the "blank results"
after executing the query?
3. do you have any suggestions in respect to the
form/subform relationships for data entry?


THANKS IN ADVANCE!!!

Tom




******* SUGGESTED STRUCTURE ************************
tblEmployees
employeeID pk autonumber
employeeName

tblJobs
jobID pk autonumber
jobTitle

tblProjects
projectID pk autonumber
ProjectName

tblEmployeesOnProject
ID pk autonumber
employeeIDfk
jobIDfk
projectIDfk

tblLocations
locationID pk autonumber
locationName

tblProjectLocations
ID pk autonumber
projectIDfk
locationIDfk

******* SUGGESTED STRUCTURE ************************



******* STRUCTURE AFTER RENAMING ************************


tblClassification (old: tblEmployees)
ClassificationID pk autonumber
Classifiction

tblCorrespondence (old: tblEmployeesOnProject)
CorrespondenceID pk autonumber
ClassificationIDfk
LocationID
JunctionID
Subject (TEXT field for testing case)

tblLocation (old: tblJobs)
LocationID pk autonumber
Location

tblJunction (old: tblProjects)
JunctionID pk autonumber

tblActionOfficers (old: tblProjectLocations)
ActionOfficerID pk autonumber
JunctionID
CommentsID

tblComments (old: tblLocations)
CommentsID pk autonumber
Comment

******* STRUCTURE AFTER RENAMING ************************

 

[Tim Ferguson]

was the "junction table" which had 2 PKs in there.

No, one PK made up of two fields. It's an important difference because you
can have as many keys as you like (although only one can be primary) and
each one can be made up of as many fields as you like.

To return to your original question, you never told us what Comments and
Locations apply to. Is it the Location of the person; or the location of
the Job; or the Location of where the particular person does that job? If
Eric does Analysis in Boston and Engineering in Chicago, and Fred does
Analysis in Chicago and Managing in New York: is that okay? Or does all the
Engineering happen in Chicago; or does Eric have to do everything in
Boston?

The same applies to Comments: are you commenting on George, or on
Cementing, or on how good George's cementing is?

Once you understand these things, then -- and only then -- you will
understand your relationships.

HTH


Tim F

 

[Tom]

Tim:

Thanks for providing me feedback...

I understand that I have been probably a bit vague. That
wasn't on purpose; however, I'm still a bit uncertain
about the process myself.

I need to come up w/ a flexible design that allows to
track correspondence. I know that this cannot
necessarily be described within only a few sentences.

However, as mentioned in the previous message, here's the
general idea of what needs to be achieved:

1. Track CORRESPONDENCE (incoming and outgoing)
2. CORRESPONDENCE has multiple "CLASSIFICATIONS"
(important, asap, etc.)
3. CORRESPONDENCE will be distributed into one or
multiple LOCATION (departments)
4. via JUNCTION table), CORRESPONDENCE is
linked to multiple ACTION OFFICERS (employees)
5. ACTION OFFICERS will taken appropriate actions and
write multiple COMMENTS per CORRESPONDENCE case

I may need to be able to create more "Left Outer Join"
relationships that allow for using an FK to point
to "something else" (i.e. correspondence may uses
multiple "references" for correspondence case).

However, currently when I tie in a one-to-may
relationship into the many-to-many relationship (the
junction table), the query does not give me any results
any more (it did before joining the i.e. comments table).

I'm not sure where to go from here but I'm a bit "under
the gun" and don't know really how to overcome this
relationship challenge.

Again, I think I need to use the junction table but so
far I have not been able to join another one-to-many
table without "ruining" the query (rather than seeing
a "blank record" I don't get too see anything (only the
gray field names after executing the query).

Hopefully this has helped to fill in some gaps. Please
be so kind and enlighten me further (as best as you can)
so that I can get this to work.

Thanks!!!

Tom

 

[Tim Ferguson]

Sorry: I have broken up your post a bit in order to answer it in some
order:

I may need to be able to create more "Left Outer Join"
relationships that allow for using an FK to point
to "something else"

The thing that troubles me a lot about this thread is the mixing up of
things and methods. "Left outer join" is a method we can come to later: at
the moment I am still trying to understand how the entities themselves
interact.

1. Track CORRESPONDENCE (incoming and outgoing)
2. CORRESPONDENCE has multiple "CLASSIFICATIONS"
(important, asap, etc.)
3. CORRESPONDENCE will be distributed into one or
multiple LOCATION (departments)
4. via JUNCTION table), CORRESPONDENCE is
linked to multiple ACTION OFFICERS (employees)
5. ACTION OFFICERS will taken appropriate actions and
write multiple COMMENTS per CORRESPONDENCE case

Some of this is fairly straightforward: you have mentioned entities like
Correspondence, Classifications, Locations, Officers, and Comments. Right
in the last line, however, you refer to a Case -- so this sounds like
another entity. So at this stage, you have to go back through all the
questions I suggested the other day: does a Case have one Officer, and does
the Location depend on the Officer or the Case or the particular item of
Correspondence? Do the Officers for each item of Correspondence have to be
the same for all items belonging to the same Case?

Don't worry if this sounds circular: the design process is usually
iterative. It is normal to get nearly there, spot the missing piece and go
all the way round at least a couple of times, and many more so for a
sophisticated model.

Best of luck!


Tim F