Re: Keyfield and fields

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

From: Lin (Lin_at_discussions.microsoft.com)
Date: 02/16/05 

Date: Wed, 16 Feb 2005 13:15:03 -0800

Hello Larry and thanks for reply,

I have read and tried normalization tables, in fact I had a few variations
running. I think the overall problem is that I have clients with a few first
names and last names. I have to be able to search for all variations. It's
probably going to need a many to many situation. Basically if I understand
correctly, you should not replicate information. Unfortunately in my
business this cannot be avoided because of the complexity of the families we
deal with. We need to be able to locate a client under all their aliases.
In some cases this could be a few depending on how much detail they are
willing to provide the agency.

We have a file number attached to (one) person. That part is easy. But
that person may have many aliases. My most recent attempt is to have a table
with key file number, a table with related id and surname, and lastly a table
with related id and first name. We also have the additional problem of the
constant change of addresses for our clients. So Ive created another table
with related id and addresses. Normalizing just doen't seem to work unless I
am way off base. However, one person wrote in about a fmily database he was
trying to create and the response was similar.

Any suggestions would be very much appreciated. And thank you.

"Larry Daugherty" wrote:

> Yes, it will cause you problems later.
>
> Find and read the rules for Normalization. According to those rules, a
> field in a relational database system must be "atomic" that is it must
> contain a single instance of a single attribute. Your suggested solution
> allows many instances of the same thing. As you learn more about Access you
> will want to create queries and Reports. With something like you're putting
> together you'd find it difficult to create powerful queries or useful
> reports.
>
> HTH
> --
> -Larry-
> --
>
> "Lin" <Lin@discussions.microsoft.com> wrote in message
> news:AFB84443-D5AB-43A4-94CE-D075D6839701@microsoft.com...
> > Sorry one other question. I have a sample running, what I did was create
> a
> > larger text field to 100 for both first names and last names. People
> would
> > use enter for new line in field. This looks to work okay, even for
> searches,
> > but because the fields are so big, would this cause me problems down the
> > road???????
> >
> >
> > Any help, please. Thanks.
> >
> > "Lin" wrote:
> >
> > > Another questioin in regards to multiple names. What if you have
> clients
> > > with multy surnames and multi first names? Would I have to create three
> > > tables?
> > > One for Index key, One for surnames and one for given names?
> > >
> > > Please help, thanks in advance.
> > >
> > > "John Vinson" wrote:
> > >
> > > > On Tue, 21 Sep 2004 13:23:06 -0700, Lin
> > > > <Lin@discussions.microsoft.com> wrote:
> > > >
> > > > >If I have a table with file numbers, and each family (record) may
> have more
> > > > >than one surname. Example. The primary person may be known under
> multiple
> > > > >aliases. There may also be extended family names to include. How
> can this
> > > > >be done if you can't have replicates under a key field? Please
> help.
> > > >
> > > > If you have a one (file) to many (surnames) relationship, you need
> > > > *more tables* - a table with file numbers related one-to-many to a
> > > > table of people, which may in turn be related one-to-many to a table
> > > > of aliases.
> > > >
> > > > John W. Vinson[MVP]
> > > > Join the online Access Chats
> > > > Tuesday 11am EDT - Thursday 3:30pm EDT
> > > > http://community.compuserve.com/msdevapps
> > > >
>
>
>

Quantcast