Still Struggling...

[Beetle]

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.

 

[BruceM]

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 here:
http://allenbrowne.com/AppIssueBadWord.html

 

[Aria]

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.

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]

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

 

[BruceM]

Responses inline.

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.

 

[Aria]

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

 

[Beetle]

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.

 

[Aria]

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 are:
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 (<shudder>please
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!

 

[Beetle]

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;

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 (<shudder>please
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>

 

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

Where do I even start with this post? Beetle, I'm in shock; I'm stunned; I
can barely think clearly; I'm horrified! When I see all that you have
done...so thorough...extensive...well thought out...patient and with great
examples. Where do I begin?

Ok, let's get into this because my comments are going to be long as well.
Bruce, I am going to make comments to you as well.
1st, I thought your post was OUTSTANDING! I'm horrified because I feel like
a student who failed the mid-term and it was *open book* with *great
teachers* who explained the concepts *in detail*!! And here you are going
over it *again*!!! I feel really bad that you two are giving so much of
yourselves to help me and I am still not picking it up. I have a book
(although at this point some of the suggestions in it are questionable), I
have read the Access Help section and printed out the ones I need to
concentrate on. As I previously stated, I scoured the Internet and read and
printed all posts that I thought will help. But know *this*, your efforts are
not in vain. I *will* learn this and get this up and running if it's the
*last* thing I do (it may very well be with the amount of frustration I
feel)!

Onward:
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.

Yes, this I understand and it makes perfect sense to me.

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.

Ok...I don't think I understood that before.

EmpID would be the PK for both tables. In tblSiteEmp it acts as both the PK for that table and the FK to tblEmployees.
Really?!

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.

I understand. Of course, it makes so much sense *now*. I wish I had figured
that out for myself. I see that I am still having trouble with the
application of the 1:1, 1:M, M:M, PK, FK, and the parent/child relationship.
I understand the concept, I think, but what that actually means in each
table...no...not so much. I understand that PK is the ONE side and FK is the
many side but there's a disconnect as to how that works in each table. I
haven't done a good job explaining what I mean. Well, obviously you have
given me homework. I'll work on it.

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.

Ok, agreed; Home and employee cell belong in tblEmployees; Rm. Phone# and
district cell in tblPhones.

Bruce, I didn't quite understand your reply when you stated, "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."

Question:
Are we dropping the M:M relationship between tblEmployees and tblphones and
inputting the phone # for each employee? 1:M? I'm a little confused. There
may be as many as 3 staff members assigned to a classroom. Bruce, I'm going
to go with our present scenario in that staff doesn't roam. Wait a minute, I
thought room phone # was going into tblLocations? In light of my stellar
performance so far in establishing relationships, I think this could get
messy. I just want to make sure I can get a directory/reverse directory out
of this.

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.

So EmpID goes in tblPhones as a FK, not the other way around (which is how you >have it now).

Ugh...my head hurts; I'll change it.

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.

Let me "think" out loud here...o.k., more than 1 child is the many side and
you said in a previous post that the 1 side (PK) would exist in the many side
as an FK. Did I get that right? Hmmm...I might still need to work on this
parent/child thing. I've highlighted and will keep re-reading until I master
it.

tblPhones
*******
PhoneID (Autonumber PK)
EmpID (FK to tblEmployees - long integer number)
PhoneDescription
PhoneNumber
Bruce:
I'm not following how PhoneID is a FK to tblEmpTitles.

Yeah and after re-reading what I posted; neither am I. I'm so sorry I
messed up the post and confused everyone. That's not even what I had written
on my paper. At any rate, what I did have was still wrong so... I will make
the changes to reflect the new information.

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.

Finally! I did something right!

Where you went wrong is by putting EmpTitleID in tblEmployees as a FK.

....and then hope was dashed. :-(

EmpTitleID does not need to exist in *either* table.

Whoa;*what*?! <trying hard not to cry> I needed this explanation because
some things I was doing w/o really understanding why; simple because someone
said do this and then I would read a post that said no, do this, and the book
would say something different. In tblKeyEmployees, I have a combo PK. That
was one of the original tables I had and yet I don't do the same thing here.

there is nothing to prevent the same title being assigned to the same
employee many times over.

There it is. What I was inadequately trying to explain earlier. I can see
this is where I'm going to get into trouble because I don't really understand
how you would look at that and *know* it means the same title assigned to the
same employee over and over. I'm going to have to get a handle on this; more
homework.

It is not good practice to introduce unnecessary elements into your db.

Nor do I want to; I have enough trouble already.

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

....zzzz...zzzzz...huh?...what?!...I'm awake, I'm awake! (Besides I slid off

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

Well, maybe not for you...

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)

I apologize to you now, Beetle, for the confusion because in my earlier post
I stated, " 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.

One of those tables with the word Employee in it was tblEmpSubjects. I don't
know why I dropped it.

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.

I'm almost afraid to ask because I think I should have this down since
you've already explained but, "describe a persons role within the dept.?"
Wouldn't that info be in tblEmpDept? Let me think about this some more. If I
read it over and over; it will come.

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.

Hmm...I think you're giving me too much credit although we're all aware of
my dislike for empty fields. :-(

2) tblSiteEmp exists for one reason only. To store address information for your >full time staff.

What?! When did this happen? I thought it was to store info that didn't
pertain to subs (addresses and emergency info). BTW Bruce, that's how we
ended up with tblSiteEmp because Subs aren't obligated to nor will they
disclose their address or any emergency info. I will break out in hives if I
have all of those empty fields (approx. 14-16) for *every* sub record. :-(
Just so you both understand, we aren't talking about 1 or 2 subs per day
here. We're a large school. We've had as many as 20 sub requests in a single
day; depending on what's going on (training, workshops, conferences, illness,
etc.). There's always something going on.

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.

At this point, so do I; done!

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.

???...not following you. I don't track site staff credentials. The district
handles that. As long as subs have a teaching credential, they can sub in
whatever subject they want. My only reason for tracking who has a particular
credential (Special Education, English, Math, etc.) is to handle emergencies
that may come up. Sp. Ed. and Math at least, are special subjects in my book.
I know the issues Sp. Ed. faces having worked in that dept. many years. You
can't have just any one there. They won't make it. Math is another subject
that needs a specialist so to speak. Every year something happens where we
need a long-term sub. I just wanted to be ready. But I don't understand what
you are proposing; just add a field to tblEmployees?

but I think it's normalized enough that it would be completely functional.
Agreed!

Once you get your head around how the relationships work, it should start to come into focus.

How long does that take? Regardless, I'll be staying the course.

Hopefully I didn't get carpel tunnel for nothing

That's right, pile on the guilt. <g> In light of your "medical condition"
and all the effort you two have put in I'm definitely feeling the pressure to
pick it up quickly. I'm studying hard. I'll get it.

Ok, I think that about covers it. Beetle...Beetle...wake up!!

 

[Aria]

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

 

[Aria]

Update: I had trouble posting this yesterday so I am reposting with updated
comments where appropriate. Bruce, my comments to you are also within the
post.

Where do I even start with this post? Beetle, I'm in shock; I'm stunned; I
can barely think clearly; I'm horrified! When I see all that you have
done...so thorough...well thought out...patient and with examples I would
easily understand because they are from my own application. I'm overwhelmed.
Where do I begin?

Ok, let's get into this because my comments are going to be long as well.
1st, I thought you post was OUTSTANDING! I'm horified because I feel like a
student who failed the mid-term exam even though it was *open book* with
*execeptional teacher* who explained the concepts *in detail*! And here you
are going over it again!! I feel so bad that you two are giving so much of
yourselves to help me and I am still not picking it up. I have a book
(although at this point some of the suggestions in it a re questionable). I
have read the Access Help section and printed out the ones where I need to
concentrate. As I previously stated, I scoured the Internet and this
newsgroup. I read and printed every post that I thought would help me.
Understanding ebbs and flows. One moment I think "of course", the next it has
quietly slipped away. But know *this*, your efforts are not in vain. I *will*
learn how to do this and get this application up and running if it's the last
thing I do (it may very well be with the amount of frustration I feel)!

Onward:

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.

Yes, this I understand and it makes perfect sense to me.

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.

Ok...I don't think that was clear to me before.

EmpID would be the PK for both tables. In tblSiteEmp it acts as both the PK for >that table and the FK to tblEmployees.

Really?! Both tables?

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.

I understand. Of course, it makes so much sense *now*. I wish I had figured
that out for myself. I see that I am still having trouble with the
application of the 1:1, 1:M, M:M, PK, the FK and the parent/child
relationship. I understand the concept, I think, but the what that actully
means in each table, the concrete...no...not so much. I understand that PK is
the ONE side and FK is the MANY side but there seems to be a disconnect as to
how that works in each table. I haven't done a good job explaining what I
mean. Well, obviously, you have given me homework. I'll work on it.

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.

Ok, agreed; Home and employee cell # belong in tblEmployees; Rm. phone # and
district cell in tblPhones.

Bruce, I didn't quite understand your reply when you stated, "Ordinarily I
would have a one>>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."

Updated comments: The room is shared but they are a team. I don't know if
this will change for the upcoming school year, but as of today, the team
doesn't roam.

Update Question:
Are we dropping the M:M relationship between tblEmployees and tblPhones and
inputting the phone # for each employee? 1:M? I'm a little confused. I
thought the room phone # was attached to tblLocations? In light of my stellar
performance thus far in establishing relationships, it seems to me this could
get messy. I just want to make sure I can get a directory/reverse directory
out of this.

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.

You're killing me here! What?! ...understanding flowing out. Is it
because in the first example it was 1:1 and in this one it's 1:M?

So EmpID goes in tblPhones as a FK, not the other way around (which is how you >have it now).

I'll change it.

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.

Let me "think" out loud here...ok, more than 1 child is the many side and
you said in a previous post that the 1 side (PK) would exist in the many side
as an FK. Did I get that right? Hmm...I might still need to work on this
parent/child thing. I've highlighted and will keep trying until I master it.

Bruce:
I'm not following how PhoneID is a FK to tblEmpTitles.

I'm so sorry. I messed up the post and confused everyone. That's not even
what I had written on my diagram. At any rate, what I did have was still
wrong so...I will make the changes to reflect the new information.

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.

Finally! I did something right!

Where you went wrong is by putting EmpTitleID in tblEmployees as a FK.

....and then hope was dashed. :-(

In fact, EmpTitleID does not need to exist in *either* table.

Whoa, *what*?! <trying hard not to cry> This is hard. I needed this
explanation because some things I was doing without really understanding why;
simply because
someone told me it's done this way and then I would read in a post, "no...do
this", and then the book would say something different. In tblKeyEmployees, I
have a combo PK. That was one of the original tables I had and yet I don't do
the same thing here.

there is nothing to prevent the same title being assigned to the same
employee many times over.

There it is. What I was inadequately trying to explain earlier. I can see
this is where I'm going to get into trouble because I don't really understand
how you would look at that and *know* it means the same title assigned over
and over to the same employee. I'm going to have to get a handle on this;
more homework.

It is not good practice to introduce unnecessary elements into your db.

Nor do I want to; I have enough trouble already.

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

....zzzz...zzzz...huh?...what?!...I'm awake, I'm awake! (Besides, I slid off

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

Well, maybe not for you...

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)

Again, apologies for the confusion of my earlier post. I stated there, "The
other problem I had was something 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 it became to difficult to see
anything. I had spun off School Data from the employee table like we
discussed, but I had also put Emergency Info into a seperarte table too. One
of those tables with the word Employee in it was tblEmpSubjects. I don't know
why I dropped it; probably second guessing.

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.

Updated comment: I'm almost afraid to ask because I think I should have this
down since you've already explained but, "describe a persons role within a
Department"? Isn't that the same as EmpTitle?

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.

Hmm...I think you're giving me too much credit, although we're all aware of
my dislike for empty fields. :-(

2) tblSiteEmp exists for one reason only. To store address information
for your full time staff.

What?! When did this happen? I thought it was to store info that didn't
pertain to subs (addresses and emergency info). BTW Bruce, that's how we
ended up with tblSiteEmp because Subs aren't obligated nor will they disclose
their address or any emergency info. I will break out in hives if I have all
of those empty fields (approx. 14-16) for *every* sub record. :-( Just so you
both understand, we aren't talking about 1 or 2 subs per day here. We're a
large school. We've had as many as 20 sub requests in a single day; depending
on what's going on (training, workshops, conferences, illness, etc.) There's
always something going on.

Update: Upon further review, I see what you mean.

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.

At this point, so do I; done!

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.

???...not following you. I don't track site staff credentials. The district
handles that. As long as subs have a teaching credential, they can sub in
whatever subject they want. My only reason for tracking who has a particular
credential (Special Education, English, Math, etc.) is to handle emergencies
that may come up. Sp. Ed. and Math at least, are special subjects in my book.
I know the issues Sp. Ed. faces having worked in that dept. many years. You
can't have just anyone there. Some subs are ill equipped for the job and it's
doesn't serve our students well. Math is another subject that needs a
"specialist" so to speak. Every year something happens where we need a
long-term sub. I just want to be ready. But I don't understand what you are
proposing; just add a field to tblEmployees?

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.

In my inexperienced opinon, I agree.

Once you get your head around how the relationships work, it should start to >come into focus.

How long does that take? Regardless, I'll be staying the course.

Hopefully I didn't get carpel tunnel for nothing

That's right--pile on the guilt. <g> In light of your "medical condition"
and all the effort you two have put in, I'm definitely feeling the presure to
pick it up quickly. Oh, have we already passed the picking it up quickly
stage? I'm studying hard. I'll get it...soon I hope.

Since your last post, I have been reading your comments repeatedly. I'm not
there yet on all issues but some of them I think I understand better now. I
want to thank you for pushing through and doing all of that typing. I hope
your carpal tunnel has subsided.
Ok, I think that about covers it. Beetle...Beetle...wake up!!

 

[Aria]

I posted again but for whatever reason it is not appearing here. You may have
to go to Access Monster to read it. I see it there.

 

[Aria]

Doesn't me I'm not making progress. Sorry, I didn't realize their was a time
limit on learning.

 

[John... Visio MVP]

Isn't about time for you to back into hiding again? So where do you
disappear to for ten months after you ply your snake oil?

These newsgroups are for FREE peer to peer support, not a venue for low
lifes like Master Santos, AKA PCD and now, Roberta to give users false
promises of help.

Aria, Steve is not interested in helping, he is just interested in
seperating you from your money.

John...

 

[John... Visio MVP]

Doesn't me I'm not making progress. Sorry, I didn't realize their was a
time
limit on learning.

If you are enjoying your trip of discovery, take your time, there is no
rush. If you run into any problems there are many qualified people here who
are more than willing to help. Steve of course, is not one of them. His only
interest is helping himself to your money.

John... Visio MVP

 

[BruceM]

Good one. If Steve or whoever he is had any real competence he wouldn't
need to troll here for business, and would have something other than Coming
Soon (several years now) on the Access Tips (or something) section of his
web site.

 

[BruceM]

I have gone to Access Monster and found the thread, from which I copied the
following. I don't know why that one post didn't come through. Maybe it
was a temporary thing, because all the rest seem to be here.

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

Where do I even start with this post? Beetle, I'm in shock; I'm stunned; I
can barely think clearly; I'm horrified! When I see all that you have
done...so thorough...extensive...well thought out...patient and with great
examples. Where do I begin?

Ok, let's get into this because my comments are going to be long as well.
Bruce, I am going to make comments to you as well.
1st, I thought your post was OUTSTANDING! I'm horrified because I feel like
a student who failed the mid-term and it was *open book* with *great
teachers* who explained the concepts *in detail*!! And here you are going
over it *again*!!! I feel really bad that you two are giving so much of
yourselves to help me and I am still not picking it up. I have a book
(although at this point some of the suggestions in it are questionable), I
have read the Access Help section and printed out the ones I need to
concentrate on. As I previously stated, I scoured the Internet and read and
printed all posts that I thought will help. But know *this*, your efforts
are
not in vain. I *will* learn this and get this up and running if it's the
*last* thing I do (it may very well be with the amount of frustration I
feel)!

Onward:
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.

Yes, this I understand and it makes perfect sense to me.

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.

Ok...I don't think I understood that before.

EmpID would be the PK for both tables. In tblSiteEmp it acts as both the PK
for that table and the FK to tblEmployees.
Really?!


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.

I understand. Of course, it makes so much sense *now*. I wish I had figured
that out for myself. I see that I am still having trouble with the
application of the 1:1, 1:M, M:M, PK, FK, and the parent/child relationship.
I understand the concept, I think, but what that actually means in each
table...no...not so much. I understand that PK is the ONE side and FK is the
many side but there's a disconnect as to how that works in each table. I
haven't done a good job explaining what I mean. Well, obviously you have
given me homework. I'll work on it.

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.

Ok, agreed; Home and employee cell belong in tblEmployees; Rm. Phone# and
district cell in tblPhones.

Bruce, I didn't quite understand your reply when you stated, "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."

Question:
Are we dropping the M:M relationship between tblEmployees and tblphones and
inputting the phone # for each employee? 1:M? I'm a little confused. There
may be as many as 3 staff members assigned to a classroom. Bruce, I'm going
to go with our present scenario in that staff doesn't roam. Wait a minute, I
thought room phone # was going into tblLocations? In light of my stellar
performance so far in establishing relationships, I think this could get
messy. I just want to make sure I can get a directory/reverse directory out
of this.

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.

Oh my gosh, what?! *Why* are you torturing me? <g> This definitely
contributes to the ebb and flow of understanding...more homework.

So EmpID goes in tblPhones as a FK, not the other way around (which is how
you >have it now).

Ugh...my head hurts; I'll change it.

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.

Let me "think" out loud here...o.k., more than 1 child is the many side and
you said in a previous post that the 1 side (PK) would exist in the many
side
as an FK. Did I get that right? Hmmm...I might still need to work on this
parent/child thing. I've highlighted and will keep re-reading until I master
it.

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

I'm not following how PhoneID is a FK to tblEmpTitles.

Yeah and after re-reading what I posted; neither am I. I'm so sorry I
messed up the post and confused everyone. That's not even what I had written
on my paper. At any rate, what I did have was still wrong so... I will make
the changes to reflect the new information.

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.

Finally! I did something right!

Where you went wrong is by putting EmpTitleID in tblEmployees as a FK.

....and then hope was dashed. :-(

EmpTitleID does not need to exist in *either* table.

Whoa;*what*?! <trying hard not to cry> I needed this explanation because
some things I was doing w/o really understanding why; simple because someone
said do this and then I would read a post that said no, do this, and the
book
would say something different. In tblKeyEmployees, I have a combo PK. That
was one of the original tables I had and yet I don't do the same thing here.

there is nothing to prevent the same title being assigned to the same
employee many times over.

There it is. What I was inadequately trying to explain earlier. I can see
this is where I'm going to get into trouble because I don't really
understand
how you would look at that and *know* it means the same title assigned to
the
same employee over and over. I'm going to have to get a handle on this; more
homework.

It is not good practice to introduce unnecessary elements into your db.

Nor do I want to; I have enough trouble already.

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

....zzzz...zzzzz...huh?...what?!...I'm awake, I'm awake! (Besides I slid off

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

Well, maybe not for you...

tblEmployees M:M tblDepartments

[quoted text clipped - 26 lines]

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

I apologize to you now, Beetle, for the confusion because in my earlier post
I stated, " 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.

One of those tables with the word Employee in it was tblEmpSubjects. I don't
know why I dropped it.

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.

I'm almost afraid to ask because I think I should have this down since
you've already explained but, "describe a persons role within the dept.?"
Wouldn't that info be in tblEmpDept? Let me think about this some more. If I
read it over and over; it will come.

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.

Hmm...I think you're giving me too much credit although we're all aware of
my dislike for empty fields. :-(

2) tblSiteEmp exists for one reason only. To store address information for
your >full time staff.

What?! When did this happen? I thought it was to store info that didn't
pertain to subs (addresses and emergency info). BTW Bruce, that's how we
ended up with tblSiteEmp because Subs aren't obligated to nor will they
disclose their address or any emergency info. I will break out in hives if I
have all of those empty fields (approx. 14-16) for *every* sub record. :-(
Just so you both understand, we aren't talking about 1 or 2 subs per day
here. We're a large school. We've had as many as 20 sub requests in a single
day; depending on what's going on (training, workshops, conferences,
illness,
etc.). There's always something going on.

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.

At this point, so do I; done!

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.

???...not following you. I don't track site staff credentials. The district
handles that. As long as subs have a teaching credential, they can sub in
whatever subject they want. My only reason for tracking who has a particular
credential (Special Education, English, Math, etc.) is to handle emergencies
that may come up. Sp. Ed. and Math at least, are special subjects in my
book.
I know the issues Sp. Ed. faces having worked in that dept. many years. You
can't have just any one there. They won't make it. Math is another subject
that needs a specialist so to speak. Every year something happens where we
need a long-term sub. I just wanted to be ready. But I don't understand what
you are proposing; just add a field to tblEmployees?

but I think it's normalized enough that it would be completely functional.
Agreed!


Once you get your head around how the relationships work, it should start
to come into focus.

How long does that take? Regardless, I'll be staying the course.

Hopefully I didn't get carpel tunnel for nothing

That's right, pile on the guilt. <g> In light of your "medical condition"
and all the effort you two have put in I'm definitely feeling the pressure
to
pick it up quickly. I'm studying hard. I'll get it.

Ok, I think that about covers it. Beetle...Beetle...wake up!!

Aria W.

 

[BruceM]

I have been curious as to why the posting does not appear here. Twice I
attempted to copy and paste the text. Now I am going to try copying and
pasting half of it. If that works, I'll copy and paste the second half.

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

Where do I even start with this post? Beetle, I'm in shock; I'm stunned; I
can barely think clearly; I'm horrified! When I see all that you have
done...so thorough...extensive...well thought out...patient and with great
examples. Where do I begin?

Ok, let's get into this because my comments are going to be long as well.
Bruce, I am going to make comments to you as well.
1st, I thought your post was OUTSTANDING! I'm horrified because I feel like
a student who failed the mid-term and it was *open book* with *great
teachers* who explained the concepts *in detail*!! And here you are going
over it *again*!!! I feel really bad that you two are giving so much of
yourselves to help me and I am still not picking it up. I have a book
(although at this point some of the suggestions in it are questionable), I
have read the Access Help section and printed out the ones I need to
concentrate on. As I previously stated, I scoured the Internet and read and
printed all posts that I thought will help. But know *this*, your efforts
are
not in vain. I *will* learn this and get this up and running if it's the
*last* thing I do (it may very well be with the amount of frustration I
feel)!

Onward:
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.

Yes, this I understand and it makes perfect sense to me.

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.

Ok...I don't think I understood that before.

EmpID would be the PK for both tables. In tblSiteEmp it acts as both
the PK for that table and the FK to tblEmployees.
Really?!
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.

I understand. Of course, it makes so much sense *now*. I wish I had figured
that out for myself. I see that I am still having trouble with the
application of the 1:1, 1:M, M:M, PK, FK, and the parent/child relationship.
I understand the concept, I think, but what that actually means in each
table...no...not so much. I understand that PK is the ONE side and FK is the
many side but there's a disconnect as to how that works in each table. I
haven't done a good job explaining what I mean. Well, obviously you have
given me homework. I'll work on it.

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.

Ok, agreed; Home and employee cell belong in tblEmployees; Rm. Phone# and
district cell in tblPhones.

Bruce, I didn't quite understand your reply when you stated, "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."

Question:
Are we dropping the M:M relationship between tblEmployees and tblphones and
inputting the phone # for each employee? 1:M? I'm a little confused. There
may be as many as 3 staff members assigned to a classroom. Bruce, I'm going
to go with our present scenario in that staff doesn't roam. Wait a minute, I
thought room phone # was going into tblLocations? In light of my stellar
performance so far in establishing relationships, I think this could get
messy. I just want to make sure I can get a directory/reverse directory out
of this.

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.

So EmpID goes in tblPhones as a FK, not the other way around (which is how
you
have it now).

Ugh...my head hurts; I'll change it.

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.

Let me "think" out loud here...o.k., more than 1 child is the many side and
you said in a previous post that the 1 side (PK) would exist in the many
side
as an FK. Did I get that right? Hmmm...I might still need to work on this
parent/child thing. I've highlighted and will keep re-reading until I master
it.

tblPhones
*******
PhoneID (Autonumber PK)
EmpID (FK to tblEmployees - long integer number)
PhoneDescription
PhoneNumber
Bruce:
I'm not following how PhoneID is a FK to tblEmpTitles.

Yeah and after re-reading what I posted; neither am I. I'm so sorry I
messed up the post and confused everyone. That's not even what I had written
on my paper. At any rate, what I did have was still wrong so... I will make
the changes to reflect the new information.

 

[BruceM]

I tried sending Part 2, but still NG. Here is half of Part 2.

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.

Finally! I did something right!

Where you went wrong is by putting EmpTitleID in tblEmployees as a FK.

....and then hope was dashed. :-(

EmpTitleID does not need to exist in *either* table.

Whoa;*what*?! <trying hard not to cry> I needed this explanation because
some things I was doing w/o really understanding why; simple because someone
said do this and then I would read a post that said no, do this, and the
book
would say something different. In tblKeyEmployees, I have a combo PK. That
was one of the original tables I had and yet I don't do the same thing here.

there is nothing to prevent the same title being assigned to the same
employee many times over.

There it is. What I was inadequately trying to explain earlier. I can see
this is where I'm going to get into trouble because I don't really
understand
how you would look at that and *know* it means the same title assigned to
the
same employee over and over. I'm going to have to get a handle on this; more
homework.

It is not good practice to introduce unnecessary elements into your db.

Nor do I want to; I have enough trouble already.

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

....zzzz...zzzzz...huh?...what?!...I'm awake, I'm awake! (Besides I slid off

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

Well, maybe not for you...

tblEmployees M:M tblDepartments

[quoted text clipped - 26 lines]

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

I apologize to you now, Beetle, for the confusion because in my earlier post
I stated, " 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.

One of those tables with the word Employee in it was tblEmpSubjects. I don't
know why I dropped it.

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.

I'm almost afraid to ask because I think I should have this down since
you've already explained but, "describe a persons role within the dept.?"
Wouldn't that info be in tblEmpDept? Let me think about this some more. If I
read it over and over; it will come.