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?

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.

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