A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Still Struggling...



 
 
Thread Tools Display Modes
  #1  
Old June 17th, 2008, 05:40 PM posted to microsoft.public.access.gettingstarted
Aria
external usenet poster
 
Posts: 150
Default Still Struggling...

Hi,
I'm still struggling to organize my tables and fields but I'm not as
*totally confused* as my original post; at least I hope I'm not. I'm new to
Access. This is my first database and I fluctuate between feeling hopeful I
can do this one minute and despairing that I can't the next because I've
thought of yet another complication that I don't know how to handle. I have
scoured the Internet and this disscussion group searching for the answers
that I need. I found some but I am still at a loss as to what to do about
others. I was wondering if someone would be kind enough to review my table
structure and respond to some questions at the end. I am truly grateful.

The information is as follows:

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text

tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber

tblKeysRequests
Request ID-Autonumber
Key ID (FK to tblKeys)- text
Emp. ID
Rm. number- text--I think there is a problem here. This info is part of
tblrooms.
Rm. phone- text
Date Requested- Date/time
Date Recvd.- Date/time
Date Issued- Date/time

tblRooms
Key ID (PK)-text
Room number- text
Remarks-text

tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time

If you're still reading, I have the following questions:
1. How many fields are too many in a table? I understand that Access will
accept up to 255. I read a post that suggested that 20-30 fields may indicate
there may be a normalization problem. I'm wondering about tblEmployees where
there are approx. 25 fields.

2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to
tblRooms because I may have to describe a location rather than a room #. Will
there be a problem with this method?
One of the desired reports is a reverse directory by room #/room phone #.
The phone # is currently in tblEmployees. Should it be moved to tblRooms? If
I leave it where it is, will I be able to update any queries and reports?
I've read that multitable queries aren't updateable. We have had mass staff
room changes right before the school year begins. It just seems it would be
better if the phone # was listed with tblRooms but someone told me that's
incorrect.

3. I forgot to include the following fields: Date assigned, Return Date and
Permission to Retain (over the summer) for Emp. Keys. Should this be added to
tblKeys or tblEmployees? I was told not to put *anything* into the junction
table (tblKeysEmployees) besides the composite key already listed.

4.Keys Requests--Somethimes are request will not have a name associated with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I need to
order a sub. key, I can use the name of the permanent staff member associated
with that room. What do I do about the vault? Should I enter "vault" in the
emp. table w/o any additional data?

5. Maybe I should cross this bridge when I get to it but I eventually will
get to the point where I will make forms and subforms. There are approx. 400
seperate keys, not including multiple copies of the same key. If I have a
combo box, is there a way to filter so that I am not looking at all 400
possiblities at once?

I am so sorry if this is too long and I'm asking too many questions at once.
I understand that you are trying to help as many people as possible. I have
searched and searched for answers and tried to adapt the customers/orders
format as much as possible to my situation but...I need help.

--
Aria W.
  #2  
Old June 17th, 2008, 08:20 PM posted to microsoft.public.access.gettingstarted
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default Still Struggling...

Responses inline.

"Aria" wrote in message
...
Hi,
I'm still struggling to organize my tables and fields but I'm not as
*totally confused* as my original post; at least I hope I'm not. I'm new
to
Access. This is my first database and I fluctuate between feeling hopeful
I
can do this one minute and despairing that I can't the next because I've
thought of yet another complication that I don't know how to handle. I
have
scoured the Internet and this disscussion group searching for the answers
that I need. I found some but I am still at a loss as to what to do about
others. I was wondering if someone would be kind enough to review my table
structure and respond to some questions at the end. I am truly grateful.

The information is as follows:

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus, or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query as
the Row Source for a RoomType combo box on your Keys form.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber


EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.


tblKeysRequests
Request ID-Autonumber
Key ID (FK to tblKeys)- text
Emp. ID
Rm. number- text--I think there is a problem here. This info is part of
tblrooms.
Rm. phone- text
Date Requested- Date/time
Date Recvd.- Date/time
Date Issued- Date/time




tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


KeyID as the PK of tblRooms could be confusing.


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


This table's purpose is unclear.


If you're still reading, I have the following questions:
1. How many fields are too many in a table? I understand that Access will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.


No problem. More than 30 fields could suggest normalization problems, but
it not an invariable rule. If the SchoolData fields are not all filled in
for every record it could be that SchoolData could be in a separate table.
Nothing else stands out much.


2.tblRooms has a field for Rm.#; the problem is that not all rooms have a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to
tblRooms because I may have to describe a location rather than a room #.
Will
there be a problem with this method?


Only if RoomNumber is a linking field. By the way, I assume you are using
the number sign for description, not as a field name. Names should include
only letters, number, and underscores. Spaces and other non-alphanumeric
characters are best avoided.

One of the desired reports is a reverse directory by room #/room phone #.
The phone # is currently in tblEmployees. Should it be moved to tblRooms?
If
I leave it where it is, will I be able to update any queries and reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it would
be
better if the phone # was listed with tblRooms but someone told me that's
incorrect.


If the phone number is associated with a room it should be part of a room
record. Presumably an employee can be associated with several rooms, and
vice versa. However, if an employee has an office where only he or she
answers the phone, the phone number is associated with the employee. Often
in such case the phone number follows the employee if they move to another
office. There is no single answer to your question. Your database can be
made to accept either a phone associated with a room or with an employee, or
both. It does start to become more complex if you have both situations.


3. I forgot to include the following fields: Date assigned, Return Date
and
Permission to Retain (over the summer) for Emp. Keys. Should this be added
to
tblKeys or tblEmployees? I was told not to put *anything* into the
junction
table (tblKeysEmployees) besides the composite key already listed.


You can have other fields in a junction table. For instance, the
KeysEmployees junction table may include a DateIssued field, which is a
property of neither the Employee nor the Key, but rather of this particular
key together with this particular employee. If a key may be retained over
the summer by one employee, but not another, then this too is a candidate
for a field in the junction table.


4.Keys Requests--Somethimes are request will not have a name associated
with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I need to
order a sub. key, I can use the name of the permanent staff member
associated
with that room. What do I do about the vault? Should I enter "vault" in
the
emp. table w/o any additional data?


Vault chould not be in the employee table, but the situation is unclear. Is
nobody responsible for the vault key? How will you know who has it? How do
you know now?

5. Maybe I should cross this bridge when I get to it but I eventually will
get to the point where I will make forms and subforms. There are approx.
400
seperate keys, not including multiple copies of the same key. If I have a
combo box, is there a way to filter so that I am not looking at all 400
possiblities at once?


Again, I'm not sure what you're asking. You will need forms and subforms as
soon as you start doing data entry. It may be best to begin with just
tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to
work with the junction table arrangement.


I am so sorry if this is too long and I'm asking too many questions at
once.
I understand that you are trying to help as many people as possible. I
have
searched and searched for answers and tried to adapt the customers/orders
format as much as possible to my situation but...I need help.

--
Aria W.


  #3  
Old June 18th, 2008, 08:38 PM posted to microsoft.public.access.gettingstarted
Aria
external usenet poster
 
Posts: 150
Default Still Struggling...

Bruce,
First, I would really like to thank you for responding. I need to clarify
some of the information that seems to be confusing. tblSubs is a table for
temporary, roving district staff. They may be employed at any district site
anywhere from a couple of hours to long-term(months). They need access to
their assignment location and if they are certificated, temporary passwords
for certain reports.

My comments are as follows:
tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

I think I'm fairly comfortable with this table. I just thought maybe it
needed to be broken down (not that I'm looking for another table to put in).
Yes, the fields are seperate.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus, or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query as
the Row Source for a RoomType combo box on your Keys form.


Now, this is where you lose me. I don't understand the purpose of a Locks
table. Isn't that what the keys table is for? We are only interested in the
locks as far as what key will staff to gain access to the room, gate,
stadium, etc. We don't normally track file cabinet keys. We may if the
contents are extremely confidential.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber


EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.

Sorry; I dropped the ball on this one. This *is* long interger. I forgot to
make the change in designation from my original.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text

KeyID as the PK of tblRooms could be confusing.

Again, you lost me on this one. Why is the KeyID confusing? It relates to
the room; knowing the key ID will allow you access. My thinking was that if I
could do a parameter query (is that what I would want?) by room, I would know
which key will unlock it. Then I could make a key request or whatever else
needs to be done.


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


This table's purpose is unclear.

I'm sorry. I should have explained in the beginning. Like I stated, this is
for temporary, roving staff. We need Access only in relation to keys for this
group. We have other applications for the rest of the information we need.

1. How many fields are too many in a table? I understand that Access will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.


No problem. More than 30 fields could suggest normalization problems, but
it not an invariable rule. If the SchoolData fields are not all filled in
for every record it could be that SchoolData could be in a separate table.
Nothing else stands out much.

All the fields relating to school data wil be filled in, so I guess we're
good here.


2.tblRooms has a field for Rm.#; the problem is that not all rooms have a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to
tblRooms because I may have to describe a location rather than a room #.
Will
there be a problem with this method?


Only if RoomNumber is a linking field. By the way, I assume you are using
the number sign for description, not as a field name. Names should include
only letters, number, and underscores. Spaces and other non-alphanumeric
characters are best avoided.

Oh my gosh! How many times have I read the same thing and because I *knew*
my fields weren't like that, moved on. Well, I just wanted to double check
and lo and behold, there it was; a # sign in my field name. Thanks for the
heads up!

One of the desired reports is a reverse directory by room #/room phone #.
The phone # is currently in tblEmployees. Should it be moved to tblRooms?
If
I leave it where it is, will I be able to update any queries and reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it would
be
better if the phone # was listed with tblRooms but someone told me that's
incorrect.


If the phone number is associated with a room it should be part of a room
record. Presumably an employee can be associated with several rooms, and
vice versa. However, if an employee has an office where only he or she
answers the phone, the phone number is associated with the employee. Often
in such case the phone number follows the employee if they move to another
office. There is no single answer to your question. Your database can be
made to accept either a phone associated with a room or with an employee, or
both. It does start to become more complex if you have both situations.

Well, for classroom staff, if they have to change rooms (happens every year,
so I have no illusions here), they switch to the new rooms' phone #. For
office staff, it could go either way. What do I need to do if anything?


3. I forgot to include the following fields: Date assigned, Return Date
and
Permission to Retain (over the summer) for Emp. Keys. Should this be added
to
tblKeys or tblEmployees? I was told not to put *anything* into the
junction
table (tblKeysEmployees) besides the composite key already listed.


You can have other fields in a junction table. For instance, the
KeysEmployees junction table may include a DateIssued field, which is a
property of neither the Employee nor the Key, but rather of this particular
key together with this particular employee. If a key may be retained over
the summer by one employee, but not another, then this too is a candidate
for a field in the junction table.

Your statements are true enough; only the master keys are coded to a
particular employee. Classroom keys are a little more generic in that anyone
who works out of that classroom or office will have the same key. O.k., I'll
put the new fields here.

4.Keys Requests--Sometimes a request will not have a name associated
with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I need to
order a sub. key, I can use the name of the permanent staff member
associated
with that room. What do I do about the vault? Should I enter "vault" in
the
emp. table w/o any additional data?


Vault chould not be in the employee table, but the situation is unclear. Is
nobody responsible for the vault key? How will you know who has it? How do
you know now?

Every key available on both campuses should be in the vault. Theoretically,
vault keys remain in the vault. I guess I'm responsible for those keys but
anyone who has the combination can remove a key without my knowledge. I guess
I could list myself and under Remarks indicate it's a vault request.

5. Maybe I should cross this bridge when I get to it but I eventually will
get to the point where I will make forms and subforms. There are approx.
400
seperate keys, not including multiple copies of the same key. If I have a
combo box, is there a way to filter so that I am not looking at all 400
possiblities at once?


Again, I'm not sure what you're asking. You will need forms and subforms as
soon as you start doing data entry. It may be best to begin with just
tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to
work with the junction table arrangement.

I'll hold off on this question and concentrate on getting the tables and
fields correct.

Bruce, again, I really appreciate all of your help. Thanks so much for
talking me through it!











--
Aria W.


"BruceM" wrote:

Responses inline.

"Aria" wrote in message
...
Hi,
I'm still struggling to organize my tables and fields but I'm not as
*totally confused* as my original post; at least I hope I'm not. I'm new
to
Access. This is my first database and I fluctuate between feeling hopeful
I
can do this one minute and despairing that I can't the next because I've
thought of yet another complication that I don't know how to handle. I
have
scoured the Internet and this disscussion group searching for the answers
that I need. I found some but I am still at a loss as to what to do about
others. I was wondering if someone would be kind enough to review my table
structure and respond to some questions at the end. I am truly grateful.

The information is as follows:

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus, or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query as
the Row Source for a RoomType combo box on your Keys form.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber


EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.


tblKeysRequests
Request ID-Autonumber
Key ID (FK to tblKeys)- text
Emp. ID
Rm. number- text--I think there is a problem here. This info is part of
tblrooms.
Rm. phone- text
Date Requested- Date/time
Date Recvd.- Date/time
Date Issued- Date/time




tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


KeyID as the PK of tblRooms could be confusing.


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


This table's purpose is unclear.


If you're still reading, I have the following questions:
1. How many fields are too many in a table? I understand that Access will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.


No problem. More than 30 fields could suggest normalization problems, but
it not an invariable rule. If the SchoolData fields are not all filled in
for every record it could be that SchoolData could be in a separate table.
Nothing else stands out much.


2.tblRooms has a field for Rm.#; the problem is that not all rooms have a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to
tblRooms because I may have to describe a location rather than a room #.
Will
there be a problem with this method?


Only if RoomNumber is a linking field. By the way, I assume you are using
the number sign for description, not as a field name. Names should include
only letters, number, and underscores. Spaces and other non-alphanumeric
characters are best avoided.

One of the desired reports is a reverse directory by room #/room phone #.
The phone # is currently in tblEmployees. Should it be moved to tblRooms?
If
I leave it where it is, will I be able to update any queries and reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it would
be
better if the phone # was listed with tblRooms but someone told me that's
incorrect.


If the phone number is associated with a room it should be part of a room
record. Presumably an employee can be associated with several rooms, and
vice versa. However, if an employee has an office where only he or she
answers the phone, the phone number is associated with the employee. Often
in such case the phone number follows the employee if they move to another
office. There is no single answer to your question. Your database can be
made to accept either a phone associated with a room or with an employee, or
both. It does start to become more complex if you have both situations.


3. I forgot to include the following fields: Date assigned, Return Date
and
Permission to Retain (over the summer) for Emp. Keys. Should this be added
to
tblKeys or tblEmployees? I was told not to put *anything* into the
junction
table (tblKeysEmployees) besides the composite key already listed.


You can have other fields in a junction table. For instance, the
KeysEmployees junction table may include a DateIssued field, which is a
property of neither the Employee nor the Key, but rather of this particular
key together with this particular employee. If a key may be retained over
the summer by one employee, but not another, then this too is a candidate
for a field in the junction table.


4.Keys Requests--Somethimes are request will not have a name associated
with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I need to
order a sub. key, I can use the name of the permanent staff member
associated
with that room. What do I do about the vault? Should I enter "vault" in
the
emp. table w/o any additional data?


Vault chould not be in the employee table, but the situation is unclear. Is
nobody responsible for the vault key? How will you know who has it? How do
you know now?

5. Maybe I should cross this bridge when I get to it but I eventually will
get to the point where I will make forms and subforms. There are approx.
400
seperate keys, not including multiple copies of the same key. If I have a
combo box, is there a way to filter so that I am not looking at all 400
possiblities at once?


Again, I'm not sure what you're asking. You will need forms and subforms as
soon as you start doing data entry. It may be best to begin with just
tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to
work with the junction table arrangement.


I am so sorry if this is too long and I'm asking too many questions at
once.
I understand that you are trying to help as many people as possible. I
have
searched and searched for answers and tried to adapt the customers/orders
format as much as possible to my situation but...I need help.

--
Aria W.



  #4  
Old June 19th, 2008, 12:51 AM posted to microsoft.public.access.gettingstarted
Beetle
external usenet poster
 
Posts: 1,254
Default Still Struggling...

Hate to throw in the monkey wrench, but I'm going to have to disagree
with some of what's been posted (comments inline).

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


Sorry, but the school data belongs in a separate table/tables. With the above
structure you will repeatedly be entering the same descriptive data over and
over. Additionally, what if an instructor teaches more than one Subject, works
in more than one Dept., etc? You will have to enter additional records for
the same employee.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in is
an attribute of the Wing (not the Room or the Key). You should have separate
tables for Campus' and Wings

tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


This table should have RoomID as a PK (KeyID does not belong here).
A room can have many keys, but a key can only open one room,
so RoomID goes in tblKeys as a foreign key, not the other way around.

tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


IMO this table is unecessary. Whether an employee is "full time" or
"temporary" is just an attribute that would go in tblEmployees. The fact
that a person may come and go as a member of your staff is irrelevant
to the fact that that person was in posession of a certain key at a
certain time. The date that a key was issued/returned belongs in
tblKeysEmployees.

You might be thinking "great, now I have to add more tables" but keep
in mind that in an application like this you will typically have several
tables
that will basically just be "lookup" tables. They aren't really a big
deal to set up. Just from the little I know about your app, I would say
you would probably have the following tables that would be
"lookup" tables;

tblClassifications

tblDepartments

tblSubjects

tblCampuses

tblWings

tblRoomTypes

You may also need some additional junction tables, depending on if - as
I stated earlier - an instructor can teach more than one subject, work in
more than one department, etc.

For you first app, you didn't exactly pick a simple one, but everyone loves
a challenge, right? g

Good luck and welcome to the world of Access.

--
_________

Sean Bailey


"Aria" wrote:

Bruce,
First, I would really like to thank you for responding. I need to clarify
some of the information that seems to be confusing. tblSubs is a table for
temporary, roving district staff. They may be employed at any district site
anywhere from a couple of hours to long-term(months). They need access to
their assignment location and if they are certificated, temporary passwords
for certain reports.

My comments are as follows:
tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

I think I'm fairly comfortable with this table. I just thought maybe it
needed to be broken down (not that I'm looking for another table to put in).
Yes, the fields are seperate.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus, or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query as
the Row Source for a RoomType combo box on your Keys form.


Now, this is where you lose me. I don't understand the purpose of a Locks
table. Isn't that what the keys table is for? We are only interested in the
locks as far as what key will staff to gain access to the room, gate,
stadium, etc. We don't normally track file cabinet keys. We may if the
contents are extremely confidential.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber


EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.

Sorry; I dropped the ball on this one. This *is* long interger. I forgot to
make the change in designation from my original.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text

KeyID as the PK of tblRooms could be confusing.

Again, you lost me on this one. Why is the KeyID confusing? It relates to
the room; knowing the key ID will allow you access. My thinking was that if I
could do a parameter query (is that what I would want?) by room, I would know
which key will unlock it. Then I could make a key request or whatever else
needs to be done.


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


This table's purpose is unclear.

I'm sorry. I should have explained in the beginning. Like I stated, this is
for temporary, roving staff. We need Access only in relation to keys for this
group. We have other applications for the rest of the information we need.

1. How many fields are too many in a table? I understand that Access will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.


No problem. More than 30 fields could suggest normalization problems, but
it not an invariable rule. If the SchoolData fields are not all filled in
for every record it could be that SchoolData could be in a separate table.
Nothing else stands out much.

All the fields relating to school data wil be filled in, so I guess we're
good here.


2.tblRooms has a field for Rm.#; the problem is that not all rooms have a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to
tblRooms because I may have to describe a location rather than a room #.
Will
there be a problem with this method?


Only if RoomNumber is a linking field. By the way, I assume you are using
the number sign for description, not as a field name. Names should include
only letters, number, and underscores. Spaces and other non-alphanumeric
characters are best avoided.

Oh my gosh! How many times have I read the same thing and because I *knew*
my fields weren't like that, moved on. Well, I just wanted to double check
and lo and behold, there it was; a # sign in my field name. Thanks for the
heads up!

One of the desired reports is a reverse directory by room #/room phone #.
The phone # is currently in tblEmployees. Should it be moved to tblRooms?
If
I leave it where it is, will I be able to update any queries and reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it would
be
better if the phone # was listed with tblRooms but someone told me that's
incorrect.


If the phone number is associated with a room it should be part of a room
record. Presumably an employee can be associated with several rooms, and
vice versa. However, if an employee has an office where only he or she
answers the phone, the phone number is associated with the employee. Often
in such case the phone number follows the employee if they move to another
office. There is no single answer to your question. Your database can be
made to accept either a phone associated with a room or with an employee, or
both. It does start to become more complex if you have both situations.

Well, for classroom staff, if they have to change rooms (happens every year,
so I have no illusions here), they switch to the new rooms' phone #. For
office staff, it could go either way. What do I need to do if anything?


3. I forgot to include the following fields: Date assigned, Return Date
and
Permission to Retain (over the summer) for Emp. Keys. Should this be added
to
tblKeys or tblEmployees? I was told not to put *anything* into the
junction
table (tblKeysEmployees) besides the composite key already listed.


You can have other fields in a junction table. For instance, the
KeysEmployees junction table may include a DateIssued field, which is a
property of neither the Employee nor the Key, but rather of this particular
key together with this particular employee. If a key may be retained over
the summer by one employee, but not another, then this too is a candidate
for a field in the junction table.

Your statements are true enough; only the master keys are coded to a
particular employee. Classroom keys are a little more generic in that anyone
who works out of that classroom or office will have the same key. O.k., I'll
put the new fields here.

4.Keys Requests--Sometimes a request will not have a name associated
with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I need to
order a sub. key, I can use the name of the permanent staff member
associated
with that room. What do I do about the vault? Should I enter "vault" in
the
emp. table w/o any additional data?


Vault chould not be in the employee table, but the situation is unclear. Is
nobody responsible for the vault key? How will you know who has it? How do
you know now?

Every key available on both campuses should be in the vault. Theoretically,
vault keys remain in the vault. I guess I'm responsible for those keys but
anyone who has the combination can remove a key without my knowledge. I guess
I could list myself and under Remarks indicate it's a vault request.

5. Maybe I should cross this bridge when I get to it but I eventually will
get to the point where I will make forms and subforms. There are approx.
400
seperate keys, not including multiple copies of the same key. If I have a
combo box, is there a way to filter so that I am not looking at all 400
possiblities at once?


Again, I'm not sure what you're asking. You will need forms and subforms as
soon as you start doing data entry. It may be best to begin with just
tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to
work with the junction table arrangement.

I'll hold off on this question and concentrate on getting the tables and
fields correct.

Bruce, again, I really appreciate all of your help. Thanks so much for
talking me through it!











--
Aria W.


"BruceM" wrote:

Responses inline.

"Aria" wrote in message
...
Hi,
I'm still struggling to organize my tables and fields but I'm not as
*totally confused* as my original post; at least I hope I'm not. I'm new
to
Access. This is my first database and I fluctuate between feeling hopeful
I
can do this one minute and despairing that I can't the next because I've
thought of yet another complication that I don't know how to handle. I
have
scoured the Internet and this disscussion group searching for the answers
that I need. I found some but I am still at a loss as to what to do about
others. I was wondering if someone would be kind enough to review my table
structure and respond to some questions at the end. I am truly grateful.

The information is as follows:

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus, or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query as
the Row Source for a RoomType combo box on your Keys form.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber


EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.


tblKeysRequests
Request ID-Autonumber
Key ID (FK to tblKeys)- text
Emp. ID
Rm. number- text--I think there is a problem here. This info is part of
tblrooms.

  #5  
Old June 19th, 2008, 01:10 PM posted to microsoft.public.access.gettingstarted
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default Still Struggling...

"Beetle" wrote in message
...
Hate to throw in the monkey wrench, but I'm going to have to disagree
with some of what's been posted (comments inline).

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


Sorry, but the school data belongs in a separate table/tables. With the
above
structure you will repeatedly be entering the same descriptive data over
and
over. Additionally, what if an instructor teaches more than one Subject,
works
in more than one Dept., etc? You will have to enter additional records for
the same employee.


You make some valid points about subjects and departments, and about titles
too if somebody holds more than one. I'm not sure exactly what is meant by
classification, so I couldn't say if there could be more than one. It
depends in part on how they do things. However, the posting was about keys,
so correctly or not I chose not to get into a discussion of the Employee
table.


tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in is
an attribute of the Wing (not the Room or the Key). You should have
separate
tables for Campus' and Wings


You may be correct about the RoomID as the FK. However, it isn't
necessarily as simple as a key opening a room (period). The building could
have a key, for instance, or there could be a padlock or other lock that is
not a room (or supply closet) lock. The OP said they don't "really" track
file cabinet keys and such, but if they ever have a key to something other
than a room provisions will need to be made for that. This is why I
suggeested a Locks table. A lock could have several keys. A Locks table
would need to specify the lock's location in a way that does not necessarily
reference a room (in what room is a building's front door located?). Also,
a room having several different locks (in an auditorium, for instance, or a
room with an outside door and a hallway door) is not out of the question.


tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


This table should have RoomID as a PK (KeyID does not belong here).
A room can have many keys, but a key can only open one room,
so RoomID goes in tblKeys as a foreign key, not the other way around.

tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


IMO this table is unecessary. Whether an employee is "full time" or
"temporary" is just an attribute that would go in tblEmployees. The fact
that a person may come and go as a member of your staff is irrelevant
to the fact that that person was in posession of a certain key at a
certain time. The date that a key was issued/returned belongs in
tblKeysEmployees.

You might be thinking "great, now I have to add more tables" but keep
in mind that in an application like this you will typically have several
tables
that will basically just be "lookup" tables. They aren't really a big
deal to set up. Just from the little I know about your app, I would say
you would probably have the following tables that would be
"lookup" tables;

tblClassifications

tblDepartments

tblSubjects

tblCampuses

tblWings

tblRoomTypes

You may also need some additional junction tables, depending on if - as
I stated earlier - an instructor can teach more than one subject, work in
more than one department, etc.

For you first app, you didn't exactly pick a simple one, but everyone
loves
a challenge, right? g

Good luck and welcome to the world of Access.

--
_________

Sean Bailey


"Aria" wrote:

Bruce,
First, I would really like to thank you for responding. I need to clarify
some of the information that seems to be confusing. tblSubs is a table
for
temporary, roving district staff. They may be employed at any district
site
anywhere from a couple of hours to long-term(months). They need access
to
their assignment location and if they are certificated, temporary
passwords
for certain reports.

My comments are as follows:
tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of
fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

I think I'm fairly comfortable with this table. I just thought maybe it
needed to be broken down (not that I'm looking for another table to put
in).
Yes, the fields are seperate.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus,
or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup
fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of
RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query
as
the Row Source for a RoomType combo box on your Keys form.


Now, this is where you lose me. I don't understand the purpose of a Locks
table. Isn't that what the keys table is for? We are only interested in
the
locks as far as what key will staff to gain access to the room, gate,
stadium, etc. We don't normally track file cabinet keys. We may if the
contents are extremely confidential.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber


EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.

Sorry; I dropped the ball on this one. This *is* long interger. I forgot
to
make the change in designation from my original.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text

KeyID as the PK of tblRooms could be confusing.

Again, you lost me on this one. Why is the KeyID confusing? It relates to
the room; knowing the key ID will allow you access. My thinking was that
if I
could do a parameter query (is that what I would want?) by room, I would
know
which key will unlock it. Then I could make a key request or whatever
else
needs to be done.


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


This table's purpose is unclear.

I'm sorry. I should have explained in the beginning. Like I stated, this
is
for temporary, roving staff. We need Access only in relation to keys for
this
group. We have other applications for the rest of the information we
need.

1. How many fields are too many in a table? I understand that Access
will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.


No problem. More than 30 fields could suggest normalization problems, but
it not an invariable rule. If the SchoolData fields are not all filled in
for every record it could be that SchoolData could be in a separate
table.
Nothing else stands out much.

All the fields relating to school data wil be filled in, so I guess we're
good here.


2.tblRooms has a field for Rm.#; the problem is that not all rooms have
a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks
to
tblRooms because I may have to describe a location rather than a room
#.
Will
there be a problem with this method?


Only if RoomNumber is a linking field. By the way, I assume you are using
the number sign for description, not as a field name. Names should
include
only letters, number, and underscores. Spaces and other non-alphanumeric
characters are best avoided.

Oh my gosh! How many times have I read the same thing and because I
*knew*
my fields weren't like that, moved on. Well, I just wanted to double
check
and lo and behold, there it was; a # sign in my field name. Thanks for
the
heads up!

One of the desired reports is a reverse directory by room #/room phone
#.
The phone # is currently in tblEmployees. Should it be moved to
tblRooms?
If
I leave it where it is, will I be able to update any queries and
reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it
would
be
better if the phone # was listed with tblRooms but someone told me
that's
incorrect.


If the phone number is associated with a room it should be part of a room
record. Presumably an employee can be associated with several rooms, and
vice versa. However, if an employee has an office where only he or she
answers the phone, the phone number is associated with the employee.
Often
in such case the phone number follows the employee if they move to
another
office. There is no single answer to your question. Your database can be
made to accept either a phone associated with a room or with an employee,
or
both. It does start to become more complex if you have both situations.

Well, for classroom staff, if they have to change rooms (happens every
year,
so I have no illusions here), they switch to the new rooms' phone #. For
office staff, it could go either way. What do I need to do if anything?


3. I forgot to include the following fields: Date assigned, Return Date
and
Permission to Retain (over the summer) for Emp. Keys. Should this be
added
to
tblKeys or tblEmployees? I was told not to put *anything* into the
junction
table (tblKeysEmployees) besides the composite key already listed.


You can have other fields in a junction table. For instance, the
KeysEmployees junction table may include a DateIssued field, which is a
property of neither the Employee nor the Key, but rather of this
particular
key together with this particular employee. If a key may be retained over
the summer by one employee, but not another, then this too is a candidate
for a field in the junction table.

Your statements are true enough; only the master keys are coded to a
particular employee. Classroom keys are a little more generic in that
anyone
who works out of that classroom or office will have the same key. O.k.,
I'll
put the new fields here.

4.Keys Requests--Sometimes a request will not have a name associated
with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I need
to
order a sub. key, I can use the name of the permanent staff member
associated
with that room. What do I do about the vault? Should I enter "vault" in
the
emp. table w/o any additional data?


Vault chould not be in the employee table, but the situation is unclear.
Is
nobody responsible for the vault key? How will you know who has it? How
do
you know now?

Every key available on both campuses should be in the vault.
Theoretically,
vault keys remain in the vault. I guess I'm responsible for those keys
but
anyone who has the combination can remove a key without my knowledge. I
guess
I could list myself and under Remarks indicate it's a vault request.

5. Maybe I should cross this bridge when I get to it but I eventually

will
get to the point where I will make forms and subforms. There are
approx.
400
seperate keys, not including multiple copies of the same key. If I have
a
combo box, is there a way to filter so that I am not looking at all 400
possiblities at once?


Again, I'm not sure what you're asking. You will need forms and subforms
as
soon as you start doing data entry. It may be best to begin with just
tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to
work with the junction table arrangement.

I'll hold off on this question and concentrate on getting the tables and
fields correct.

Bruce, again, I really appreciate all of your help. Thanks so much for
talking me through it!











--
Aria W.


"BruceM" wrote:

Responses inline.

"Aria" wrote in message
...
Hi,
I'm still struggling to organize my tables and fields but I'm not as
*totally confused* as my original post; at least I hope I'm not. I'm
new
to
Access. This is my first database and I fluctuate between feeling
hopeful
I
can do this one minute and despairing that I can't the next because
I've
thought of yet another complication that I don't know how to handle.
I
have
scoured the Internet and this disscussion group searching for the
answers
that I need. I found some but I am still at a loss as to what to do
about
others. I was wondering if someone would be kind enough to review my
table
structure and respond to some questions at the end. I am truly
grateful.

The information is as follows:

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

I ws going to say that SchoolData and PersonalInfo should be broken
into
several fields each, but later you said something about the number of
fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text

One lock could have several keys (one for each of several people).
This
suggests you need a Locks table, separate from the Keys table. Each
lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be
flexible.
One approach to the Locks table would be to have fields for Campus,
Wing,
and Room. You could query the table for locks that go with a
particular
campus, building, wing, and room, or for all of the locks for a campus,
or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup
fields)
to use for selecting campus, wing, and roomtype from combo boxes on
your
form. For instance, a RoomType table may be simply a listing of
RoomTypes.
Make a query based on this table (sorted by RoomType), and use the
query as
the Row Source for a RoomType combo box on your Keys form.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber

EmployeeID must be Number (Long Integer), not Autonumber. It is a
foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by
themselves
composite keys.


tblKeysRequests
Request ID-Autonumber
Key ID (FK to tblKeys)- text
Emp. ID
Rm. number- text--I think there is a problem here. This info is part
of
tblrooms.


  #6  
Old June 19th, 2008, 01:27 PM posted to microsoft.public.access.gettingstarted
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default Still Struggling...


"Aria" wrote in message
...
Bruce,
First, I would really like to thank you for responding. I need to clarify
some of the information that seems to be confusing. tblSubs is a table for
temporary, roving district staff. They may be employed at any district
site
anywhere from a couple of hours to long-term(months). They need access to
their assignment location and if they are certificated, temporary
passwords
for certain reports.

My comments are as follows:
tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of
fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

I think I'm fairly comfortable with this table. I just thought maybe it
needed to be broken down (not that I'm looking for another table to put
in).
Yes, the fields are seperate.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus, or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup
fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query
as
the Row Source for a RoomType combo box on your Keys form.


Now, this is where you lose me. I don't understand the purpose of a Locks
table. Isn't that what the keys table is for? We are only interested in
the
locks as far as what key will staff to gain access to the room, gate,
stadium, etc. We don't normally track file cabinet keys. We may if the
contents are extremely confidential.


A lock could have several keys. If a supply closet has three keys issued
and a fourth person needs a key, you need to know that another key has to be
made. The keys is an attribute of the lock, not the other way around. At
least that's how I see it. If a lock is changed you need a way to find the
existing keys so they can be swapped with the new ones.



tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber


EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.

Sorry; I dropped the ball on this one. This *is* long interger. I forgot
to
make the change in designation from my original.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text

KeyID as the PK of tblRooms could be confusing.

Again, you lost me on this one. Why is the KeyID confusing? It relates to
the room; knowing the key ID will allow you access. My thinking was that
if I
could do a parameter query (is that what I would want?) by room, I would
know
which key will unlock it. Then I could make a key request or whatever else
needs to be done.


As Beetle pointed out, RoomID is the PK of tblRooms (although again, I would
identify Locks, which are not necessarily for rooms). A room (or lock)
could have several keys, so KeyID is the FK to tblRooms (or tblLocks).


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


This table's purpose is unclear.

I'm sorry. I should have explained in the beginning. Like I stated, this
is
for temporary, roving staff. We need Access only in relation to keys for
this
group. We have other applications for the rest of the information we need.


Again, as Beetle suggested there is no need for this group to be separate
from the people listed in tblEmployees (or tblPersonnel, if you prefer to
look at it that way).


1. How many fields are too many in a table? I understand that Access will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.


No problem. More than 30 fields could suggest normalization problems, but
it not an invariable rule. If the SchoolData fields are not all filled in
for every record it could be that SchoolData could be in a separate table.
Nothing else stands out much.

All the fields relating to school data wil be filled in, so I guess we're
good here.


Beetle made a good point about this if there somebody could have more than
one subject, etc. I wish he had included more of the remarks when he
replied. He posted that he was disagreeing with me (I think) about some
things, but he did not include those things, so this thread is a bit
fragmented. I have some other remarks in response to his reply.



2.tblRooms has a field for Rm.#; the problem is that not all rooms have a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to
tblRooms because I may have to describe a location rather than a room #.
Will
there be a problem with this method?


Only if RoomNumber is a linking field. By the way, I assume you are using
the number sign for description, not as a field name. Names should include
only letters, number, and underscores. Spaces and other non-alphanumeric
characters are best avoided.

Oh my gosh! How many times have I read the same thing and because I *knew*
my fields weren't like that, moved on. Well, I just wanted to double check
and lo and behold, there it was; a # sign in my field name. Thanks for the
heads up!

One of the desired reports is a reverse directory by room #/room phone #.
The phone # is currently in tblEmployees. Should it be moved to tblRooms?
If
I leave it where it is, will I be able to update any queries and reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it would
be
better if the phone # was listed with tblRooms but someone told me that's
incorrect.


If the phone number is associated with a room it should be part of a room
record. Presumably an employee can be associated with several rooms, and
vice versa. However, if an employee has an office where only he or she
answers the phone, the phone number is associated with the employee. Often
in such case the phone number follows the employee if they move to another
office. There is no single answer to your question. Your database can be
made to accept either a phone associated with a room or with an employee,
or
both. It does start to become more complex if you have both situations.

Well, for classroom staff, if they have to change rooms (happens every
year,
so I have no illusions here), they switch to the new rooms' phone #. For
office staff, it could go either way. What do I need to do if anything?


If a staff member has a fixed phone number, that should be part of their
Employee record. If a room has a phone number, that is an attribute of the
room, so you would start to get into having the phone number be associated
with a person's schedule, which is an other project. Essentially you would
need to have the person's schedule. If they are in room 101 from 10:00 -
noon, from those hours you would need to contact them there. I'm not sure
there is a simple answer to this question.



3. I forgot to include the following fields: Date assigned, Return Date
and
Permission to Retain (over the summer) for Emp. Keys. Should this be
added
to
tblKeys or tblEmployees? I was told not to put *anything* into the
junction
table (tblKeysEmployees) besides the composite key already listed.


You can have other fields in a junction table. For instance, the
KeysEmployees junction table may include a DateIssued field, which is a
property of neither the Employee nor the Key, but rather of this
particular
key together with this particular employee. If a key may be retained over
the summer by one employee, but not another, then this too is a candidate
for a field in the junction table.

Your statements are true enough; only the master keys are coded to a
particular employee. Classroom keys are a little more generic in that
anyone
who works out of that classroom or office will have the same key. O.k.,
I'll
put the new fields here.

4.Keys Requests--Sometimes a request will not have a name associated
with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I need
to
order a sub. key, I can use the name of the permanent staff member
associated
with that room. What do I do about the vault? Should I enter "vault" in
the
emp. table w/o any additional data?


Vault chould not be in the employee table, but the situation is unclear.
Is
nobody responsible for the vault key? How will you know who has it? How do
you know now?

Every key available on both campuses should be in the vault.
Theoretically,
vault keys remain in the vault. I guess I'm responsible for those keys but
anyone who has the combination can remove a key without my knowledge. I
guess
I could list myself and under Remarks indicate it's a vault request.


It sounds as if the vault contains the originals from which copies are made.
If so, and if the vault has a combination lock, I'm not sure the vault fits
into your schema.


5. Maybe I should cross this bridge when I get to it but I eventually
will
get to the point where I will make forms and subforms. There are approx.
400
seperate keys, not including multiple copies of the same key. If I have a
combo box, is there a way to filter so that I am not looking at all 400
possiblities at once?


Again, I'm not sure what you're asking. You will need forms and subforms
as
soon as you start doing data entry. It may be best to begin with just
tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to
work with the junction table arrangement.

I'll hold off on this question and concentrate on getting the tables and
fields correct.

Bruce, again, I really appreciate all of your help. Thanks so much for
talking me through it!











--
Aria W.


"BruceM" wrote:

Responses inline.

"Aria" wrote in message
...
Hi,
I'm still struggling to organize my tables and fields but I'm not as
*totally confused* as my original post; at least I hope I'm not. I'm
new
to
Access. This is my first database and I fluctuate between feeling
hopeful
I
can do this one minute and despairing that I can't the next because
I've
thought of yet another complication that I don't know how to handle. I
have
scoured the Internet and this disscussion group searching for the
answers
that I need. I found some but I am still at a loss as to what to do
about
others. I was wondering if someone would be kind enough to review my
table
structure and respond to some questions at the end. I am truly
grateful.

The information is as follows:

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of
fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus,
or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup
fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of
RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query
as
the Row Source for a RoomType combo box on your Keys form.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber


EmployeeID must be Number (Long Integer), not Autonumber. It is a
foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.


tblKeysRequests
Request ID-Autonumber
Key ID (FK to tblKeys)- text
Emp. ID
Rm. number- text--I think there is a problem here. This info is part of
tblrooms.
Rm. phone- text
Date Requested- Date/time
Date Recvd.- Date/time
Date Issued- Date/time




tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


KeyID as the PK of tblRooms could be confusing.


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


This table's purpose is unclear.


If you're still reading, I have the following questions:
1. How many fields are too many in a table? I understand that Access
will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.


No problem. More than 30 fields could suggest normalization problems,
but
it not an invariable rule. If the SchoolData fields are not all filled
in
for every record it could be that SchoolData could be in a separate
table.
Nothing else stands out much.


2.tblRooms has a field for Rm.#; the problem is that not all rooms have
a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks
to
tblRooms because I may have to describe a location rather than a room
#.
Will
there be a problem with this method?


Only if RoomNumber is a linking field. By the way, I assume you are
using
the number sign for description, not as a field name. Names should
include
only letters, number, and underscores. Spaces and other non-alphanumeric
characters are best avoided.

One of the desired reports is a reverse directory by room #/room phone
#.
The phone # is currently in tblEmployees. Should it be moved to
tblRooms?
If
I leave it where it is, will I be able to update any queries and
reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it
would
be
better if the phone # was listed with tblRooms but someone told me
that's
incorrect.


If the phone number is associated with a room it should be part of a room
record. Presumably an employee can be associated with several rooms, and
vice versa. However, if an employee has an office where only he or she
answers the phone, the phone number is associated with the employee.
Often
in such case the phone number follows the employee if they move to
another
office. There is no single answer to your question. Your database can
be
made to accept either a phone associated with a room or with an employee,
or
both. It does start to become more complex if you have both situations.


3. I forgot to include the following fields: Date assigned, Return Date
and
Permission to Retain (over the summer) for Emp. Keys. Should this be
added
to
tblKeys or tblEmployees? I was told not to put *anything* into the
junction
table (tblKeysEmployees) besides the composite key already listed.


You can have other fields in a junction table. For instance, the
KeysEmployees junction table may include a DateIssued field, which is a
property of neither the Employee nor the Key, but rather of this
particular
key together with this particular employee. If a key may be retained
over
the summer by one employee, but not another, then this too is a candidate
for a field in the junction table.


4.Keys Requests--Somethimes are request will not have a name associated
with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I need
to
order a sub. key, I can use the name of the permanent staff member
associated
with that room. What do I do about the vault? Should I enter "vault" in
the
emp. table w/o any additional data?


Vault chould not be in the employee table, but the situation is unclear.
Is
nobody responsible for the vault key? How will you know who has it? How
do
you know now?

5. Maybe I should cross this bridge when I get to it but I eventually
will
get to the point where I will make forms and subforms. There are
approx.
400
seperate keys, not including multiple copies of the same key. If I have
a
combo box, is there a way to filter so that I am not looking at all 400
possiblities at once?


Again, I'm not sure what you're asking. You will need forms and subforms
as
soon as you start doing data entry. It may be best to begin with just
tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to
work with the junction table arrangement.


I am so sorry if this is too long and I'm asking too many questions at
once.
I understand that you are trying to help as many people as possible. I
have
searched and searched for answers and tried to adapt the
customers/orders
format as much as possible to my situation but...I need help.

--
Aria W.




  #7  
Old June 19th, 2008, 05:41 PM posted to microsoft.public.access.gettingstarted
Aria
external usenet poster
 
Posts: 150
Default Still Struggling...

Beetle,
(lol) Yes, you have definitely thrown a monkey wrench into the works! You
have raised some valid points that I need to take into account. I have
thought about what you posted and would like to clarify some points and ask
additional questions.

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


Sorry, but the school data belongs in a separate table/tables. With the above
structure you will repeatedly be entering the same descriptive data over and
over. Additionally, what if an instructor teaches more than one Subject, works
in more than one Dept., etc? You will have to enter additional records for
the same employee.


Does school data need an additional table based solely on instuctors
teaching more than one subject? You are correct though. Teachers can teach
more than one subject. I only ask because you said, "additionally", which
implies that this will need a new table regardless. Other than "Subject", I
don't understand how I would be entering the same information again and
again. What am I missing?

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in is
an attribute of the Wing (not the Room or the Key). You should have separate
tables for Campus' and Wings


Point taken; although I don't understand why the campus location is not a
room attribute. I need to know where that room or storage room is
located(especially since storage rooms don't have room #s). Does it make a
difference if there are only 2 campuses? No? So, is it CampusID (FK to
tblRooms)? Same for tblWings?
Also, I meant to put KeyType (Storage, Classroom, Gate Master, Grand Master,
etc.) as a field here.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


This table should have RoomID as a PK (KeyID does not belong here).
A room can have many keys, but a key can only open one room,
so RoomID goes in tblKeys as a foreign key, not the other way around.


Good point; I'll change this. Your second sentence is not exactly true. Just
to clarify, key assignments are based on job title and extracurricular duties
(coaching, tutoring, special projects, etc.). Masters allow all access per
location. So one key may open one or many doors. As I stated previously, you
are correct about the room type and wing being attributes of the room. Why
not "Campus"?
In addition, the stadium and storage rooms do not have a room #, nor does it
belong to a wing. Do I need additional tables for this?

tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


IMO this table is unecessary. Whether an employee is "full time" or
"temporary" is just an attribute that would go in tblEmployees. The fact
that a person may come and go as a member of your staff is irrelevant
to the fact that that person was in posession of a certain key at a
certain time. The date that a key was issued/returned belongs in
tblKeysEmployees.


I don't understand. If I add the substitute staff to the our employee table,
most of the fields will be empty. They are not required to disclose their
address or emergency info. There address is on file with the district. In
addition, they are not bound to teach any subject in particular. If there is
a request for a P.E. instructor but they normally teach English, they are
free to accept the position for the time requested. This is why I am adding a
"Preferred Subject" field. I want the sub to enjoy there time teaching and
the permanent staff to feel comfortable with whomever is taking over their
class. I am often asked for recommendations; I'd like some info to go along
with that. If we have a long-term position for a Special Education
instructor, I need to know who has that credential so we can move ASAP. Oddly
enough, the district doesn't track this info; I've asked. If I have the subs
in their own table, I think all the info I need will be together.

You might be thinking "great, now I have to add more tables" but keep
in mind that in an application like this you will typically have several
tables
that will basically just be "lookup" tables. They aren't really a big
deal to set up.


(lol) That is *exactly* what I was thinking!

You may also need some additional junction tables, depending on if - as
I stated earlier - an instructor can teach more than one subject, work in
more than one department, etc.


(heavy sigh...as my head hits the desk) Which ones? tblSubjects?

For you first app, you didn't exactly pick a simple one, but everyone loves
a challenge, right? g


Thank you for saying that! I kept wondering,"Why am I having such a hard
time with this?" Why am I still on the pen, paper and diagram stage?
I like a challenge because it makes success that much sweeter but *come
on*...! It makes it difficult to make a move when you are about ready to step
off the cliff at every turn. If it weren't for this discussion group, I would
have. Thanks to both you and Bruce for posting your reasons and thought
process along with your comments. I don't know about others, but it helps me
to follow along. I t takes an extra measure of patience that I appreciate. I
am going to rethink my tables and post them again. I hope you will take a
look and tell me what you think.

--
Aria W.


"Beetle" wrote:

Hate to throw in the monkey wrench, but I'm going to have to disagree
with some of what's been posted (comments inline).

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


Sorry, but the school data belongs in a separate table/tables. With the above
structure you will repeatedly be entering the same descriptive data over and
over. Additionally, what if an instructor teaches more than one Subject, works
in more than one Dept., etc? You will have to enter additional records for
the same employee.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in is
an attribute of the Wing (not the Room or the Key). You should have separate
tables for Campus' and Wings

tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


This table should have RoomID as a PK (KeyID does not belong here).
A room can have many keys, but a key can only open one room,
so RoomID goes in tblKeys as a foreign key, not the other way around.

tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


IMO this table is unecessary. Whether an employee is "full time" or
"temporary" is just an attribute that would go in tblEmployees. The fact
that a person may come and go as a member of your staff is irrelevant
to the fact that that person was in posession of a certain key at a
certain time. The date that a key was issued/returned belongs in
tblKeysEmployees.

You might be thinking "great, now I have to add more tables" but keep
in mind that in an application like this you will typically have several
tables
that will basically just be "lookup" tables. They aren't really a big
deal to set up. Just from the little I know about your app, I would say
you would probably have the following tables that would be
"lookup" tables;

tblClassifications

tblDepartments

tblSubjects

tblCampuses

tblWings

tblRoomTypes

You may also need some additional junction tables, depending on if - as
I stated earlier - an instructor can teach more than one subject, work in
more than one department, etc.

For you first app, you didn't exactly pick a simple one, but everyone loves
a challenge, right? g

Good luck and welcome to the world of Access.

--
_________

Sean Bailey


"Aria" wrote:

Bruce,
First, I would really like to thank you for responding. I need to clarify
some of the information that seems to be confusing. tblSubs is a table for
temporary, roving district staff. They may be employed at any district site
anywhere from a couple of hours to long-term(months). They need access to
their assignment location and if they are certificated, temporary passwords
for certain reports.

My comments are as follows:
tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

I think I'm fairly comfortable with this table. I just thought maybe it
needed to be broken down (not that I'm looking for another table to put in).
Yes, the fields are seperate.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus, or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query as
the Row Source for a RoomType combo box on your Keys form.


Now, this is where you lose me. I don't understand the purpose of a Locks
table. Isn't that what the keys table is for? We are only interested in the
locks as far as what key will staff to gain access to the room, gate,
stadium, etc. We don't normally track file cabinet keys. We may if the
contents are extremely confidential.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber


EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.

Sorry; I dropped the ball on this one. This *is* long interger. I forgot to
make the change in designation from my original.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text

KeyID as the PK of tblRooms could be confusing.

Again, you lost me on this one. Why is the KeyID confusing? It relates to
the room; knowing the key ID will allow you access. My thinking was that if I
could do a parameter query (is that what I would want?) by room, I would know
which key will unlock it. Then I could make a key request or whatever else
needs to be done.


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


This table's purpose is unclear.

I'm sorry. I should have explained in the beginning. Like I stated, this is
for temporary, roving staff. We need Access only in relation to keys for this
group. We have other applications for the rest of the information we need.

1. How many fields are too many in a table? I understand that Access will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.


No problem. More than 30 fields could suggest normalization problems, but
it not an invariable rule. If the SchoolData fields are not all filled in
for every record it could be that SchoolData could be in a separate table.
Nothing else stands out much.

All the fields relating to school data wil be filled in, so I guess we're
good here.


2.tblRooms has a field for Rm.#; the problem is that not all rooms have a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to
tblRooms because I may have to describe a location rather than a room #.
Will
there be a problem with this method?


Only if RoomNumber is a linking field. By the way, I assume you are using
the number sign for description, not as a field name. Names should include
only letters, number, and underscores. Spaces and other non-alphanumeric
characters are best avoided.

Oh my gosh! How many times have I read the same thing and because I *knew*
my fields weren't like that, moved on. Well, I just wanted to double check
and lo and behold, there it was; a # sign in my field name. Thanks for the
heads up!

One of the desired reports is a reverse directory by room #/room phone #.
The phone # is currently in tblEmployees. Should it be moved to tblRooms?
If
I leave it where it is, will I be able to update any queries and reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it would
be
better if the phone # was listed with tblRooms but someone told me that's
incorrect.


If the phone number is associated with a room it should be part of a room
record. Presumably an employee can be associated with several rooms, and
vice versa. However, if an employee has an office where only he or she
answers the phone, the phone number is associated with the employee. Often
in such case the phone number follows the employee if they move to another
office. There is no single answer to your question. Your database can be
made to accept either a phone associated with a room or with an employee, or
both. It does start to become more complex if you have both situations.

Well, for classroom staff, if they have to change rooms (happens every year,
so I have no illusions here), they switch to the new rooms' phone #. For
office staff, it could go either way. What do I need to do if anything?


3. I forgot to include the following fields: Date assigned, Return Date
and
Permission to Retain (over the summer) for Emp. Keys. Should this be added
to
tblKeys or tblEmployees? I was told not to put *anything* into the
junction
table (tblKeysEmployees) besides the composite key already listed.


You can have other fields in a junction table. For instance, the
KeysEmployees junction table may include a DateIssued field, which is a
property of neither the Employee nor the Key, but rather of this particular
key together with this particular employee. If a key may be retained over
the summer by one employee, but not another, then this too is a candidate
for a field in the junction table.

Your statements are true enough; only the master keys are coded to a
particular employee. Classroom keys are a little more generic in that anyone
who works out of that classroom or office will have the same key. O.k., I'll
put the new fields here.

4.Keys Requests--Sometimes a request will not have a name associated
with
it. For instance, we need to request a key for the vault. I have not
accounted for this situation and don't know how to handle it. If I need to
order a sub. key, I can use the name of the permanent staff member
associated
with that room. What do I do about the vault? Should I enter "vault" in
the
emp. table w/o any additional data?


Vault chould not be in the employee table, but the situation is unclear. Is
nobody responsible for the vault key? How will you know who has it? How do
you know now?

Every key available on both campuses should be in the vault. Theoretically,
vault keys remain in the vault. I guess I'm responsible for those keys but
anyone who has the combination can remove a key without my knowledge. I guess
I could list myself and under Remarks indicate it's a vault request.

5. Maybe I should cross this bridge when I get to it but I eventually will
get to the point where I will make forms and subforms. There are approx.
400
seperate keys, not including multiple copies of the same key. If I have a
combo box, is there a way to filter so that I am not looking at all 400
possiblities at once?


Again, I'm not sure what you're asking. You will need forms and subforms as
soon as you start doing data entry. It may be best to begin with just

  #8  
Old June 19th, 2008, 06:46 PM posted to microsoft.public.access.gettingstarted
Beetle
external usenet poster
 
Posts: 1,254
Default Still Struggling...

Sorry if my reply came off kind of negative. I didn't mean to be.

Sorry, but the school data belongs in a separate table/tables. With the
above
structure you will repeatedly be entering the same descriptive data over
and
over. Additionally, what if an instructor teaches more than one Subject,
works
in more than one Dept., etc? You will have to enter additional records for
the same employee.


You make some valid points about subjects and departments, and about titles
too if somebody holds more than one. I'm not sure exactly what is meant by
classification, so I couldn't say if there could be more than one. It
depends in part on how they do things. However, the posting was about keys,
so correctly or not I chose not to get into a discussion of the Employee
table.


I sort of knew you were trying to focus more on the keys, so when the OP
said they were satisfied with the Employees table I wanted them to at
least take another look at it and give it some more thought before moving on.

Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in is
an attribute of the Wing (not the Room or the Key). You should have
separate
tables for Campus' and Wings


You may be correct about the RoomID as the FK. However, it isn't
necessarily as simple as a key opening a room (period). The building could
have a key, for instance, or there could be a padlock or other lock that is
not a room (or supply closet) lock. The OP said they don't "really" track
file cabinet keys and such, but if they ever have a key to something other
than a room provisions will need to be made for that. This is why I
suggeested a Locks table. A lock could have several keys. A Locks table
would need to specify the lock's location in a way that does not necessarily
reference a room (in what room is a building's front door located?). Also,
a room having several different locks (in an auditorium, for instance, or a
room with an outside door and a hallway door) is not out of the question.


I should have re-worded this part of my response, as I can see now that
it does not get my point across very well. I actually agree with you about the
locks. What I was trying to do was get the OP to realize that things like
Room Type, etc. are not attributes of the key. All a key does is - as you
correctly point out - open a particular lock. Where that lock is located is
an attribute of the lock, not the key.

Again, apologies if I came across as if I was saying you were wrong, that
wasn't my intention.

Regards,

Sean Bailey


"BruceM" wrote:

"Beetle" wrote in message
...
Hate to throw in the monkey wrench, but I'm going to have to disagree
with some of what's been posted (comments inline).

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


Sorry, but the school data belongs in a separate table/tables. With the
above
structure you will repeatedly be entering the same descriptive data over
and
over. Additionally, what if an instructor teaches more than one Subject,
works
in more than one Dept., etc? You will have to enter additional records for
the same employee.


You make some valid points about subjects and departments, and about titles
too if somebody holds more than one. I'm not sure exactly what is meant by
classification, so I couldn't say if there could be more than one. It
depends in part on how they do things. However, the posting was about keys,
so correctly or not I chose not to get into a discussion of the Employee
table.


tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in is
an attribute of the Wing (not the Room or the Key). You should have
separate
tables for Campus' and Wings


You may be correct about the RoomID as the FK. However, it isn't
necessarily as simple as a key opening a room (period). The building could
have a key, for instance, or there could be a padlock or other lock that is
not a room (or supply closet) lock. The OP said they don't "really" track
file cabinet keys and such, but if they ever have a key to something other
than a room provisions will need to be made for that. This is why I
suggeested a Locks table. A lock could have several keys. A Locks table
would need to specify the lock's location in a way that does not necessarily
reference a room (in what room is a building's front door located?). Also,
a room having several different locks (in an auditorium, for instance, or a
room with an outside door and a hallway door) is not out of the question.


tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


This table should have RoomID as a PK (KeyID does not belong here).
A room can have many keys, but a key can only open one room,
so RoomID goes in tblKeys as a foreign key, not the other way around.

tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


IMO this table is unecessary. Whether an employee is "full time" or
"temporary" is just an attribute that would go in tblEmployees. The fact
that a person may come and go as a member of your staff is irrelevant
to the fact that that person was in posession of a certain key at a
certain time. The date that a key was issued/returned belongs in
tblKeysEmployees.

You might be thinking "great, now I have to add more tables" but keep
in mind that in an application like this you will typically have several
tables
that will basically just be "lookup" tables. They aren't really a big
deal to set up. Just from the little I know about your app, I would say
you would probably have the following tables that would be
"lookup" tables;

tblClassifications

tblDepartments

tblSubjects

tblCampuses

tblWings

tblRoomTypes

You may also need some additional junction tables, depending on if - as
I stated earlier - an instructor can teach more than one subject, work in
more than one department, etc.

For you first app, you didn't exactly pick a simple one, but everyone
loves
a challenge, right? g

Good luck and welcome to the world of Access.

--
_________

Sean Bailey


"Aria" wrote:

Bruce,
First, I would really like to thank you for responding. I need to clarify
some of the information that seems to be confusing. tblSubs is a table
for
temporary, roving district staff. They may be employed at any district
site
anywhere from a couple of hours to long-term(months). They need access
to
their assignment location and if they are certificated, temporary
passwords
for certain reports.

My comments are as follows:
tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text
I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of
fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

I think I'm fairly comfortable with this table. I just thought maybe it
needed to be broken down (not that I'm looking for another table to put
in).
Yes, the fields are seperate.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text

One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus,
or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup
fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of
RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query
as
the Row Source for a RoomType combo box on your Keys form.


Now, this is where you lose me. I don't understand the purpose of a Locks
table. Isn't that what the keys table is for? We are only interested in
the
locks as far as what key will staff to gain access to the room, gate,
stadium, etc. We don't normally track file cabinet keys. We may if the
contents are extremely confidential.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber

EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.

Sorry; I dropped the ball on this one. This *is* long interger. I forgot
to
make the change in designation from my original.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text
KeyID as the PK of tblRooms could be confusing.

Again, you lost me on this one. Why is the KeyID confusing? It relates to
the room; knowing the key ID will allow you access. My thinking was that
if I
could do a parameter query (is that what I would want?) by room, I would
know
which key will unlock it. Then I could make a key request or whatever
else
needs to be done.


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time

This table's purpose is unclear.

I'm sorry. I should have explained in the beginning. Like I stated, this
is
for temporary, roving staff. We need Access only in relation to keys for
this
group. We have other applications for the rest of the information we
need.

1. How many fields are too many in a table? I understand that Access
will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about tblEmployees
where
there are approx. 25 fields.

No problem. More than 30 fields could suggest normalization problems, but
it not an invariable rule. If the SchoolData fields are not all filled in
for every record it could be that SchoolData could be in a separate
table.
Nothing else stands out much.

All the fields relating to school data wil be filled in, so I guess we're
good here.


2.tblRooms has a field for Rm.#; the problem is that not all rooms have
a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks
to
tblRooms because I may have to describe a location rather than a room
#.
Will
there be a problem with this method?

Only if RoomNumber is a linking field. By the way, I assume you are using
the number sign for description, not as a field name. Names should
include
only letters, number, and underscores. Spaces and other non-alphanumeric
characters are best avoided.

Oh my gosh! How many times have I read the same thing and because I
*knew*
my fields weren't like that, moved on. Well, I just wanted to double
check
and lo and behold, there it was; a # sign in my field name. Thanks for
the
heads up!

One of the desired reports is a reverse directory by room #/room phone
#.
The phone # is currently in tblEmployees. Should it be moved to
tblRooms?
If
I leave it where it is, will I be able to update any queries and
reports?
I've read that multitable queries aren't updateable. We have had mass
staff
room changes right before the school year begins. It just seems it
would
be
better if the phone # was listed with tblRooms but someone told me
that's
incorrect.

If the phone number is associated with a room it should be part of a room
record. Presumably an employee can be associated with several rooms, and

  #9  
Old June 19th, 2008, 07:04 PM posted to microsoft.public.access.gettingstarted
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default Still Struggling...

The curious thing was that you were replying to me without including my
wording, but in any case no offense taken.

I see from another posting that the OP has recognized some rethinking of the
Employee table may be in order. I built an Employee table for one
application, then ended up using it for others, so I can attest that a
well-designed table is a worthwhile investment of time. I say this because
my first attempt was not as well-designed as it might have been, so other
applications had to be updated in some cases.

Other comments in the OP's most recent reply show a growing realization
about other design elements. It was certainly a lot of project for a first
attempt. I expect the full extent wasn't really known at the time.

"Beetle" wrote in message
...
Sorry if my reply came off kind of negative. I didn't mean to be.

Sorry, but the school data belongs in a separate table/tables. With the
above
structure you will repeatedly be entering the same descriptive data
over
and
over. Additionally, what if an instructor teaches more than one
Subject,
works
in more than one Dept., etc? You will have to enter additional records
for
the same employee.


You make some valid points about subjects and departments, and about
titles
too if somebody holds more than one. I'm not sure exactly what is meant
by
classification, so I couldn't say if there could be more than one. It
depends in part on how they do things. However, the posting was about
keys,
so correctly or not I chose not to get into a discussion of the Employee
table.


I sort of knew you were trying to focus more on the keys, so when the OP
said they were satisfied with the Employees table I wanted them to at
least take another look at it and give it some more thought before moving
on.

Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of
it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in
is
an attribute of the Wing (not the Room or the Key). You should have
separate
tables for Campus' and Wings


You may be correct about the RoomID as the FK. However, it isn't
necessarily as simple as a key opening a room (period). The building
could
have a key, for instance, or there could be a padlock or other lock that
is
not a room (or supply closet) lock. The OP said they don't "really"
track
file cabinet keys and such, but if they ever have a key to something
other
than a room provisions will need to be made for that. This is why I
suggeested a Locks table. A lock could have several keys. A Locks table
would need to specify the lock's location in a way that does not
necessarily
reference a room (in what room is a building's front door located?).
Also,
a room having several different locks (in an auditorium, for instance, or
a
room with an outside door and a hallway door) is not out of the question.


I should have re-worded this part of my response, as I can see now that
it does not get my point across very well. I actually agree with you about
the
locks. What I was trying to do was get the OP to realize that things like
Room Type, etc. are not attributes of the key. All a key does is - as you
correctly point out - open a particular lock. Where that lock is located
is
an attribute of the lock, not the key.

Again, apologies if I came across as if I was saying you were wrong, that
wasn't my intention.

Regards,

Sean Bailey


"BruceM" wrote:

"Beetle" wrote in message
...
Hate to throw in the monkey wrench, but I'm going to have to disagree
with some of what's been posted (comments inline).

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text

Sorry, but the school data belongs in a separate table/tables. With the
above
structure you will repeatedly be entering the same descriptive data
over
and
over. Additionally, what if an instructor teaches more than one
Subject,
works
in more than one Dept., etc? You will have to enter additional records
for
the same employee.


You make some valid points about subjects and departments, and about
titles
too if somebody holds more than one. I'm not sure exactly what is meant
by
classification, so I couldn't say if there could be more than one. It
depends in part on how they do things. However, the posting was about
keys,
so correctly or not I chose not to get into a discussion of the Employee
table.


tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text

Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of
it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in
is
an attribute of the Wing (not the Room or the Key). You should have
separate
tables for Campus' and Wings


You may be correct about the RoomID as the FK. However, it isn't
necessarily as simple as a key opening a room (period). The building
could
have a key, for instance, or there could be a padlock or other lock that
is
not a room (or supply closet) lock. The OP said they don't "really"
track
file cabinet keys and such, but if they ever have a key to something
other
than a room provisions will need to be made for that. This is why I
suggeested a Locks table. A lock could have several keys. A Locks table
would need to specify the lock's location in a way that does not
necessarily
reference a room (in what room is a building's front door located?).
Also,
a room having several different locks (in an auditorium, for instance, or
a
room with an outside door and a hallway door) is not out of the question.


tblRooms
Key ID (PK)-text
Room number- text
Remarks-text

This table should have RoomID as a PK (KeyID does not belong here).
A room can have many keys, but a key can only open one room,
so RoomID goes in tblKeys as a foreign key, not the other way around.

tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time

IMO this table is unecessary. Whether an employee is "full time" or
"temporary" is just an attribute that would go in tblEmployees. The
fact
that a person may come and go as a member of your staff is irrelevant
to the fact that that person was in posession of a certain key at a
certain time. The date that a key was issued/returned belongs in
tblKeysEmployees.

You might be thinking "great, now I have to add more tables" but keep
in mind that in an application like this you will typically have
several
tables
that will basically just be "lookup" tables. They aren't really a big
deal to set up. Just from the little I know about your app, I would say
you would probably have the following tables that would be
"lookup" tables;

tblClassifications

tblDepartments

tblSubjects

tblCampuses

tblWings

tblRoomTypes

You may also need some additional junction tables, depending on if - as
I stated earlier - an instructor can teach more than one subject, work
in
more than one department, etc.

For you first app, you didn't exactly pick a simple one, but everyone
loves
a challenge, right? g

Good luck and welcome to the world of Access.

--
_________

Sean Bailey


"Aria" wrote:

Bruce,
First, I would really like to thank you for responding. I need to
clarify
some of the information that seems to be confusing. tblSubs is a table
for
temporary, roving district staff. They may be employed at any district
site
anywhere from a couple of hours to long-term(months). They need
access
to
their assignment location and if they are certificated, temporary
passwords
for certain reports.

My comments are as follows:
tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text
I ws going to say that SchoolData and PersonalInfo should be broken
into
several fields each, but later you said something about the number of
fields
in the table, so it seems the way you have written it here is a sort
of
shorthand, and that you have separate fields as needed.

I think I'm fairly comfortable with this table. I just thought maybe
it
needed to be broken down (not that I'm looking for another table to
put
in).
Yes, the fields are seperate.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text

One lock could have several keys (one for each of several people).
This
suggests you need a Locks table, separate from the Keys table. Each
lock
could have several keys, so there is a one-to-many relationship
between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be
flexible.
One approach to the Locks table would be to have fields for Campus,
Wing,
and Room. You could query the table for locks that go with a
particular
campus, building, wing, and room, or for all of the locks for a
campus,
or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup
fields)
to use for selecting campus, wing, and roomtype from combo boxes on
your
form. For instance, a RoomType table may be simply a listing of
RoomTypes.
Make a query based on this table (sorted by RoomType), and use the
query
as
the Row Source for a RoomType combo box on your Keys form.


Now, this is where you lose me. I don't understand the purpose of a
Locks
table. Isn't that what the keys table is for? We are only interested
in
the
locks as far as what key will staff to gain access to the room, gate,
stadium, etc. We don't normally track file cabinet keys. We may if the
contents are extremely confidential.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber

EmployeeID must be Number (Long Integer), not Autonumber. It is a
foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by
themselves
composite keys.

Sorry; I dropped the ball on this one. This *is* long interger. I
forgot
to
make the change in designation from my original.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text
KeyID as the PK of tblRooms could be confusing.

Again, you lost me on this one. Why is the KeyID confusing? It relates
to
the room; knowing the key ID will allow you access. My thinking was
that
if I
could do a parameter query (is that what I would want?) by room, I
would
know
which key will unlock it. Then I could make a key request or whatever
else
needs to be done.


tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time

This table's purpose is unclear.

I'm sorry. I should have explained in the beginning. Like I stated,
this
is
for temporary, roving staff. We need Access only in relation to keys
for
this
group. We have other applications for the rest of the information we
need.

1. How many fields are too many in a table? I understand that Access
will
accept up to 255. I read a post that suggested that 20-30 fields may
indicate
there may be a normalization problem. I'm wondering about
tblEmployees
where
there are approx. 25 fields.

No problem. More than 30 fields could suggest normalization problems,
but
it not an invariable rule. If the SchoolData fields are not all filled
in
for every record it could be that SchoolData could be in a separate
table.
Nothing else stands out much.

All the fields relating to school data wil be filled in, so I guess
we're
good here.


2.tblRooms has a field for Rm.#; the problem is that not all rooms
have
a
#.
Ex.-Storage rms., clinic, auditorium, etc. This is why I added
remarks
to
tblRooms because I may have to describe a location rather than a
room
#.
Will
there be a problem with this method?

Only if RoomNumber is a linking field. By the way, I assume you are
using
the number sign for description, not as a field name. Names should
include
only letters, number, and underscores. Spaces and other
non-alphanumeric
characters are best avoided.

Oh my gosh! How many times have I read the same thing and because I
*knew*
my fields weren't like that, moved on. Well, I just wanted to double
check
and lo and behold, there it was; a # sign in my field name. Thanks for
the
heads up!

One of the desired reports is a reverse directory by room #/room
phone
#.
The phone # is currently in tblEmployees. Should it be moved to
tblRooms?
If
I leave it where it is, will I be able to update any queries and
reports?
I've read that multitable queries aren't updateable. We have had
mass
staff
room changes right before the school year begins. It just seems it
would
be
better if the phone # was listed with tblRooms but someone told me
that's
incorrect.

If the phone number is associated with a room it should be part of a
room
record. Presumably an employee can be associated with several rooms,
and


  #10  
Old June 19th, 2008, 08:48 PM posted to microsoft.public.access.gettingstarted
Beetle
external usenet poster
 
Posts: 1,254
Default Still Struggling...

Does school data need an additional table based solely on instuctors
teaching more than one subject? You are correct though. Teachers can teach
more than one subject. I only ask because you said, "additionally", which
implies that this will need a new table regardless. Other than "Subject", I
don't understand how I would be entering the same information again and
again. What am I missing?


In my opinion it should be in separate tables regardless. I don't know
how Classification and Title relate to your employees, so for now I'll
use Dept. Name and Subject as examples. As your table is currently
designed, each time you enter an employee record the user will have
to manually type in the Dept. Name and Subject. This is not only extra
work, it also invites spelling errors and invalid data in your table. The
correct way is to have separate "lookup" tables that store all possible
Departments and Subjects. These would be very simple tables that would
likely just have a few fields like;

DeptID
DeptName

SubjectID
SubjectName

Then you would use DeptID and SubjectID as foreign keys in other tables.
In your data entry forms, you would typically use combo boxes to allow
users to select the correct Dept./Subject from the list of choices.

Which tables should DeptID/SubjectID go in? Good question. I think this is a
very important part of your application that you have not completely
defined yet. In a relational database like Access, it is not only imerative
that
you determine what relationships you have, but what *type* each relationship
is. Is it One-to-Many? Is it Many-to-Many? If it is 1:m, then the PK field
from the "One" side table goes in the "Many" side table as a foreign key. If
it is m:m, then you need a third (junction) table to define the relationship,
like your
tblKeysEmployees. You said a teacher can teach more than one subject,
and, presumably, a subject can be taught by more than one teacher, so
this relationship is m:m. Therefore, you not only need another table to
define the list of available subjects, but also a junction table to define
the relationship.

Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in is
an attribute of the Wing (not the Room or the Key). You should have separate
tables for Campus' and Wings


Point taken; although I don't understand why the campus location is not a
room attribute. I need to know where that room or storage room is
located(especially since storage rooms don't have room #s). Does it make a
difference if there are only 2 campuses? No? So, is it CampusID (FK to
tblRooms)? Same for tblWings?
Also, I meant to put KeyType (Storage, Classroom, Gate Master, Grand Master,
etc.) as a field here.


As I stated in my reply to Bruce, I don't think I worded this part of my reply
very well. I was trying to point out that things like RoomType are not
attributes
of a key. I actually think bruce is right about this. When you break things
down
to lowest common denominator, all a key does is open a lock (or many locks if
it is a master key). Where that lock is located is an attribute of the lock,
not the key. As far as whether the Campus is an attribute of the Room? Maybe
it should be, I don't know enough about your app to say for sure. My train
of thought in my previous reply wasa basically the following;

A Campus would usually encompass more than one building, a building
may have more than one wing, and a wing may have more than one room.
So, again, if we break it down to LCD (so to speak) a room is only indirectly
related to a Campus, it's direct relationship would the wing in which it is
located (or perhaps the building if that building has no "wings"). However,
as Bruce correctly pointed out, what we are really talking about is locks,
so it should probably be broken down to that level.

Good point; I'll change this. Your second sentence is not exactly true. Just
to clarify, key assignments are based on job title and extracurricular duties
(coaching, tutoring, special projects, etc.). Masters allow all access per
location. So one key may open one or many doors.


I hadn't thought about Master keys. You may want to consider a separate table
for those, since they have a different type of relationship with the locks
than
the regular keys.

I don't understand. If I add the substitute staff to the our employee table,
most of the fields will be empty. They are not required to disclose their
address or emergency info. There address is on file with the district. In
addition, they are not bound to teach any subject in particular. If there is
a request for a P.E. instructor but they normally teach English, they are
free to accept the position for the time requested. This is why I am adding a
"Preferred Subject" field. I want the sub to enjoy there time teaching and
the permanent staff to feel comfortable with whomever is taking over their
class. I am often asked for recommendations; I'd like some info to go along
with that. If we have a long-term position for a Special Education
instructor, I need to know who has that credential so we can move ASAP. Oddly
enough, the district doesn't track this info; I've asked. If I have the subs
in their own table, I think all the info I need will be together.


OK, I can see where that is a little tricky. Still, I think I would just
list them
in the employee table and live with a few empty address fields. If you have
them in a separate table, then you're going to have to add another FK
field to your tblKeysEmployees, so either way you end up with empty fields
in one of your tables.

Thank you for saying that! I kept wondering,"Why am I having such a hard
time with this?" Why am I still on the pen, paper and diagram stage?
I like a challenge because it makes success that much sweeter but *come
on*...! It makes it difficult to make a move when you are about ready to step
off the cliff at every turn. If it weren't for this discussion group, I would
have. Thanks to both you and Bruce for posting your reasons and thought
process along with your comments. I don't know about others, but it helps me
to follow along. I t takes an extra measure of patience that I appreciate. I
am going to rethink my tables and post them again. I hope you will take a
look and tell me what you think.


Yeah, you kind of jumped right into the deep end of the pool, but hopefully
with help from the group here you'll get it all sorted out. You'll get
differing
opinions from people too, so sometimes you just have to absorb the different
ideas and then decide what you think will work best for you.

--
_________

Sean Bailey


"Aria" wrote:

Beetle,
(lol) Yes, you have definitely thrown a monkey wrench into the works! You
have raised some valid points that I need to take into account. I have
thought about what you posted and would like to clarify some points and ask
additional questions.

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


Sorry, but the school data belongs in a separate table/tables. With the above
structure you will repeatedly be entering the same descriptive data over and
over. Additionally, what if an instructor teaches more than one Subject, works
in more than one Dept., etc? You will have to enter additional records for
the same employee.


Does school data need an additional table based solely on instuctors
teaching more than one subject? You are correct though. Teachers can teach
more than one subject. I only ask because you said, "additionally", which
implies that this will need a new table regardless. Other than "Subject", I
don't understand how I would be entering the same information again and
again. What am I missing?

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in is
an attribute of the Wing (not the Room or the Key). You should have separate
tables for Campus' and Wings


Point taken; although I don't understand why the campus location is not a
room attribute. I need to know where that room or storage room is
located(especially since storage rooms don't have room #s). Does it make a
difference if there are only 2 campuses? No? So, is it CampusID (FK to
tblRooms)? Same for tblWings?
Also, I meant to put KeyType (Storage, Classroom, Gate Master, Grand Master,
etc.) as a field here.
tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


This table should have RoomID as a PK (KeyID does not belong here).
A room can have many keys, but a key can only open one room,
so RoomID goes in tblKeys as a foreign key, not the other way around.


Good point; I'll change this. Your second sentence is not exactly true. Just
to clarify, key assignments are based on job title and extracurricular duties
(coaching, tutoring, special projects, etc.). Masters allow all access per
location. So one key may open one or many doors. As I stated previously, you
are correct about the room type and wing being attributes of the room. Why
not "Campus"?
In addition, the stadium and storage rooms do not have a room #, nor does it
belong to a wing. Do I need additional tables for this?

tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


IMO this table is unecessary. Whether an employee is "full time" or
"temporary" is just an attribute that would go in tblEmployees. The fact
that a person may come and go as a member of your staff is irrelevant
to the fact that that person was in posession of a certain key at a
certain time. The date that a key was issued/returned belongs in
tblKeysEmployees.


I don't understand. If I add the substitute staff to the our employee table,
most of the fields will be empty. They are not required to disclose their
address or emergency info. There address is on file with the district. In
addition, they are not bound to teach any subject in particular. If there is
a request for a P.E. instructor but they normally teach English, they are
free to accept the position for the time requested. This is why I am adding a
"Preferred Subject" field. I want the sub to enjoy there time teaching and
the permanent staff to feel comfortable with whomever is taking over their
class. I am often asked for recommendations; I'd like some info to go along
with that. If we have a long-term position for a Special Education
instructor, I need to know who has that credential so we can move ASAP. Oddly
enough, the district doesn't track this info; I've asked. If I have the subs
in their own table, I think all the info I need will be together.

You might be thinking "great, now I have to add more tables" but keep
in mind that in an application like this you will typically have several
tables
that will basically just be "lookup" tables. They aren't really a big
deal to set up.


(lol) That is *exactly* what I was thinking!

You may also need some additional junction tables, depending on if - as
I stated earlier - an instructor can teach more than one subject, work in
more than one department, etc.


(heavy sigh...as my head hits the desk) Which ones? tblSubjects?

For you first app, you didn't exactly pick a simple one, but everyone loves
a challenge, right? g


Thank you for saying that! I kept wondering,"Why am I having such a hard
time with this?" Why am I still on the pen, paper and diagram stage?
I like a challenge because it makes success that much sweeter but *come
on*...! It makes it difficult to make a move when you are about ready to step
off the cliff at every turn. If it weren't for this discussion group, I would
have. Thanks to both you and Bruce for posting your reasons and thought
process along with your comments. I don't know about others, but it helps me
to follow along. I t takes an extra measure of patience that I appreciate. I
am going to rethink my tables and post them again. I hope you will take a
look and tell me what you think.

--
Aria W.


"Beetle" wrote:

Hate to throw in the monkey wrench, but I'm going to have to disagree
with some of what's been posted (comments inline).

tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text


Sorry, but the school data belongs in a separate table/tables. With the above
structure you will repeatedly be entering the same descriptive data over and
over. Additionally, what if an instructor teaches more than one Subject, works
in more than one Dept., etc? You will have to enter additional records for
the same employee.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text


Again, this is wrong. Campus, Wing and RoomType are not attributes
of the Key. This table should have RoomID as a foreign key to tblRooms
and probably not much else, unless you store some other descriptive
information about the key itself, like "color" or something. Think of it
this way - you said you don't really track filing cabinet keys so we'll
just take rooms into consideration here. A key opens a room. Period.
The Room Type and the Wing in which that room is located are attributes
of the Room, not the Key. Likewise, the Campus that Wing is located in is
an attribute of the Wing (not the Room or the Key). You should have separate
tables for Campus' and Wings

tblRooms
Key ID (PK)-text
Room number- text
Remarks-text


This table should have RoomID as a PK (KeyID does not belong here).
A room can have many keys, but a key can only open one room,
so RoomID goes in tblKeys as a foreign key, not the other way around.

tblSubs
Sub ID (PK)-Autonumber
SubLN-text
SubFN-text
MI-text
SubPhone-text
Key ID (FK to tblKeys)
Date Issued-Date/Time
Date Returned-Date/Time


IMO this table is unecessary. Whether an employee is "full time" or
"temporary" is just an attribute that would go in tblEmployees. The fact
that a person may come and go as a member of your staff is irrelevant
to the fact that that person was in posession of a certain key at a
certain time. The date that a key was issued/returned belongs in
tblKeysEmployees.

You might be thinking "great, now I have to add more tables" but keep
in mind that in an application like this you will typically have several
tables
that will basically just be "lookup" tables. They aren't really a big
deal to set up. Just from the little I know about your app, I would say
you would probably have the following tables that would be
"lookup" tables;

tblClassifications

tblDepartments

tblSubjects

tblCampuses

tblWings

tblRoomTypes

You may also need some additional junction tables, depending on if - as
I stated earlier - an instructor can teach more than one subject, work in
more than one department, etc.

For you first app, you didn't exactly pick a simple one, but everyone loves
a challenge, right? g

Good luck and welcome to the world of Access.

--
_________

Sean Bailey


"Aria" wrote:

Bruce,
First, I would really like to thank you for responding. I need to clarify
some of the information that seems to be confusing. tblSubs is a table for
temporary, roving district staff. They may be employed at any district site
anywhere from a couple of hours to long-term(months). They need access to
their assignment location and if they are certificated, temporary passwords
for certain reports.

My comments are as follows:
tblEmployees
Inactive- Yes/No
Date- Date/Time
Date Modified- Date/Time
Employee ID- (PK) Auto number Long Integer
School Data (Classification, Title, Dept. Name, Subject)- text
Personal Info (LN FN MI etc.)- text
Emergency Info - text
I ws going to say that SchoolData and PersonalInfo should be broken into
several fields each, but later you said something about the number of fields
in the table, so it seems the way you have written it here is a sort of
shorthand, and that you have separate fields as needed.

I think I'm fairly comfortable with this table. I just thought maybe it
needed to be broken down (not that I'm looking for another table to put in).
Yes, the fields are seperate.

tblKeys
Key ID (PK)- text
Campus- text
Wing- text
RoomType(Classroom, Auditorium, Grand Master, etc.)- text

One lock could have several keys (one for each of several people). This
suggests you need a Locks table, separate from the Keys table. Each lock
could have several keys, so there is a one-to-many relationship between
locks and keys. Since a filing cabinet could be moved from one room to
another, the correlation between locks and rooms may need to be flexible.
One approach to the Locks table would be to have fields for Campus, Wing,
and Room. You could query the table for locks that go with a particular
campus, building, wing, and room, or for all of the locks for a campus, or
all of the locks for a building, etc.
You may want to build some lookup tables (very different from lookup fields)
to use for selecting campus, wing, and roomtype from combo boxes on your
form. For instance, a RoomType table may be simply a listing of RoomTypes.
Make a query based on this table (sorted by RoomType), and use the query as
the Row Source for a RoomType combo box on your Keys form.


Now, this is where you lose me. I don't understand the purpose of a Locks
table. Isn't that what the keys table is for? We are only interested in the
locks as far as what key will staff to gain access to the room, gate,
stadium, etc. We don't normally track file cabinet keys. We may if the
contents are extremely confidential.


tblKeysEmployees
Key ID- composite key(FK to tblKeys)- text
EmployeeID-composite key (FK to tblEmployees)- Autonumber

EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign
key field, so it cannot be assigned automatically. Rather, it gets its
value from the parent table when a record is created. The two fields
together can be a composite PK for this table, but are not by themselves
composite keys.

Sorry; I dropped the ball on this one. This *is* long interger. I forgot to
make the change in designation from my original.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.