What a wonderfully gracious reply. Thank you for your kind words.

I know you said you are going to work on this for a few days, but here are a
couple of thoughts anyhow. For the phone table you may want to add a
time-of-day field. I gather that updating such information is a once per
year chore, which would be a simpler task than trying to tie in the phone
numbers with a schedule, particularly since you aren't using the dtabase for
scheduling.

A sort of standard example of a one-to-one relationship is a company
softball team. A SoftballTeam table may contain information that is not in
the main Employee record, so it is set apart in its own table. An employee
would only be in the SoftballTeam table once, and likewise an employee on
the softball team will have just one corresponding record in the main
Employee table.


"Aria" wrote in message
...
First, let me say,"WOW"! You both have given me *so much* information.
Thank
you! My comments are below (I'm combining posts. I didn't know how else to
do
this):
Bruce M:
You could have a Titles table and an Employees table, with a EmployeeTitle
junction table. That may make some sense if several employees have the
same
title, but if a lot of titles are unique that approach could become
awkward.
My thinking is that there would be a main form based on tblEmployees, with
a
subform based on the junction table.

Aria:
Do I need a subform if the majority of the staff only hold one title? Yes,
I
still trying to get over having empty fields but I haven't made it this
far
so I don't know. The form I envision would have this info on the main form
using a combobox for the titles. I am trying to think of every possible
title
that I can. I don't want any suprises here.

Bruce M:

If you would like to do something such as generate reports that list
employees by
title, the junction table is probably the best way to go (or the most
flexible, at least).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do the
junction table.

Bruce M:
You may do well to start a new thread on this specific topic. I'm not sure
I have the experience to advise you confidently that one choice is better
than another in a particular situation.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I
know
I need to learn how to figure out some of these issues myself. I have to
try
first. If I continue to have problems, I will of course follow your advice
(you and Beetle).

Bruce M:
The thing is that this database is about keys, but once you have an
Employee
table you will probably use it again and again (as a linked table in other
projects), so it is well to
design it as carefully as can be done.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the
hard
work at the beginning so that it will seem effortless in the end. Well,
here
it is. I am willing to put in the effort but some days I am frustrated
beyond
belief.

I had to add a phone table because there are a possiblity of 3 phone #s
that
a staff member may have aside from the room phone#, which I moved to
tblLocations per our previous discussion. I think this is a 1:M
relationship.

Bruce M:
One employee: several phones is one-to-many, but if a phone could have
several people answering it, depending on the time of day, you may need a
jPhonePerson unction table. StartTime and EndTime may be fields in the
junction table for the room phones. Ideally this would be linked to
scheduling, so that a class schedule would indicate when an employee could
be reached at a particular room phone (or something like that), but that
could become quite complex.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone could
have several people answer it. This will depend on who is assigned to the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a room
assignment list from the Principal soon. We'll see....

Aria:
We also discussed department affiliation but I can't recall an instance
where staff will belong to more than one department.

Bruce M:
If so, you could just store the department in tblEmployees. However, be
sure of this, as it is moredifficult to rearrange the database later.

Aria:
Ugh! Nooooooo...I was so sure! I checked over and over and over again and
last night I finally found it...one person, two departments. Will this
never
end?!


"Beetle" wrote:

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by
resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted
his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of
information there. I had to really pay attention though and relate it to
my
situation. After reading it, I'm not as frustrated. Yes, there are 1:1
relationships here but it may be what I need. I do think that I have
sub-types. Let me work with this some more before I let you both know
which
1:1 relationships I have.

A sub-type can of course have sub-types of its own; Chris Date in one of
his
books gives the example of a type Employees with sub-type programmers,
and
sub-types of programmers, System programmers and Application programmers.

Great...I think I have at least one situation where this is true.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an
autonumber
PersonID column in the People table, but the primary key of Pastors or
other
sub-type of people must be a straightforward long integer number data
type,
not an autonumber.
To take an extreme example, in a personnel database you would not have an
Employees table and a separate table for every Job Title in the
organization; you'd be more likely to have a Positions table and an
EmployeeHistory table with EmployeeID, PositionID, DateFrom and
DateTo columns to model the many-to-many relationship between Employees
and positions.

Hey, perfect example!

Hopefully this will help you. If you still have questions, as Bruce said
it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread.

It does help. Thank you for that. I want to continue trying before I throw
in the towel and ask for help.

Comments for both of you:
1. You two are *great* teachers who always give me homework! There is such
a
wealth of information, instructions and suggestions that you put into your
replies that I can't post back right away; I'm studying, highlighting and
taking notes!

2. I've learned SO much from you (the collective you). I consider myself
incredibly fortunate that you responded to my post.

3. I have varying degrees of frustration and confusion. I'm confused by
some
of my own table names (yeah, I know that doesn't even begin to make sense
to
you. I'll work on more logical naming).
I'm also confusing myself by thinking to hard and long about some of these
issues. I know the answer is right in front of me but I can't see it.

4. Like I said before, you both pan wide and then zoom in, incrementally,
to
get to the crux of a problem. I want to do that. This is starting to come
into focus a little more now. I just have to keep reminding myself that
problems I encounter now, will be be something I won't have to deal with
later after everything is set. It's frustrating but I'm trying to keep it
in
perspective.

5. See #3. I'm going to have to take a step back. I have been working on
this day and night (in between my day job). I have been thinking about
database design in my sleep (I know, I've got it bad).
6. I have taken up so much of your time already. You have moved me forward
by leaps and bounds. I understand that there are others who need help too.
Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have
that
fear when I posted). You have no idea how dejected I was when I first
posted,
but every question answered unraveled the knot just a little bit more. I'm
going to take a couple of days and see if I can work out this employee
table.
When I return, I would like to get back to tblLocations, tblKeys, etc. I
hope
you will still be here, but if not, I completely understand.

I just wanted you to know how grateful I am. I'm sure this is more than
you
*ever* wanted to know about locks, keys and school "issues".

--
Aria W.


"Beetle" wrote:

The Employee section of the db seems to be expanding. I'm still
struggling
to work it out. As far as I can see, there seems to be some 1:1
relationships. As it stands now, there are at least 3 junction tables.
One
thing is clear though, no matter how I work this, there will be empty
fields
in some of the tables. I guess I *am* going to have to live with it.

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by
resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted
his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

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

When it comes to people connected to the church in some way or other
things
get a little more complex, but not frighteningly so, as the principles
involved are quite simple. All people share certain attribute types of
course; we all have names, an address, a date of birth and so on. So
there
is an entity type People with these common attribute types, and a table
can
represent this attribute type. People with different roles may well have
attribute types which are specific to their role, e.g. a pastor is likely
to
have attribute types which a member of his congregation would not have.
If I
can use an example from the C of E an attribute type for a C of E priest
might be Date of Ordination. So the entity type Pastors is a sub-type of
the
entity type People. The way a sub-type is modelled in a relational data
base
is by means of a one-to-one relationship. In the case of People and its
sub-type Pastors this would mean that there would be a table people with
a
numeric primary key PersonID (don't use names as a primary key, they can
be
duplicated), and a Pastors table also with a numeric primary key PersonID
(you can call it PastorID if you wish, but I prefer to keep the column
names
the same). In the case of the Pastors table PersonID would also be a
foreign
key referencing the primary key of People. The people table would have
columns for the common attributes like names and address etc., the
Pastors
table would have columns only for those attribute type specific to the
Pastors entity type, e.g. Date of Ordination, but not the common
attributes
like names and address.

A sub-type can of course have sub-types of its own; Chris Date in one of
his
books gives the example of a type Employees with sub-type programmers,
and
sub-types of programmers, System programmers and Application programmers.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an
autonumber
PersonID column in the People table, but the primary key of Pastors or
other
sub-type of people must be a straightforward long integer number data
type,
not an autonumber.

When it comes to events connected with a church you need to think
carefully
about how these fit into the model. One thing you need to be careful of
is
deciding whether an event type is a sub-type of another type, or whether
the
type of an event is an attribute type of a single entity type. You might
have a topmost Events entity type with attribute type such as Event Date,
Location etc and a Baptisms sub-type with attribute types such as
ChildID,
FatherID, MotherID, all three referencing the People table. Another way
of
modelling it would be to have a single Events table with an attribute
type
EventType, in which a value 'Baptism' would be entered. The problem with
the
latter approach of course is that if you also have ChildID, FatherID,
MotherID columns these won't be appropriate to other types of events in
the
same table. In this case therefore a type/sub-type model is probably the
most appropriate. In the case of other entity types, however, the
second
approach might be more suitable. To take an extreme example, in a
personnel
database you would not have an Employees table and a separate table for
every
Job Title in the organization; you'd be more likely to have a Positions
table
and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and
DateTo
columns to model the many-to-many relationship between Employees and
positions.

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

Hopefully this will help you. If you still have questions, as Bruce said
it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread. If you need to repost, it may be helpful if
you include some more detailed information about your Employees table
such as;

1) What different types of employees you need to track

2) Which attributes are common to *all* employees

3) Which attributes are specific to only certain employees.

--
_________

Sean Bailey


"Aria" wrote:

I'd like to address this part of your post 1st, Bruce:

It would probably be best to keep a single thread going. Beetle and I
may
end up saying the same thing, or slightly different things about the
same
topic, in the two branches of this thread. Since there is more
information,
including discussions of master keys and other matters, in the other
thread,
let's keep the discussion there.

...as you wish. I confess my ignorance here; all of this is new to me
(Access and posting). Before I was referred here by someone in my
district, I
had never heard of a newsgroup/discussion group before. I had never
posted
anywhere at anytime. I didn't realize I was creating additional
threads. I
think I may have messed up this thread too. I'm just not sure how all
of this
is supposed to work. I read the Getting Started section and read a lot
of
posts before launching my own but... My apologies for any confusion I
may
have caused.

Your tblLocks is heading in the right direction. Let me suggest
LocationID
rather than RoomID (in case the lock is to a building or a stadium or
something), and tblLocations instead of tblRooms. You could have a
field in
tblLocations to identify the Location type (Room, Closet, etc.). Wing,
Campus, etc. are attributes of the Location. If you have linked to
tblLocations, all of the information in tblLocations is available
(including
Wing, etc.). No need to store such items in the Locks table.

I think your suggestion is perfect and rectifies the limitations
encountered
with tblRooms. I have made the changes and am working on incorporating
this
into the db.

Update-
Upon further reflection, I have scrapped tblSubs. No one cared for that
table except me.(smile) I thought it made sense, but I don't have any
experience in this area so I defer to your judgement and suggestions.
I am continuing to have problems with tblEmployees. It seemed so
straightforward in the beginning but now...additional issues keep
popping up.
Before yesterday, I would have said that staff can only hold a single
title.
I have thought of several situations where this is not true. If I have
this
right, it would be a many-to-many relationship and requires a junction
table.
Let me know if I've stepped off the cliff here.
I had to add a phone table because there are a possiblity of 3 phone #s
that
a staff member may have aside from the room phone#, which I moved to
tblLocations per our previous discussion. I think this is a 1:M
relationship.
We also discussed department affiliation but I can't recall an instance
where staff will belong to more than one department.
The Employee section of the db seems to be expanding. I'm still
struggling
to work it out. As far as I can see, there seems to be some 1:1
relationships. As it stands now, there are at least 3 junction tables.
One
thing is clear though, no matter how I work this, there will be empty
fields
in some of the tables. I guess I *am* going to have to live with it.

--
Aria W.


"Beetle" wrote:

Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)?

Yes tblKeysEmployees because that is the table that is used to track
which
employee has which key at a given time. If you put KeyID in tblSubs
to
assign keys to subs, then you have the same problem you had with
tblEmployees. A sub will use more than one key, so you have to add
additional records for the same sub.

So you have to add another field in tblKeysEmployees as a foreign key
to tblSubs. You can't use the existing EmployeeID FK because subs
wouldn't exist in that table. So now you have at least one empty
field
for *every* record in tblKeysEmployees (either EmployeeID or SubID
would be an empty field). That's what I mean when I say you end up
with empty fields either way.

However, there is a way to solve both problems, which I should have
mentioned in my last post, but it involves - yes, that's right -
ANOTHER TABLE...AAAAARRRRGGGGHHH! g

You reduce tblEmployees to only those fields that apply to *all*
personell
(full time and subs), then you add another table for the data that
applies to only *some* employees, and relate it back via EmployeeID.

Serenity Now!g
--
_________

Sean Bailey


"Aria" wrote:

I have read and reread your comments and suggestions. I am
commenting on the
following:

Does school data need an additional table based solely on
instuctors
teaching more than one subject? You are correct though.
Teachers can teach
more than one subject. I only ask because you said,
"additionally", which
implies that this will need a new table regardless. Other than
"Subject", I
don't understand how I would be entering the same information
again and
again. What am I missing?

In my opinion it should be in separate tables regardless. I don't
know
how Classification and Title relate to your employees, so for now
I'll
use Dept. Name and Subject as examples. As your table is
currently
designed, each time you enter an employee record the user will
have
to manually type in the Dept. Name and Subject. This is not only
extra
work, it also invites spelling errors and invalid data in your
table.

...and the light goes on. Of course! How could I have missed
*that*?!

Which tables should DeptID/SubjectID go in? Good question. I
think this is a
very important part of your application that you have not
completely
defined yet. In a relational database like Access, it is not only
imerative
that
you determine what relationships you have, but what *type* each
relationship
is.

I follow you. That was a great explanation. I understand and will
be
thinking about this further.

A Campus would usually encompass more than one building, a
building
may have more than one wing, and a wing may have more than one
room.
So, again, if we break it down to LCD (so to speak) a room is
only indirectly
related to a Campus, it's direct relationship would the wing in
which it is
located (or perhaps the building if that building has no
"wings"). However,
as Bruce correctly pointed out, what we are really talking about
is locks,
so it should probably be broken down to that level.

Again, I follow what you're saying and having had time to think
about all of
the information that both you and Bruce pointed out, I agree. He is
correct.
He also threw in a monkey wrench. I did not want to hear about a
Locks table
when I was having trouble with the tables I already have. But he
posted
something in his reply that just knocked the wind out of me, but it
opened my
eyes to see what he was saying.

I hadn't thought about Master keys. You may want to consider a
separate table
for those, since they have a different type of relationship with
the locks
than
the regular keys.
MasterKeys
MasterID
EmpID
KeyID (FK to tblKeys)?

I don't understand. If I add the substitute staff to the our
employee table,
most of the fields will be empty.
OK, I can see where that is a little tricky. Still, I think I
would just
list them
in the employee table and live with a few empty address fields.
If you have
them in a separate table, then you're going to have to add
another FK
field to your tblKeysEmployees, so either way you end up with
empty fields
in one of your tables.

Huh? tblKeysEmployees? Not KeyID (FK to tblKeys)? You know I don't
know what
it is about this table that makes me want to keep it. I respect
your opinion
and will probably rue the day that I didn't follow your advice;
especially if
I have to post back and have someone extricate me from my own dumb
mistake
(be kind if that happens!). After all, I came here for advice. I
think it's
all of the empty fields that I'm going to have. They bother me.
They won't
need the inactive field or the "Subject", "Address", or all of the
Emergency
Contact info (Hospital, Emergency Contact, Medications, Allergies,
etc.) I'm
still thinking about this one.
Again, my sincerest thanks for your help. This has been
eye-opening. Both
you and Bruce have shown me how to think about some of these
problems in a
different way. You both pan wide and then zoom in on the tiny
details that
are easily overlooked by novices. It's easy to answer the cut and
dry
questions. But someone who takes on a rock bottom beginner and
walks you step
by step through the process...well, that's extra special.
--
Aria W.


"Beetle" wrote:

Does school data need an additional table based solely on
instuctors
teaching more than one subject? You are correct though.
Teachers can teach
more than one subject. I only ask because you said,
"additionally", which
implies that this will need a new table regardless. Other than
"Subject", I
don't understand how I would be entering the same information
again and
again. What am I missing?

In my opinion it should be in separate tables regardless. I don't
know
how Classification and Title relate to your employees, so for now
I'll
use Dept. Name and Subject as examples. As your table is
currently
designed, each time you enter an employee record the user will
have
to manually type in the Dept. Name and Subject. This is not only
extra
work, it also invites spelling errors and invalid data in your
table. The
correct way is to have separate "lookup" tables that store all
possible
Departments and Subjects. These would be very simple tables that
would
likely just have a few fields like;

DeptID
DeptName

SubjectID
SubjectName

Then you would use DeptID and SubjectID as foreign keys in other
tables.
In your data entry forms, you would typically use combo boxes to
allow
users to select the correct Dept./Subject from the list of
choices.

Which tables should DeptID/SubjectID go in? Good question. I
think this is a
very important part of your application that you have not
completely
defined yet. In a relational database like Access, it is not only
imerative
that
you determine what relationships you have, but what *type* each
relationship
is. Is it One-to-Many? Is it Many-to-Many? If it is 1:m, then the
PK field
from the "One" side table goes in the "Many" side table as a
foreign key. If
it is m:m, then you need a third (junction) table to define the
relationship,
like your
tblKeysEmployees. You said a teacher can teach more than one
subject,