Re: Still Struggling...

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" <Aria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:110BDA30-102F-4449-9041-7766EAFAC9C1@xxxxxxxxxxxxxxxx
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 *** (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" <Aria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:BC0C0F83-14A9-4830-88EC-BB2EBF73DA6C@xxxxxxxxxxxxxxxx
> >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?
>
> If you select the combo box, open the Property *** (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.
> >
> > 2.Ever since this started taking off in earnest, I have often thought
> > that
> > this db seems to be complicated; maybe that's just beginner's woe. I
> > don't
> > know because I don't have a point of reference. Both you and Beetle
> > stated
> > in
> > your posts to each other in the beginning that it wasn't simple for a
> > first
> > app. My question is how did you know? We hadn't even really gotten
> > into it
> > past the original 6 tables. How does one determine complexity?
>
> 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.
> >
> > Hoping this is not too much for you in one post...
> >
> > We know:
> > 1. This is a staff database for a school setting.
> > 2. We encompass 2 campuses.
> > 3.Db emphasis is on keys because they have been problematic.
> > 4. We have already suffered through a re-key of an entire campus,
> > including
> > the stadium, gyms and gates.
> > 5. Many locations w/o a room #. (Ex. Auditorium, Stadium, Storage
> > Rms.,
> > etc.)
> > 6. Master keys allow general access for a single campus.
> > 7. Master keys are unique, employee specific and their allocation is
> > severely restricted.
> > 8. Storage, Stadium, Food Service and Gate Masters are location
> > specific
> > *and* follow rule #7.
> > 9. Wing masters will only open all rooms for a specific section of
> > the
> > site.
> > 10. Key assignments are based on job title, room assignment and
> > extracurricular duties.
> >
> > Where we left off:
> > 1. We had 4 tables relating to keys or location: tblRooms,
> > tblKeysEmployees
> > (junction), tblKeys and tblKeysRequests.
>
> What became of tblLocks? If one lock may have several keys, locks are
> the
> top level.
> >
> > 2. There was a lot of discussion involving home, personal cell,
> > district
> > cell and room phone #s. The last decision was that home/personal cell
> > is
> > part
> > of tblEmployees; district cell and room phone #s are part of
> > tblphones.
> >
> > 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.
> >
> > If your eyes haven't glazed over... You don't have to answer today; I
> > know
> > it's a lot.
> >
> > 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.
>
> >
> > 2. Master keys--This is a sub-type of key, correct? You can have one
> > key
> > and
> > many masters or is it many sub-types (Gate, Stadium, etc.) But you
> > can
> > also
> > have one specific master that has many keys assigned. While the
> > master is
> > employee specific, it *is* essentially the same key that is assigned
> > to
> > every
> > employee who has clearnace. It's just coded so we know in advance who
> > we
> > gave
> > it to. This is a 1:M relationship? You can't have a M:M relationship
> > with
> > sub-types can you? Either way it will have its own PK. This is where
> > I'm
> > confusing myself and going around in circles.
>
> I'll have to ponder this one later.
>
> >
> > I'll stop here for now. Thanks!
> >
> > -- > > Aria W.
> >
> >
> > "BruceM" wrote:
> >
> >> I think that Macros may be considered to be something other than
> >> code,
> >> but
> >> I'm not sure. If you use the wizard the code is added
> >> automatically.
> >> You
> >> can view it, but you may not know it was added. In any case, unless
> >> you
> >> use
> >> macros, which are rather limited, you pretty much need VBA code for
> >> anything
> >> other than a very simple database.
> >> To find the Customize option I mentioned, open your database. In
> >> the
> >> database window, click the Forms tab. Click View >> Code, or click
> >> the
> >> Code
> >> icon on the toolbar. What you see is the VBA editor. Right click a
> >> blank
> >> spot on the toolbar or menu bar. You should see Customize, probably
> >> as
> >> the
> >> last item on the list. There are of course other ways to open the
> >> VBA
> >> editor, so choose another method if you prefer. The way you open it
> >> is
> >> not
> >> important (although I wouldn't try to customize while debugging).
> >>
> >> 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. For
> >> instance, for
> >> tblEmployees:
> >>
> >> EmployeeID (PK - Number)
> >> LastName
> >> FirstName
> >> etc.
> >>
> >> may be enough. For tblSiteEmps, list the PK/FK field, the
> >> relationship
> >> type, and a few fields that will give the idea of how the table is
> >> used:
> >>
> >> tblSiteEmps (1:1 tblEmployees)
> >> EmpID (PK/FK)
> >> Home Phone-txt
> >> Address-txt
> >> etc.
> >>
> >> This is enough for our purposes. Give yourself a break from typing
> >> all
> >> of
> >> the details, unless for instance the fact there is a cell phone
> >> number is
> >> relevant to the problem at hand, and make it easier for me or
> >> another
> >> responder to read. Limit your description to a few typical fields,
> >> or to
> >> fields that are part of your code or that are involved in
> >> relationships.
> >>
> >>
> >> "Aria" <Aria@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> >> news:FB41E620-FF52-47A8-96EE-DC3BD5C40C3B@xxxxxxxxxxxxxxxx
> >> > You know I had never been to this section of my database before.
> >> > Originally,
> >> > I had no intention of putting in any programming code. I tried
> >> > doing as
> >> > you
> >> > suggested but when I go to Customize...Options...I don't see what
> >> > you
> >> > are
> >> > describing. What I see in the Options tab is Personalized Toolbar
> >> > and
> >> > Menu
> >> > but it's greyed. The only button you can push is Reset my Usage
> >> > Data
> >> > (?).
> >> > Maybe I'm in the wrong place.
> >> >
> >> > I wanted to return to the 2nd half of the db. I still need to work
> >> > on
> >> > tables
> >> > but I have questions (naturally!) Do you mind?
> >> > -- > >> > Aria W.
> >> >
> >> >
> >> > "BruceM" wrote:
> >> >
> >> >> I should have said 'Still in the VBA editor, click Debug >>
> >> >> Compile."
> >> >> This
> >> >> is in the menu bar, not in the Tools >> Options dialog. I like
> >> >> to
> >> >> have
> >> >> that
> >> >> command readily available, so I added it to the toolbar. To do
> >> >> that,
> >> >> right
> >> >> click on the toolbar, click Customize, click the Options tab,
> >> >> click

.