Field in Table

[LMB]

In my Employee database, not everyone has a pager number but we need to know the pager numbers of the flight therapists so we can page them when they are needed to come in for a transport. Should I make another table and link it to the Employee Table?

tblPager
PagerID
EmployeeID
PagerNumber

Would this be right?

Thanks,
Linda

 

[John Nurick]

Hi Linda,

If you already have something like this
tblTelephoneNumbers
EmployeeID
TelephoneNumberType ("home", "office", "mobile", etc.)
TelephoneNumber
just add "Pager" as a type. Otherwise I'd probably keep things simple
and add a PageNumber field to the main table.

 

[LMB]

Actually I had telephone number in the main employee table. With all the different communication devices available now, perhaps I should have a separate table. We don't want to have 3 different numbers to contact people though so I wonder how this will work. I was also trying to set the database up right and know you aren't supposed to have blank fields in a table. There are probably only 20 of 70 employees who have pager numbers....so, knowing this, would it be ok to have blank fields that many blank fields to make it simple or should I make a separate table. If I do set up a Telephone Table, and have the TelephoneNumberType, then would some people have blank fields if they don't have a mobile phone or would this whole table be filled and some would have 3 devices and some have only one? The field TelephoneNumberType have a drop down list with home, office, mobil, pager in it?

Thanks,
Linda
Hi Linda,

If you already have something like this
tblTelephoneNumbers
EmployeeID
TelephoneNumberType ("home", "office", "mobile", etc.)
TelephoneNumber
just add "Pager" as a type. Otherwise I'd probably keep things simple
and add a PageNumber field to the main table.

 

[John Vinson]

I was also trying to set the database up right and know you aren't supposed to have blank fields in a table.

ummm... don't get too doctrinaire about that principle!

NULL is indeed a controversial beast, and some (Dr. Codd for one)
don't like it - but it's a very HANDY controversial beast, for just
this reason.

If every employee has either just one pager number, or no pager number
at all, I'd start by putting in a Pager field in the employee table
and leaving it NULL as appropriate. The queries then become very easy.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[LMB]

Thank-you. I got a little hung up on the state part of the address too but I have looked at some other databases and they have state in the main table too. How about phone numbers and cell phones? How would you handle that? I think everyone in our department except me has a cell phone.....I have 8 computers though <g>

Thanks,
Linda

I was also trying to set the database up right and know you aren't supposed to have blank fields in a table.

ummm... don't get too doctrinaire about that principle!

NULL is indeed a controversial beast, and some (Dr. Codd for one)
don't like it - but it's a very HANDY controversial beast, for just
this reason.

If every employee has either just one pager number, or no pager number
at all, I'd start by putting in a Pager field in the employee table
and leaving it NULL as appropriate. The queries then become very easy.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Nurick]

If your people tend to have multiple phone numbers (home, main office,
personal assistant, other office, mobile phone, car phone, weekend
cottage and satellite phone, etc.) it can be simplest to use a separate
table like the one I outlined below. There's a 1 to many relationship
between employees and telephone numbers, so if someone has three phones
there'd be three records for this employee in tblTelephoneNumbers, and
if only one phone then only one record.

Usually one would use a dropdown list for TelephoneNumberType; you'll
see something similar in the standard Outlook contact form, although in
that case the phone numbers and phone number types are both stored in
multiple fields in the main table, not (as they should be) in a related
one.

As John Vinson says, it's simplest just to add a PagerNumber field to
the main table and not worry about the nulls. That makes querying for a
pager number dead easy, while if the number was in a related table the
query would have to join the two tables.

OTOH tasks like this would probably be simpler with a separate table for
the phone numbers:
-If the employee has a pager, return the pager number. Otherwise:
-If in working hours, return the office number.
-If in commuting hours, return the carphone (if any)
or else the cellphone number (if any).
-Otherwise return the home number.

Actually I had telephone number in the main employee table.

With all the different communication devices available now, perhaps I
should have a separate table. We don't want to have 3 different numbers
to contact people though so I wonder how this will work. I was also
trying to set the database up right and know you aren't supposed to have
blank fields in a table. There are probably only 20 of 70 employees who
have pager numbers....so, knowing this, would it be ok to have blank
fields that many blank fields to make it simple or should I make a
separate table. If I do set up a Telephone Table, and have the
TelephoneNumberType, then would some people have blank fields if they
don't have a mobile phone or would this whole table be filled and some
would have 3 devices and some have only one? The field
TelephoneNumberType have a drop down list with home, office, mobil,
pager in it?

 

[John Vinson]

Thank-you. I got a little hung up on the state part of the address too but I have looked at some other databases and they have state in the main table too. How about phone numbers and cell phones? How would you handle that? I think everyone in our department except me has a cell phone.....I have 8 computers though <g>

I'd keep State in the main table (assuming only one address, in the
Address table otherwise). These days for phones you can pretty much
assume that *somebody* in the database will have at least two; all it
takes is one such person to make a separate PhoneNumbers table - as
suggested elsewhere in the thread - a formal necessity. There are
enough people in most any organization to make it a practical
necessity as well. Just have a table with EmployeeID, PhoneType, Phone
fields, and maybe a little lookup table (NOT lookup field...!)
PhoneTypes with a primary key text field containing "Work", "Home",
"Cell", "Pager" and whatever other values turn up.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[LMB]

Thank-you. I got a little hung up on the state part of the address too but I have looked at some other databases and they have state in the main table too. How about phone numbers and cell phones? How would you handle that? I think everyone in our department except me has a cell phone.....I have 8 computers though <g>

I'd keep State in the main table (assuming only one address, in the
Address table otherwise). These days for phones you can pretty much
assume that *somebody* in the database will have at least two; all it
takes is one such person to make a separate PhoneNumbers table - as
suggested elsewhere in the thread - a formal necessity. There are
enough people in most any organization to make it a practical
necessity as well. Just have a table with EmployeeID, PhoneType, Phone
fields, and maybe a little lookup table (NOT lookup field...!)
PhoneTypes with a primary key text field containing "Work", "Home",
"Cell", "Pager" and whatever other values turn up.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps


Thanks again....Sorry, I didn't mean look up field....my access language is not really great yet.

Linda

 

[LMB]

Thank-you so much. I'll get to work on this and keep coming back!

Linda
If your people tend to have multiple phone numbers (home, main office,
personal assistant, other office, mobile phone, car phone, weekend
cottage and satellite phone, etc.) it can be simplest to use a separate
table like the one I outlined below. There's a 1 to many relationship
between employees and telephone numbers, so if someone has three phones
there'd be three records for this employee in tblTelephoneNumbers, and
if only one phone then only one record.

Usually one would use a dropdown list for TelephoneNumberType; you'll
see something similar in the standard Outlook contact form, although in
that case the phone numbers and phone number types are both stored in
multiple fields in the main table, not (as they should be) in a related
one.

As John Vinson says, it's simplest just to add a PagerNumber field to
the main table and not worry about the nulls. That makes querying for a
pager number dead easy, while if the number was in a related table the
query would have to join the two tables.

OTOH tasks like this would probably be simpler with a separate table for
the phone numbers:
-If the employee has a pager, return the pager number. Otherwise:
-If in working hours, return the office number.
-If in commuting hours, return the carphone (if any)
or else the cellphone number (if any).
-Otherwise return the home number.

Actually I had telephone number in the main employee table.

With all the different communication devices available now, perhaps I
should have a separate table. We don't want to have 3 different numbers
to contact people though so I wonder how this will work. I was also
trying to set the database up right and know you aren't supposed to have
blank fields in a table. There are probably only 20 of 70 employees who
have pager numbers....so, knowing this, would it be ok to have blank
fields that many blank fields to make it simple or should I make a
separate table. If I do set up a Telephone Table, and have the
TelephoneNumberType, then would some people have blank fields if they
don't have a mobile phone or would this whole table be filled and some
would have 3 devices and some have only one? The field
TelephoneNumberType have a drop down list with home, office, mobil,
pager in it?

 

[LMB]

I like the idea of "dead simple" and if Nulls are ok then why not just put all of the Telephone/Contact Numbers in the main table? But, my intent here is to learn the right way to do things. So, would this table design below be ok? Also, for the look up tables, do you guys use lutbl or just tbl to name them and is there a primary key in this table (why or why not)? Next to the contact type I'll put the number of employees that have each contact type in case that makes a difference in how one would go about the design of these tables.

lutblContactNumbersType
Home Number (70 out of 70)
Cell Number (69 out of 70)
Work Number (10 out of 70)
Fax Number (10 out of 70)
Pager Number (25 out of 70)

tblContactNumbers
ContactNumberID (PK)
Employee ID (FK)
ContactNumberType
ContactNumber

Thanks,
Linda





If your people tend to have multiple phone numbers (home, main office,
personal assistant, other office, mobile phone, car phone, weekend
cottage and satellite phone, etc.) it can be simplest to use a separate
table like the one I outlined below. There's a 1 to many relationship
between employees and telephone numbers, so if someone has three phones
there'd be three records for this employee in tblTelephoneNumbers, and
if only one phone then only one record.

Usually one would use a dropdown list for TelephoneNumberType; you'll
see something similar in the standard Outlook contact form, although in
that case the phone numbers and phone number types are both stored in
multiple fields in the main table, not (as they should be) in a related
one.

As John Vinson says, it's simplest just to add a PagerNumber field to
the main table and not worry about the nulls. That makes querying for a
pager number dead easy, while if the number was in a related table the
query would have to join the two tables.

OTOH tasks like this would probably be simpler with a separate table for
the phone numbers:
-If the employee has a pager, return the pager number. Otherwise:
-If in working hours, return the office number.
-If in commuting hours, return the carphone (if any)
or else the cellphone number (if any).
-Otherwise return the home number.

 

[John Nurick]

Hi Linda,

It's a trade-off. If you have separate fields in the main table for
HomeNumber, CellNumber, NextOfKinNumber etc. it keeps things simple for
the time being ... but every time you need to storea another kind of
contact number (e.g. PagerNumber) you have to modify the table (to add a
field) and the queries, forms and reports (to display the new field).
Simple queries are simple, but complex ones (like the example I gave
below) can get very difficult.

On the other hand if you go the whole hog with something like this

lutblContactNumberTypes
NumberType (Text, PK)
DisplayOrder (Long, allows the dropdown lists to be
sorted into something other than alphabetic
order)
PriorityWorkingHours
PriorityCommuteHours
PriorityNight
PriorityWeekend
PriorityVacation

tblContactNumbers
EmployeeID (FK into tblEmployees)
NumberType (FK into lutblContactNumberTypes)
(EmployeeID and NumberType are both in the
primary key)
ContactNumber

adding another type of contact number is simply a matter of adding a
record to the lookup table.

NB: in case you haven't guessed, the "PriorityXxx" fields in the lookup
table (or something similar) are there to make it easy for queries to
return either the first number to try in various circumstances, or a
list of numbers in the order in which they should be tried. That's
useful for a human operator, and essential for an automated call-out
system.

Taking this a step further and a step off topic, since text messages,
email and messaging to mobile devices and VOIP to mobile devices are all
intruding on the role of the traditional pager, it might be worth
considering whether to generalise the concept of "contact number" beyond
just something one dials on a telephone to include email addresses, IM
handles, etc.

I like the idea of "dead simple" and if Nulls are ok then

why not just put all of the Telephone/Contact Numbers in the main table?
But, my intent here is to learn the right way to do things. So, would
this table design below be ok? Also, for the look up tables, do you
guys use lutbl or just tbl to name them and is there a primary key in
this table (why or why not)? Next to the contact type I'll put the
number of employees that have each contact type in case that makes a
difference in how one would go about the design of these tables.

 

[LMB]

I don't think I understand. Do you suggest I use

lutblContactNumberTypes
NumberType (Text, PK)
DisplayOrder (Long, allows the dropdown lists to be
sorted into something other than alphabetic
order)
PriorityWorkingHours
PriorityCommuteHours
PriorityNight
PriorityWeekend
PriorityVacation

Instead of

lutblContactNumbersType
Home Number
Cell Number
Work Number
Fax Number
Pager Number

I also don't understand where DisplayOrder (Long, allows the dropdown lists to be
sorted into something other than alphabetic
order) is

Thanks,
Linda




Hi Linda,

It's a trade-off. If you have separate fields in the main table for
HomeNumber, CellNumber, NextOfKinNumber etc. it keeps things simple for
the time being ... but every time you need to storea another kind of
contact number (e.g. PagerNumber) you have to modify the table (to add a
field) and the queries, forms and reports (to display the new field).
Simple queries are simple, but complex ones (like the example I gave
below) can get very difficult.

On the other hand if you go the whole hog with something like this

lutblContactNumberTypes
NumberType (Text, PK)
DisplayOrder (Long, allows the dropdown lists to be
sorted into something other than alphabetic
order)
PriorityWorkingHours
PriorityCommuteHours
PriorityNight
PriorityWeekend
PriorityVacation

tblContactNumbers
EmployeeID (FK into tblEmployees)
NumberType (FK into lutblContactNumberTypes)
(EmployeeID and NumberType are both in the
primary key)
ContactNumber

adding another type of contact number is simply a matter of adding a
record to the lookup table.

NB: in case you haven't guessed, the "PriorityXxx" fields in the lookup
table (or something similar) are there to make it easy for queries to
return either the first number to try in various circumstances, or a
list of numbers in the order in which they should be tried. That's
useful for a human operator, and essential for an automated call-out
system.

Taking this a step further and a step off topic, since text messages,
email and messaging to mobile devices and VOIP to mobile devices are all
intruding on the role of the traditional pager, it might be worth
considering whether to generalise the concept of "contact number" beyond
just something one dials on a telephone to include email addresses, IM
handles, etc.

I like the idea of "dead simple" and if Nulls are ok then

why not just put all of the Telephone/Contact Numbers in the main table?
But, my intent here is to learn the right way to do things. So, would
this table design below be ok? Also, for the look up tables, do you
guys use lutbl or just tbl to name them and is there a primary key in
this table (why or why not)? Next to the contact type I'll put the
number of employees that have each contact type in case that makes a
difference in how one would go about the design of these tables.

 

[John Nurick]

Hi Linda,

If you want to keep it simple, just put a PagerNumber field in the main
table. Otherwise, see comments inline:

I don't think I understand. Do you suggest I use

lutblContactNumberTypes

"lutblContactNumberTypes" is the name of the table, and what follows is
a list of the fields I suggested might be useful in that table.

NumberType (Text, PK)
DisplayOrder (Long, allows the dropdown lists to be
sorted into something other than alphabetic
order)
PriorityWorkingHours
PriorityCommuteHours
PriorityNight
PriorityWeekend
PriorityVacation

Actual records in this table might be (first 3 fields)

NumberType, DisplayOrder, PriorityWorkingHours,
Work Number, 100, 100
Home Number, 200, 10
Cellphone Number, 300, 50
Pager Number, 400, 60

You see that by sorting on Display Order instead of NumberType you can
list numbers in the order Work, Home, Cellphone, Pager, which is
probablhy more useful than alphabetical order (Cellphone, Home, Pager,
Work).

The "PriorityXXX" fields are only needed if you want to have a query
pull up a person's contact numbers in the order in which they should be
tried. If it's in working hours, presumably you'd want the Work number
first, followed by the pager and/or cellphone, and then the home number.
But if it's after hours you might want the pager first (if there is one)
followed by home and cellphone.

Instead of

lutblContactNumbersType

Here it looks as if you've got a list of values, rather than the list of
fields you want in the lookup table.

 

[LMB]

Oh...so Long is long integer in (Long, allows the dropdown lists to be

sorted into something other than alphabetic
order) ?

And...."Priority while working" would be the number to call when they are working?


I have never done a table without using an autonumber for a primary key but this is just a lookup so is that different than a regular table?

Last question (maybe <g>) How will this data be entered in a form? Will there be a different textbox for each type of number?
Hi Linda,

If you want to keep it simple, just put a PagerNumber field in the main
table. Otherwise, see comments inline:

I don't think I understand. Do you suggest I use

lutblContactNumberTypes

"lutblContactNumberTypes" is the name of the table, and what follows is
a list of the fields I suggested might be useful in that table.

NumberType (Text, PK)
DisplayOrder (Long, allows the dropdown lists to be
sorted into something other than alphabetic
order)
PriorityWorkingHours
PriorityCommuteHours
PriorityNight
PriorityWeekend
PriorityVacation

Actual records in this table might be (first 3 fields)

NumberType, DisplayOrder, PriorityWorkingHours,
Work Number, 100, 100
Home Number, 200, 10
Cellphone Number, 300, 50
Pager Number, 400, 60

You see that by sorting on Display Order instead of NumberType you can
list numbers in the order Work, Home, Cellphone, Pager, which is
probablhy more useful than alphabetical order (Cellphone, Home, Pager,
Work).

The "PriorityXXX" fields are only needed if you want to have a query
pull up a person's contact numbers in the order in which they should be
tried. If it's in working hours, presumably you'd want the Work number
first, followed by the pager and/or cellphone, and then the home number.
But if it's after hours you might want the pager first (if there is one)
followed by home and cellphone.

Instead of

lutblContactNumbersType

Here it looks as if you've got a list of values, rather than the list of
fields you want in the lookup table.

 

[John Nurick]

Oh...so Long is long integer in (Long, allows the dropdown lists to be
Yes.

And...."Priority while working" would be the number to call when they are working?

No. Remember that this table just stores the types of contact number
that are allowed in the database; it will only have a handful of
records, one for Work, one for Home, one for Pager and so on. The actual
numbers are stored in a separate table related to both
lutblContactNumberTypes and tblEmployees, something like this:

tblContactNumbers
EmployeeID (FK into tblEmployees)
NumberType (Text, FK into lutblContactNumberTypes)
ContactNumber (Text, the actual number)
NB: Primary key consists of the two fields EmployeeID and NumberType

What the "PriorityWorkingHours" and the other Priority fields do is
allow the records to be returned in different orders depending on the
time in relation to the working day and week. Look at the example in my
last message. The actual values in PriorityWorkingHours don't mean
anything in themselves, what matters is their relative size. So the Work
number has the highest priority (100), making it the first number to
try, followed by Pager (60), Celphone (50) and finally Home (10).

By contrast, the values for PriorityWeekend might be Pager (100), Home
(80), Cellphone (60), Work (0).

As I said, these fields aren't essential unless you want the database to
"know" which number to try first in various circumstances. If the calls
will be placed by ordinary humans who just look up the numbers in the
database and then decide for themselves which to use, you can do without
them.

I have never done a table without using an autonumber for a
primary key but this is just a lookup so is that different
than a regular table?

Every table needs a primary key, but it doesn't *have* to be an
autonumber.

Last question (maybe <g>) How will this data be entered in a form?
Will there be a different textbox for each type of number?

The standard way of doing this is with a subform in continuous view,
with a combobox to select the ContactNumberType and a textbox next to it
for the actual number; similar to the Orders subform in the Northwind
sample database.