Re: Table Normalization

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 12/15/04 

Date: Wed, 15 Dec 2004 12:20:21 -0500

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

-- 
        Ken Snell
<MS ACCESS MVP>
"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@Spameater.orgZERO_SPAM> wrote in
message news:uKYy$rr4EHA.2180@TK2MSFTNGP10.phx.gbl...
> 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.
> > > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>
Quantcast