Table Normalization

[Guest]

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.

 

[Ken Snell [MVP]]

Build the new tables EmpPhones and the PhoneTypes. Decide the numbers that
will be used for the three types of phones.

Then run three append queries, one for each of the phone types. The
following example assumes that the ID value of 1 corresponds to a home phone
type:

INSERT INTO EmpPhones
([Emp#], [Phone#], PhoneTypeID)
SELECT [Emp#], [HomePhone], 1
FROM EmpInfo;

Then modify as needed for the other two types and run them separately.

 

['69 Camaro]

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

 

[Guest]

Ken,

Your suggestion is working well except for one thing, and I think it is
probably because of problems with my own database but when I run the query it
displays an error message saying "it didn't add 46 records to the tabel due
to key violations."

What do you suppose that might mean?

Thanks
Jonathan

Build the new tables EmpPhones and the PhoneTypes. Decide the numbers that
will be used for the three types of phones.

Then run three append queries, one for each of the phone types. The
following example assumes that the ID value of 1 corresponds to a home phone
type:

INSERT INTO EmpPhones
([Emp#], [Phone#], PhoneTypeID)
SELECT [Emp#], [HomePhone], 1
FROM EmpInfo;

Then modify as needed for the other two types and run them separately.
--

Ken Snell
<MS ACCESS MVP>

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.

 

[Guest]

Nevermind, I answered that question myself. It's because some of the fields
(65 of them) are blank. To verify this I went ahead and ran the append query
and it ended up eppending only 119. I then created a select query that
pulled Emp#, and HomePhone and added the "is not null" criteria under the
HomePhone and it came up with 119 records. So apparently everything is cool.

Thanks Ken

Ken,

Your suggestion is working well except for one thing, and I think it is
probably because of problems with my own database but when I run the query it
displays an error message saying "it didn't add 46 records to the tabel due
to key violations."

What do you suppose that might mean?

Thanks
Jonathan

Build the new tables EmpPhones and the PhoneTypes. Decide the numbers that
will be used for the three types of phones.

Then run three append queries, one for each of the phone types. The
following example assumes that the ID value of 1 corresponds to a home phone
type:

INSERT INTO EmpPhones
([Emp#], [Phone#], PhoneTypeID)
SELECT [Emp#], [HomePhone], 1
FROM EmpInfo;

Then modify as needed for the other two types and run them separately.
--

Ken Snell
<MS ACCESS MVP>

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.

 

[tina]

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.

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

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.

 

['69 Camaro]

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

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.


message news:[email protected]...

Hi, Jonathan.


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

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.

 

[Jeff Conrad]

Excuse me Gunny, but I assume I am on that short list, correct?
I bookmarked your site a long time ago!

--
Jeff Conrad
Access Junkie
Bend, Oregon

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

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.


message news:[email protected]...

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


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.

 

['69 Camaro]

Hi, Jeff.

I bookmarked your site a long time ago!

Yup! Back in the middle of August, wasn't it?

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

Excuse me Gunny, but I assume I am on that short list, correct?
I bookmarked your site a long time ago!

--
Jeff Conrad
Access Junkie
Bend, Oregon

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

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" <[email protected]_SPAM>

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


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.

 

[Jeff Conrad]

Yep, right around Aug 9th sometime.
;-)

It's been on my massive list of Access links (which I occasionally post in the NG) ever since then.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Hi, Jeff.

I bookmarked your site a long time ago!

Yup! Back in the middle of August, wasn't it?

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

Excuse me Gunny, but I assume I am on that short list, correct?
I bookmarked your site a long time ago!

--
Jeff Conrad
Access Junkie
Bend, Oregon

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


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.


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


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.

 

[tina]

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.

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

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.


message news:[email protected]...

likely

to

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


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.

 

['69 Camaro]

Hi, Jeff.

It's a pretty comprehensive list. I know I've referenced it a few times
myself when I was hunting for information. Thanks for posting such a
valuable list for the rest of us. And thanks, of course, for including our
company's Web site on your list, too!

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

Yep, right around Aug 9th sometime.
;-)

It's been on my massive list of Access links (which I occasionally post in the NG) ever since then.

--
Jeff Conrad
Access Junkie
Bend, Oregon

Hi, Jeff.

I bookmarked your site a long time ago!

Yup! Back in the middle of August, wasn't it?

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

Excuse me Gunny, but I assume I am on that short list, correct?
I bookmarked your site a long time ago!

--
Jeff Conrad
Access Junkie
Bend, Oregon

"'69 Camaro" <[email protected]_SPAM>

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


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.


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


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.

 

['69 Camaro]

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

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.


message news:[email protected]...

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

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" <[email protected]_SPAM>

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


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 BusinessPhone
into

 

[tina]

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!

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

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.


message news:[email protected]...

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


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.


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


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.

 

[John Vinson]

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)

Don't sell yourself short, Tina... you're being watched (with interest
and approval, not out of malice!!)

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

 

[tina]

thanks for your kind words, John. i was sincere about the "gently"! all you
MVPs are always kind to me, and helpful - and i'm always grateful when
somebody "catches my back" and posts a corrected or better solution. the
most important thing is helping the person who asked. besides that, when
somebody corrects me, or goes one better, then i learn too - and then i'm
really happy!

and i'm sincere in my reference to the MVPs. i have enormous respect for you
folks. while i know i'm a good intermediate developer, i'm also realistic
about my limitations. for someone to think of me when they think of MVP
material, is the highest compliment! <bows and smiles, blushing>

 

[Fred Boer]

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

 

['69 Camaro]

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

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

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!


message news:[email protected]...

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

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" <[email protected]_SPAM>

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


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.


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

 

[Ken Snell [MVP]]

"Hear Hear" to Gunny's words... they are very much correct and true!

--

Ken Snell
<MS ACCESS MVP>

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

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

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!


message news:[email protected]... but draw
the name
was

case

is

blip

on

somebody's radar - y'all must really skim the treetops! lol

nope, not in L.A. Inland Empire.


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


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"

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


have

 

[tina]

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>

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

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

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!


message news:[email protected]... but draw
the name
was

case

is

blip

on

somebody's radar - y'all must really skim the treetops! lol

nope, not in L.A. Inland Empire.


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


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"

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


have