Need help with Tables Design and Relationships

(the sound of foot being extracted - deep breath - "aaaaah")

Thank you for that, Lynn. :-)

(note to self - learned a little more)

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

rpw,
I wouldn't consider your approach to have "errors". There are always
multiple ways to solve the same problem. I was also confused as to whether
or not Projects might be related to a Board or not. You and I just made
different assumptions. That is why it's important for developers to have
ongoing discussions with the end users.

You can pull your foot out of your mouth...it didn't need to be inserted to
start with. g

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


"rpw" wrote in message
...
Hi Lynn,
Thanks for jumping in here and diplomatically fixing my errors. ;-)
I have learned some more from the experts once again: Tom never said that
Board and Projects were related (my assumption), allow for upgradeability
(more than one Organization), 'listen' closer - Tom wanted table design, not
relationships.
arggh, go stick foot in mouth...
rpw
----- Lynn Trapp wrote: -----
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

snork

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


"rpw" wrote in message
...

(the sound of foot being extracted - deep breath - "aaaaah")

Thank you for that, Lynn. :-)

(note to self - learned a little more)

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

rpw,
I wouldn't consider your approach to have "errors". There are always
multiple ways to solve the same problem. I was also confused as to
whether
or not Projects might be related to a Board or not. You and I just
made
different assumptions. That is why it's important for developers to
have
ongoing discussions with the end users.

You can pull your foot out of your mouth...it didn't need to be
inserted to
start with. g

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


"rpw" wrote in message
...
Hi Lynn,
Thanks for jumping in here and diplomatically fixing my errors.
;-)
I have learned some more from the experts once again: Tom never
said that
Board and Projects were related (my assumption), allow for
upgradeability
(more than one Organization), 'listen' closer - Tom wanted table
design, not
relationships.
arggh, go stick foot in mouth...
rpw
----- Lynn Trapp wrote: -----
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

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

Tom,

Looks like Lynn is going to spend a considerable amount of time over the weekend working on your problem with these tables. Lucky for you!

After reading your question #3, it seems like the concept of 'composite' keys is not quite solidified for you. So I'm going to review some basic information and maybe by the end of it everything will have a little more clarity. (And please don't take offense if it is over-simplified - I don't really know what you do and don't know.)

As you know, every table should have a primary key (PK) and this (PK) could be an 'Autonumber'. Of course, using an 'autonumber' (PK) insures that each record ID is unique. Also, as you have learned, 'autonumbers' are long-integer by default. When you refer to a (PK) from a different table it is typically identified as a foreign key (FK). The field that holds the (FK) must be of the same data type as the (PK) for Access to allow the link between the two tables. This means that your (FK) field must be long-integer if it relates to an 'autonumber' (PK).

Now, when you have a 'junction table' like TC suggested in your earlier thread, you can have a 'composite primary key'. You know that this 'junction table' needs a primary key, but that doesn't necessarily mean that there must be one field designated for that purpose - it could be a combination of fields. On the other hand, just because a table has foreign keys, that doesn't mean that you really need a 'composite PK' made up with thoses (FK)'s.

I'm going to use an unlikey example for illustrative purposes. Imagine three tables; tblNames, tblCity, tblAddress. Now imagine another 'junction table' called tblNCA. This last table might have the following fields:

tblNCA
ncaID (PK)
NameID (FK)
CityID (FK)
AddressID (FK)

With these fields, the table functions very well - there is a unique primary key and the foriegn keys link the information from the other tables to the NCA table.

Now,let's assume that we did not want a single field PK and we are going to combine the (FK) fields into a 'composite PK'. The resultant table might look like this:

tblNCA
NameID (FK) (These 3
CityID (FK) (fields combine to make
AddressID (FK) (the 'composite' PK

This table could also function very well.

I sorta figured that with your line of thinking, you would set up the table this way:

tblNCA
ncaID (Having all
NameID (FK) (four of these fields
CityID (FK) (combined into a
AddressID (FK) (single 'composite PK'

When TC suggested using a combination of foriegn keys as a 'composite primary key', he determined that the combination of two unique ID numbers was unique enough that there would likely never be duplication. In those cases, a separate 'autonumber' field is not necessary. So while this last table is functional, it does have an extra and unnecessary ncaID field.

In your question #3 you asked: "and make all 3 of them composite PKs?". Not quite, there is still only one (PK) even though it is made up of multiple (FK)'s.

I hope this helps you along.

rpw

btw, in all of my 5 months of using Access I still haven't needed or wanted to use 'composite PK's. It's usually easier for me to understand when I use a single field 'autonumber' PK.


----- Tom wrote: -----

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
2. If yes, should BoardIDPK become an "Autonumber"
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)?

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?)

Other tables:
1. How do I integrate a) tblProjects, b) tblSkillSets, c) tblPriorities, and
d) tblBudgetCategories into the existing schema?
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:

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

RPW:

Thanks again! These Newsgroups are simply awesome... I'm learning a lot
here and I always "run into" very helpful individuals, like you or Lynn.

Anyhow, I appreciate your feedback and will review it further. I just
posted an additional thread to Lynn's last reply.

It seems like I'm getting there, although slowly... please bear w/ me....
still learning.

Again, I'll review your feedback (later on tonight or tomorrow morning) and
post my replies to you then. Thanks so much for your help.

--
Thanks,
Tom


"rpw" wrote in message
...
Tom,

Looks like Lynn is going to spend a considerable amount of time over the
weekend working on your problem with these tables. Lucky for you!

After reading your question #3, it seems like the concept of 'composite'
keys is not quite solidified for you. So I'm going to review some basic
information and maybe by the end of it everything will have a little more
clarity. (And please don't take offense if it is over-simplified - I don't
really know what you do and don't know.)

As you know, every table should have a primary key (PK) and this (PK)
could be an 'Autonumber'. Of course, using an 'autonumber' (PK) insures
that each record ID is unique. Also, as you have learned, 'autonumbers' are
long-integer by default. When you refer to a (PK) from a different table it
is typically identified as a foreign key (FK). The field that holds the
(FK) must be of the same data type as the (PK) for Access to allow the link
between the two tables. This means that your (FK) field must be
long-integer if it relates to an 'autonumber' (PK).

Now, when you have a 'junction table' like TC suggested in your earlier
thread, you can have a 'composite primary key'. You know that this
'junction table' needs a primary key, but that doesn't necessarily mean that
there must be one field designated for that purpose - it could be a
combination of fields. On the other hand, just because a table has foreign
keys, that doesn't mean that you really need a 'composite PK' made up with
thoses (FK)'s.

I'm going to use an unlikey example for illustrative purposes. Imagine
three tables; tblNames, tblCity, tblAddress. Now imagine another 'junction
table' called tblNCA. This last table might have the following fields:

tblNCA
ncaID (PK)
NameID (FK)
CityID (FK)
AddressID (FK)

With these fields, the table functions very well - there is a unique
primary key and the foriegn keys link the information from the other tables
to the NCA table.

Now,let's assume that we did not want a single field PK and we are going
to combine the (FK) fields into a 'composite PK'. The resultant table might
look like this:

tblNCA
NameID (FK) (These 3
CityID (FK) (fields combine to make
AddressID (FK) (the 'composite' PK

This table could also function very well.

I sorta figured that with your line of thinking, you would set up the
table this way:

tblNCA
ncaID (Having all
NameID (FK) (four of these fields
CityID (FK) (combined into a
AddressID (FK) (single 'composite PK'

When TC suggested using a combination of foriegn keys as a 'composite
primary key', he determined that the combination of two unique ID numbers
was unique enough that there would likely never be duplication. In those
cases, a separate 'autonumber' field is not necessary. So while this last
table is functional, it does have an extra and unnecessary ncaID field.

In your question #3 you asked: "and make all 3 of them composite PKs?".
Not quite, there is still only one (PK) even though it is made up of
multiple (FK)'s.

I hope this helps you along.

rpw

btw, in all of my 5 months of using Access I still haven't needed or
wanted to use 'composite PK's. It's usually easier for me to understand whe
n I use a single field 'autonumber' PK.


----- Tom wrote: -----

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
2. If yes, should BoardIDPK become an "Autonumber"
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)?

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?)

Other tables:
1. How do I integrate a) tblProjects, b) tblSkillSets, c)
tblPriorities, and
d) tblBudgetCategories into the existing schema?
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

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:

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

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

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