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.

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.

"Aria" wrote in message
...
Bruce,

A lock could have several keys.

True.

If a supply closet has three keys issued
and a fourth person needs a key, you need to know that another key has to
be
made.

*Very* true.

The keys is an attribute of the lock, not the other way around.
At least that's how I see it.
I'm beginning to see what you mean here.

If a lock is changed you need a way to find the
existing keys so they can be swapped with the new ones.

(stunned silence)
You have my attention. This is exactly the situation we encountered this
school year. Someone who had a master key was working at our site one
weekend. The key was "lost". The decision was made to re-key an entire
campus; every single room and door. The locksmith and I worked well
together.
It was a smooth process that took well over a month and a half to
complete;
but we both suffered abuse. This is not a situation I care to repeat.
I see your point. Because our system is handwritten cards, I had no way of
knowing who had what key unless I was given a name and could look it up.
This
was totally inadequate. I didn't mean to dismiss your idea out of hand,
but I
loathed the idea of adding another table to the database since I was
having
problems with the ones I currently have.

So, should it look like this:

tblLocks
******
LockID (PK) Autonumber
KeyID (FK to tblKeys)
RoomID (FK to tblRooms)
Campus-text
Wing-text
RoomType-text

If not, can you help me? I understand about I will need look-up tables.
Not
quite sure how all of this will fit together.
I am going through all of the information that you and Beetle have given
me.
I want to say to you as I posted to him, my sincerest thanks for all of
your
help. To take on a novice and patiently give suggestions and explain your
thinking process in a step by step manner, is not only appreciated but
very
special.
--
Aria W.


"BruceM" wrote:


"Aria" wrote in message
...
Bruce,
First, I would really like to thank you for responding. I need to
clarify
some of the information that seems to be confusing. tblSubs is a table
for
temporary, roving district staff. They may be employed at any district
site
anywhere from a couple of hours to long-term(months). They need access
to
their assignment location and if they are certificated, temporary
passwords
for certain reports.

My comments are as follows:
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
I ws going to say that SchoolData and PersonalInfo should be broken
into
several fields each, but later you said something about the number of
fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

I think I'm fairly comfortable with this table. I just thought maybe it
needed to be broken down (not that I'm looking for another table to put
in).
Yes, the fields are seperate.

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

One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each
lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be
flexible.
One approach to the Locks table would be to have fields for Campus,
Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus,
or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup
fields)
to use for selecting campus, wing, and roomtype from combo boxes on
your
form. For instance, a RoomType table may be simply a listing of
RoomTypes.
Make a query based on this table (sorted by RoomType), and use the
query
as
the Row Source for a RoomType combo box on your Keys form.


Now, this is where you lose me. I don't understand the purpose of a
Locks
table. Isn't that what the keys table is for? We are only interested in
the
locks as far as what key will staff to gain access to the room, gate,
stadium, etc. We don't normally track file cabinet keys. We may if the
contents are extremely confidential.

A lock could have several keys. If a supply closet has three keys issued
and a fourth person needs a key, you need to know that another key has to
be
made. The keys is an attribute of the lock, not the other way around.
At
least that's how I see it. If a lock is changed you need a way to find
the
existing keys so they can be swapped with the new ones.



tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber

EmployeeID must be Number (Long Integer), not Autonumber. It is a
foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by
themselves
composite keys.

Sorry; I dropped the ball on this one. This *is* long interger. I
forgot
to
make the change in designation from my original.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text
KeyID as the PK of tblRooms could be confusing.

Again, you lost me on this one. Why is the KeyID confusing? It relates
to
the room; knowing the key ID will allow you access. My thinking was
that
if I
could do a parameter query (is that what I would want?) by room, I
would
know
which key will unlock it. Then I could make a key request or whatever
else
needs to be done.

As Beetle pointed out, RoomID is the PK of tblRooms (although again, I
would
identify Locks, which are not necessarily for rooms). A room (or lock)
could have several keys, so KeyID is the FK to tblRooms (or tblLocks).


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time

This table's purpose is unclear.

I'm sorry. I should have explained in the beginning. Like I stated,
this
is
for temporary, roving staff. We need Access only in relation to keys
for
this
group. We have other applications for the rest of the information we
need.

Again, as Beetle suggested there is no need for this group to be separate
from the people listed in tblEmployees (or tblPersonnel, if you prefer to
look at it that way).


1. How many fields are too many in a table? I understand that Access
will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.

No problem. More than 30 fields could suggest normalization problems,
but
it not an invariable rule. If the SchoolData fields are not all filled
in
for every record it could be that SchoolData could be in a separate
table.
Nothing else stands out much.

All the fields relating to school data wil be filled in, so I guess
we're
good here.

Beetle made a good point about this if there somebody could have more
than
one subject, etc. I wish he had included more of the remarks when he
replied. He posted that he was disagreeing with me (I think) about some
things, but he did not include those things, so this thread is a bit
fragmented. I have some other remarks in response to his reply.



2.tblRooms has a field for Rm.#; the problem is that not all rooms
have a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks
to
tblRooms because I may have to describe a location rather than a room
#.
Will
there be a problem with this method?

Only if RoomNumber is a linking field. By the way, I assume you are
using
the number sign for description, not as a field name. Names should
include
only letters, number, and underscores. Spaces and other
non-alphanumeric
characters are best avoided.

Oh my gosh! How many times have I read the same thing and because I
*knew*
my fields weren't like that, moved on. Well, I just wanted to double
check
and lo and behold, there it was; a # sign in my field name. Thanks for
the
heads up!

One of the desired reports is a reverse directory by room #/room phone
#.
The phone # is currently in tblEmployees. Should it be moved to
tblRooms?
If
I leave it where it is, will I be able to update any queries and
reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it
would
be
better if the phone # was listed with tblRooms but someone told me
that's
incorrect.

If the phone number is associated with a room it should be part of a
room
record. Presumably an employee can be associated with several rooms,
and
vice versa. However, if an employee has an office where only he or she
answers the phone, the phone number is associated with the employee.
Often
in such case the phone number follows the employee if they move to
another
office. There is no single answer to your question. Your database can
be
made to accept either a phone associated with a room or with an
employee,
or
both. It does start to become more complex if you have both situations.

Well, for classroom staff, if they have to change rooms (happens every
year,
so I have no illusions here), they switch to the new rooms' phone #.
For
office staff, it could go either way. What do I need to do if anything?

If a staff member has a fixed phone number, that should be part of their
Employee record. If a room has a phone number, that is an attribute of
the
room, so you would start to get into having the phone number be
associated
with a person's schedule, which is an other project. Essentially you
would
need to have the person's schedule. If they are in room 101 from 10:00 -
noon, from those hours you would need to contact them there. I'm not
sure
there is a simple answer to this question.



3. I forgot to include the following fields: Date assigned, Return
Date
and
Permission to Retain (over the summer) for Emp. Keys. Should this be
added
to
tblKeys or tblEmployees? I was told not to put *anything* into the
junction
table (tblKeysEmployees) besides the composite key already listed.

You can have other fields in a junction table. For instance, the
KeysEmployees junction table may include a DateIssued field, which is a
property of neither the Employee nor the Key, but rather of this
particular
key together with this particular employee. If a key may be retained
over
the summer by one employee, but not another, then this too is a
candidate
for a field in the junction table.

Your statements are true enough; only the master keys are coded to a
particular employee. Classroom keys are a little more generic in that
anyone
who works out of that classroom or office will have the same key. O.k.,
I'll
put the new fields here.

4.Keys Requests--Sometimes a request will not have a name associated
with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I
need
to
order a sub. key, I can use the name of the permanent staff member
associated
with that room. What do I do about the vault? Should I enter "vault"
in
the
emp. table w/o any additional data?

Vault chould not be in the employee table, but the situation is
unclear.
Is
nobody responsible for the vault key? How will you know who has it? How
do
you know now?

Every key available on both campuses should be in the vault.
Theoretically,
vault keys remain in the vault. I guess I'm responsible for those keys
but
anyone who has the combination can remove a key without my knowledge. I
guess
I could list myself and under Remarks indicate it's a vault request.

It sounds as if the vault contains the originals from which copies are
made.
If so, and if the vault has a combination lock, I'm not sure the vault
fits
into your schema.


5. Maybe I should cross this bridge when I get to it but I eventually
will
get to the point where I will make forms and subforms. There are
approx.
400
seperate keys, not including multiple copies of the same key. If I
have a
combo box, is there a way to filter so that I am not looking at all
400
possiblities at once?

Again, I'm not sure what you're asking. You will need forms and
subforms
as
soon as you start doing data entry. It may be best to begin with just
tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how
to
work with the junction table arrangement.

I'll hold off on this question and concentrate on getting the tables
and
fields correct.

Bruce, again, I really appreciate all of your help. Thanks so much for
talking me through it!











--
Aria W.


"BruceM" wrote:

Responses inline.

"Aria" wrote in message
...
Hi,
I'm still struggling to organize my tables and fields but I'm not as
*totally confused* as my original post; at least I hope I'm not. I'm
new
to
Access. This is my first database and I fluctuate between feeling