Need help with Tables Design and Relationships

Yes, it is a valid process, but I would start much simpler first. Just query
a couple of tables first. Then, when you are sure it is giving you the right
data, add another, etc. That will, hopefully, lead you to realize where the
connections are incorrect.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I have been doing this for testing purposes. I was under the impression
that running such "testing query" would determine whether or the
relationship between all tables and fields are joined properly.

The way you sound, this is not a valid process for checking the
relationships, right?

--
Thanks,
Tom


"Lynn Trapp" wrote in message
...
Tom,
Why are you trying to select EVERY field in ALL 6 tables????

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

Again, thanks for the feedback. I made the changes accordingly.
All
relationships are joined properly (I believe) in the relationship
view.

In query view, however, when I select every field of all 6 tables and
then
execute the query, "nothing" shows up.

What I mean by "nothing" is that I don't even see a blank record. I
simply
see the gray field labels. In the past, this normally was an
indication
for me that something was not properly joined.

Assuming that I have created the relationships as you suggested,
should
I
expect to see "nothing" in the query that pulls all fields from all
tables.
I hope my terminology makes sense?

--
Thanks,
Tom


"Lynn Trapp" wrote in message
...
Tom,
I think I may have led you astray with my terminology. You dont'
want
the
AssigneeID in Both of those tables. Rather, you need to lookup the
MemberID
from the Employees table and store it in the AssigneeID of the
BoardTaskAssignments table. Look at the modified layout below

BoardTaskAssignmentID PK
BoardTaskID FK (to tblBoardTasks)
AssigneeID (do a lookup of EmployeeID from tblEmployees)
..other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
....Other Employee related fields


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

Again, thanks for the feedback. Please bear with me on this.

Okay, I made some small progress but I'm still uncertain about the
relationship between
tblEmployees and tblBoardTaskAssignments.

tblEmployees:
Pk: MemberID (Text data type); 1:M to MemberIDfk of
tblBoardMembers
(that's
okay right now)
AssigneeID (Text data type)

tblBoardTaskAssignments:
Pk: BoardTaskAssignmentsID
AssigneeID (Text data type)


My Questions:
1. Right now, when creating the join between
tblEmployees.AssigneeID
&
tblBoardTaskAssignments.AssigneeID,
I get an "Indeterminate Relationship" error? What am I doing
wrong?
Does
either of the AssigneeID needs
to become a composite PK?

2. When you mentioned that Skills set should be a "subset" of the
Employee
records, did you mean that "Skill Set"
will be a field of the tblEmployees or did you mean that
tblSkillSet
is
a
child table of tblEmployees?

3. Just in Q2, will priorities & budget categories become fields
of
tblProjects or will they become tables themselves?



BTW, I appreciate your mentioning to provide me an ER diagram? I
believe
that really would help me better understand
your design.


Again, I truly appreciate your help in this.


Thanks in advance,
Tom



"Lynn Trapp" wrote in message
...
Answers inline below

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

I had an intial look at this... I followed your
recommendations
and
developed the tables as suggested.

Here are now some additional questions:

tblBoards:
1. Is tblBoards a "junction table" for tblBoardMembers &
tblBoardTasks

No, tblBoardMembers is a "junction table" between tblBoards and
tblEmployees

2. If yes, should BoardIDPK become an "Autonumber"

It can be or you can use some natural key instead

3. If yes, do you then recommend to add 2 additional Number
fields
(Long
Integers) and make all 3 of them composite PKs? Otherwise,
can
I
link
two
foreign keys (BoardMemberID and BoardTaskID) to one and the
same
Autonumber
(BoardIDPK)?

I don't think you have quite understood what I'm after here. I
will
try
to
take the time to draw out a better ER diagram and get it to you
next
week.


tblEmployees:
1. Is this a "stand-alone" table that is used as a source for
MemberIDfk
and
AssigneeIDfk (both of the FKs are then Text fields, right?)

Yes


Other tables:
1. How do I integrate a) tblProjects, b) tblSkillSets, c)
tblPriorities,
and
d) tblBudgetCategories into the existing schema?

I would see Skill sets as a subset of the Employee records and
Priorities
and BudgetCategories as subsets of Projects

2. I believe between all of these, there could be a M:M
relationship?


I appreciate any additional help on this. And (please) keep
in
mind
that
I'm just learning more about the relational design, so I may
ask
some
beginner level questions.

Thanks so much in advance, Lynn.


Tom












"Lynn Trapp" wrote in
message
...
You're quite welcome, Tom. I hope it helps out. I will be
sure
to
monitor
this thread.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

Thanks for the prompt reply and help in this matter.

I will attempt to design the architecture over the course
of
the
weekend.
If I get stuck on something, I will post another message
in
this
thread...
in hope that you might check it again.

Thanks so much!!! I truly appreciate your feedback.

Tom



"Lynn Trapp" wrote in
message
...
Here is ONE way to design this. I have only listed the
tables
necessary
for
the Boards. You would need to extend the same thing for
Projects.
Keep
in
mind that this approach assumes that Boards and Projects
are
totally
separate. If Boards can have projects then you would
have
to
go
a
different
route. Also, while you way there is only one
organization,
this
design
allows for multiples, if the need ever arises. Please
feel
free
to
post
back
with further questions.

tblOrganization

Organization_id PK

..Other organization fields



tblBoard

BoardID PK

OrganizationID FK (to tblOrganization)

.Other Board Fields



tblBoardMembers

BoardMemberID PK

BoardID FK (to tblBoard)

MemberID (do a lookup from tblEmployees)

.Other BoardMember related fields



tblBoardTasks

BoardTaskID PK

BoardID FK (to tblBoard)

.other BoardTask related fields



tblBoardTaskAssignments

BoardTaskAssignmentID PK

BoardTaskID FK (to tblBoardTasks)

AssigneeID (do a lookup from tblEmployees)

..other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
....Other Employee related fields

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I need to come up with a design for the tables listed
below.
Although
I
provided some general information about the
relationships
as well, I am not too sure as to how the actual table
joins
should
be
designed.

I truly reply on someone's expertise here (reading
between
the
lines)
and
hope to get some ideas how the table
design might work.


Here are the tables:

- Organization
- Boards
- Employees
- Tasks
- Priorities
- Projects
- Budget Categories
- Skill Sets

Again, I don't have all the exact information myself
as
to
how
the
tables
should be joined. Maybe, there's even a chance that
I
don't
need
all
the
tables listed above.

But, let me try to provide some more information:

Organization:
1. There is 1 organization
2. The organization has multiple boards
3. The organization has multiple projects
4. There are multiple tasks that either result from
the
boards
or
the
projects
5. Naturally, there are multiple members in the
organization
that
can
be
assigned to a) boards, b) projects ) boards and
projects

Boards:
1. Multiple boards will have multiple members
2. As a result of board meetings, there will be
multiple
tasks
3. These tasks will have different priorities
4. The tasks will be subsidized via different funds
(budget
categories)

Employees:
1. Multiple employees can be assigned to multiple
boards
2. Multiple employees can be assigned to multiple
projects
3. All employees have different skills sets (multiple
skills
sets)
4. The employees will be assigned to different tasks


Budget Categories, Skill Sets have been listed in the
description
above.
Not sure how else they could be further described
here.


Again, if I could get some ideas as to how best
construct
this,
I
would
really appreciate it!!! Any ideas would really help
me
in
my
job.
THANKS!

Please don't hesitate to post additional questions if
I
didn't
provide
sufficient information.


Thanks so much,

Tom

Thanks, I'll proceed with step-by-step validation.

--
Thanks,
Tom


"Lynn Trapp" wrote in message
...
Yes, it is a valid process, but I would start much simpler first. Just
query
a couple of tables first. Then, when you are sure it is giving you the
right
data, add another, etc. That will, hopefully, lead you to realize where
the
connections are incorrect.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I have been doing this for testing purposes. I was under the
impression
that running such "testing query" would determine whether or the
relationship between all tables and fields are joined properly.

The way you sound, this is not a valid process for checking the
relationships, right?

--
Thanks,
Tom


"Lynn Trapp" wrote in message
...
Tom,
Why are you trying to select EVERY field in ALL 6 tables????

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

Again, thanks for the feedback. I made the changes accordingly.
All
relationships are joined properly (I believe) in the relationship
view.

In query view, however, when I select every field of all 6 tables
and
then
execute the query, "nothing" shows up.

What I mean by "nothing" is that I don't even see a blank record.
I
simply
see the gray field labels. In the past, this normally was an
indication
for me that something was not properly joined.

Assuming that I have created the relationships as you suggested,
should
I
expect to see "nothing" in the query that pulls all fields from all
tables.
I hope my terminology makes sense?

--
Thanks,
Tom


"Lynn Trapp" wrote in message
...
Tom,
I think I may have led you astray with my terminology. You dont'
want
the
AssigneeID in Both of those tables. Rather, you need to lookup the
MemberID
from the Employees table and store it in the AssigneeID of the
BoardTaskAssignments table. Look at the modified layout below

BoardTaskAssignmentID PK
BoardTaskID FK (to tblBoardTasks)
AssigneeID (do a lookup of EmployeeID from tblEmployees)
..other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
....Other Employee related fields


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

Again, thanks for the feedback. Please bear with me on this.

Okay, I made some small progress but I'm still uncertain about
the
relationship between
tblEmployees and tblBoardTaskAssignments.

tblEmployees:
Pk: MemberID (Text data type); 1:M to MemberIDfk of
tblBoardMembers
(that's
okay right now)
AssigneeID (Text data type)

tblBoardTaskAssignments:
Pk: BoardTaskAssignmentsID
AssigneeID (Text data type)


My Questions:
1. Right now, when creating the join between
tblEmployees.AssigneeID
&
tblBoardTaskAssignments.AssigneeID,
I get an "Indeterminate Relationship" error? What am I doing
wrong?
Does
either of the AssigneeID needs
to become a composite PK?

2. When you mentioned that Skills set should be a "subset" of
the
Employee
records, did you mean that "Skill Set"
will be a field of the tblEmployees or did you mean that
tblSkillSet
is
a
child table of tblEmployees?

3. Just in Q2, will priorities & budget categories become fields
of
tblProjects or will they become tables themselves?



BTW, I appreciate your mentioning to provide me an ER diagram?
I
believe
that really would help me better understand
your design.


Again, I truly appreciate your help in this.


Thanks in advance,
Tom



"Lynn Trapp" wrote in
message
...
Answers inline below

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

I had an intial look at this... I followed your
recommendations
and
developed the tables as suggested.

Here are now some additional questions:

tblBoards:
1. Is tblBoards a "junction table" for tblBoardMembers &
tblBoardTasks

No, tblBoardMembers is a "junction table" between tblBoards
and
tblEmployees

2. If yes, should BoardIDPK become an "Autonumber"

It can be or you can use some natural key instead

3. If yes, do you then recommend to add 2 additional Number
fields
(Long
Integers) and make all 3 of them composite PKs? Otherwise,
can
I
link
two
foreign keys (BoardMemberID and BoardTaskID) to one and the
same
Autonumber
(BoardIDPK)?

I don't think you have quite understood what I'm after here. I
will
try
to
take the time to draw out a better ER diagram and get it to
you
next
week.


tblEmployees:
1. Is this a "stand-alone" table that is used as a source
for
MemberIDfk
and
AssigneeIDfk (both of the FKs are then Text fields, right?)

Yes


Other tables:
1. How do I integrate a) tblProjects, b) tblSkillSets, c)
tblPriorities,
and
d) tblBudgetCategories into the existing schema?

I would see Skill sets as a subset of the Employee records and
Priorities
and BudgetCategories as subsets of Projects

2. I believe between all of these, there could be a M:M
relationship?


I appreciate any additional help on this. And (please) keep
in
mind
that
I'm just learning more about the relational design, so I may
ask
some
beginner level questions.

Thanks so much in advance, Lynn.


Tom












"Lynn Trapp" wrote in
message
...
You're quite welcome, Tom. I hope it helps out. I will be
sure
to
monitor
this thread.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
Lynn:

Thanks for the prompt reply and help in this matter.

I will attempt to design the architecture over the
course
of
the
weekend.
If I get stuck on something, I will post another message
in
this
thread...
in hope that you might check it again.

Thanks so much!!! I truly appreciate your feedback.

Tom



"Lynn Trapp" wrote
in
message
...
Here is ONE way to design this. I have only listed the
tables
necessary
for
the Boards. You would need to extend the same thing
for
Projects.
Keep
in
mind that this approach assumes that Boards and
Projects
are
totally
separate. If Boards can have projects then you would
have
to
go
a
different
route. Also, while you way there is only one
organization,
this
design
allows for multiples, if the need ever arises. Please
feel
free
to
post
back
with further questions.

tblOrganization

Organization_id PK

..Other organization fields



tblBoard

BoardID PK

OrganizationID FK (to tblOrganization)

.Other Board Fields



tblBoardMembers

BoardMemberID PK

BoardID FK (to tblBoard)

MemberID (do a lookup from tblEmployees)

.Other BoardMember related fields



tblBoardTasks

BoardTaskID PK

BoardID FK (to tblBoard)

.other BoardTask related fields



tblBoardTaskAssignments

BoardTaskAssignmentID PK

BoardTaskID FK (to tblBoardTasks)

AssigneeID (do a lookup from tblEmployees)

..other BoardTaskAssignment related fields

tblEmployees
EmployeeID PK
....Other Employee related fields

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security:
www.ltcomputerdesigns.com/Security.htm


"Tom" wrote in message
...
I need to come up with a design for the tables
listed
below.
Although
I
provided some general information about the
relationships
as well, I am not too sure as to how the actual
table
joins
should
be
designed.

I truly reply on someone's expertise here (reading
between
the
lines)
and
hope to get some ideas how the table
design might work.


Here are the tables:

- Organization
- Boards
- Employees
- Tasks
- Priorities
- Projects
- Budget Categories
- Skill Sets

Again, I don't have all the exact information myself
as
to
how
the
tables
should be joined. Maybe, there's even a chance
that
I
don't
need
all
the
tables listed above.

But, let me try to provide some more information:

Organization:
1. There is 1 organization
2. The organization has multiple boards
3. The organization has multiple projects
4. There are multiple tasks that either result from
the
boards
or
the
projects
5. Naturally, there are multiple members in the
organization
that
can
be
assigned to a) boards, b) projects ) boards and
projects

Boards:
1. Multiple boards will have multiple members
2. As a result of board meetings, there will be
multiple
tasks
3. These tasks will have different priorities
4. The tasks will be subsidized via different funds
(budget
categories)

Employees:
1. Multiple employees can be assigned to multiple
boards
2. Multiple employees can be assigned to multiple
projects
3. All employees have different skills sets
(multiple
skills
sets)
4. The employees will be assigned to different tasks


Budget Categories, Skill Sets have been listed in
the
description
above.
Not sure how else they could be further described
here.


Again, if I could get some ideas as to how best
construct
this,
I
would
really appreciate it!!! Any ideas would really
help
me
in
my
job.
THANKS!

Please don't hesitate to post additional questions
if
I
didn't
provide
sufficient information.


Thanks so much,

Tom

----- Lynn Trapp wrote: -----
snip
BoardTaskAssignmentID PK
BoardTaskID FK (to tblBoardTasks)
AssigneeID (do a lookup of EmployeeID from tblEmployees)
...other BoardTaskAssignment related fields

Hi Lynn,

Just for clarification - In the above table description did you mean to use a combo box on a form with a recordsource of the employee table, or make the AssigneeID field a lookup field in the BoardTaskAssignment table?

thanks

rpw,

I ALWAYS do lookups from a combobox on a form. I NEVER, NEVER, NEVEr use a
lookup field at the table level. Some say there are some situations where it
might be good -- like initial development -- but I don't agree and don't do
it.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"rpw" wrote in message
...
----- Lynn Trapp wrote: -----
snip
BoardTaskAssignmentID PK
BoardTaskID FK (to tblBoardTasks)
AssigneeID (do a lookup of EmployeeID from tblEmployees)
...other BoardTaskAssignment related fields

Hi Lynn,

Just for clarification - In the above table description did you mean to
use a combo box on a form with a recordsource of the employee table, or make
the AssigneeID field a lookup field in the BoardTaskAssignment table?

thanks

Hi Lynn,

I sorta figured that, but by mentioning "lookup" in the table description I wasn't certain and I figured that other newbies that might be following this thread might not understand your true meaning.

thanks again,

rpw

----- Lynn Trapp wrote: -----

rpw,

I ALWAYS do lookups from a combobox on a form. I NEVER, NEVER, NEVEr use a
lookup field at the table level. Some say there are some situations where it
might be good -- like initial development -- but I don't agree and don't do
it.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"rpw" wrote in message
...
----- Lynn Trapp wrote: -----
snip BoardTaskAssignmentID PK
BoardTaskID FK (to tblBoardTasks)
AssigneeID (do a lookup of EmployeeID from tblEmployees)
...other BoardTaskAssignment related fields
Hi Lynn,
Just for clarification - In the above table description did you mean to
use a combo box on a form with a recordsource of the employee table, or make
the AssigneeID field a lookup field in the BoardTaskAssignment table?
thanks