Re: Table Normalization

From: '69 Camaro (ForwardZERO_SPAM.To.69Camaro_at_Spameater.orgZERO_SPAM)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 07:28:06 -0800

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" <nospam@address.com> wrote in message
news:Hnavd.119577$7i4.11258@bgtnsc05-news.ops.worldnet.att.net...
> 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" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM> wrote
in
> message news:Oma0$UK4EHA.1400@TK2MSFTNGP11.phx.gbl...
> > 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" <nospam@address.com> wrote in message
> > news:af2vd.1081493$Gx4.55978@bgtnsc04-news.ops.worldnet.att.net...
> > > 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" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM>
wrote
> > in
> > > message news:O5SfQSB4EHA.924@TK2MSFTNGP14.phx.gbl...
> > > > 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" <nospam@address.com> wrote in message
> > > > news:3GQud.116058$7i4.47507@bgtnsc05-news.ops.worldnet.att.net...
> > > > > 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" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM>
> > wrote
> > > > in
> > > > > message news:eLfEdL$3EHA.3376@TK2MSFTNGP12.phx.gbl...
> > > > > > 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 Subdata*** 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 Brown@discussions.microsoft.com>
wrote
> in
> > > > > message
> > > > > > news:E153D5DD-F59B-4879-B546-A77F67E242FA@microsoft.com...
> > > > > > > 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.
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>