Still Struggling...

I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own.

Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted).

Happy to help. At least you want to learn and do things the right way. The
posters we don't like are the ones that have already decided how they want
to do things, even though it may be wrong, and expect someone here to
explain to them how to do it the *wrong* way.

Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields. If an attribute doesn't apply to all employees, all the
time,
then it is an attribute of a sub-type, and therefore probably belongs in
another table.

Some other things to keep in mind for the future in the development cycle
of your application;

1)Do not use "lookup fields" in your tables. This basically ampunts to
a combo box, or list box, in a table. Combo/List boxes in forms are
appropriate, but they should never be used in tables. See this link
for more info;

http://www.mvps.org/access/lookupfields.htm

2)Don't use spaces or other special characters in your table or field
names. The same goes for queries, forms and reports (when you
get to that point). Having spaces, etc. in the names will only cause
you headaches later on. So instead of "Employees Table", it would
more commonly be "tblEmployees".

3)Certain words are "reserved" in Access and should not be used as
names of objects in your app. They are the names of different
properties, etc. of the application, and if you use them as, for
example, a field name it will cause problems. Two very common
examples are Name and Date. See this link for a more complete
list;

http://support.microsoft.com/kb/286335

4)Most developers use some sort of "naming convention" to help
keep track of things as the application grows in size. Everyone has
some of their own variations on naming conventions, but some things
are common. For example;

Table names commonly start with tbl
Queries start with qry (or something similar)
Forms start with frm, Sub forms start with fsub, sfrm, etc.
Reports start with rpt
A text box (on a form or report) would start with txt
A combo box (same as above) would start with cbo
etc.

5)Continuing with the naming theme. When you get to the point
where you start designing forms and reports, you will notice that when
you add a control (text box, combo box, etc.) to a form/report, Access
will give will give it a completely useless name like "Text52" or
"Combo38". Do yourself a favor and name them something meaningful
like "txtEmployeePhone" or "cboSelectASubject".

Good luck and post back when you have more questions.

--
_________

Sean Bailey


"Aria" wrote:

First, let me say,"WOW"! You both have given me *so much* information. Thank
you! My comments are below (I'm combining posts. I didn't know how else to do
this):
Bruce M:
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.

Aria:
Do I need a subform if the majority of the staff only hold one title? Yes, I
still trying to get over having empty fields but I haven't made it this far
so I don't know. The form I envision would have this info on the main form
using a combobox for the titles. I am trying to think of every possible title
that I can. I don't want any suprises here.

Bruce M:

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).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do the
junction table.

Bruce M:
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.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I know
I need to learn how to figure out some of these issues myself. I have to try
first. If I continue to have problems, I will of course follow your advice
(you and Beetle).

Bruce M:
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.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the hard
work at the beginning so that it will seem effortless in the end. Well, here
it is. I am willing to put in the effort but some days I am frustrated beyond
belief.

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.

Bruce M:
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.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone could
have several people answer it. This will depend on who is assigned to the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a room
assignment list from the Principal soon. We'll see....

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

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

Aria:
Ugh! Nooooooo...I was so sure! I checked over and over and over again and
last night I finally found it...one person, two departments. Will this never
end?!


"Beetle" wrote:

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of
information there. I had to really pay attention though and relate it to my
situation. After reading it, I'm not as frustrated. Yes, there are 1:1
relationships here but it may be what I need. I do think that I have
sub-types. Let me work with this some more before I let you both know which
1:1 relationships I have.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

Great...I think I have at least one situation where this is true.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.
To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions.

Hey, perfect example!

Hopefully this will help you. If you still have questions, as Bruce said it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread.

It does help. Thank you for that. I want to continue trying before I throw
in the towel and ask for help.

Comments for both of you:
1. You two are *great* teachers who always give me homework! There is such a
wealth of information, instructions and suggestions that you put into your
replies that I can't post back right away; I'm studying, highlighting and
taking notes!

2. I've learned SO much from you (the collective you). I consider myself
incredibly fortunate that you responded to my post.

3. I have varying degrees of frustration and confusion. I'm confused by some
of my own table names (yeah, I know that doesn't even begin to make sense to
you. I'll work on more logical naming).
I'm also confusing myself by thinking to hard and long about some of these
issues. I know the answer is right in front of me but I can't see it.

4. Like I said before, you both pan wide and then zoom in, incrementally, to
get to the crux of a problem. I want to do that. This is starting to come
into focus a little more now. I just have to keep reminding myself that
problems I encounter now, will be be something I won't have to deal with
later after everything is set. It's frustrating but I'm trying to keep it in
perspective.

5. See #3. I'm going to have to take a step back. I have been working on
this day and night (in between my day job). I have been thinking about
database design in my sleep (I know, I've got it bad).
6. I have taken up so much of your time already. You have moved me forward
by leaps and bounds. I understand that there are others who need help too.
Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted). You have no idea how dejected I was when I first posted,
but every question answered unraveled the knot just a little bit more. I'm
going to take a couple of days and see if I can work out this employee table.
When I return, I would like to get back to tblLocations, tblKeys, etc. I hope
you will still be here, but if not, I completely understand.

I just wanted you to know how grateful I am. I'm sure this is more than you
*ever* wanted to know about locks, keys and school "issues".

--
Aria W.


"Beetle" wrote:

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.

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

************************************************** *

When it comes to people connected to the church in some way or other things
get a little more complex, but not frighteningly so, as the principles
involved are quite simple. All people share certain attribute types of
course; we all have names, an address, a date of birth and so on. So there
is an entity type People with these common attribute types, and a table can
represent this attribute type. People with different roles may well have
attribute types which are specific to their role, e.g. a pastor is likely to
have attribute types which a member of his congregation would not have. If I
can use an example from the C of E an attribute type for a C of E priest
might be Date of Ordination. So the entity type Pastors is a sub-type of the
entity type People. The way a sub-type is modelled in a relational data base
is by means of a one-to-one relationship. In the case of People and its
sub-type Pastors this would mean that there would be a table people with a
numeric primary key PersonID (don't use names as a primary key, they can be
duplicated), and a Pastors table also with a numeric primary key PersonID
(you can call it PastorID if you wish, but I prefer to keep the column names
the same). In the case of the Pastors table PersonID would also be a foreign
key referencing the primary key of People. The people table would have
columns for the common attributes like names and address etc., the Pastors
table would have columns only for those attribute type specific to the
Pastors entity type, e.g. Date of Ordination, but not the common attributes
like names and address.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.

When it comes to events connected with a church you need to think carefully
about how these fit into the model. One thing you need to be careful of is
deciding whether an event type is a sub-type of another type, or whether the
type of an event is an attribute type of a single entity type. You might
have a topmost Events entity type with attribute type such as Event Date,
Location etc and a Baptisms sub-type with attribute types such as ChildID,
FatherID, MotherID, all three referencing the People table. Another way of
modelling it would be to have a single Events table with an attribute type
EventType, in which a value 'Baptism' would be entered. The problem with the
latter approach of course is that if you also have ChildID, FatherID,
MotherID columns these won't be appropriate to other types of events in the
same table. In this case therefore a type/sub-type model is probably the
most appropriate. In the case of other entity types, however, the second
approach might be more suitable. To take an extreme example, in a personnel
database you would not have an Employees table and a separate table for every
Job Title in the organization; you'd be more likely to have a Positions table
and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo
columns to model the many-to-many relationship between Employees and
positions.

************************************************** **

Hopefully this will help you. If you still have questions, as Bruce said it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread. If you need to repost, it may be helpful if
you include some more detailed information about your Employees table
such as;

1) What different types of employees you need to track

2) Which attributes are common to *all* employees

3) Which attributes are specific to only certain employees.

--
_________

Sean Bailey


"Aria" wrote:

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

As for control names, a field dragged onto a form or report from the field
list will give a text box the same name as the field, which can cause its
own problems. Microsoft causes problems at times with its efforts to be
helpful.
While I agree in general about empty fields, there are reasonable exceptions
to the rule. For instance, I don't think I would create a table for name
suffixes such as Jr., Sr. etc.
For reserved words the single best resource of which I am aware is he
http://allenbrowne.com/Ap****ueBadWord.html

"Beetle" wrote in message
...
I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own.

Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have
that
fear when I posted).

Happy to help. At least you want to learn and do things the right way. The
posters we don't like are the ones that have already decided how they want
to do things, even though it may be wrong, and expect someone here to
explain to them how to do it the *wrong* way.

Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of
empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields. If an attribute doesn't apply to all employees, all the
time,
then it is an attribute of a sub-type, and therefore probably belongs in
another table.

Some other things to keep in mind for the future in the development cycle
of your application;

1)Do not use "lookup fields" in your tables. This basically ampunts to
a combo box, or list box, in a table. Combo/List boxes in forms are
appropriate, but they should never be used in tables. See this link
for more info;

http://www.mvps.org/access/lookupfields.htm

2)Don't use spaces or other special characters in your table or field
names. The same goes for queries, forms and reports (when you
get to that point). Having spaces, etc. in the names will only cause
you headaches later on. So instead of "Employees Table", it would
more commonly be "tblEmployees".

3)Certain words are "reserved" in Access and should not be used as
names of objects in your app. They are the names of different
properties, etc. of the application, and if you use them as, for
example, a field name it will cause problems. Two very common
examples are Name and Date. See this link for a more complete
list;

http://support.microsoft.com/kb/286335

4)Most developers use some sort of "naming convention" to help
keep track of things as the application grows in size. Everyone has
some of their own variations on naming conventions, but some things
are common. For example;

Table names commonly start with tbl
Queries start with qry (or something similar)
Forms start with frm, Sub forms start with fsub, sfrm, etc.
Reports start with rpt
A text box (on a form or report) would start with txt
A combo box (same as above) would start with cbo
etc.

5)Continuing with the naming theme. When you get to the point
where you start designing forms and reports, you will notice that when
you add a control (text box, combo box, etc.) to a form/report, Access
will give will give it a completely useless name like "Text52" or
"Combo38". Do yourself a favor and name them something meaningful
like "txtEmployeePhone" or "cboSelectASubject".

Good luck and post back when you have more questions.

--
_________

Sean Bailey


"Aria" wrote:

First, let me say,"WOW"! You both have given me *so much* information.
Thank
you! My comments are below (I'm combining posts. I didn't know how else
to do
this):
Bruce M:
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.

Aria:
Do I need a subform if the majority of the staff only hold one title?
Yes, I
still trying to get over having empty fields but I haven't made it this
far
so I don't know. The form I envision would have this info on the main
form
using a combobox for the titles. I am trying to think of every possible
title
that I can. I don't want any suprises here.

Bruce M:

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).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do the
junction table.

Bruce M:
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.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I
know
I need to learn how to figure out some of these issues myself. I have to
try
first. If I continue to have problems, I will of course follow your
advice
(you and Beetle).

Bruce M:
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.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the
hard
work at the beginning so that it will seem effortless in the end. Well,
here
it is. I am willing to put in the effort but some days I am frustrated
beyond
belief.

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.

Bruce M:
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.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone could
have several people answer it. This will depend on who is assigned to the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a
room
assignment list from the Principal soon. We'll see....

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

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

Aria:
Ugh! Nooooooo...I was so sure! I checked over and over and over again and
last night I finally found it...one person, two departments. Will this
never
end?!


"Beetle" wrote:

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by
resident
guru Ken Sheridan that talks about this type of relationship (I
couldn't
possibly explain it any better). Hopefully he won't mind that I
reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot
of
information there. I had to really pay attention though and relate it to
my
situation. After reading it, I'm not as frustrated. Yes, there are 1:1
relationships here but it may be what I need. I do think that I have
sub-types. Let me work with this some more before I let you both know
which
1:1 relationships I have.

A sub-type can of course have sub-types of its own; Chris Date in one
of his
books gives the example of a type Employees with sub-type programmers,
and
sub-types of programmers, System programmers and Application
programmers.

Great...I think I have at least one situation where this is true.

As far as the primary keys are concerned, if you use an autonumber
column
you can only do so for the topmost type, e.g. you could have an
autonumber
PersonID column in the People table, but the primary key of Pastors or
other
sub-type of people must be a straightforward long integer number data
type,
not an autonumber.
To take an extreme example, in a personnel database you would not have
an Employees table and a separate table for every Job Title in the
organization; you'd be more likely to have a Positions table and an
EmployeeHistory table with EmployeeID, PositionID, DateFrom
and DateTo columns to model the many-to-many relationship between
Employees and positions.

Hey, perfect example!

Hopefully this will help you. If you still have questions, as Bruce
said it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread.

It does help. Thank you for that. I want to continue trying before I
throw
in the towel and ask for help.

Comments for both of you:
1. You two are *great* teachers who always give me homework! There is
such a
wealth of information, instructions and suggestions that you put into
your
replies that I can't post back right away; I'm studying, highlighting and
taking notes!

2. I've learned SO much from you (the collective you). I consider myself
incredibly fortunate that you responded to my post.

3. I have varying degrees of frustration and confusion. I'm confused by
some
of my own table names (yeah, I know that doesn't even begin to make sense
to
you. I'll work on more logical naming).
I'm also confusing myself by thinking to hard and long about some of
these
issues. I know the answer is right in front of me but I can't see it.

4. Like I said before, you both pan wide and then zoom in, incrementally,
to
get to the crux of a problem. I want to do that. This is starting to come
into focus a little more now. I just have to keep reminding myself that
problems I encounter now, will be be something I won't have to deal with
later after everything is set. It's frustrating but I'm trying to keep it
in
perspective.

5. See #3. I'm going to have to take a step back. I have been working on
this day and night (in between my day job). I have been thinking about
database design in my sleep (I know, I've got it bad).
6. I have taken up so much of your time already. You have moved me
forward
by leaps and bounds. I understand that there are others who need help
too.
Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have
that
fear when I posted). You have no idea how dejected I was when I first
posted,
but every question answered unraveled the knot just a little bit more.
I'm
going to take a couple of days and see if I can work out this employee
table.
When I return, I would like to get back to tblLocations, tblKeys, etc. I
hope
you will still be here, but if not, I completely understand.

I just wanted you to know how grateful I am. I'm sure this is more than
you
*ever* wanted to know about locks, keys and school "issues".

--
Aria W.


"Beetle" wrote:

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.

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by
resident
guru Ken Sheridan that talks about this type of relationship (I
couldn't
possibly explain it any better). Hopefully he won't mind that I
reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

************************************************** *

When it comes to people connected to the church in some way or other
things
get a little more complex, but not frighteningly so, as the principles
involved are quite simple. All people share certain attribute types of
course; we all have names, an address, a date of birth and so on. So
there
is an entity type People with these common attribute types, and a table
can
represent this attribute type. People with different roles may well
have
attribute types which are specific to their role, e.g. a pastor is
likely to
have attribute types which a member of his congregation would not have.
If I
can use an example from the C of E an attribute type for a C of E
priest
might be Date of Ordination. So the entity type Pastors is a sub-type
of the
entity type People. The way a sub-type is modelled in a relational
data base
is by means of a one-to-one relationship. In the case of People and
its
sub-type Pastors this would mean that there would be a table people
with a
numeric primary key PersonID (don't use names as a primary key, they
can be
duplicated), and a Pastors table also with a numeric primary key
PersonID
(you can call it PastorID if you wish, but I prefer to keep the column
names
the same). In the case of the Pastors table PersonID would also be a
foreign
key referencing the primary key of People. The people table would have
columns for the common attributes like names and address etc., the
Pastors
table would have columns only for those attribute type specific to the
Pastors entity type, e.g. Date of Ordination, but not the common
attributes
like names and address.

A sub-type can of course have sub-types of its own; Chris Date in one
of his
books gives the example of a type Employees with sub-type programmers,
and
sub-types of programmers, System programmers and Application
programmers.

As far as the primary keys are concerned, if you use an autonumber
column
you can only do so for the topmost type, e.g. you could have an
autonumber
PersonID column in the People table, but the primary key of Pastors or
other
sub-type of people must be a straightforward long integer number data
type,
not an autonumber.

When it comes to events connected with a church you need to think
carefully
about how these fit into the model. One thing you need to be careful
of is
deciding whether an event type is a sub-type of another type, or
whether the
type of an event is an attribute type of a single entity type. You
might
have a topmost Events entity type with attribute type such as Event
Date,
Location etc and a Baptisms sub-type with attribute types such as
ChildID,
FatherID, MotherID, all three referencing the People table. Another
way of
modelling it would be to have a single Events table with an attribute
type
EventType, in which a value 'Baptism' would be entered. The problem
with the
latter approach of course is that if you also have ChildID, FatherID,
MotherID columns these won't be appropriate to other types of events in
the
same table. In this case therefore a type/sub-type model is probably
the
most appropriate. In the case of other entity types, however, the
second
approach might be more suitable. To take an extreme example, in a
personnel
database you would not have an Employees table and a separate table for
every
Job Title in the organization; you'd be more likely to have a Positions
table
and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and
DateTo
columns to model the many-to-many relationship between Employees and
positions.

************************************************** **

Hopefully this will help you. If you still have questions, as Bruce
said it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread. If you need to repost, it may be helpful
if
you include some more detailed information about your Employees table
such as;

1) What different types of employees you need to track

2) Which attributes are common to *all* employees

3) Which attributes are specific to only certain employees.

--
_________

Sean Bailey


"Aria" wrote:

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

Testing...1...2...3
Hoping you're still here... I'm going to forge ahead as if you are.

I am humbled by your responses; still looking out for ways to help me. Thank
you.

My comments follow:

Bruce:
For the phone table you may want to add a time-of-day field. I gather that
updating such information is a once per
year chore,

Aria:
lol ...if only. There's never a dull moment working in a school. There are
never-ending changes.

"Beetle" wrote:
Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields.

In your defense, what you didn't know and what I failed to mention is that
we were talking about *16* empty fields per Sub record. Perhaps if I had
mentioned that in the beginning...at any rate, you provided a solution.


1)Do not use "lookup fields" in your tables.
I won't. I've been to the site and printed that info and put it in my binder
along with Crystal's tutorial (although I still don't understand a lot of
what she is talking about.) and every post I read that I think can help me.
Of course, it goes without saying that all of my highlighted, note filled
posts from the two of you are included.

4)Most developers use some sort of "naming convention" to help keep track of
things as the application grows in size.

"...as the application grows in size?" You could have knocked me over with a
feather! "...as the application grows in size." I may be naive, but I didn't
expect the application to grow; at least not until it was up and running for
awhile and we needed to revise or expand it. Most of the posts I read stated
they had 2 or 3 tables. Initially, I had 6 and then it started to grow! Part
of the reason I thought it was growing was because of the new table that you
suggested to keep me out of trouble with tblSubs. My growing application left
me feeling *very* unsettled. I thought something was wrong.
The other problem I had was somethng you mentioned earlier in your sentence
"..to help keep track of things." I was having trouble tracking what was
happening; a situation that was not helped by some of the table names I used
(too many tables with the word Employee in it) and the fact that I kept
adding notes and comments to my diagram so that I couldn't see anything. I
had spun off School Data from the employee table like we discussed, but I had
also put Emergency Info into a seperate table too. All of these stupid
mistakes were of my own doing. I kept wondering if my tables were normalized.
So, I was trying to compact it and was having trouble. But something good may
have come out of all that angst. You (both of you) hammered home the concepts
of attributes and sub-types. I think it "stuck".
Beetle, I also think relationship type may have "stuck" because you used my
own words and application as an example. I think it has... I hope it has...

I was wondering if you could take a look at what came out of that time and
tell me if I've stepped off the cliff. This is just the part for Employees.
If you're done here (as we discussed earlier), don't post back. I
understand...
************************************************** ***********************************

tblEmployees
(sfrm) tblSiteEmp (1:1)
**********
********
Inactive Yes/No
SiteEmpID (PK)
EmpID (PK) Autonumber, long integer
EmpID (FK) number, long integer
ClassID (FK to tblClassifications)
DeptsSubjectsID (FK) number, long integer
EmpTitleID (FK to tblEmpTitles)
(FK to tblDeptsSubjects)
PhoneID (FK to tblEmpTitles)
Address-txt
LN- txt
City-txt
FN-txt
State-txt
MI-txt
Emergency Info

(Hospital, Medications, Allergies etc.)

DateEntered Date/Time


DateModified Date/Time
tblPhones(sfrm) 1:M


***************
PhoneID (PK) Autonumber, long integer

HomePhone-txt
CellPhone-txt
DistrictCell-txt
DateEntered-Date/Time
DateModified-Date/Time

tblTitles 1:M
tblSubjects 1:M
*********
***********
TitlesID (PK) Autonumber, long integer
SubjectsID (PK) Autonumber, long integer
TitleName-txt
SubjectName-txt

tblClassifications 1:M
tblDepts 1:M
***************
*********
ClassID (PK) Autonumber, long integer
DeptsID (PK) Autonumber, long integer
ClassName-txt
DeptsName-txt


tblTitlesEmps M:M
tblDeptsSubjects M:M
*************
****************
TitlesEmpsID (PK) Autonumber
DeptsSubjectsID (PK) Autonumber
EmpID (FK to tblEmps)
SubjectsID (FK to tblSubjects)
TitlesID (FK to tblTitles)
DeptsID (FK to tblDepts)


*All FK keys are number, long integer

Relationships:
tblSiteEmp 1:1 tblEmps

tblPhones 1:M tblEmps

tblTitles 1:M tblTitlesEmps

tblClassifications 1:M tblEmps

tblTitlesEmps M:M tblEmps

tblSubjects 1:M tblDeptsSubjects

tblDepts 1:M tblDeptsSubjects

tblDeptsSubjects M:M tblSiteEmp
--
Aria W.


"Beetle" wrote:

I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own.

Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted).

Happy to help. At least you want to learn and do things the right way. The
posters we don't like are the ones that have already decided how they want
to do things, even though it may be wrong, and expect someone here to
explain to them how to do it the *wrong* way.

Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields. If an attribute doesn't apply to all employees, all the
time,
then it is an attribute of a sub-type, and therefore probably belongs in
another table.

Some other things to keep in mind for the future in the development cycle
of your application;

1)Do not use "lookup fields" in your tables. This basically ampunts to
a combo box, or list box, in a table. Combo/List boxes in forms are
appropriate, but they should never be used in tables. See this link
for more info;

http://www.mvps.org/access/lookupfields.htm

2)Don't use spaces or other special characters in your table or field
names. The same goes for queries, forms and reports (when you
get to that point). Having spaces, etc. in the names will only cause
you headaches later on. So instead of "Employees Table", it would
more commonly be "tblEmployees".

3)Certain words are "reserved" in Access and should not be used as
names of objects in your app. They are the names of different
properties, etc. of the application, and if you use them as, for
example, a field name it will cause problems. Two very common
examples are Name and Date. See this link for a more complete
list;

http://support.microsoft.com/kb/286335

4)Most developers use some sort of "naming convention" to help
keep track of things as the application grows in size. Everyone has
some of their own variations on naming conventions, but some things
are common. For example;

Table names commonly start with tbl
Queries start with qry (or something similar)
Forms start with frm, Sub forms start with fsub, sfrm, etc.
Reports start with rpt
A text box (on a form or report) would start with txt
A combo box (same as above) would start with cbo
etc.

5)Continuing with the naming theme. When you get to the point
where you start designing forms and reports, you will notice that when
you add a control (text box, combo box, etc.) to a form/report, Access
will give will give it a completely useless name like "Text52" or
"Combo38". Do yourself a favor and name them something meaningful
like "txtEmployeePhone" or "cboSelectASubject".

Good luck and post back when you have more questions.

--
_________

Sean Bailey


"Aria" wrote:

First, let me say,"WOW"! You both have given me *so much* information. Thank
you! My comments are below (I'm combining posts. I didn't know how else to do
this):
Bruce M:
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.

Aria:
Do I need a subform if the majority of the staff only hold one title? Yes, I
still trying to get over having empty fields but I haven't made it this far
so I don't know. The form I envision would have this info on the main form
using a combobox for the titles. I am trying to think of every possible title
that I can. I don't want any suprises here.

Bruce M:

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).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do the
junction table.

Bruce M:
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.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I know
I need to learn how to figure out some of these issues myself. I have to try
first. If I continue to have problems, I will of course follow your advice
(you and Beetle).

Bruce M:
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.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the hard
work at the beginning so that it will seem effortless in the end. Well, here
it is. I am willing to put in the effort but some days I am frustrated beyond
belief.

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.

Bruce M:
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.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone could
have several people answer it. This will depend on who is assigned to the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a room
assignment list from the Principal soon. We'll see....

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

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

Aria:
Ugh! Nooooooo...I was so sure! I checked over and over and over again and
last night I finally found it...one person, two departments. Will this never
end?!


"Beetle" wrote:

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of
information there. I had to really pay attention though and relate it to my
situation. After reading it, I'm not as frustrated. Yes, there are 1:1
relationships here but it may be what I need. I do think that I have
sub-types. Let me work with this some more before I let you both know which
1:1 relationships I have.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

Great...I think I have at least one situation where this is true.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.
To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions.

Hey, perfect example!

Hopefully this will help you. If you still have questions, as Bruce said it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread.

It does help. Thank you for that. I want to continue trying before I throw
in the towel and ask for help.

Comments for both of you:
1. You two are *great* teachers who always give me homework! There is such a
wealth of information, instructions and suggestions that you put into your
replies that I can't post back right away; I'm studying, highlighting and
taking notes!

2. I've learned SO much from you (the collective you). I consider myself
incredibly fortunate that you responded to my post.

3. I have varying degrees of frustration and confusion. I'm confused by some
of my own table names (yeah, I know that doesn't even begin to make sense to
you. I'll work on more logical naming).
I'm also confusing myself by thinking to hard and long about some of these
issues. I know the answer is right in front of me but I can't see it.

4. Like I said before, you both pan wide and then zoom in, incrementally, to
get to the crux of a problem. I want to do that. This is starting to come
into focus a little more now. I just have to keep reminding myself that
problems I encounter now, will be be something I won't have to deal with
later after everything is set. It's frustrating but I'm trying to keep it in
perspective.

5. See #3. I'm going to have to take a step back. I have been working on
this day and night (in between my day job). I have been thinking about
database design in my sleep (I know, I've got it bad).
6. I have taken up so much of your time already. You have moved me forward
by leaps and bounds. I understand that there are others who need help too.
Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted). You have no idea how dejected I was when I first posted,
but every question answered unraveled the knot just a little bit more. I'm
going to take a couple of days and see if I can work out this employee table.
When I return, I would like to get back to tblLocations, tblKeys, etc. I hope
you will still be here, but if not, I completely understand.

I just wanted you to know how grateful I am. I'm sure this is more than you
*ever* wanted to know about locks, keys and school "issues".

--
Aria W.


"Beetle" wrote:

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.

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

************************************************** *

When it comes to people connected to the church in some way or other things
get a little more complex, but not frighteningly so, as the principles
involved are quite simple. All people share certain attribute types of
course; we all have names, an address, a date of birth and so on. So there
is an entity type People with these common attribute types, and a table can
represent this attribute type. People with different roles may well have
attribute types which are specific to their role, e.g. a pastor is likely to
have attribute types which a member of his congregation would not have. If I
can use an example from the C of E an attribute type for a C of E priest
might be Date of Ordination. So the entity type Pastors is a sub-type of the
entity type People. The way a sub-type is modelled in a relational data base
is by means of a one-to-one relationship. In the case of People and its
sub-type Pastors this would mean that there would be a table people with a
numeric primary key PersonID (don't use names as a primary key, they can be
duplicated), and a Pastors table also with a numeric primary key PersonID
(you can call it PastorID if you wish, but I prefer to keep the column names
the same). In the case of the Pastors table PersonID would also be a foreign
key referencing the primary key of People. The people table would have
columns for the common attributes like names and address etc., the Pastors
table would have columns only for those attribute type specific to the
Pastors entity type, e.g. Date of Ordination, but not the common attributes
like names and address.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.

Well, I learned something new. Let's try this again so that's it's readable.

************************************************** ***********************************

tblEmployees

**********

Inactive Yes/No

EmpID (PK) Autonumber, long integer

ClassID (FK to tblClassifications)

EmpTitleID (FK to tblEmpTitles)

PhoneID (FK to tblEmpTitles)

LN- txt

FN-txt

MI-txt



(sfrm) tblSiteEmp (1:1)
SiteEmpID (PK)

EmpID (FK) number, long integer
DeptsSubjectsID (FK) number, long integer
Address-txt
City-txt
State-txt
Emergency Info(Hospital, Medications, Allergies etc.)
DateEntered Date/Time
DateModified Date/Time


tblPhones(sfrm) 1:M


***************
PhoneID (PK) Autonumber, long integer

HomePhone-txt
CellPhone-txt
DistrictCell-txt
DateEntered-Date/Time
DateModified-Date/Time

tblTitles 1:M

*********

TitlesID (PK) Autonumber, long integer
TitleName-txt

tblSubjects 1:M
***********

SubjectsID (PK) Autonumber, long integer
SubjectName-txt


tblDepts 1:M
*********
DeptsID (PK) Autonumber, long integer
DeptsName-txt

tblDeptsSubjects M:M
************
DeptsSubjectsID (PK) Autonumber
SubjectsID (FK to tblSubjects)
DeptsID (FK to tblDepts)


tblClassifications 1:M

***************

ClassID (PK) Autonumber, long integer

ClassName-txt



tblTitlesEmps M:M
*************


TitlesEmpsID (PK) Autonumber

EmpID (FK to tblEmps)

TitlesID (FK to tblTitles)



*All FK keys are number, long integer

Relationships:
tblSiteEmp 1:1 tblEmps

tblPhones 1:M tblEmps

tblTitles 1:M tblTitlesEmps

tblClassifications 1:M tblEmps

tblTitlesEmps M:M tblEmps

tblSubjects 1:M tblDeptsSubjects

tblDepts 1:M tblDeptsSubjects

tblDeptsSubjects M:M tblSiteEmp
--
Aria W.


"Beetle" wrote:

I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own.

Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted).

Happy to help. At least you want to learn and do things the right way. The
posters we don't like are the ones that have already decided how they want
to do things, even though it may be wrong, and expect someone here to
explain to them how to do it the *wrong* way.

Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields. If an attribute doesn't apply to all employees, all the
time,
then it is an attribute of a sub-type, and therefore probably belongs in
another table.

Some other things to keep in mind for the future in the development cycle
of your application;

1)Do not use "lookup fields" in your tables. This basically ampunts to
a combo box, or list box, in a table. Combo/List boxes in forms are
appropriate, but they should never be used in tables. See this link
for more info;

http://www.mvps.org/access/lookupfields.htm

2)Don't use spaces or other special characters in your table or field
names. The same goes for queries, forms and reports (when you
get to that point). Having spaces, etc. in the names will only cause
you headaches later on. So instead of "Employees Table", it would
more commonly be "tblEmployees".

3)Certain words are "reserved" in Access and should not be used as
names of objects in your app. They are the names of different
properties, etc. of the application, and if you use them as, for
example, a field name it will cause problems. Two very common
examples are Name and Date. See this link for a more complete
list;

http://support.microsoft.com/kb/286335

4)Most developers use some sort of "naming convention" to help
keep track of things as the application grows in size. Everyone has
some of their own variations on naming conventions, but some things
are common. For example;

Table names commonly start with tbl
Queries start with qry (or something similar)
Forms start with frm, Sub forms start with fsub, sfrm, etc.
Reports start with rpt
A text box (on a form or report) would start with txt
A combo box (same as above) would start with cbo
etc.

5)Continuing with the naming theme. When you get to the point
where you start designing forms and reports, you will notice that when
you add a control (text box, combo box, etc.) to a form/report, Access
will give will give it a completely useless name like "Text52" or
"Combo38". Do yourself a favor and name them something meaningful
like "txtEmployeePhone" or "cboSelectASubject".

Good luck and post back when you have more questions.

--
_________

Sean Bailey


"Aria" wrote:

First, let me say,"WOW"! You both have given me *so much* information. Thank
you! My comments are below (I'm combining posts. I didn't know how else to do
this):
Bruce M:
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.

Aria:
Do I need a subform if the majority of the staff only hold one title? Yes, I
still trying to get over having empty fields but I haven't made it this far
so I don't know. The form I envision would have this info on the main form
using a combobox for the titles. I am trying to think of every possible title
that I can. I don't want any suprises here.

Bruce M:

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).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do the
junction table.

Bruce M:
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.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I know
I need to learn how to figure out some of these issues myself. I have to try
first. If I continue to have problems, I will of course follow your advice
(you and Beetle).

Bruce M:
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.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the hard
work at the beginning so that it will seem effortless in the end. Well, here
it is. I am willing to put in the effort but some days I am frustrated beyond
belief.

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.

Bruce M:
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.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone could
have several people answer it. This will depend on who is assigned to the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a room
assignment list from the Principal soon. We'll see....

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

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

Aria:
Ugh! Nooooooo...I was so sure! I checked over and over and over again and
last night I finally found it...one person, two departments. Will this never
end?!


"Beetle" wrote:

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of
information there. I had to really pay attention though and relate it to my
situation. After reading it, I'm not as frustrated. Yes, there are 1:1
relationships here but it may be what I need. I do think that I have
sub-types. Let me work with this some more before I let you both know which
1:1 relationships I have.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

Great...I think I have at least one situation where this is true.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.
To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions.

Hey, perfect example!

Hopefully this will help you. If you still have questions, as Bruce said it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread.

It does help. Thank you for that. I want to continue trying before I throw
in the towel and ask for help.

Comments for both of you:
1. You two are *great* teachers who always give me homework! There is such a
wealth of information, instructions and suggestions that you put into your
replies that I can't post back right away; I'm studying, highlighting and
taking notes!

2. I've learned SO much from you (the collective you). I consider myself
incredibly fortunate that you responded to my post.

3. I have varying degrees of frustration and confusion. I'm confused by some
of my own table names (yeah, I know that doesn't even begin to make sense to
you. I'll work on more logical naming).
I'm also confusing myself by thinking to hard and long about some of these
issues. I know the answer is right in front of me but I can't see it.

4. Like I said before, you both pan wide and then zoom in, incrementally, to
get to the crux of a problem. I want to do that. This is starting to come
into focus a little more now. I just have to keep reminding myself that
problems I encounter now, will be be something I won't have to deal with
later after everything is set. It's frustrating but I'm trying to keep it in
perspective.

5. See #3. I'm going to have to take a step back. I have been working on
this day and night (in between my day job). I have been thinking about
database design in my sleep (I know, I've got it bad).
6. I have taken up so much of your time already. You have moved me forward
by leaps and bounds. I understand that there are others who need help too.
Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted). You have no idea how dejected I was when I first posted,
but every question answered unraveled the knot just a little bit more. I'm
going to take a couple of days and see if I can work out this employee table.
When I return, I would like to get back to tblLocations, tblKeys, etc. I hope
you will still be here, but if not, I completely understand.

I just wanted you to know how grateful I am. I'm sure this is more than you
*ever* wanted to know about locks, keys and school "issues".

--
Aria W.


"Beetle" wrote:

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.

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

************************************************** *

When it comes to people connected to the church in some way or other things
get a little more complex, but not frighteningly so, as the principles
involved are quite simple. All people share certain attribute types of
course; we all have names, an address, a date of birth and so on. So there
is an entity type People with these common attribute types, and a table can
represent this attribute type. People with different roles may well have
attribute types which are specific to their role, e.g. a pastor is likely to
have attribute types which a member of his congregation would not have. If I
can use an example from the C of E an attribute type for a C of E priest
might be Date of Ordination. So the entity type Pastors is a sub-type of the
entity type People. The way a sub-type is modelled in a relational data base
is by means of a one-to-one relationship. In the case of People and its
sub-type Pastors this would mean that there would be a table people with a
numeric primary key PersonID (don't use names as a primary key, they can be
duplicated), and a Pastors table also with a numeric primary key PersonID
(you can call it PastorID if you wish, but I prefer to keep the column names
the same). In the case of the Pastors table PersonID would also be a foreign
key referencing the primary key of People. The people table would have
columns for the common attributes like names and address etc., the Pastors
table would have columns only for those attribute type specific to the
Pastors entity type, e.g. Date of Ordination, but not the common attributes
like names and address.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.

Responses inline.

"Aria" wrote in message
...
Well, I learned something new. Let's try this again so that's it's
readable.

************************************************** ***********************************

tblEmployees

**********

Inactive Yes/No

EmpID (PK) Autonumber, long integer

ClassID (FK to tblClassifications)

EmpTitleID (FK to tblEmpTitles)

PhoneID (FK to tblEmpTitles)

LN- txt

FN-txt

MI-txt

If one employee can have several classifications, EmpID is a FK in
tblClassifications. If a classification can be used for several employees
but each employee can have only one classification, your approach may be the
correct one. If an employee can have several classifications and vice
versa, a junction table is indicated. The same would apply to tblEmpTitles.
I'm not following how PhoneID is a FK to tblEmpTitles. If one employee may
have several phones, that is one-to-many. If a phone may be used by several
people depending on when they are in a particular room, the phone is an
attribute of the room, in a sense. On the other hand, presumably some
phones are for offices used by just one person. In that case the phone
number would probably go with the person if they move to another office, so
the phone is an attribute of the person rather than the room. Cell phones
cannot be considered attributes of a room.
Ordinarily I would have a one employee several phones situation. Each
phone would be associated with a single employee. The Phone table could
have a PhoneLocation field, and maybe a TimeOfDay field for phones in shared
rooms. Frankly, I would be inclined to go with the same approach for now.
If you enter the same number for several different employees because they
are in the same room at different times, so be it. Anything else that
incorporates private phones and shared phones may be needlessly complex for
now. If you are determined to make tblEmployee as flexible as you can right
away we may be able to come up with a strategy, but this may be bogging you
down in the short term if you are trying to keep track of keys.



(sfrm) tblSiteEmp (1:1)
SiteEmpID (PK)

EmpID (FK) number, long integer
DeptsSubjectsID (FK) number, long integer
Address-txt
City-txt
State-txt
Emergency Info(Hospital, Medications, Allergies etc.)
DateEntered Date/Time
DateModified Date/Time

I think Address information should be in the main employee table, unless
each employee could have several addresses (a summner address and a school
year address, for instance). In that case, a related Address table is
needed.
Emergency information maybe should be in its own table, 1:M from
tblEmployees.

I'm afraid that's all I have time to write now. I may not be available much
next week, but I'll check in with this thread if I can.



tblPhones(sfrm) 1:M


***************
PhoneID (PK) Autonumber, long integer

HomePhone-txt
CellPhone-txt
DistrictCell-txt
DateEntered-Date/Time
DateModified-Date/Time

tblTitles 1:M

*********

TitlesID (PK) Autonumber, long integer
TitleName-txt

tblSubjects 1:M
***********

SubjectsID (PK) Autonumber, long integer
SubjectName-txt


tblDepts 1:M
*********
DeptsID (PK) Autonumber, long integer
DeptsName-txt

tblDeptsSubjects M:M
************
DeptsSubjectsID (PK) Autonumber
SubjectsID (FK to tblSubjects)
DeptsID (FK to tblDepts)


tblClassifications 1:M

***************

ClassID (PK) Autonumber, long integer

ClassName-txt



tblTitlesEmps M:M
*************


TitlesEmpsID (PK) Autonumber

EmpID (FK to tblEmps)

TitlesID (FK to tblTitles)



*All FK keys are number, long integer

Relationships:
tblSiteEmp 1:1 tblEmps

tblPhones 1:M tblEmps

tblTitles 1:M tblTitlesEmps

tblClassifications 1:M tblEmps

tblTitlesEmps M:M tblEmps

tblSubjects 1:M tblDeptsSubjects

tblDepts 1:M tblDeptsSubjects

tblDeptsSubjects M:M tblSiteEmp
--
Aria W.


"Beetle" wrote:

I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own.

Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have
both
been so kind and patient and have not yelled at me once (yes, I did
have that
fear when I posted).

Happy to help. At least you want to learn and do things the right way.
The
posters we don't like are the ones that have already decided how they
want
to do things, even though it may be wrong, and expect someone here to
explain to them how to do it the *wrong* way.

Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of
empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields. If an attribute doesn't apply to all employees, all the
time,
then it is an attribute of a sub-type, and therefore probably belongs in
another table.

Some other things to keep in mind for the future in the development cycle
of your application;

1)Do not use "lookup fields" in your tables. This basically ampunts to
a combo box, or list box, in a table. Combo/List boxes in forms are
appropriate, but they should never be used in tables. See this link
for more info;

http://www.mvps.org/access/lookupfields.htm

2)Don't use spaces or other special characters in your table or field
names. The same goes for queries, forms and reports (when you
get to that point). Having spaces, etc. in the names will only cause
you headaches later on. So instead of "Employees Table", it would
more commonly be "tblEmployees".

3)Certain words are "reserved" in Access and should not be used as
names of objects in your app. They are the names of different
properties, etc. of the application, and if you use them as, for
example, a field name it will cause problems. Two very common
examples are Name and Date. See this link for a more complete
list;

http://support.microsoft.com/kb/286335

4)Most developers use some sort of "naming convention" to help
keep track of things as the application grows in size. Everyone has
some of their own variations on naming conventions, but some things
are common. For example;

Table names commonly start with tbl
Queries start with qry (or something similar)
Forms start with frm, Sub forms start with fsub, sfrm, etc.
Reports start with rpt
A text box (on a form or report) would start with txt
A combo box (same as above) would start with cbo
etc.

5)Continuing with the naming theme. When you get to the point
where you start designing forms and reports, you will notice that when
you add a control (text box, combo box, etc.) to a form/report, Access
will give will give it a completely useless name like "Text52" or
"Combo38". Do yourself a favor and name them something meaningful
like "txtEmployeePhone" or "cboSelectASubject".

Good luck and post back when you have more questions.

--
_________

Sean Bailey


"Aria" wrote:

First, let me say,"WOW"! You both have given me *so much* information.
Thank
you! My comments are below (I'm combining posts. I didn't know how else
to do
this):
Bruce M:
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.

Aria:
Do I need a subform if the majority of the staff only hold one title?
Yes, I
still trying to get over having empty fields but I haven't made it this
far
so I don't know. The form I envision would have this info on the main
form
using a combobox for the titles. I am trying to think of every possible
title
that I can. I don't want any suprises here.

Bruce M:

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).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do
the
junction table.

Bruce M:
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.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I
know
I need to learn how to figure out some of these issues myself. I have
to try
first. If I continue to have problems, I will of course follow your
advice
(you and Beetle).

Bruce M:
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.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the
hard
work at the beginning so that it will seem effortless in the end. Well,
here
it is. I am willing to put in the effort but some days I am frustrated
beyond
belief.

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.

Bruce M:
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.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone
could
have several people answer it. This will depend on who is assigned to
the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a
room
assignment list from the Principal soon. We'll see....

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

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

Aria:
Ugh! Nooooooo...I was so sure! I checked over and over and over again
and
last night I finally found it...one person, two departments. Will this
never
end?!


"Beetle" wrote:

One-to-One relationships are less common than 1:m and m:m, but in
your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by
resident
guru Ken Sheridan that talks about this type of relationship (I
couldn't
possibly explain it any better). Hopefully he won't mind that I
reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the
concept
may be helpful in your situation.

Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot
of
information there. I had to really pay attention though and relate it
to my
situation. After reading it, I'm not as frustrated. Yes, there are 1:1
relationships here but it may be what I need. I do think that I have
sub-types. Let me work with this some more before I let you both know
which
1:1 relationships I have.

A sub-type can of course have sub-types of its own; Chris Date in one
of his
books gives the example of a type Employees with sub-type
programmers, and
sub-types of programmers, System programmers and Application
programmers.

Great...I think I have at least one situation where this is true.

As far as the primary keys are concerned, if you use an autonumber
column
you can only do so for the topmost type, e.g. you could have an
autonumber
PersonID column in the People table, but the primary key of Pastors
or other
sub-type of people must be a straightforward long integer number data
type,
not an autonumber.
To take an extreme example, in a personnel database you would not
have an Employees table and a separate table for every Job Title in
the organization; you'd be more likely to have a Positions table
and an EmployeeHistory table with EmployeeID, PositionID,
DateFrom and DateTo columns to model the many-to-many relationship
between Employees and positions.

Hey, perfect example!

Hopefully this will help you. If you still have questions, as Bruce
said it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread.

It does help. Thank you for that. I want to continue trying before I
throw
in the towel and ask for help.

Comments for both of you:
1. You two are *great* teachers who always give me homework! There is
such a
wealth of information, instructions and suggestions that you put into
your
replies that I can't post back right away; I'm studying, highlighting
and
taking notes!

2. I've learned SO much from you (the collective you). I consider
myself
incredibly fortunate that you responded to my post.

3. I have varying degrees of frustration and confusion. I'm confused by
some
of my own table names (yeah, I know that doesn't even begin to make
sense to
you. I'll work on more logical naming).
I'm also confusing myself by thinking to hard and long about some of
these
issues. I know the answer is right in front of me but I can't see it.

4. Like I said before, you both pan wide and then zoom in,
incrementally, to
get to the crux of a problem. I want to do that. This is starting to
come
into focus a little more now. I just have to keep reminding myself that
problems I encounter now, will be be something I won't have to deal
with
later after everything is set. It's frustrating but I'm trying to keep
it in
perspective.

5. See #3. I'm going to have to take a step back. I have been working
on
this day and night (in between my day job). I have been thinking about
database design in my sleep (I know, I've got it bad).
6. I have taken up so much of your time already. You have moved me
forward
by leaps and bounds. I understand that there are others who need help
too.
Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have
both
been so kind and patient and have not yelled at me once (yes, I did
have that
fear when I posted). You have no idea how dejected I was when I first
posted,
but every question answered unraveled the knot just a little bit more.
I'm
going to take a couple of days and see if I can work out this employee
table.
When I return, I would like to get back to tblLocations, tblKeys, etc.
I hope
you will still be here, but if not, I completely understand.

I just wanted you to know how grateful I am. I'm sure this is more than
you
*ever* wanted to know about locks, keys and school "issues".

--
Aria W.


"Beetle" wrote:

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.

One-to-One relationships are less common than 1:m and m:m, but in
your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by
resident
guru Ken Sheridan that talks about this type of relationship (I
couldn't
possibly explain it any better). Hopefully he won't mind that I
reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the
concept
may be helpful in your situation.

************************************************** *

When it comes to people connected to the church in some way or other
things
get a little more complex, but not frighteningly so, as the
principles
involved are quite simple. All people share certain attribute types
of
course; we all have names, an address, a date of birth and so on.
So there
is an entity type People with these common attribute types, and a
table can
represent this attribute type. People with different roles may well
have
attribute types which are specific to their role, e.g. a pastor is
likely to
have attribute types which a member of his congregation would not
have. If I
can use an example from the C of E an attribute type for a C of E
priest
might be Date of Ordination. So the entity type Pastors is a
sub-type of the
entity type People. The way a sub-type is modelled in a relational
data base
is by means of a one-to-one relationship. In the case of People and
its
sub-type Pastors this would mean that there would be a table people
with a
numeric primary key PersonID (don't use names as a primary key, they
can be
duplicated), and a Pastors table also with a numeric primary key
PersonID
(you can call it PastorID if you wish, but I prefer to keep the
column names
the same). In the case of the Pastors table PersonID would also be a
foreign
key referencing the primary key of People. The people table would
have
columns for the common attributes like names and address etc., the
Pastors
table would have columns only for those attribute type specific to
the
Pastors entity type, e.g. Date of Ordination, but not the common
attributes
like names and address.

A sub-type can of course have sub-types of its own; Chris Date in one
of his
books gives the example of a type Employees with sub-type
programmers, and
sub-types of programmers, System programmers and Application
programmers.

As far as the primary keys are concerned, if you use an autonumber
column
you can only do so for the topmost type, e.g. you could have an
autonumber
PersonID column in the People table, but the primary key of Pastors
or other
sub-type of people must be a straightforward long integer number data
type,
not an autonumber.

I'm sorry. I messed it up. Sorry Bruce. Here is the revised version. Have a
great weekend!

tblEmployees
**********
Inactive Yes/No

EmpID (PK) Autonumber, long integer

ClassID (FK to tblClassifications)

EmpTitleID (FK to tblEmpTitles)

PhoneID (FK to tblPhones)

LN- txt

FN-txt

MI-txt



(sfrm) tblSiteEmp (1:1)
************
SiteEmpID (PK)
EmpID (FK) number, long integer
DeptsSubjectsID (FK) number, long integer
Address-txt
City-txt
State-txt
Emergency Info(Hospital, Medications, Allergies etc.)
DateEntered Date/Time
DateModified Date/Time


tblPhones (sfrm) 1:M
***************
PhoneID (PK) Autonumber, long integer
HomePhone-txt
CellPhone-txt
DistrictCell-txt
DateEntered-Date/Time
DateModified-Date/Time


tblTitles 1:M
*********

TitlesID (PK) Autonumber, long integer
TitleName-txt

tblSubjects 1:M
***********
SubjectsID (PK) Autonumber, long integer
SubjectName-txt


tblDepts 1:M
*********
DeptsID (PK) Autonumber, long integer
DeptsName-txt

tblDeptsSubjects M:M
************
DeptsSubjectsID (PK) Autonumber
SubjectsID (FK to tblSubjects)
DeptsID (FK to tblDepts)


tblClassifications 1:M
***************
ClassID (PK) Autonumber, long integer

ClassName-txt



tblTitlesEmps M:M
*************
TitlesEmpsID (PK) Autonumber

EmpID (FK to tblEmps)

TitlesID (FK to tblTitles)



*All FK keys are number, long integer

Relationships:
tblSiteEmp 1:1 tblEmps

tblPhones 1:M tblEmps

tblTitles 1:M tblTitlesEmps

tblClassifications 1:M tblEmps

tblTitlesEmps M:M tblEmps

tblSubjects 1:M tblDeptsSubjects

tblDepts 1:M tblDeptsSubjects

tblDeptsSubjects M:M tblSiteEmp
--
Aria W.


"Beetle" wrote:

I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own.

Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted).

Happy to help. At least you want to learn and do things the right way. The
posters we don't like are the ones that have already decided how they want
to do things, even though it may be wrong, and expect someone here to
explain to them how to do it the *wrong* way.

Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields. If an attribute doesn't apply to all employees, all the
time,
then it is an attribute of a sub-type, and therefore probably belongs in
another table.

Some other things to keep in mind for the future in the development cycle
of your application;

1)Do not use "lookup fields" in your tables. This basically ampunts to
a combo box, or list box, in a table. Combo/List boxes in forms are
appropriate, but they should never be used in tables. See this link
for more info;

http://www.mvps.org/access/lookupfields.htm

2)Don't use spaces or other special characters in your table or field
names. The same goes for queries, forms and reports (when you
get to that point). Having spaces, etc. in the names will only cause
you headaches later on. So instead of "Employees Table", it would
more commonly be "tblEmployees".

3)Certain words are "reserved" in Access and should not be used as
names of objects in your app. They are the names of different
properties, etc. of the application, and if you use them as, for
example, a field name it will cause problems. Two very common
examples are Name and Date. See this link for a more complete
list;

http://support.microsoft.com/kb/286335

4)Most developers use some sort of "naming convention" to help
keep track of things as the application grows in size. Everyone has
some of their own variations on naming conventions, but some things
are common. For example;

Table names commonly start with tbl
Queries start with qry (or something similar)
Forms start with frm, Sub forms start with fsub, sfrm, etc.
Reports start with rpt
A text box (on a form or report) would start with txt
A combo box (same as above) would start with cbo
etc.

5)Continuing with the naming theme. When you get to the point
where you start designing forms and reports, you will notice that when
you add a control (text box, combo box, etc.) to a form/report, Access
will give will give it a completely useless name like "Text52" or
"Combo38". Do yourself a favor and name them something meaningful
like "txtEmployeePhone" or "cboSelectASubject".

Good luck and post back when you have more questions.

--
_________

Sean Bailey


"Aria" wrote:

First, let me say,"WOW"! You both have given me *so much* information. Thank
you! My comments are below (I'm combining posts. I didn't know how else to do
this):
Bruce M:
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.

Aria:
Do I need a subform if the majority of the staff only hold one title? Yes, I
still trying to get over having empty fields but I haven't made it this far
so I don't know. The form I envision would have this info on the main form
using a combobox for the titles. I am trying to think of every possible title
that I can. I don't want any suprises here.

Bruce M:

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).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do the
junction table.

Bruce M:
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.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I know
I need to learn how to figure out some of these issues myself. I have to try
first. If I continue to have problems, I will of course follow your advice
(you and Beetle).

Bruce M:
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.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the hard
work at the beginning so that it will seem effortless in the end. Well, here
it is. I am willing to put in the effort but some days I am frustrated beyond
belief.

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.

Bruce M:
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.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone could
have several people answer it. This will depend on who is assigned to the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a room
assignment list from the Principal soon. We'll see....

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

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

Aria:
Ugh! Nooooooo...I was so sure! I checked over and over and over again and
last night I finally found it...one person, two departments. Will this never
end?!


"Beetle" wrote:

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of
information there. I had to really pay attention though and relate it to my
situation. After reading it, I'm not as frustrated. Yes, there are 1:1
relationships here but it may be what I need. I do think that I have
sub-types. Let me work with this some more before I let you both know which
1:1 relationships I have.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

Great...I think I have at least one situation where this is true.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.
To take an extreme example, in a personnel database you would not have an Employees table and a separate table for every Job Title in the organization; you'd be more likely to have a Positions table and an EmployeeHistory table with EmployeeID, PositionID, DateFrom and DateTo columns to model the many-to-many relationship between Employees and positions.

Hey, perfect example!

Hopefully this will help you. If you still have questions, as Bruce said it
may be helpbul to start a new thread. He and I may be the only ones
still following this thread.

It does help. Thank you for that. I want to continue trying before I throw
in the towel and ask for help.

Comments for both of you:
1. You two are *great* teachers who always give me homework! There is such a
wealth of information, instructions and suggestions that you put into your
replies that I can't post back right away; I'm studying, highlighting and
taking notes!

2. I've learned SO much from you (the collective you). I consider myself
incredibly fortunate that you responded to my post.

3. I have varying degrees of frustration and confusion. I'm confused by some
of my own table names (yeah, I know that doesn't even begin to make sense to
you. I'll work on more logical naming).
I'm also confusing myself by thinking to hard and long about some of these
issues. I know the answer is right in front of me but I can't see it.

4. Like I said before, you both pan wide and then zoom in, incrementally, to
get to the crux of a problem. I want to do that. This is starting to come
into focus a little more now. I just have to keep reminding myself that
problems I encounter now, will be be something I won't have to deal with
later after everything is set. It's frustrating but I'm trying to keep it in
perspective.

5. See #3. I'm going to have to take a step back. I have been working on
this day and night (in between my day job). I have been thinking about
database design in my sleep (I know, I've got it bad).
6. I have taken up so much of your time already. You have moved me forward
by leaps and bounds. I understand that there are others who need help too.
Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted). You have no idea how dejected I was when I first posted,
but every question answered unraveled the knot just a little bit more. I'm
going to take a couple of days and see if I can work out this employee table.
When I return, I would like to get back to tblLocations, tblKeys, etc. I hope
you will still be here, but if not, I completely understand.

I just wanted you to know how grateful I am. I'm sure this is more than you
*ever* wanted to know about locks, keys and school "issues".

--
Aria W.


"Beetle" wrote:

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.

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

************************************************** *

When it comes to people connected to the church in some way or other things
get a little more complex, but not frighteningly so, as the principles
involved are quite simple. All people share certain attribute types of
course; we all have names, an address, a date of birth and so on. So there
is an entity type People with these common attribute types, and a table can
represent this attribute type. People with different roles may well have
attribute types which are specific to their role, e.g. a pastor is likely to
have attribute types which a member of his congregation would not have. If I
can use an example from the C of E an attribute type for a C of E priest
might be Date of Ordination. So the entity type Pastors is a sub-type of the
entity type People. The way a sub-type is modelled in a relational data base
is by means of a one-to-one relationship. In the case of People and its
sub-type Pastors this would mean that there would be a table people with a
numeric primary key PersonID (don't use names as a primary key, they can be
duplicated), and a Pastors table also with a numeric primary key PersonID
(you can call it PastorID if you wish, but I prefer to keep the column names
the same). In the case of the Pastors table PersonID would also be a foreign
key referencing the primary key of People. The people table would have
columns for the common attributes like names and address etc., the Pastors
table would have columns only for those attribute type specific to the
Pastors entity type, e.g. Date of Ordination, but not the common attributes
like names and address.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

As far as the primary keys are concerned, if you use an autonumber column
you can only do so for the topmost type, e.g. you could have an autonumber
PersonID column in the People table, but the primary key of Pastors or other
sub-type of people must be a straightforward long integer number data type,
not an autonumber.

I have printed your post and will look it over, but I do have a few questions.

1) Can an employee have more than one Classification, or can a
Classification have more than one employee, or both?

2) Same as above but for Titles. You appear to have it set up as m:m
but I just want to clarify.

3) You have set up a m:m relationship between Departments and Subjects
which I don't quite understand. For example, the Mathematics dept.
would obviously have more than one Subject, but Trigonometry would
only be part of one dept. (Mathematics). I must be missing something
about what you are tracking here.

4) You appear to have no relationship between an employee and the dept.
they work in or the subject they teach (at least not that I can see).

There are some other issues also, but I'll cover those after I have a better
understanding of the above questions.
--
_________

Sean Bailey


"Aria" wrote:

I'm sorry. I messed it up. Sorry Bruce. Here is the revised version. Have a
great weekend!

tblEmployees
**********
Inactive Yes/No

EmpID (PK) Autonumber, long integer

ClassID (FK to tblClassifications)

EmpTitleID (FK to tblEmpTitles)

PhoneID (FK to tblPhones)

LN- txt

FN-txt

MI-txt



(sfrm) tblSiteEmp (1:1)
************
SiteEmpID (PK)
EmpID (FK) number, long integer
DeptsSubjectsID (FK) number, long integer
Address-txt
City-txt
State-txt
Emergency Info(Hospital, Medications, Allergies etc.)
DateEntered Date/Time
DateModified Date/Time


tblPhones (sfrm) 1:M
***************
PhoneID (PK) Autonumber, long integer
HomePhone-txt
CellPhone-txt
DistrictCell-txt
DateEntered-Date/Time
DateModified-Date/Time


tblTitles 1:M
*********

TitlesID (PK) Autonumber, long integer
TitleName-txt

tblSubjects 1:M
***********
SubjectsID (PK) Autonumber, long integer
SubjectName-txt


tblDepts 1:M
*********
DeptsID (PK) Autonumber, long integer
DeptsName-txt

tblDeptsSubjects M:M
************
DeptsSubjectsID (PK) Autonumber
SubjectsID (FK to tblSubjects)
DeptsID (FK to tblDepts)


tblClassifications 1:M
***************
ClassID (PK) Autonumber, long integer

ClassName-txt



tblTitlesEmps M:M
*************
TitlesEmpsID (PK) Autonumber

EmpID (FK to tblEmps)

TitlesID (FK to tblTitles)



*All FK keys are number, long integer

Relationships:
tblSiteEmp 1:1 tblEmps

tblPhones 1:M tblEmps

tblTitles 1:M tblTitlesEmps

tblClassifications 1:M tblEmps

tblTitlesEmps M:M tblEmps

tblSubjects 1:M tblDeptsSubjects

tblDepts 1:M tblDeptsSubjects

tblDeptsSubjects M:M tblSiteEmp
--
Aria W.


"Beetle" wrote:

I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own.

Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted).

Happy to help. At least you want to learn and do things the right way. The
posters we don't like are the ones that have already decided how they want
to do things, even though it may be wrong, and expect someone here to
explain to them how to do it the *wrong* way.

Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields. If an attribute doesn't apply to all employees, all the
time,
then it is an attribute of a sub-type, and therefore probably belongs in
another table.

Some other things to keep in mind for the future in the development cycle
of your application;

1)Do not use "lookup fields" in your tables. This basically ampunts to
a combo box, or list box, in a table. Combo/List boxes in forms are
appropriate, but they should never be used in tables. See this link
for more info;

http://www.mvps.org/access/lookupfields.htm

2)Don't use spaces or other special characters in your table or field
names. The same goes for queries, forms and reports (when you
get to that point). Having spaces, etc. in the names will only cause
you headaches later on. So instead of "Employees Table", it would
more commonly be "tblEmployees".

3)Certain words are "reserved" in Access and should not be used as
names of objects in your app. They are the names of different
properties, etc. of the application, and if you use them as, for
example, a field name it will cause problems. Two very common
examples are Name and Date. See this link for a more complete
list;

http://support.microsoft.com/kb/286335

4)Most developers use some sort of "naming convention" to help
keep track of things as the application grows in size. Everyone has
some of their own variations on naming conventions, but some things
are common. For example;

Table names commonly start with tbl
Queries start with qry (or something similar)
Forms start with frm, Sub forms start with fsub, sfrm, etc.
Reports start with rpt
A text box (on a form or report) would start with txt
A combo box (same as above) would start with cbo
etc.

5)Continuing with the naming theme. When you get to the point
where you start designing forms and reports, you will notice that when
you add a control (text box, combo box, etc.) to a form/report, Access
will give will give it a completely useless name like "Text52" or
"Combo38". Do yourself a favor and name them something meaningful
like "txtEmployeePhone" or "cboSelectASubject".

Good luck and post back when you have more questions.

--
_________

Sean Bailey


"Aria" wrote:

First, let me say,"WOW"! You both have given me *so much* information. Thank
you! My comments are below (I'm combining posts. I didn't know how else to do
this):
Bruce M:
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.

Aria:
Do I need a subform if the majority of the staff only hold one title? Yes, I
still trying to get over having empty fields but I haven't made it this far
so I don't know. The form I envision would have this info on the main form
using a combobox for the titles. I am trying to think of every possible title
that I can. I don't want any suprises here.

Bruce M:

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).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do the
junction table.

Bruce M:
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.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I know
I need to learn how to figure out some of these issues myself. I have to try
first. If I continue to have problems, I will of course follow your advice
(you and Beetle).

Bruce M:
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.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the hard
work at the beginning so that it will seem effortless in the end. Well, here
it is. I am willing to put in the effort but some days I am frustrated beyond
belief.

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.

Bruce M:
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.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone could
have several people answer it. This will depend on who is assigned to the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a room
assignment list from the Principal soon. We'll see....

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

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

Aria:
Ugh! Nooooooo...I was so sure! I checked over and over and over again and
last night I finally found it...one person, two departments. Will this never
end?!


"Beetle" wrote:

One-to-One relationships are less common than 1:m and m:m, but in your
case it may very well be applicable. They are typically used when you
are sub-typing. The following is an excerpt from a recent post by resident
guru Ken Sheridan that talks about this type of relationship (I couldn't
possibly explain it any better). Hopefully he won't mind that I reposted his
comments. The thread in question is about setting up a Church db, so
you will see references to the Church of England etc., but the concept
may be helpful in your situation.

Thank you! I am familiar with Mr. Sheridan's posts. Wow, there is a lot of
information there. I had to really pay attention though and relate it to my
situation. After reading it, I'm not as frustrated. Yes, there are 1:1
relationships here but it may be what I need. I do think that I have
sub-types. Let me work with this some more before I let you both know which
1:1 relationships I have.

A sub-type can of course have sub-types of its own; Chris Date in one of his
books gives the example of a type Employees with sub-type programmers, and
sub-types of programmers, System programmers and Application programmers.

I'm sorry for messing up the post. I think it added to the confusion.

1) Can an employee have more than one Classification, or can a
Classification have more than one employee, or both?

Maybe it would help if I explained the Classifications and what I'm thinking.

The classifications a
Administrator (Principal, Vice Principals)

Certificated (anyone who holds a teaching credential such as Teachers,
Counselors, possibly some other staffers)

Classified (anyone who doesn't have a credential such as Custodians, Food
Service, Public Safety Officers, Clerical, etc.)

This is where I decided to add Substitutes. It seemed to me to be an
attribute/sub-type of employee. True? Administrators can belong to both
Certificated and Admin. but I don't even want to get into that. For my
purposes, they are Administrators, period. That is of course unless you two
present a situation where I will need to revise that (shudderplease
don't...it's getting complicated).

2) Same as above but for Titles. You appear to have it set up as m:m
but I just want to clarify.

I set it up as a m:m because in a previous discussion, Bruce wanted me to be
*sure* that one employee could only hold one title. I couldn't think of a
situation where this wasn't true until I pulled our department list and saw
that we do have some situations where one employee holds multiple titles (ex.
Athlectic Director/Teacher, or Department Chair/Teacher, or Department Chair
of 2 Departments/Teacher or co-Dept. chairs (you get the idea)). I"m drawing
the line at Coach/Teacher. Why? Because Dept. Chair info is more important
for our purposes than Coach. I can get that info elsewhere.

3) You have set up a m:m relationship between Departments and Subjects
which I don't quite understand. For example, the Mathematics dept.
would obviously have more than one Subject, but Trigonometry would
only be part of one dept. (Mathematics). I must be missing something
about what you are tracking here.

O.k., now *I* don't understand. I thought this was related to the employee
and whether they could teach more than one subject or belong to more than one
dept.? The answer is yes, they can. Did I misunderstand? That's why I related
it to tblSiteEmployee and not tblEmployees because this information does not
pertain to Subs. Hmmm...I'm confused. Did I miss something here? I placed it
in tblSiteEmps as FK to tblDeptSubjects. Obviously, I don't have the grasp on
this that I thought I did. I've confused you both.

4) You appear to have no relationship between an employee and the dept.
they work in or the subject they teach (at least not that I can see).

What?! Please see above. I thought I had. I thought it should be in
tblSiteEmp. I decided to use tblEmployees for *all* employees. Remember? You
said in a previous post, whether they were permanent or temporary, they
needed to be a part of the employee table (this is why tblSubs was deleted).
So, to me, tblEmployees represents district employees (our school site
employees plus any subs on campus for the day). This is the whole. The
sub-type would be tblSiteEmp which includes only our sites permanent
employees. I have fields in tblEmployees for Classification (Class) and
Title. Then I could list for example Class: Substitute Title: Teacher or
Custodian or whatever they happen to be.

I admit I haven't resolved how I am going to work Preferred Subject or the
Subs Credential (if they're Certificated) into the mix. I was thinking maybe
I could just list it under Subject but that brought up other issues that was
going to complicate things even more. Maybe I am trying to do to much, but,
per our previous discussion, I really want to have a place for information
about the Sub. It is critical to us; well, to me anyway, since I'm
responsible for covering any absences for our campuses. I guarantee we are
going to need that info. Right now, our system is my memory or if they gave
me a business card, 98% of them don't have business cards.
But, one thing at a time, I guess.

"There are some other issues also..."

Oh no...I was so happy when I finished and thought I had a good handle on
things. You have a great weekend!

--
Aria W.


"Beetle" wrote:

I have printed your post and will look it over, but I do have a few questions.

1) Can an employee have more than one Classification, or can a
Classification have more than one employee, or both?

2) Same as above but for Titles. You appear to have it set up as m:m
but I just want to clarify.

3) You have set up a m:m relationship between Departments and Subjects
which I don't quite understand. For example, the Mathematics dept.
would obviously have more than one Subject, but Trigonometry would
only be part of one dept. (Mathematics). I must be missing something
about what you are tracking here.

4) You appear to have no relationship between an employee and the dept.
they work in or the subject they teach (at least not that I can see).

There are some other issues also, but I'll cover those after I have a better
understanding of the above questions.
--
_________

Sean Bailey


"Aria" wrote:

I'm sorry. I messed it up. Sorry Bruce. Here is the revised version. Have a
great weekend!

tblEmployees
**********
Inactive Yes/No

EmpID (PK) Autonumber, long integer

ClassID (FK to tblClassifications)

EmpTitleID (FK to tblEmpTitles)

PhoneID (FK to tblPhones)

LN- txt

FN-txt

MI-txt



(sfrm) tblSiteEmp (1:1)
************
SiteEmpID (PK)
EmpID (FK) number, long integer
DeptsSubjectsID (FK) number, long integer
Address-txt
City-txt
State-txt
Emergency Info(Hospital, Medications, Allergies etc.)
DateEntered Date/Time
DateModified Date/Time


tblPhones (sfrm) 1:M
***************
PhoneID (PK) Autonumber, long integer
HomePhone-txt
CellPhone-txt
DistrictCell-txt
DateEntered-Date/Time
DateModified-Date/Time


tblTitles 1:M
*********

TitlesID (PK) Autonumber, long integer
TitleName-txt

tblSubjects 1:M
***********
SubjectsID (PK) Autonumber, long integer
SubjectName-txt


tblDepts 1:M
*********
DeptsID (PK) Autonumber, long integer
DeptsName-txt

tblDeptsSubjects M:M
************
DeptsSubjectsID (PK) Autonumber
SubjectsID (FK to tblSubjects)
DeptsID (FK to tblDepts)


tblClassifications 1:M
***************
ClassID (PK) Autonumber, long integer

ClassName-txt



tblTitlesEmps M:M
*************
TitlesEmpsID (PK) Autonumber

EmpID (FK to tblEmps)

TitlesID (FK to tblTitles)



*All FK keys are number, long integer

Relationships:
tblSiteEmp 1:1 tblEmps

tblPhones 1:M tblEmps

tblTitles 1:M tblTitlesEmps

tblClassifications 1:M tblEmps

tblTitlesEmps M:M tblEmps

tblSubjects 1:M tblDeptsSubjects

tblDepts 1:M tblDeptsSubjects

tblDeptsSubjects M:M tblSiteEmp
--
Aria W.


"Beetle" wrote:

I would echo what Bruce said and thank you for your kind reply. Here are
a few additional thoughts of my own.

Thank you for being here to answer all of my questions. I know my posts
aren't the normal kind (a bazillion questions all at once). You have both
been so kind and patient and have not yelled at me once (yes, I did have that
fear when I posted).

Happy to help. At least you want to learn and do things the right way. The
posters we don't like are the ones that have already decided how they want
to do things, even though it may be wrong, and expect someone here to
explain to them how to do it the *wrong* way.

Bothered by empty fields? Good. You should be. I know in a previous post
I said something along the lines of "I might just live with a couple of empty
fields in the Employees table", but I was just being lazy (shame on me!).
Part of the goal of normalization is to eliminate redundancy and empty
or "dead" fields. If an attribute doesn't apply to all employees, all the
time,
then it is an attribute of a sub-type, and therefore probably belongs in
another table.

Some other things to keep in mind for the future in the development cycle
of your application;

1)Do not use "lookup fields" in your tables. This basically ampunts to
a combo box, or list box, in a table. Combo/List boxes in forms are
appropriate, but they should never be used in tables. See this link
for more info;

http://www.mvps.org/access/lookupfields.htm

2)Don't use spaces or other special characters in your table or field
names. The same goes for queries, forms and reports (when you
get to that point). Having spaces, etc. in the names will only cause
you headaches later on. So instead of "Employees Table", it would
more commonly be "tblEmployees".

3)Certain words are "reserved" in Access and should not be used as
names of objects in your app. They are the names of different
properties, etc. of the application, and if you use them as, for
example, a field name it will cause problems. Two very common
examples are Name and Date. See this link for a more complete
list;

http://support.microsoft.com/kb/286335

4)Most developers use some sort of "naming convention" to help
keep track of things as the application grows in size. Everyone has
some of their own variations on naming conventions, but some things
are common. For example;

Table names commonly start with tbl
Queries start with qry (or something similar)
Forms start with frm, Sub forms start with fsub, sfrm, etc.
Reports start with rpt
A text box (on a form or report) would start with txt
A combo box (same as above) would start with cbo
etc.

5)Continuing with the naming theme. When you get to the point
where you start designing forms and reports, you will notice that when
you add a control (text box, combo box, etc.) to a form/report, Access
will give will give it a completely useless name like "Text52" or
"Combo38". Do yourself a favor and name them something meaningful
like "txtEmployeePhone" or "cboSelectASubject".

Good luck and post back when you have more questions.

--
_________

Sean Bailey


"Aria" wrote:

First, let me say,"WOW"! You both have given me *so much* information. Thank
you! My comments are below (I'm combining posts. I didn't know how else to do
this):
Bruce M:
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.

Aria:
Do I need a subform if the majority of the staff only hold one title? Yes, I
still trying to get over having empty fields but I haven't made it this far
so I don't know. The form I envision would have this info on the main form
using a combobox for the titles. I am trying to think of every possible title
that I can. I don't want any suprises here.

Bruce M:

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).

Aria:
That is exactly what I want to do; grouped by title. I think I'll do the
junction table.

Bruce M:
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.

Aria:
I know I'm struggling but sometimes that's o.k. (I can't believe I said
*that*!) Let me qualify that remark by saying it depends on the hour. I know
I need to learn how to figure out some of these issues myself. I have to try
first. If I continue to have problems, I will of course follow your advice
(you and Beetle).

Bruce M:
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.

Aria:
Absolutely!!! You know our history here (some bad memories). Keys are
problematic for us. I have always said that I don't mind putting in the hard
work at the beginning so that it will seem effortless in the end. Well, here
it is. I am willing to put in the effort but some days I am frustrated beyond
belief.

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.

Bruce M:
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.

Aria:
Whew! Thank goodness I don't have to worry about that. A room phone could
have several people answer it. This will depend on who is assigned to the
room. The good thing is that they don't roam. All bets are off for the
upcoming school year though. I've heard rumors. I should be getting a room
assignment list from the Principal soon. We'll see....

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

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

Aria:

OK, this post may get a bit long winded, so hopefully you won't pass
out from boredom before you get to the end.

To start with, I would like to clarify some aspects of the relationship types.
I will cover each type separately, using your data for examples.

1:1
***
You have a 1:1 relationship between tblEmployees and tblSiteEmp. In
this case tblEmployees could be thought of as the "Parent" table and
tblSiteEmp is the "Child" table. tblSiteEmp is the child because it is
dependent upon tblEmployees. You can have an employee in
tblEmployees without a related address in tblSiteEmp, but you cannot
have an address in tblSiteEmp without a related employee in
tblEmployees.

The rules of this relationship also state that there can be only one
child record for each parent record. Because of this, the child
table (tblSiteEmp) does not need a separate primary key. EmpID would
be the PK for both tables. In tblSiteEmp it acts as both the PK for that
table and the FK to tblEmployees. In the parent table it can be an
Autonumber but in the child table it cannot, because its value must be
derived from an existing PK value in the parent table. When a new
address record in entered in tblSiteEmp, an existing EmpID from
tblEmployees is inserted into the EmpID field in tblSiteEmp. Now, when
I say that the value is inserted, keep in mind that all data entry is
done through forms, and that the form handles this process automatically.
Your users would never even see the PK value, nor should they.
In this case the tables might look like;

tblEmployees
**********
EmpID (Autonumber PK)
LN
FN
MI

tblSiteEmp
********
EmpID (PK/FK - long integer number)
Address
City
State
ZipCode


1:M
***
For this example I am going to use tblEmployees and tblPhones, but with
a couple of "disclaimers", so to speak.

1) There has been some back an forth in previous posts about whether
a phone should be related to a Room or an Employee, as well as
whether the relationship should be 1:M or M:M. I don't know enough
details to answer either one of these questions, so this is only an
example
of how to set up a 1:M relationship.

2) I agree with Bruce in that an employees home phone and personal cell
phone are part of their personal information, and therefore belong
in tblEmployees, not tblPhones. IMO tblPhones should only store data
about phones that are owned and maintained by your school, which
may include cell phones *if* they are owned by the school and are
assigned to employees.

So in this relationship tblEmployees is again the parent, and tblPhones
is the child. In this case we are allowed to have more than one child
per parent, so the child table does need to have it's own PK. In this type
of relationship the Foreign Key goes in the child table, or the table
that is on the "many" side of the relationship. So EmpID goes in
tblPhones as a FK, not the other way around (which is how you have
it now).

Another way of thinking about this is that a parent record does
not necessarily have a child, but every child record must have a parent.
Therefore, for every record in the child table, we need to know who
the parent is. So the tables might look like;

tblEmployees
**********
EmpID (Autonumber PK)
LN
FN
MI

tblPhones
*******
PhoneID (Autonumber PK)
EmpID (FK to tblEmployees - long integer number)
PhoneDescription
PhoneNumber


M:M
***
You have more than one of this type, but I will use Employees and Titles
for the example. As you know, this type of relationship needs a junction
table. You can also look at this as two 1:M relationships where the
junction table is the "many" side in both relationships. So you have;

tblEmployees 1:M tblEmployeeTitles

tblTitles 1:M tblEmployeeTitles

So the combination of the two constitutes;

tblEmployees M:M tblTitles

Now, as I said previously, the "many" side table holds the foreign key.
So, in this case, the junction table holds two FK's, which you have
done correctly in your junction table. Where you went wrong is by
putting EmpTitleID in tblEmployees as a FK.

In fact, EmpTitleID does not need to exist in *either* table. In a
junction table, it is the combination of the FK's that constitute the
PK. In other words, each individual FK can be repeated many times,
but for each record the *combination* of the FK's must be unique.
The way you have it now, with EmpTitleID as the only unique identifier,
there is nothing to prevent the same title being assigned to the same
employee many times over. Now, you could leave EmpTitleID as the PK,
and create a unique index on the two FK's, but that would be ignoring
the fact that EmpTitleID is unnecessary. It is not good practice to
introduce unnecessary elements into your db. To create the proper
PK in this table, you would highlight both FK fields (EmpID and TitleID)
in design view, and then set them as the PK. For this example, the
table structure might look like;

tblEmployees
**********
EmpID (Autonumber PK)
LN
FN
MI

tblTitles
******
TitleID (Autonumber PK)
TitleDescription

tblEmpTitles
*********
EmpID (Fk to tblEmployees and first part of PK - long integer number)
TitleID (FK to tblTitles and second part of PK - long integer number)

This same basic concept/structure should apply to your M:M relationship
between Employees and Classifications also.

Still awake?..........Hello?........ Pick your head up off that desk. Sleep
on
your own time, dammit! ;-)


So now, let's move on from concepts to things that are more specific
to your application.

First, let's try to sort out the Department and Subject relationships. If all
you were tracking was teachers this would be a bit simpler, because you
would only need to relate them to Subjects. Since each Subject would
belong to a Department, then you could determine what Departments a
teacher is related to by virtue of the Subjects they teach. You would not
need a direct relationship between the teacher and the department.

However, that obviously will not work for you because not all of your
employees teach a Subject but, presumably, they do all work in some
Department. So essentially, as I see it anyway, you have two separate
M:M relationships that you need to keep track of. Don't worry, it's
not as complicated as it may sound at first. Right now you have
tblDeptSubjects, which isn't quite right because in this case the
relationship isn't between Departments and Subjects. Basically you
have;

tblEmployees M:M tblDepartments

tblEmployees M:M tblSubjects

So the junction tables would be tblEmpDepartments and
tblEmpSubjects and the structure might look like;

tblEmployees
**********
(same fields as in the previous examples)

tblDepartments
***********
DeptID (Autonumber PK)
DeptName

tblSubjects
********
SubjectID (Autonumber PK)
SubjectName

tblEmpDepts
*********
EmpID (FK to tblEmployees and first part of PK - long integer number)
DeptID (FK to tblDepts and second part of PK - LI number)

tblEmpSubjects
***********
EmpID (FK to tblEmployees and first part of PK - LI number)
SubjID (FK to tblSubjects and second part of PK - LI number)

So in essence, your Subjects table has evolved. It is not just a way
to track subjects that are taught. Rather, it is a way to describe a
persons role within a Department. It would still include values that
describe subjects like Economics, European History, and the like, but
it would also have values like Department Chair and Custodial Staff.


Moving on again, addressing some specific things from your last post;

4) You appear to have no relationship between an employee and
the dept. they work in or the subject they teach.

What?! Please see above. I thought I had. I thought it should be in
tblSiteEmp.

I didn't notice the field in tblSiteEmp at first. I can see why you thought
it should go there. It's because you misunderstood where the FK's go.
You thought they belonged in the "One" side table. You then probably
thought if you put it in tblEmployees, you would end up with an empty
field in the Substitute teacher records, so you put it in tblSiteEmp. This
is actually wrong for two reasons;

1) As discussed before, the FK's don't go in the "One" side table

2) tblSiteEmp exists for one reason only. To store address information
for your full time staff. Employee names are not in tblSiteEmp, so
if you were to relate something to tblSiteEmp, you would essentially
be relating it to an address, which would be somewhat meaningless
without a name. I can see no scenario in which you would relate
anything to tblSiteEmp (other than tblEmployees, which is its parent).

I admit I haven't resolved how I am going to work Preferred Subject or the
Subs Credential (if they're Certificated) into the mix. I was thinking maybe
I could just list it under Subject but that brought up other issues that was
going to complicate things even more.

For Preferred Subject, just add a field to tblEmployees and have the users
manually enter whatever the preferred subject is. This will add relatively
little to the data entry process, and it will prevent you from having to
add yet another relationship to your db. If you end up with a few records
where the PreferredSubject field is empty, I think that's acceptable.

As far as the Subs credentials, that would be handled in the same manner
as the full time staff. If they have some type of credential, then there will
be a record in tblEmpClassifications to reflect that fact. If not, there won't
be a record. Simple.

This is where I decided to add Substitutes. It seemed to me to be an
attribute/sub-type of employee. True? Administrators can belong to both
Certificated and Admin. but I don't even want to get into that. For my
purposes, they are Administrators, period. That is of course unless you two
present a situation where I will need to revise that (shudderplease
don't...it's getting complicated).

I agree. An Administrator is just that. Let's not add any more complication.


Well, that's my two cents worth. Keep in mind that some of this is just
my opinion. Someone else may disagree with some of what I've suggested
because it is probably not *fully* normalized, but I think it's normalized
enough that it would be completely functional. Once you get your head
around how the relationships work, it should start to come into focus.
Hopefully I didn't get carpel tunnel for nothing :-)

BTW - Howcome I have to put in my two cents worth, but I only get a
penny for my thoughts? g

--
_________

Sean Bailey

I've seen your last post but have been having trouble posting back. If this
goes through I will respond later.
--
Aria W.


"Beetle" wrote:

OK, this post may get a bit long winded, so hopefully you won't pass
out from boredom before you get to the end.

To start with, I would like to clarify some aspects of the relationship types.
I will cover each type separately, using your data for examples.

1:1
***
You have a 1:1 relationship between tblEmployees and tblSiteEmp. In
this case tblEmployees could be thought of as the "Parent" table and
tblSiteEmp is the "Child" table. tblSiteEmp is the child because it is
dependent upon tblEmployees. You can have an employee in
tblEmployees without a related address in tblSiteEmp, but you cannot
have an address in tblSiteEmp without a related employee in
tblEmployees.

The rules of this relationship also state that there can be only one
child record for each parent record. Because of this, the child
table (tblSiteEmp) does not need a separate primary key. EmpID would
be the PK for both tables. In tblSiteEmp it acts as both the PK for that
table and the FK to tblEmployees. In the parent table it can be an
Autonumber but in the child table it cannot, because its value must be
derived from an existing PK value in the parent table. When a new
address record in entered in tblSiteEmp, an existing EmpID from
tblEmployees is inserted into the EmpID field in tblSiteEmp. Now, when
I say that the value is inserted, keep in mind that all data entry is
done through forms, and that the form handles this process automatically.
Your users would never even see the PK value, nor should they.
In this case the tables might look like;

tblEmployees
**********
EmpID (Autonumber PK)
LN
FN
MI

tblSiteEmp
********
EmpID (PK/FK - long integer number)
Address
City
State
ZipCode


1:M
***
For this example I am going to use tblEmployees and tblPhones, but with
a couple of "disclaimers", so to speak.

1) There has been some back an forth in previous posts about whether
a phone should be related to a Room or an Employee, as well as
whether the relationship should be 1:M or M:M. I don't know enough
details to answer either one of these questions, so this is only an
example
of how to set up a 1:M relationship.

2) I agree with Bruce in that an employees home phone and personal cell
phone are part of their personal information, and therefore belong
in tblEmployees, not tblPhones. IMO tblPhones should only store data
about phones that are owned and maintained by your school, which
may include cell phones *if* they are owned by the school and are
assigned to employees.

So in this relationship tblEmployees is again the parent, and tblPhones
is the child. In this case we are allowed to have more than one child
per parent, so the child table does need to have it's own PK. In this type
of relationship the Foreign Key goes in the child table, or the table
that is on the "many" side of the relationship. So EmpID goes in
tblPhones as a FK, not the other way around (which is how you have
it now).

Another way of thinking about this is that a parent record does
not necessarily have a child, but every child record must have a parent.
Therefore, for every record in the child table, we need to know who
the parent is. So the tables might look like;

tblEmployees
**********
EmpID (Autonumber PK)
LN
FN
MI

tblPhones
*******
PhoneID (Autonumber PK)
EmpID (FK to tblEmployees - long integer number)
PhoneDescription
PhoneNumber


M:M
***
You have more than one of this type, but I will use Employees and Titles
for the example. As you know, this type of relationship needs a junction
table. You can also look at this as two 1:M relationships where the
junction table is the "many" side in both relationships. So you have;

tblEmployees 1:M tblEmployeeTitles

tblTitles 1:M tblEmployeeTitles

So the combination of the two constitutes;

tblEmployees M:M tblTitles

Now, as I said previously, the "many" side table holds the foreign key.
So, in this case, the junction table holds two FK's, which you have
done correctly in your junction table. Where you went wrong is by
putting EmpTitleID in tblEmployees as a FK.

In fact, EmpTitleID does not need to exist in *either* table. In a
junction table, it is the combination of the FK's that constitute the
PK. In other words, each individual FK can be repeated many times,
but for each record the *combination* of the FK's must be unique.
The way you have it now, with EmpTitleID as the only unique identifier,
there is nothing to prevent the same title being assigned to the same
employee many times over. Now, you could leave EmpTitleID as the PK,
and create a unique index on the two FK's, but that would be ignoring
the fact that EmpTitleID is unnecessary. It is not good practice to
introduce unnecessary elements into your db. To create the proper
PK in this table, you would highlight both FK fields (EmpID and TitleID)
in design view, and then set them as the PK. For this example, the
table structure might look like;

tblEmployees
**********
EmpID (Autonumber PK)
LN
FN
MI

tblTitles
******
TitleID (Autonumber PK)
TitleDescription

tblEmpTitles
*********
EmpID (Fk to tblEmployees and first part of PK - long integer number)
TitleID (FK to tblTitles and second part of PK - long integer number)

This same basic concept/structure should apply to your M:M relationship
between Employees and Classifications also.

Still awake?..........Hello?........ Pick your head up off that desk. Sleep
on
your own time, dammit! ;-)


So now, let's move on from concepts to things that are more specific
to your application.

First, let's try to sort out the Department and Subject relationships. If all
you were tracking was teachers this would be a bit simpler, because you
would only need to relate them to Subjects. Since each Subject would
belong to a Department, then you could determine what Departments a
teacher is related to by virtue of the Subjects they teach. You would not
need a direct relationship between the teacher and the department.

However, that obviously will not work for you because not all of your
employees teach a Subject but, presumably, they do all work in some
Department. So essentially, as I see it anyway, you have two separate
M:M relationships that you need to keep track of. Don't worry, it's
not as complicated as it may sound at first. Right now you have
tblDeptSubjects, which isn't quite right because in this case the
relationship isn't between Departments and Subjects. Basically you
have;

tblEmployees M:M tblDepartments

tblEmployees M:M tblSubjects

So the junction tables would be tblEmpDepartments and
tblEmpSubjects and the structure might look like;

tblEmployees
**********
(same fields as in the previous examples)

tblDepartments
***********
DeptID (Autonumber PK)
DeptName

tblSubjects
********
SubjectID (Autonumber PK)
SubjectName

tblEmpDepts
*********
EmpID (FK to tblEmployees and first part of PK - long integer number)
DeptID (FK to tblDepts and second part of PK - LI number)

tblEmpSubjects
***********
EmpID (FK to tblEmployees and first part of PK - LI number)
SubjID (FK to tblSubjects and second part of PK - LI number)

So in essence, your Subjects table has evolved. It is not just a way
to track subjects that are taught. Rather, it is a way to describe a
persons role within a Department. It would still include values that
describe subjects like Economics, European History, and the like, but
it would also have values like Department Chair and Custodial Staff.


Moving on again, addressing some specific things from your last post;

4) You appear to have no relationship between an employee and
the dept. they work in or the subject they teach.

What?! Please see above. I thought I had. I thought it should be in
tblSiteEmp.

I didn't notice the field in tblSiteEmp at first. I can see why you thought
it should go there. It's because you misunderstood where the FK's go.
You thought they belonged in the "One" side table. You then probably
thought if you put it in tblEmployees, you would end up with an empty
field in the Substitute teacher records, so you put it in tblSiteEmp. This
is actually wrong for two reasons;

1) As discussed before, the FK's don't go in the "One" side table

2) tblSiteEmp exists for one reason only. To store address information
for your full time staff. Employee names are not in tblSiteEmp, so
if you were to relate something to tblSiteEmp, you would essentially
be relating it to an address, which would be somewhat meaningless
without a name. I can see no scenario in which you would relate
anything to tblSiteEmp (other than tblEmployees, which is its parent).

I admit I haven't resolved how I am going to work Preferred Subject or the
Subs Credential (if they're Certificated) into the mix. I was thinking maybe
I could just list it under Subject but that brought up other issues that was
going to complicate things even more.

For Preferred Subject, just add a field to tblEmployees and have the users
manually enter whatever the preferred subject is. This will add relatively
little to the data entry process, and it will prevent you from having to
add yet another relationship to your db. If you end up with a few records
where the PreferredSubject field is empty, I think that's acceptable.

As far as the Subs credentials, that would be handled in the same manner
as the full time staff. If they have some type of credential, then there will
be a record in tblEmpClassifications to reflect that fact. If not, there won't
be a record. Simple.

This is where I decided to add Substitutes. It seemed to me to be an
attribute/sub-type of employee. True? Administrators can belong to both
Certificated and Admin. but I don't even want to get into that. For my
purposes, they are Administrators, period. That is of course unless you two
present a situation where I will need to revise that (shudderplease
don't...it's getting complicated).

I agree. An Administrator is just that. Let's not add any more complication.


Well, that's my two cents worth. Keep in mind that some of this is just
my opinion. Someone else may disagree with some of what I've suggested
because it is probably not *fully* normalized, but I think it's normalized
enough that it would be completely functional. Once you get your head
around how the relationships work, it should start to come into focus.
Hopefully I didn't get carpel tunnel for nothing :-)

BTW - Howcome I have to put in my two cents worth, but I only get a
penny for my thoughts? g

--
_________

Sean Bailey