"Aria" wrote in message
...
First, thank you for your honesty. I smiled reading your post. That is the
same thing that happens to me. I think, "hmmm...it could be like
this...but
then what about that...oh, and I forgot to account for this...but then I
have
to switch that...and the next thing you know, I'm right back where I
started.
g I don't think you go through quite the same thing because you have a
*lot* more experience but it *is* confusing.
While keys are what drove my desire to create this db in the first place,
this is also the section I have been dreading. I reviewed all of our posts
and took notes on what was suggested and any decisions that were made.
When I
refer to a statement as from a post in the beginning, that's what I mean.
So,
let's see if we can make any progress on some of these areas.

Is the vault key just like any other key for a particular lock, except
that it is in the vault?

Yes.

If so, is the vault copy essentially a clean copy to be used as the master
for additional copies?

No, although at times they may come and borrow a key to make a quick copy
if
they are already on campus and I just need one or two. It's rare. The
district has its own locksmith who are permanent staff. They have the
originals or blanks or whatever they call them.

If so, you can identify the valut key either by an extra field (maybe a
Yes/No Vault field) in tblKeys, or you can "assign" the key to the vault
just as you would assign it to a person.

Ok, what happened here? We have apparently switched our origianl
positions.
I suggested assigning the vault key like an employee key in the very first
post, but after learning about table attributes from the two of you ( I
will
never forget tblSubs), I no longer hold that position. You two are
absolutely
right. It isn't an employee attribute.
I don't think it should be a yes/no field because of the sheer number of
keys we're talking about...approx. 400. Am I going to run into problems
with
that many yes/no boxes? You may be right about this. I'm not sure because
again, I don't have the experience behind me. So where does that leave us?

I don't know if it is a switch or not, but the fact is you are assigning the
key to the vault. Take a look at this:
http://www.mvps.org/access/forms/frm0043.htm

It explains how to produce a list such as this:

(Vault)
Adams, John
Adams, John Quincy
Jefferson, Thomas
Madison, James
Monroe, James
Washington, George

When you select (Vault) you can have the AssignedTo field be null or 0. In
a query you could have something like this:
AssignedEntity: IIf([AssignedTo] Is Null,"Vault",[LastName] & ", " &
[FirstName])

The code in the link I provided (use the part at the top of the page, not
the part that talks about a Value List) adds a sort of artificial record to
the Employees listing, but it *does not* create an employee record.

Are you oppossed to the vault table or vault as a location in
tbllocations(now that I think about it, somethings wrong with this line of
thinking but I'm not sure what)?

The vault is issued a key. We can modify the part about keys being assigned
to people to say that keys are assigned to entities including people. The
vault is in a location, but it is not a location. If the vault has a keyed
lock there is a corresponding record in tblLocks, including Location
information.

Regarding the Locks table, I don't think it would be a junction table.

I 'm now confused about the purpose and fields in this table. You had
posted
in the beginning that it should include lookup tables for campus, wing and
roomtype. I by no means expect you to remember this because I didn't
either.
There was a lot of discussion about a great many things. I'm only
mentioning
it now because you asked me to post my table structure for this part of
the
db. I was unsure about some of the structure and wanted to make sure I had
it
right before I posted. It was a confusing mess that I had to keep looking
at.
I couldn't make heads nor tails of what it should be. I had to live with
it
awhile. I had to sit down and go through all the posts suggestions and
advice, move fields from one place to another, change table names and add
fields and lookup tables. When I finished the only fields left in
tblLocker
were KeyID and LocationID. I originally had campus and wing in this
section
but once we changed tblRooms to tblLocations, it seemed better suited in
tblLocations. Since I was now left with only 2 fields in tblLocks, I
started
wondering if it was a junction table.

Let's say Room 222 of Building A on the South campus has a lock with Serial
Number 12345. Your tblLocks would be something like this:

tblLocks
LockID LNumber LCampus LBuilding LLocation
111 12345 South Building A Room 222

You can store numbers instead of text for LBuilding, etc. If somebody
changes the building name the keys will remain the same, and all records
will reflect the new name. For now just assume the text value is stored.

One Lock can have many keys. Therefore tblKeys is something like this:

tblKeys
KeyID (PK)
LockID (FK)
KeyNumber (SA-12 or whatever)
Retired (Yes/No)

The Key records for Lock 111 could be something like this:

KeyID LockID KeyNumber
1 111 SA-1
2 111 SA-2
3 111 SA-3

Note that each key record is associated with a Lock record. KeyID is not a
part of the lock record, but rather the other way around. The lock has
keys. The key does not have locks.

You will note I have used several location fields (Campus, Building, and
Location). If several buildings have a Room 222, or several campuses have a
Maintenance Shed, or something like that, then you will need all three
location types to identify where you are in at least some cases. After
selecting South Campus the Building combo box could be limited to just
buildings on the South Campus. Similarly, the Room combo box could limit
Room Numbers in the selected building. More on this later, if you like, but
you can ignore it for now.


There is no need to keep an active listing of keys nobody can find.

lol ...good one! No, let's not.

There are more such listings than you may imagine.

I'm going to post my table structure later today, even though it still
needs
work. I know there are mistakes and that there is plenty of room for
improvement, but you have posted additional comments that I believe (if
I'm
not mistaken) may have been addressed within the structure.

I will wait to see what you post, and will add some comments tomorrow,
including (if I can) a strategy for the junction table for storing
KeyAssignment information, but after that I will be away for a week. I
guess Sean will be back, so I hope I'm not at cross purposes with him to too
great an extent.

--
Aria W.


"BruceM" wrote:

Is the vault key just like any other key for a particular lock, except
that
it is in the vault? If so, is the vault copy essentially a clean copy to
be
used as the master for additional copies? If so, you can identify the
valut
key either by an extra field (maybe a Yes/No Vault field) in tblKeys, or
you
can "assign" the key to the vault just as you would assign it to a
person.
To do this you could add (Vault) to the top of the drop-down list of
Employees to whom keys are issued, similar to the way some drop-down
lists
show (All) at the top. I won't go too far down that road until I hear
back
from you.

Regarding the Locks table, I don't think it would be a junction table. A
lock is an entity with certain characteristics such as brand, location,
date
installed, and so forth. One Lock can have many keys. Iff Master Keys
are
included in the thinking, one Key can be for many Locks, but in this case
the junction table would be tblKeyLock or something like that.

Regarding tblKey, I expect there should be a Lost field or something like
that. There is no need to keep an active listing of keys nobody can
find.
Regarding the question of natural key or surrogate key (any "artificial
identifier", including autonumber), it really doesn't matter as long as
the
"natural" number such as SA-2 is used once for one physical (metal) key,
and
never used again. If you are not sure this is the case, autonumber would
be
a simpler choice as the PK. The same idea comes into play with the Lock
identifier, I expect. If they change the lock in such a way that the old
keys can be used you will need to update the FK of those keys so that
they
are associated with the new lock.

From an earlier post you wrote:

Suggestions still on the table:
1. Create tblLocations to replace tblRooms
Sounds like a good idea
..
2. Create tblLocks to define the relationsip between keys and locks.
There
should be a 2 field PK involving Key ID and LocationID.
Already discussed. Let me know if something is unclear, or if I am
missing
something

3. Create Master Key table to account for the special attributes of
Master
Keys.
One possibility is to see the situation as One Lock Many Keys and One
Key
Many Locks, in which tblKeyLock is needed to resolve the relationship
for
all locks and all keys. However, I think a separate table for MasterKeys
and a junction table tblKeyLock would be simpler to manage. If you are
looking at a Lock record you would have a subform listing the ordinary
keys
and the person to whom they are assigned (some may not be assigned at
all, I
expect). One Lock Many Keys, so there is a 1:M between tblLock and
tblKey, and the subform is based on tblKey. Another subform based on
tblKeyLock could list the master key holders.
One lock could have keys assigned to many people, and each person could
be
assigned keys. Another consideration is that a Lock record should have a
listing of available keys. If it was me I think I would have an
AssignedTo
field in tblKeys:

tblKeys
KeyID (PK)
LockID (FK to tblLocks)
KeyCode (S2-A, etc., or whatever)
AssignedTo (FK to tblEmployees)
AssignedDate
Retired (Yes/No)

If I wanted to list people and the keys they hold I would use a query.

This is not necessarily the best design in that AssignedTo and
AssignedDate
are not really attributes of keys. The complexity here is that one
person
may be assigned many keys, but a key can be assigned to only one person.
Similarly, one lock may have many keys, but other than master keys a key
may
open only one lock. On the other hand, one person may open many locks,
and
a lock may be opened by many people. By the way, it just occurred to me
that if there may be several locks keyed to accept the same key you may
need
to add a LockLocation table related to tblLocks to take care of this
detail.
One solution here may be to have a LockPerson junction table (one person

many locks and vice versa). This would be the source for a subform on
the
Locks form. A list box could contain a listing of unassigned keys for
that
lock. The list box row source would have to be built as you go, since
the
available keys are always changing. When somebody needs to be assigned a
key you would go to the Lock form, see the listing of available keys, and
created a new LockPerson record that stores the KeyID, EmployeeID, and
maybe
AssignedDate and other details.
It may be worth your while to start a new thread on this specific topic.
All
you would need to say is that you have:
A Locks table containing the LockId, LockLocation, etc.
A Keys table containing KeyID (PK), LockID (FK), KeyCode, etc.
An Employee table

Explain that you understand a Lock may have many keys, and that a person
may
be assigned many keys. However, you are unsure how to store the
AssignedTo
information for Keys. Should it be in the Keys table? If not, how is
that
relationship modeled.

I suggest this because frankly I am unsure how best to proceed on this
point, and in a new thread you would attract the attention of very
experienced designers.

4. Create additional look-up tables: tblCampuses, tblWings and
tblLocationTypes.

Sounds good.

**********

"Aria" wrote in message
...
I think I may have a problem that I need to work through. We can discuss
this
when you have time. I just wanted to get this down before I lose track
and
start dealing with other issues. I think we are going to need to
re-visit
an
issue from the very first post: vault keys. Originally, we decided that
the
situation was unclear and dropped it. In looking through some of the
data
that I copied to help map my direction, I just noticed something that I
probably saw before but did not grab my attention as it has now.
During
the
re-key, among the many replacement keys made, one key was important
enough
to
be "assigned" to the vault. There are probably others, but that
happened
before my time so I am not aware of the previous history. This does
change
things a bit. I'm going to have to account for not only that key, but
all
of
the vault keys.

I have two trains of thought right now:

The first is that the vault key is a sub type key that will need its
own
table. I don't know about this because it doesn't seem to fit the model
of
a
sub type (at least not as far as I can see). It's not like the masters
which
are actually a different type of key. Their attributes are
significantly
different. Not so with the vault key; there's nothing special or
different
about them.

The second thought is that this is really a location type and will need
to
be included with tbllocations or is it tbllocationtypes. I'm still
working
on
this because I'm unsure right now. It does seem to fit the "where"
model
though.

Just throwing it out there...

--
Aria W.


"Aria" wrote:

I don't have much time to reply today, but I have noted a few things
inline.

Please, don't give it a second thought. I appreciate all of the
support
you
have both given me. I understand.

If you select the combo box, open the Property Sheet (with the tabs
for
Format, Data, etc.), select an event, click the three dots, click
Code
Builder, and click OK the name of the control will be filled in for
you
in
the code window. If you type it yourself you need the underscore.
I
think
that is so there are no spaces in the procedure name.

I'll check on that.

There's no definitive answer for that, but anything that involves
junction
tables is a lot to get your mind around if you are just starting.
Before
long, if it hasn't already happened, the need for junction tables,
and
how
to use them, will be clear in your mind. In your case there were a
number
of junction tables, and a situation that involved keys, locks,
people
to
whom the keys were issued, locations of the locks, campuses, and a
number of
other entities, with a variety of relationships between the
entities.

Yeah, no kidding. I started thinking about that because I was trying
to
avoid adding yet another junction table. I recall a previous
discussion
where
we decided that the room phone could go into tblLocations. That would
leave
district cell phone orphaned and I could not figure out where to put
it.
It
doesn't belong in tblLocations and doesn't belong in tblEmployees so I
figured why am I drawing the line at this junction table when there
are 5
others just like it. Oh well...c'est la vie! I keep looking at the
relationship diagram and I 'm concerned how I will manage all of this.
In
particular, what kind of form to design and whether I should have
another
subform for tbKleysRequests on frmEmployees. I'm trying not to borrow
trouble; one thing at a time.

What became of tblLocks? If one lock may have several keys, locks
are
the
top level.

"A lock can have several keys". There have been a number of turning
points
in this journey. You both have dropped statements that on the surface
seemed
inconsequential ,but in reality had a major impact. That is one thing
I
am
*never* going to forget. You made 3 on the mark statements in that
post.
tblLocks is still here. It*is* a junction table, correct? It sure
looks
like
one.
Suggestions still on the table:
1. Create tblLocations to replace tblRooms.
2. Create tblLocks to define the relationsip between keys and
locks.
There
should be a 2 field PK involving Key ID and LocationID.
3. Create Master Key table to account for the special attributes
of
Master
Keys.
4. Create additional look-up tables: tblCampuses, tblWings and
tblLocationTypes.

Questions/Comments:
1.Decision--Unbeknowst to you and Beetle, I was going to use a
natural key
for KeyID in tblKeys. Your statement about a possible re-key and
our
history
of such finally made me realize that this was a *lousy* idea from
the
beginning. I'm switching to autonumber.

There's nothing wrong with a natural key, depending on what it is.
With a
surrogate key such as autonumber the main thing is that the record
needs to
be unique for reasons apart from the autonumber field. If two
records
are
identical except for an artificial identifier then they are not
really
unique. The autonumber is a convenience, but cannot by itself
enforce
what
is known as a unique constraint.

Won't this cause a problem? With the re-key, they not only changed the
lock,
they changed the lock identifier. So if the key was previously, let's
say,
SA-2 , it may have been changed to XJ-3A after the re-key. If I used
this
as
my primary key, I would have thought this would be a nightmarish
situation.
No? If it happened once, there's always the possibility that it
*could*
happen again. Keys are lost pretty much on a weekly basis. What do you
thinK?
Do I need an inactive button for keys? I don't know exactly *how* this
would
work.

Let me know what you think about that and the master keys. Don't
concern
yourself if you don't have time. I have problems in the db that I need
to
straighten out. Thanks so much for your time Bruce.

--
Aria W.


"BruceM" wrote:

I don't have much time to reply today, but I have noted a few things
inline.

"Aria" wrote in message
...
I found the compile button. It was under commands so I added it to
the
toolbar. Thanks.

I will take a look at your questions, but you will need to sort
of
start
over with your descriptions, as I have not been following very
closely of
late. When you do so, list only the essential fields.

Not quite ready to list the table structures for this part
yet...still
stinging from the last time when they were all wrong. I just
basically
want
to think on post. Ask a few questions and have you weigh in on
where
I'm
going wrong. Are you Ok with that? I completely understand what
you
mean
as
far as having lost track. I have all my notes and posts and I
still
have
to
read up on what's going on and what transpired.

Questions unrelated to 2nd half of db:
1. When I input the programmng code for the AfterUpdate event, I
input
cboStaff_Lookup. The line highlighted in yellow said, "Private Sub
cboStaff_Lookup_ AfterUpdate () Was I supposed to input that final
underscore
after Lookup?