Table Normalization

would you believe my caps lock is broken and i'm too cheap to buy another
keyboard? oh well, it distinguishes me from the other "tina" who posts here
sometimes! g


"Fred Boer" wrote in message
...
Both you and Gunny have given me plenty of useful messages to learn from -
I
appreciate both of your efforts. In addition, tina, your, uh, "capitally
challenged" writing style is distinctive!

Cheers!
Fred Boer

We're going to have to give you the nick name "Queen of Humble" .....

As John Vinson said, you ARE being observed in quite a positive way. :-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
i agree with you, Gunny. i read many, many more posts than i ever respond
to, and i pay attention to everything - the correct answers, the mistakes,
the corrections, and the overall quality of all responses. my respect for
the master developers here isn't based on perceived "perfection", but on
the
consistent display of expertise. and re myself, thanks for your kind
words.
smiles and bows, blushing again


"'69 Camaro" AM wrote
in
message ...
Hi, Tina.

i'm so far below the level of the MVPs that we're not even breathing
the
same concentration of oxygen

Remember that there's a steep learning curve to climb in order to become
competent in Access. To master it requires a great deal more work. And
no
one knows all there is to know about Access, not even the Access MVP's
or
the group of people who developed Access (though some seem to come
pretty
close).

There are so many categories to work in (database design, queries,
forms,
VBA, et cetera), that many Access experts specialize in their favorite
categories where they become masters, but in other areas they are "good
enough." Many Access experts don't even venture into some areas, like
database replication and security, so they can't answer many technical
questions in these areas. That doesn't mean they aren't experts,
though.
It's merely an area that they're weak in.

i've posted answers that later made me cringe - usually after an MVP
or
some
other learned folk gave a good answer

We've all been in this boat. Grab a paddle from name of choice.

gently pointed out my flub (though
occasionally i realize i blew it, without help).

Either way, you now know a correct answer to give the next time the
question
is asked.

In this forum, experts are answering questions in the categories where
they
have specific expert knowledge, not the categories where they are weak.
Look at this from another perspective. If these experts concentrated on
only answering questions in the categories where they're weak, you'd
notice
that they'd flub a number of the answers, too. Even the experts don't
know
every single answer to every possible question in their areas of
expertise,
either, so you'll see occasional flubs there, too. But professionals
don't
make the same mistake twice, and they don't let others make the same
mistakes they've made, either. If there's a better way to do something,
it
will usually be pointed out in this forum by someone else who has
already
been down the same road. We can all learn from each other.

but i do give good solutions to a fair number of the simpler
questions,

They may be simple to you, maybe, but the person who asked the question
knows that it isn't simple. Otherwise, he would have been able to
easily
figure it out without asking for help. You're at a level of expertise
where
many operations seem simple to you because you've done them so often
that
you are familiar with them.

Don't be overly critical of yourself. The good solutions you've given
show
that you know what you are talking about, and the people who received
these
solutions know that they got them from an expert. :-)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


"tina" wrote in message
...
wow, Gunny, i am beyond flattered - really. feels head expanding to
fill
the room
i'm so far below the level of the MVPs that we're not even breathing
the
same concentration of oxygen (at least it's nice and warm down here),
and
i've posted answers that later made me cringe - usually after an MVP
or
some
other learned folk gave a good answer, or gently pointed out my flub
(though
occasionally i realize i blew it, without help). g
but i do give good solutions to a fair number of the simpler
questions,
and
i'm tickled pink to get a "good job!" from people i respect. you
really
made
my day - thank you!


"'69 Camaro" AM
wrote
in
message ...
Hi, Tina.

Your ISP has your computer listed as connecting from its L.A.
region,
but
if
you know where T.O. is located, then I figured that you must be from
nearby,
like the S.F. Valley.

The discussion we had last week started out with a remark that there
are
very few women who are Access MVP's, which led to the remark that
it's
because there aren't very many women who hang around the newsgroups
helping
people and who give consistently excellent answers, so the pool to
draw
the
female Access MVP's from is actually quite small. When we tried to
count
the number of women in this category who aren't already MVP's, your
name
was
the first name that came to everybody's mind. "Everybody" in this
case
is
a
very small group of computer geeks, so take that microcosm of the
general
population in mind. But I just wanted to let you know that we
noticed
your
work and want to thank you for taking the time to join the crowd of
volunteers and making the effort to share your valuable experience,
because
there are _so_ many people who need help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)


"tina" wrote in message
...
ya know, i did feel a little itch a few days ago - i thought it
was
from
the
Santa Anas. g that's actually scary to think i made a little
blip
on
somebody's radar - y'all must really skim the treetops! lol

nope, not in L.A. Inland Empire.


"'69 Camaro" AM
wrote
in
message ...
Hi, Tina.

Glad you liked it! I'll tell the other consultants here,
because
that
makes
three people who've bookmarked our site! (Just kidding. We're
up
to
five
now. ;-) )

Have your ears been burning? Your name came up in a discussion
here
the
other day. (Don't worry. These were favorable remarks.) Where
are
you
in
L.A.? The Valley?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)


"tina" wrote in message

...
hey, Gunny, just checked out your website. very nice - it
joined
mvps.org,
lebans.com, granite.ab.ca, and all the other Access bookmarks
in
my
browser.
the article on marketing was especially interesting, and very
entertaining
too! btw, we're neighbors, i'm in so cal also.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Jonathan.

Any suggestions would be greatly appreciated.

You may want to look even further ahead in your
organization's
needs
for
this database, such as how many other types of phone numbers
are
likely
to
be added. Pagers come to mind.

I suggest taking these five steps:

1.) Create a table for the types of phone numbers. Add an
Autonumber
primary key and the "PhoneType" field to hold the name of
the
phone
type.
It might look like this:

Table: tblPhoneTypes

PTID PhoneType
1 Business
2 Cell
3 Home
4 Pager

2.) Create a query that gathers the information from the
EmpInfo
table
in
a
normalized structure and name this query qryEmpPhones:

SELECT EmpNum, HomePhone AS PhoneNum, 3 AS PTID
FROM EmpInfo
UNION
SELECT EmpNum, CellPhone, 2 AS PTID
FROM EmpInfo
UNION SELECT EmpNum, BusinessPhone, 1 AS PTID
FROM EmpInfo;

3.) Create a make table query with the following SQL
statement:

SELECT * INTO tblEmpPhones
FROM qryEmpPhones
ORDER BY EmpNum, 3;

4.) Open the tblEmpPhones table and edit the field defaults
(such
as
size
of the text field), assign the primary key, open the table
properties
and
change the Subdatasheet Name combo box to [None], then save
the
new
table.

5.) Open the Relationships window and create the
relationship
between
the
tblEmpPhones and tblPhoneTypes tables, then save the change
to
the
Relationships window.

I'd also suggest not using special keys, like # and spaces
in
field
names
and table names to avoid bugs later. You may even want to
add
an
additional
field to the tblEmpPhones table to indicate which phone
number
is
the
primary phone number to reach the employee at.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so
that
a
message
will be forwarded to me.)


"Jonathan Brown" Jonathan
wrote
in
message
...
I didn't normalize my database as well as I wish I had. I
have
a
table
called EmpInfo that's built essentially as follows:

Emp#
HomePhone
CellPhone
BusinessPhone

What I would likek to do I build a different table called
EmpPhones
that
would like like this:

Emp# (this field would be related to the Emp# field in
table
EmpInfo)
Phone#
Type (the type field would be a lookup to another table
with
a
list
of
different types of phone number)

The Emp# and Phone# fields together would make up my
multiple
field
primary
key.

How would I combine the the HomePhone, Cellphone, and
BusinessPhone
into
one
column, and then have 2 other columns with it's associated
Emp#
and
Type?

Any suggestions would be greatly appreciated.

Hi, Fred.

I'll speak for everyone who posts answers in this forum: "It's nice to be
appreciated! Thanks!"

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)


"Fred Boer" wrote in message
...
Both you and Gunny have given me plenty of useful messages to learn from -
I
appreciate both of your efforts. In addition, tina, your, uh, "capitally
challenged" writing style is distinctive!

Cheers!
Fred Boer

nah, the red face is just from bowing so much. maybe i should just incline
my head slightly. g
good thing i listened to my mother - she always told me to "be careful what
you do in public, you never know who's watching"....to this day, i never go
anywhere without a hanky in my pocket! LOL


"Lynn Trapp" wrote in message
...
We're going to have to give you the nick name "Queen of Humble" .....

As John Vinson said, you ARE being observed in quite a positive way. :-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
i agree with you, Gunny. i read many, many more posts than i ever
respond
to, and i pay attention to everything - the correct answers, the
mistakes,
the corrections, and the overall quality of all responses. my respect
for
the master developers here isn't based on perceived "perfection", but on
the
consistent display of expertise. and re myself, thanks for your kind
words.
smiles and bows, blushing again


"'69 Camaro" AM wrote
in
message ...
Hi, Tina.

i'm so far below the level of the MVPs that we're not even breathing
the
same concentration of oxygen

Remember that there's a steep learning curve to climb in order to
become
competent in Access. To master it requires a great deal more work.
And
no
one knows all there is to know about Access, not even the Access MVP's
or
the group of people who developed Access (though some seem to come
pretty
close).

There are so many categories to work in (database design, queries,
forms,
VBA, et cetera), that many Access experts specialize in their favorite
categories where they become masters, but in other areas they are
"good
enough." Many Access experts don't even venture into some areas, like
database replication and security, so they can't answer many technical
questions in these areas. That doesn't mean they aren't experts,
though.
It's merely an area that they're weak in.

i've posted answers that later made me cringe - usually after an MVP
or
some
other learned folk gave a good answer

We've all been in this boat. Grab a paddle from name of choice.

gently pointed out my flub (though
occasionally i realize i blew it, without help).

Either way, you now know a correct answer to give the next time the
question
is asked.

In this forum, experts are answering questions in the categories where
they
have specific expert knowledge, not the categories where they are
weak.
Look at this from another perspective. If these experts concentrated
on
only answering questions in the categories where they're weak, you'd
notice
that they'd flub a number of the answers, too. Even the experts don't
know
every single answer to every possible question in their areas of
expertise,
either, so you'll see occasional flubs there, too. But professionals
don't
make the same mistake twice, and they don't let others make the same
mistakes they've made, either. If there's a better way to do
something,
it
will usually be pointed out in this forum by someone else who has
already
been down the same road. We can all learn from each other.

but i do give good solutions to a fair number of the simpler
questions,

They may be simple to you, maybe, but the person who asked the
question
knows that it isn't simple. Otherwise, he would have been able to
easily
figure it out without asking for help. You're at a level of expertise
where
many operations seem simple to you because you've done them so often
that
you are familiar with them.

Don't be overly critical of yourself. The good solutions you've given
show
that you know what you are talking about, and the people who received
these
solutions know that they got them from an expert. :-)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)


"tina" wrote in message
...
wow, Gunny, i am beyond flattered - really. feels head expanding
to
fill
the room
i'm so far below the level of the MVPs that we're not even breathing
the
same concentration of oxygen (at least it's nice and warm down
here),
and
i've posted answers that later made me cringe - usually after an MVP
or
some
other learned folk gave a good answer, or gently pointed out my flub
(though
occasionally i realize i blew it, without help). g
but i do give good solutions to a fair number of the simpler
questions,
and
i'm tickled pink to get a "good job!" from people i respect. you
really
made
my day - thank you!


"'69 Camaro" AM
wrote
in
message ...
Hi, Tina.

Your ISP has your computer listed as connecting from its L.A.
region,
but
if
you know where T.O. is located, then I figured that you must be
from
nearby,
like the S.F. Valley.

The discussion we had last week started out with a remark that
there
are
very few women who are Access MVP's, which led to the remark that
it's
because there aren't very many women who hang around the
newsgroups
helping
people and who give consistently excellent answers, so the pool to
draw
the
female Access MVP's from is actually quite small. When we tried
to
count
the number of women in this category who aren't already MVP's,
your
name
was
the first name that came to everybody's mind. "Everybody" in this
case
is
a
very small group of computer geeks, so take that microcosm of the
general
population in mind. But I just wanted to let you know that we
noticed
your
work and want to thank you for taking the time to join the crowd
of
volunteers and making the effort to share your valuable
experience,
because
there are _so_ many people who need help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)


"tina" wrote in message
...
ya know, i did feel a little itch a few days ago - i thought it
was
from
the
Santa Anas. g that's actually scary to think i made a little
blip
on
somebody's radar - y'all must really skim the treetops! lol

nope, not in L.A. Inland Empire.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Tina.

Glad you liked it! I'll tell the other consultants here,
because
that
makes
three people who've bookmarked our site! (Just kidding.
We're
up
to
five
now. ;-) )

Have your ears been burning? Your name came up in a
discussion
here
the
other day. (Don't worry. These were favorable remarks.)
Where
are
you
in
L.A.? The Valley?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that
a
message
will be forwarded to me.)


"tina" wrote in message

...
hey, Gunny, just checked out your website. very nice - it
joined
mvps.org,
lebans.com, granite.ab.ca, and all the other Access
bookmarks
in
my
browser.
the article on marketing was especially interesting, and
very
entertaining
too! btw, we're neighbors, i'm in so cal also.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Jonathan.

Any suggestions would be greatly appreciated.

You may want to look even further ahead in your
organization's
needs
for
this database, such as how many other types of phone
numbers
are
likely
to
be added. Pagers come to mind.

I suggest taking these five steps:

1.) Create a table for the types of phone numbers. Add
an
Autonumber
primary key and the "PhoneType" field to hold the name of
the
phone
type.
It might look like this:

Table: tblPhoneTypes

PTID PhoneType
1 Business
2 Cell
3 Home
4 Pager

2.) Create a query that gathers the information from the
EmpInfo
table
in
a
normalized structure and name this query qryEmpPhones:

SELECT EmpNum, HomePhone AS PhoneNum, 3 AS PTID
FROM EmpInfo
UNION
SELECT EmpNum, CellPhone, 2 AS PTID
FROM EmpInfo
UNION SELECT EmpNum, BusinessPhone, 1 AS PTID
FROM EmpInfo;

3.) Create a make table query with the following SQL
statement:

SELECT * INTO tblEmpPhones
FROM qryEmpPhones
ORDER BY EmpNum, 3;

4.) Open the tblEmpPhones table and edit the field
defaults
(such
as
size
of the text field), assign the primary key, open the table
properties
and
change the Subdatasheet Name combo box to [None], then
save
the
new
table.

5.) Open the Relationships window and create the
relationship
between
the
tblEmpPhones and tblPhoneTypes tables, then save the
change
to
the
Relationships window.

I'd also suggest not using special keys, like # and
spaces
in
field
names
and table names to avoid bugs later. You may even want to
add
an
additional
field to the tblEmpPhones table to indicate which phone
number
is
the
primary phone number to reach the employee at.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access
tips.

(Please remove ZERO_SPAM from my reply E-mail address, so
that
a
message
will be forwarded to me.)


"Jonathan Brown" Jonathan

wrote
in
message
...
I didn't normalize my database as well as I wish I had.
I
have
a
table
called EmpInfo that's built essentially as follows:

Emp#
HomePhone
CellPhone
BusinessPhone

What I would likek to do I build a different table
called
EmpPhones
that
would like like this:

Emp# (this field would be related to the Emp# field in
table
EmpInfo)
Phone#
Type (the type field would be a lookup to another table
with
a
list
of
different types of phone number)

The Emp# and Phone# fields together would make up my
multiple
field
primary
key.

How would I combine the the HomePhone, Cellphone, and
BusinessPhone
into
one
column, and then have 2 other columns with it's
associated
Emp#
and
Type?

Any suggestions would be greatly appreciated.

The slight head nod would work...g

My mother always said the same thing. Maybe you're my cousin instead of e.
e. cummings' LOL

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
nah, the red face is just from bowing so much. maybe i should just incline
my head slightly. g
good thing i listened to my mother - she always told me to "be careful
what
you do in public, you never know who's watching"....to this day, i never
go
anywhere without a hanky in my pocket! LOL


"Lynn Trapp" wrote in message
...
We're going to have to give you the nick name "Queen of Humble" .....

As John Vinson said, you ARE being observed in quite a positive way. :-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
i agree with you, Gunny. i read many, many more posts than i ever
respond
to, and i pay attention to everything - the correct answers, the
mistakes,
the corrections, and the overall quality of all responses. my respect
for
the master developers here isn't based on perceived "perfection", but
on
the
consistent display of expertise. and re myself, thanks for your kind
words.
smiles and bows, blushing again


"'69 Camaro" AM
wrote
in
message ...
Hi, Tina.

i'm so far below the level of the MVPs that we're not even
breathing
the
same concentration of oxygen

Remember that there's a steep learning curve to climb in order to
become
competent in Access. To master it requires a great deal more work.
And
no
one knows all there is to know about Access, not even the Access
MVP's
or
the group of people who developed Access (though some seem to come
pretty
close).

There are so many categories to work in (database design, queries,
forms,
VBA, et cetera), that many Access experts specialize in their
favorite
categories where they become masters, but in other areas they are
"good
enough." Many Access experts don't even venture into some areas,
like
database replication and security, so they can't answer many
technical
questions in these areas. That doesn't mean they aren't experts,
though.
It's merely an area that they're weak in.

i've posted answers that later made me cringe - usually after an
MVP
or
some
other learned folk gave a good answer

We've all been in this boat. Grab a paddle from name of choice.

gently pointed out my flub (though
occasionally i realize i blew it, without help).

Either way, you now know a correct answer to give the next time the
question
is asked.

In this forum, experts are answering questions in the categories
where
they
have specific expert knowledge, not the categories where they are
weak.
Look at this from another perspective. If these experts
concentrated
on
only answering questions in the categories where they're weak, you'd
notice
that they'd flub a number of the answers, too. Even the experts
don't
know
every single answer to every possible question in their areas of
expertise,
either, so you'll see occasional flubs there, too. But
professionals
don't
make the same mistake twice, and they don't let others make the same
mistakes they've made, either. If there's a better way to do
something,
it
will usually be pointed out in this forum by someone else who has
already
been down the same road. We can all learn from each other.

but i do give good solutions to a fair number of the simpler
questions,

They may be simple to you, maybe, but the person who asked the
question
knows that it isn't simple. Otherwise, he would have been able to
easily
figure it out without asking for help. You're at a level of
expertise
where
many operations seem simple to you because you've done them so often
that
you are familiar with them.

Don't be overly critical of yourself. The good solutions you've
given
show
that you know what you are talking about, and the people who
received
these
solutions know that they got them from an expert. :-)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)


"tina" wrote in message
...
wow, Gunny, i am beyond flattered - really. feels head expanding
to
fill
the room
i'm so far below the level of the MVPs that we're not even
breathing
the
same concentration of oxygen (at least it's nice and warm down
here),
and
i've posted answers that later made me cringe - usually after an
MVP
or
some
other learned folk gave a good answer, or gently pointed out my
flub
(though
occasionally i realize i blew it, without help). g
but i do give good solutions to a fair number of the simpler
questions,
and
i'm tickled pink to get a "good job!" from people i respect. you
really
made
my day - thank you!


"'69 Camaro" AM
wrote
in
message ...
Hi, Tina.

Your ISP has your computer listed as connecting from its L.A.
region,
but
if
you know where T.O. is located, then I figured that you must be
from
nearby,
like the S.F. Valley.

The discussion we had last week started out with a remark that
there
are
very few women who are Access MVP's, which led to the remark
that
it's
because there aren't very many women who hang around the
newsgroups
helping
people and who give consistently excellent answers, so the pool
to
draw
the
female Access MVP's from is actually quite small. When we tried
to
count
the number of women in this category who aren't already MVP's,
your
name
was
the first name that came to everybody's mind. "Everybody" in
this
case
is
a
very small group of computer geeks, so take that microcosm of
the
general
population in mind. But I just wanted to let you know that we
noticed
your
work and want to thank you for taking the time to join the crowd
of
volunteers and making the effort to share your valuable
experience,
because
there are _so_ many people who need help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)


"tina" wrote in message

...
ya know, i did feel a little itch a few days ago - i thought
it
was
from
the
Santa Anas. g that's actually scary to think i made a
little
blip
on
somebody's radar - y'all must really skim the treetops! lol

nope, not in L.A. Inland Empire.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Tina.

Glad you liked it! I'll tell the other consultants here,
because
that
makes
three people who've bookmarked our site! (Just kidding.
We're
up
to
five
now. ;-) )

Have your ears been burning? Your name came up in a
discussion
here
the
other day. (Don't worry. These were favorable remarks.)
Where
are
you
in
L.A.? The Valley?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so
that
a
message
will be forwarded to me.)


"tina" wrote in message

...
hey, Gunny, just checked out your website. very nice - it
joined
mvps.org,
lebans.com, granite.ab.ca, and all the other Access
bookmarks
in
my
browser.
the article on marketing was especially interesting, and
very
entertaining
too! btw, we're neighbors, i'm in so cal also.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Jonathan.

Any suggestions would be greatly appreciated.

You may want to look even further ahead in your
organization's
needs
for
this database, such as how many other types of phone
numbers
are
likely
to
be added. Pagers come to mind.

I suggest taking these five steps:

1.) Create a table for the types of phone numbers. Add
an
Autonumber
primary key and the "PhoneType" field to hold the name
of
the
phone
type.
It might look like this:

Table: tblPhoneTypes

PTID PhoneType
1 Business
2 Cell
3 Home
4 Pager

2.) Create a query that gathers the information from
the
EmpInfo
table
in
a
normalized structure and name this query qryEmpPhones:

SELECT EmpNum, HomePhone AS PhoneNum, 3 AS PTID
FROM EmpInfo
UNION
SELECT EmpNum, CellPhone, 2 AS PTID
FROM EmpInfo
UNION SELECT EmpNum, BusinessPhone, 1 AS PTID
FROM EmpInfo;

3.) Create a make table query with the following SQL
statement:

SELECT * INTO tblEmpPhones
FROM qryEmpPhones
ORDER BY EmpNum, 3;

4.) Open the tblEmpPhones table and edit the field
defaults
(such
as
size
of the text field), assign the primary key, open the
table
properties
and
change the Subdatasheet Name combo box to [None], then
save
the
new
table.

5.) Open the Relationships window and create the
relationship
between
the
tblEmpPhones and tblPhoneTypes tables, then save the
change
to
the
Relationships window.

I'd also suggest not using special keys, like # and
spaces
in
field
names
and table names to avoid bugs later. You may even want
to
add
an
additional
field to the tblEmpPhones table to indicate which phone
number
is
the
primary phone number to reach the employee at.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access
tips.

(Please remove ZERO_SPAM from my reply E-mail address,
so
that
a
message
will be forwarded to me.)


"Jonathan Brown" Jonathan

wrote
in
message

...
I didn't normalize my database as well as I wish I
had.
I
have
a
table
called EmpInfo that's built essentially as follows:

Emp#
HomePhone
CellPhone
BusinessPhone

What I would likek to do I build a different table
called
EmpPhones
that
would like like this:

Emp# (this field would be related to the Emp# field in
table
EmpInfo)
Phone#
Type (the type field would be a lookup to another
table
with
a
list
of
different types of phone number)

The Emp# and Phone# fields together would make up my
multiple
field
primary
key.

How would I combine the the HomePhone, Cellphone, and
BusinessPhone
into
one
column, and then have 2 other columns with it's
associated
Emp#
and
Type?

Any suggestions would be greatly appreciated.

shhh - like i said, wouldn't want to trade on personal associations, cuz! g
and shn
qh


"Lynn Trapp" wrote in message
...
The slight head nod would work...g

My mother always said the same thing. Maybe you're my cousin instead of e.
e. cummings' LOL

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
nah, the red face is just from bowing so much. maybe i should just
incline
my head slightly. g
good thing i listened to my mother - she always told me to "be careful
what
you do in public, you never know who's watching"....to this day, i never
go
anywhere without a hanky in my pocket! LOL


"Lynn Trapp" wrote in message
...
We're going to have to give you the nick name "Queen of Humble" .....

As John Vinson said, you ARE being observed in quite a positive way.
:-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
i agree with you, Gunny. i read many, many more posts than i ever
respond
to, and i pay attention to everything - the correct answers, the
mistakes,
the corrections, and the overall quality of all responses. my
respect
for
the master developers here isn't based on perceived "perfection",
but
on
the
consistent display of expertise. and re myself, thanks for your kind
words.
smiles and bows, blushing again


"'69 Camaro" AM
wrote
in
message ...
Hi, Tina.

i'm so far below the level of the MVPs that we're not even
breathing
the
same concentration of oxygen

Remember that there's a steep learning curve to climb in order to
become
competent in Access. To master it requires a great deal more
work.
And
no
one knows all there is to know about Access, not even the Access
MVP's
or
the group of people who developed Access (though some seem to come
pretty
close).

There are so many categories to work in (database design, queries,
forms,
VBA, et cetera), that many Access experts specialize in their
favorite
categories where they become masters, but in other areas they are
"good
enough." Many Access experts don't even venture into some areas,
like
database replication and security, so they can't answer many
technical
questions in these areas. That doesn't mean they aren't experts,
though.
It's merely an area that they're weak in.

i've posted answers that later made me cringe - usually after an
MVP
or
some
other learned folk gave a good answer

We've all been in this boat. Grab a paddle from name of choice.

gently pointed out my flub (though
occasionally i realize i blew it, without help).

Either way, you now know a correct answer to give the next time
the
question
is asked.

In this forum, experts are answering questions in the categories
where
they
have specific expert knowledge, not the categories where they are
weak.
Look at this from another perspective. If these experts
concentrated
on
only answering questions in the categories where they're weak,
you'd
notice
that they'd flub a number of the answers, too. Even the experts
don't
know
every single answer to every possible question in their areas of
expertise,
either, so you'll see occasional flubs there, too. But
professionals
don't
make the same mistake twice, and they don't let others make the
same
mistakes they've made, either. If there's a better way to do
something,
it
will usually be pointed out in this forum by someone else who has
already
been down the same road. We can all learn from each other.

but i do give good solutions to a fair number of the simpler
questions,

They may be simple to you, maybe, but the person who asked the
question
knows that it isn't simple. Otherwise, he would have been able to
easily
figure it out without asking for help. You're at a level of
expertise
where
many operations seem simple to you because you've done them so
often
that
you are familiar with them.

Don't be overly critical of yourself. The good solutions you've
given
show
that you know what you are talking about, and the people who
received
these
solutions know that they got them from an expert. :-)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)


"tina" wrote in message
...
wow, Gunny, i am beyond flattered - really. feels head
expanding
to
fill
the room
i'm so far below the level of the MVPs that we're not even
breathing
the
same concentration of oxygen (at least it's nice and warm down
here),
and
i've posted answers that later made me cringe - usually after an
MVP
or
some
other learned folk gave a good answer, or gently pointed out my
flub
(though
occasionally i realize i blew it, without help). g
but i do give good solutions to a fair number of the simpler
questions,
and
i'm tickled pink to get a "good job!" from people i respect. you
really
made
my day - thank you!


"'69 Camaro"
AM
wrote
in
message ...
Hi, Tina.

Your ISP has your computer listed as connecting from its L.A.
region,
but
if
you know where T.O. is located, then I figured that you must
be
from
nearby,
like the S.F. Valley.

The discussion we had last week started out with a remark that
there
are
very few women who are Access MVP's, which led to the remark
that
it's
because there aren't very many women who hang around the
newsgroups
helping
people and who give consistently excellent answers, so the
pool
to
draw
the
female Access MVP's from is actually quite small. When we
tried
to
count
the number of women in this category who aren't already MVP's,
your
name
was
the first name that came to everybody's mind. "Everybody" in
this
case
is
a
very small group of computer geeks, so take that microcosm of
the
general
population in mind. But I just wanted to let you know that we
noticed
your
work and want to thank you for taking the time to join the
crowd
of
volunteers and making the effort to share your valuable
experience,
because
there are _so_ many people who need help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that
a
message
will be forwarded to me.)


"tina" wrote in message

...
ya know, i did feel a little itch a few days ago - i thought
it
was
from
the
Santa Anas. g that's actually scary to think i made a
little
blip
on
somebody's radar - y'all must really skim the treetops! lol

nope, not in L.A. Inland Empire.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Tina.

Glad you liked it! I'll tell the other consultants here,
because
that
makes
three people who've bookmarked our site! (Just kidding.
We're
up
to
five
now. ;-) )

Have your ears been burning? Your name came up in a
discussion
here
the
other day. (Don't worry. These were favorable remarks.)
Where
are
you
in
L.A.? The Valley?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access
tips.

(Please remove ZERO_SPAM from my reply E-mail address, so
that
a
message
will be forwarded to me.)


"tina" wrote in message

...
hey, Gunny, just checked out your website. very nice -
it
joined
mvps.org,
lebans.com, granite.ab.ca, and all the other Access
bookmarks
in
my
browser.
the article on marketing was especially interesting, and
very
entertaining
too! btw, we're neighbors, i'm in so cal also.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Jonathan.

Any suggestions would be greatly appreciated.

You may want to look even further ahead in your
organization's
needs
for
this database, such as how many other types of phone
numbers
are
likely
to
be added. Pagers come to mind.

I suggest taking these five steps:

1.) Create a table for the types of phone numbers.
Add
an
Autonumber
primary key and the "PhoneType" field to hold the name
of
the
phone
type.
It might look like this:

Table: tblPhoneTypes

PTID PhoneType
1 Business
2 Cell
3 Home
4 Pager

2.) Create a query that gathers the information from
the
EmpInfo
table
in
a
normalized structure and name this query qryEmpPhones:

SELECT EmpNum, HomePhone AS PhoneNum, 3 AS PTID
FROM EmpInfo
UNION
SELECT EmpNum, CellPhone, 2 AS PTID
FROM EmpInfo
UNION SELECT EmpNum, BusinessPhone, 1 AS PTID
FROM EmpInfo;

3.) Create a make table query with the following SQL
statement:

SELECT * INTO tblEmpPhones
FROM qryEmpPhones
ORDER BY EmpNum, 3;

4.) Open the tblEmpPhones table and edit the field
defaults
(such
as
size
of the text field), assign the primary key, open the
table
properties
and
change the Subdatasheet Name combo box to [None], then
save
the
new
table.

5.) Open the Relationships window and create the
relationship
between
the
tblEmpPhones and tblPhoneTypes tables, then save the
change
to
the
Relationships window.

I'd also suggest not using special keys, like # and
spaces
in
field
names
and table names to avoid bugs later. You may even
want
to
add
an
additional
field to the tblEmpPhones table to indicate which
phone
number
is
the
primary phone number to reach the employee at.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access
tips.

(Please remove ZERO_SPAM from my reply E-mail address,
so
that
a
message
will be forwarded to me.)


"Jonathan Brown" Jonathan

wrote
in
message

...
I didn't normalize my database as well as I wish I
had.
I
have
a
table
called EmpInfo that's built essentially as follows:

Emp#
HomePhone
CellPhone
BusinessPhone

What I would likek to do I build a different table
called
EmpPhones
that
would like like this:

Emp# (this field would be related to the Emp# field
in
table
EmpInfo)
Phone#
Type (the type field would be a lookup to another
table
with
a
list
of
different types of phone number)

The Emp# and Phone# fields together would make up my
multiple
field
primary
key.

How would I combine the the HomePhone, Cellphone,
and
BusinessPhone
into
one
column, and then have 2 other columns with it's
associated
Emp#
and
Type?

Any suggestions would be greatly appreciated.

;-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
shhh - like i said, wouldn't want to trade on personal associations, cuz!
g
and shn
qh


"Lynn Trapp" wrote in message
...
The slight head nod would work...g

My mother always said the same thing. Maybe you're my cousin instead of
e.
e. cummings' LOL

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
nah, the red face is just from bowing so much. maybe i should just
incline
my head slightly. g
good thing i listened to my mother - she always told me to "be careful
what
you do in public, you never know who's watching"....to this day, i
never
go
anywhere without a hanky in my pocket! LOL


"Lynn Trapp" wrote in message
...
We're going to have to give you the nick name "Queen of Humble"
......

As John Vinson said, you ARE being observed in quite a positive way.
:-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
i agree with you, Gunny. i read many, many more posts than i ever
respond
to, and i pay attention to everything - the correct answers, the
mistakes,
the corrections, and the overall quality of all responses. my
respect
for
the master developers here isn't based on perceived "perfection",
but
on
the
consistent display of expertise. and re myself, thanks for your
kind
words.
smiles and bows, blushing again


"'69 Camaro" AM
wrote
in
message ...
Hi, Tina.

i'm so far below the level of the MVPs that we're not even
breathing
the
same concentration of oxygen

Remember that there's a steep learning curve to climb in order
to
become
competent in Access. To master it requires a great deal more
work.
And
no
one knows all there is to know about Access, not even the Access
MVP's
or
the group of people who developed Access (though some seem to
come
pretty
close).

There are so many categories to work in (database design,
queries,
forms,
VBA, et cetera), that many Access experts specialize in their
favorite
categories where they become masters, but in other areas they
are
"good
enough." Many Access experts don't even venture into some
areas,
like
database replication and security, so they can't answer many
technical
questions in these areas. That doesn't mean they aren't
experts,
though.
It's merely an area that they're weak in.

i've posted answers that later made me cringe - usually after
an
MVP
or
some
other learned folk gave a good answer

We've all been in this boat. Grab a paddle from name of
choice.

gently pointed out my flub (though
occasionally i realize i blew it, without help).

Either way, you now know a correct answer to give the next time
the
question
is asked.

In this forum, experts are answering questions in the categories
where
they
have specific expert knowledge, not the categories where they
are
weak.
Look at this from another perspective. If these experts
concentrated
on
only answering questions in the categories where they're weak,
you'd
notice
that they'd flub a number of the answers, too. Even the experts
don't
know
every single answer to every possible question in their areas of
expertise,
either, so you'll see occasional flubs there, too. But
professionals
don't
make the same mistake twice, and they don't let others make the
same
mistakes they've made, either. If there's a better way to do
something,
it
will usually be pointed out in this forum by someone else who
has
already
been down the same road. We can all learn from each other.

but i do give good solutions to a fair number of the simpler
questions,

They may be simple to you, maybe, but the person who asked the
question
knows that it isn't simple. Otherwise, he would have been able
to
easily
figure it out without asking for help. You're at a level of
expertise
where
many operations seem simple to you because you've done them so
often
that
you are familiar with them.

Don't be overly critical of yourself. The good solutions you've
given
show
that you know what you are talking about, and the people who
received
these
solutions know that they got them from an expert. :-)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)


"tina" wrote in message

...
wow, Gunny, i am beyond flattered - really. feels head
expanding
to
fill
the room
i'm so far below the level of the MVPs that we're not even
breathing
the
same concentration of oxygen (at least it's nice and warm down
here),
and
i've posted answers that later made me cringe - usually after
an
MVP
or
some
other learned folk gave a good answer, or gently pointed out
my
flub
(though
occasionally i realize i blew it, without help). g
but i do give good solutions to a fair number of the simpler
questions,
and
i'm tickled pink to get a "good job!" from people i respect.
you
really
made
my day - thank you!


"'69 Camaro"
AM
wrote
in
message ...
Hi, Tina.

Your ISP has your computer listed as connecting from its
L.A.
region,
but
if
you know where T.O. is located, then I figured that you must
be
from
nearby,
like the S.F. Valley.

The discussion we had last week started out with a remark
that
there
are
very few women who are Access MVP's, which led to the remark
that
it's
because there aren't very many women who hang around the
newsgroups
helping
people and who give consistently excellent answers, so the
pool
to
draw
the
female Access MVP's from is actually quite small. When we
tried
to
count
the number of women in this category who aren't already
MVP's,
your
name
was
the first name that came to everybody's mind. "Everybody"
in
this
case
is
a
very small group of computer geeks, so take that microcosm
of
the
general
population in mind. But I just wanted to let you know that
we
noticed
your
work and want to thank you for taking the time to join the
crowd
of
volunteers and making the effort to share your valuable
experience,
because
there are _so_ many people who need help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so
that
a
message
will be forwarded to me.)


"tina" wrote in message

...
ya know, i did feel a little itch a few days ago - i
thought
it
was
from
the
Santa Anas. g that's actually scary to think i made a
little
blip
on
somebody's radar - y'all must really skim the treetops!
lol

nope, not in L.A. Inland Empire.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Tina.

Glad you liked it! I'll tell the other consultants
here,
because
that
makes
three people who've bookmarked our site! (Just kidding.
We're
up
to
five
now. ;-) )

Have your ears been burning? Your name came up in a
discussion
here
the
other day. (Don't worry. These were favorable
remarks.)
Where
are
you
in
L.A.? The Valley?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access
tips.

(Please remove ZERO_SPAM from my reply E-mail address,
so
that
a
message
will be forwarded to me.)


"tina" wrote in message

...
hey, Gunny, just checked out your website. very nice -
it
joined
mvps.org,
lebans.com, granite.ab.ca, and all the other Access
bookmarks
in
my
browser.
the article on marketing was especially interesting,
and
very
entertaining
too! btw, we're neighbors, i'm in so cal also.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Jonathan.

Any suggestions would be greatly appreciated.

You may want to look even further ahead in your
organization's
needs
for
this database, such as how many other types of phone
numbers
are
likely
to
be added. Pagers come to mind.

I suggest taking these five steps:

1.) Create a table for the types of phone numbers.
Add
an
Autonumber
primary key and the "PhoneType" field to hold the
name
of
the
phone
type.
It might look like this:

Table: tblPhoneTypes

PTID PhoneType
1 Business
2 Cell
3 Home
4 Pager

2.) Create a query that gathers the information
from
the
EmpInfo
table
in
a
normalized structure and name this query
qryEmpPhones:

SELECT EmpNum, HomePhone AS PhoneNum, 3 AS PTID
FROM EmpInfo
UNION
SELECT EmpNum, CellPhone, 2 AS PTID
FROM EmpInfo
UNION SELECT EmpNum, BusinessPhone, 1 AS PTID
FROM EmpInfo;

3.) Create a make table query with the following
SQL
statement:

SELECT * INTO tblEmpPhones
FROM qryEmpPhones
ORDER BY EmpNum, 3;

4.) Open the tblEmpPhones table and edit the field
defaults
(such
as
size
of the text field), assign the primary key, open the
table
properties
and
change the Subdatasheet Name combo box to [None],
then
save
the
new
table.

5.) Open the Relationships window and create the
relationship
between
the
tblEmpPhones and tblPhoneTypes tables, then save the
change
to
the
Relationships window.

I'd also suggest not using special keys, like # and
spaces
in
field
names
and table names to avoid bugs later. You may even
want
to
add
an
additional
field to the tblEmpPhones table to indicate which
phone
number
is
the
primary phone number to reach the employee at.

HTH.

Gunny

See http://www.QBuilt.com for all your database
needs.
See http://www.Access.QBuilt.com for Microsoft
Access
tips.

(Please remove ZERO_SPAM from my reply E-mail
address,
so
that
a
message
will be forwarded to me.)


"Jonathan Brown" Jonathan

wrote
in
message

...
I didn't normalize my database as well as I wish I
had.
I
have
a
table
called EmpInfo that's built essentially as
follows:

Emp#
HomePhone
CellPhone
BusinessPhone

What I would likek to do I build a different table
called
EmpPhones
that
would like like this:

Emp# (this field would be related to the Emp#
field
in
table
EmpInfo)
Phone#
Type (the type field would be a lookup to another
table
with
a
list
of
different types of phone number)

The Emp# and Phone# fields together would make up
my
multiple
field
primary
key.

How would I combine the the HomePhone, Cellphone,
and
BusinessPhone
into
one
column, and then have 2 other columns with it's
associated
Emp#
and
Type?

Any suggestions would be greatly appreciated.

I never would have thought that one of my posts would Morph into a social
chat room. You guys are funny.

"Lynn Trapp" wrote:

;-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
shhh - like i said, wouldn't want to trade on personal associations, cuz!
g
and shn
qh


"Lynn Trapp" wrote in message
...
The slight head nod would work...g

My mother always said the same thing. Maybe you're my cousin instead of
e.
e. cummings' LOL

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
nah, the red face is just from bowing so much. maybe i should just
incline
my head slightly. g
good thing i listened to my mother - she always told me to "be careful
what
you do in public, you never know who's watching"....to this day, i
never
go
anywhere without a hanky in my pocket! LOL


"Lynn Trapp" wrote in message
...
We're going to have to give you the nick name "Queen of Humble"
......

As John Vinson said, you ARE being observed in quite a positive way.
:-)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


"tina" wrote in message
...
i agree with you, Gunny. i read many, many more posts than i ever
respond
to, and i pay attention to everything - the correct answers, the
mistakes,
the corrections, and the overall quality of all responses. my
respect
for
the master developers here isn't based on perceived "perfection",
but
on
the
consistent display of expertise. and re myself, thanks for your
kind
words.
smiles and bows, blushing again


"'69 Camaro" AM
wrote
in
message ...
Hi, Tina.

i'm so far below the level of the MVPs that we're not even
breathing
the
same concentration of oxygen

Remember that there's a steep learning curve to climb in order
to
become
competent in Access. To master it requires a great deal more
work.
And
no
one knows all there is to know about Access, not even the Access
MVP's
or
the group of people who developed Access (though some seem to
come
pretty
close).

There are so many categories to work in (database design,
queries,
forms,
VBA, et cetera), that many Access experts specialize in their
favorite
categories where they become masters, but in other areas they
are
"good
enough." Many Access experts don't even venture into some
areas,
like
database replication and security, so they can't answer many
technical
questions in these areas. That doesn't mean they aren't
experts,
though.
It's merely an area that they're weak in.

i've posted answers that later made me cringe - usually after
an
MVP
or
some
other learned folk gave a good answer

We've all been in this boat. Grab a paddle from name of
choice.

gently pointed out my flub (though
occasionally i realize i blew it, without help).

Either way, you now know a correct answer to give the next time
the
question
is asked.

In this forum, experts are answering questions in the categories
where
they
have specific expert knowledge, not the categories where they
are
weak.
Look at this from another perspective. If these experts
concentrated
on
only answering questions in the categories where they're weak,
you'd
notice
that they'd flub a number of the answers, too. Even the experts
don't
know
every single answer to every possible question in their areas of
expertise,
either, so you'll see occasional flubs there, too. But
professionals
don't
make the same mistake twice, and they don't let others make the
same
mistakes they've made, either. If there's a better way to do
something,
it
will usually be pointed out in this forum by someone else who
has
already
been down the same road. We can all learn from each other.

but i do give good solutions to a fair number of the simpler
questions,

They may be simple to you, maybe, but the person who asked the
question
knows that it isn't simple. Otherwise, he would have been able
to
easily
figure it out without asking for help. You're at a level of
expertise
where
many operations seem simple to you because you've done them so
often
that
you are familiar with them.

Don't be overly critical of yourself. The good solutions you've
given
show
that you know what you are talking about, and the people who
received
these
solutions know that they got them from an expert. :-)

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a
message
will be forwarded to me.)


"tina" wrote in message

...
wow, Gunny, i am beyond flattered - really. feels head
expanding
to
fill
the room
i'm so far below the level of the MVPs that we're not even
breathing
the
same concentration of oxygen (at least it's nice and warm down
here),
and
i've posted answers that later made me cringe - usually after
an
MVP
or
some
other learned folk gave a good answer, or gently pointed out
my
flub
(though
occasionally i realize i blew it, without help). g
but i do give good solutions to a fair number of the simpler
questions,
and
i'm tickled pink to get a "good job!" from people i respect.
you
really
made
my day - thank you!


"'69 Camaro"
AM
wrote
in
message ...
Hi, Tina.

Your ISP has your computer listed as connecting from its
L.A.
region,
but
if
you know where T.O. is located, then I figured that you must
be
from
nearby,
like the S.F. Valley.

The discussion we had last week started out with a remark
that
there
are
very few women who are Access MVP's, which led to the remark
that
it's
because there aren't very many women who hang around the
newsgroups
helping
people and who give consistently excellent answers, so the
pool
to
draw
the
female Access MVP's from is actually quite small. When we
tried
to
count
the number of women in this category who aren't already
MVP's,
your
name
was
the first name that came to everybody's mind. "Everybody"
in
this
case
is
a
very small group of computer geeks, so take that microcosm
of
the
general
population in mind. But I just wanted to let you know that
we
noticed
your
work and want to thank you for taking the time to join the
crowd
of
volunteers and making the effort to share your valuable
experience,
because
there are _so_ many people who need help.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so
that
a
message
will be forwarded to me.)


"tina" wrote in message

...
ya know, i did feel a little itch a few days ago - i
thought
it
was
from
the
Santa Anas. g that's actually scary to think i made a
little
blip
on
somebody's radar - y'all must really skim the treetops!
lol

nope, not in L.A. Inland Empire.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Tina.

Glad you liked it! I'll tell the other consultants
here,
because
that
makes
three people who've bookmarked our site! (Just kidding.
We're
up
to
five
now. ;-) )

Have your ears been burning? Your name came up in a
discussion
here
the
other day. (Don't worry. These were favorable
remarks.)
Where
are
you
in
L.A.? The Valley?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access
tips.

(Please remove ZERO_SPAM from my reply E-mail address,
so
that
a
message
will be forwarded to me.)


"tina" wrote in message

...
hey, Gunny, just checked out your website. very nice -
it
joined
mvps.org,
lebans.com, granite.ab.ca, and all the other Access
bookmarks
in
my
browser.
the article on marketing was especially interesting,
and
very
entertaining
too! btw, we're neighbors, i'm in so cal also.


"'69 Camaro"
AM
wrote
in
message ...
Hi, Jonathan.

Any suggestions would be greatly appreciated.

You may want to look even further ahead in your
organization's
needs
for
this database, such as how many other types of phone
numbers
are
likely
to
be added. Pagers come to mind.

I suggest taking these five steps:

1.) Create a table for the types of phone numbers.
Add
an
Autonumber
primary key and the "PhoneType" field to hold the
name
of
the
phone
type.
It might look like this:

Table: tblPhoneTypes

PTID PhoneType
1 Business
2 Cell
3 Home
4 Pager

2.) Create a query that gathers the information
from
the
EmpInfo
table
in
a
normalized structure and name this query
qryEmpPhones:

SELECT EmpNum, HomePhone AS PhoneNum, 3 AS PTID
FROM EmpInfo
UNION
SELECT EmpNum, CellPhone, 2 AS PTID
FROM EmpInfo
UNION SELECT EmpNum, BusinessPhone, 1 AS PTID
FROM EmpInfo;

3.) Create a make table query with the following
SQL
statement:

SELECT * INTO tblEmpPhones
FROM qryEmpPhones
ORDER BY EmpNum, 3;

4.) Open the tblEmpPhones table and edit the field
defaults
(such
as
size
of the text field), assign the primary key, open the
table
properties
and
change the Subdatasheet Name combo box to [None],
then
save
the
new
table.

5.) Open the Relationships window and create the
relationship
between
the
tblEmpPhones and tblPhoneTypes tables, then save the
change
to
the
Relationships window.

I'd also suggest not using special keys, like # and
spaces
in
field
names
and table names to avoid bugs later. You may even
want
to
add
an
additional
field to the tblEmpPhones table to indicate which
phone
number
is
the
primary phone number to reach the employee at.

HTH.

Gunny

See http://www.QBuilt.com for all your database
needs.
See http://www.Access.QBuilt.com for Microsoft
Access
tips.

(Please remove ZERO_SPAM from my reply E-mail
address,
so
that
a
message
will be forwarded to me.)


"Jonathan Brown" Jonathan

wrote
in
message

...
I didn't normalize my database as well as I wish I
had.
I
have
a
table
called EmpInfo that's built essentially as
follows:

Emp#
HomePhone
CellPhone
BusinessPhone

What I would likek to do I build a different table
called
EmpPhones
that
would like like this:

Emp# (this field would be related to the Emp#
field
in
table
EmpInfo)
Phone#
Type (the type field would be a lookup to another
table
with
a
list
of
different types of phone number)

The Emp# and Phone# fields together would make up
my
multiple
field
primary
key.

How would I combine the the HomePhone, Cellphone,
and
BusinessPhone
into
one
column, and then have 2 other columns with it's
associated
Emp#
and
Type?

Any suggestions would be greatly appreciated.