You're doing fine with newsgroup etiquette and all that. I suggested a
single thread only because it may be easier for all involved. More comments
inline.

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

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. The subform would have a combo box
based on tblTitles. However, if a title is not in tblTitles it would need
to be added before it can be selected from the combo box. That process can
be made fairly efficient and simple to perform, but if there is a frequent
need to add a title to tblTitles (because most titles are unique) it could
become tedious. Another approach would be that tblEmployeeTitle not be a
junction table, but rather that it be related one to many from tblEmployees
(one employee: several titles). Base a combo box on tblTitles, and store
the actual title in tblEmployeeTitle. Set Limit To List to No for the combo
box, and type in a title if it is not in tblTitles (or use the Not In List
event to add it to tblTitles). Again, it depends on the situation. 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).
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. 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.

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.

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.

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

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

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.

What 1:1 relationships? There are valid reasons for using these, but they
could also just serve to make things complicated.

--
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,
and, presumably, a subject can be taught by more than one teacher, so
this relationship is m:m. Therefore, you not only need another table
to
define the list of available subjects, but also a junction table to
define
the relationship.

Again, this is wrong. Campus, Wing and RoomType are not
attributes
of the Key. This table should have RoomID as a foreign key to
tblRooms
and probably not much else, unless you store some other
descriptive
information about the key itself, like "color" or something.
Think of it
this way - you said you don't really track filing cabinet keys so
we'll
just take rooms into consideration here. A key opens a room.
Period.
The Room Type and the Wing in which that room is located are
attributes
of the Room, not the Key. Likewise, the Campus that Wing is
located in is
an attribute of the Wing (not the Room or the Key). You should
have separate
tables for Campus' and Wings

Point taken; although I don't understand why the campus location is
not a
room attribute. I need to know where that room or storage room is
located(especially since storage rooms don't have room #s). Does it
make a
difference if there are only 2 campuses? No? So, is it CampusID (FK
to
tblRooms)? Same for tblWings?
Also, I meant to put KeyType (Storage, Classroom, Gate Master,
Grand Master,
etc.) as a field here.

As I stated in my reply to Bruce, I don't think I worded this part of
my reply
very well. I was trying to point out that things like RoomType are
not
attributes
of a key. I actually think bruce is right about this. When you break
things
down
to lowest common denominator, all a key does is open a lock (or many
locks if
it is a master key). Where that lock is located is an attribute of
the lock,
not the key. As far as whether the Campus is an attribute of the
Room? Maybe
it should be, I don't know enough about your app to say for sure. My
train
of thought in my previous reply wasa basically the following;

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.

Good point; I'll change this. Your second sentence is not exactly
true. Just
to clarify, key assignments are based on job title and
extracurricular duties
(coaching, tutoring, special projects, etc.). Masters allow all
access per
location. So one key may open one or many doors.

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.

I don't understand. If I add the substitute staff to the our
employee table,
most of the fields will be empty. They are not required to disclose
their
address or emergency info. There address is on file with the
district. In
addition, they are not bound to teach any subject in particular. If
there is
a request for a P.E. instructor but they normally teach English,
they are
free to accept the position for the time requested. This is why I
am adding a
"Preferred Subject" field. I want the sub to enjoy there time
teaching and
the permanent staff to feel comfortable with whomever is taking
over their
class. I am often asked for recommendations; I'd like some info to
go along
with that. If we have a long-term position for a Special Education
instructor, I need to know who has that credential so we can move
ASAP. Oddly
enough, the district doesn't track this info; I've asked. If I have
the subs
in their own table, I think all the info I need will be together.

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.

Thank you for saying that! I kept wondering,"Why am I having such a
hard
time with this?" Why am I still on the pen, paper and diagram
stage?
I like a challenge because it makes success that much sweeter but
*come
on*...! It makes it difficult to make a move when you are about
ready to step
off the cliff at every turn. If it weren't for this discussion
group, I would
have. Thanks to both you and Bruce for posting your reasons and
thought
process along with your comments. I don't know about others, but it
helps me
to follow along. I t takes an extra measure of patience that I
appreciate. I
am going to rethink my tables and post them again. I hope you will
take a
look and tell me what you think.

Yeah, you kind of jumped right into the deep end of the pool, but
hopefully
with help from the group here you'll get it all sorted out. You'll
get
differing
opinions from people too, so sometimes you just have to absorb the
different
ideas and then decide what you think will work best for you.

--
_________

Sean Bailey


"Aria" wrote:

Beetle,
(lol) Yes, you have definitely thrown a monkey wrench into the
works! You
have raised some valid points that I need to take into account. I
have
thought about what you posted and would like to clarify some points
and ask
additional questions.

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

Sorry, but the school data belongs in a separate table/tables.
With the above
structure you will repeatedly be entering the same descriptive
data over and
over. Additionally, what if an instructor teaches more than one
Subject, works
in more than one Dept., etc? You will have to enter additional
records for
the same employee.

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?

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text

Again, this is wrong. Campus, Wing and RoomType are not
attributes
of the Key. This table should have RoomID as a foreign key to
tblRooms
and probably not much else, unless you store some other
descriptive
information about the key itself, like "color" or something.
Think of it
this way - you said you don't really track filing cabinet keys so
we'll
just take rooms into consideration here. A key opens a room.
Period.
The Room Type and the Wing in which that room is located are
attributes
of the Room, not the Key. Likewise, the Campus that Wing is
located in is
an attribute of the Wing (not the Room or the Key). You should
have separate
tables for Campus' and Wings

Point taken; although I don't understand why the campus location is
not a
room attribute. I need to know where that room or storage room is
located(especially since storage rooms don't have room #s). Does it
make a
difference if there are only 2 campuses? No? So, is it CampusID (FK
to