Re: Direction needed with a field conundrum

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Jeff Conrad (jeffc_at_ernstbrothers.com)
Date: 11/08/04 

Date: Sun, 7 Nov 2004 22:32:58 -0800

Hi Ken,

I appreciate your comments and time.

Yes, I do believe we will be needing to make frequent searches on the owner's *name*, whatever that
may be. Reports and queries will be needed to run to see what parcels this person or entity owns,
past sales, history, etc.

Currently this information is in one field and quite frankly is a huge mess, particularly with
company names. A large part of the issue comes from simple punctuation errors.

For example, there are records that look just like this:
Ken Snell, LLC.
Ken Snell LLC (notice no comma or period)
Ken Snell, LLC (notice no period)
 Ken Snell, LLC. (notice the single space in front)

In actuality all four of these entities are exactly the same company, but to the existing AS400 data
tables they are four completely different owners! Not good!

A similar problem arises with individuals:
Ken Snell
Mrs. Snell
Ken and Mrs. Snell

There are times where simple human error has made *bad* records, but in some cases the above three
record are legitimate. Mr. Snell has some property in his name, Mrs. Snell has property in her own
name, and they also have property together. This actually happens more often than I thought it
would.

So I am in a quandary about the best way to proceed. Part of me believes that it may be best to only
have one field for the owner 'name' in this table, but I'm still not convinced. I do not think we
will need to join a part of the name to a field in another table, but we are still in the initial
beginning phases at this point. 

-- 
Jeff Conrad
Access Junkie
Bend, Oregon
"Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message
news:e3gbOBSxEHA.1264@TK2MSFTNGP12.phx.gbl...
> Jeff -
>
> One way to approach a decision is to consider what you'll do with the name
> field data. Will you be needing to search on those names, say, last names?
> Will you need to join a part of the name to a field in another table, say, a
> table of owners' last names where an owner last name record would be related
> to a parcel? Or will the name just be displayed in most cases?
>
> If the name parts won't be used to join other tables, then I'd be inclined
> to consider using a single field for the entire "owner" information in terms
> of name. You can always do a search of that field for a specific last name
> or company name if you wanted, though it might be slow for a large database.
>
> Otherwise, you'll have to make some decisions about how much atomicity
> you'll want to be able to have for the name data, and then design the table
> accordingly.
>
> My experience with similar things has been that trying to figure out how to
> separate these data into all possible fields that I can envision wanting
> (e.g., first name, middle name, last name, first company name, subsidiary
> company name, "dba" name, suffix for business corporate status [e.g., LLC,
> Inc., etc.], identifier suffix for person [e.g., Ph.D., Jr.], descriptor for
> person [e.g., Dr., etc.], etc.) can become daunting. Legal names, which are
> what are used for parcel ownership, etc., can be quite different in
> structure and content than "normal" names.
>
> Just my 2 cents suggestions....
> -- 
>
>         Ken Snell
> <MS ACCESS MVP>
>
>
>
>
> "Jeff Conrad" <jeffc@ernstbrothers.com> wrote in message
> news:urTCDORxEHA.1512@TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I'm helping a local county office with a fairly big project. This county
> office does Assessment,
> > Taxation, Survey, etc. We're just at the beginning stages of setting up
> tables in Access. Tables
> > will eventually be upsized to SQL server. We're also going to have to
> migrate 15+ years of badly
> > designed flat file AS400 data tables into the new table structure. Lots of
> orphan data, redundant
> > information, etc. Lots of cleanup work will need to be done; not fun.
> >
> > I'm having some difficulty with some field arrangements for a table of
> "Owners." An owner can have
> > many Parcels (another table) and a Parcel can have more than owner (M:M of
> course). My trouble is
> > setting up the fields for the *names* in the Owner table.
> >
> > An owner, for example could be a person: John M. Doe.
> > However, an owner could also be a company entity: Wyzard Enterprises, Inc.
> > Also, an owner could very well be a husband AND wife: John M. Doe and Jane
> D. Doe
> >
> > Right now the owner information is ALL stored in one field. What I'm
> trying to figure out is do I
> > have fields for first, last, middle name AND a field for a company name if
> applicable? Something
> > like so:
> >
> > OwnerID
> > FirstName
> > MiddleName
> > LastName
> > CompanyName
> >
> > Or is this not the way to go? A different arrangement???
> > Thanks for any comments,
> > -- 
> > Jeff Conrad
> > Access Junkie
> > Bend, Oregon

Relevant Pages

  • Re: Direction needed with a field conundrum
    ... Suppose you have an owner named Jeff Conrad. ... uniquely identify *the* Jeff Conrad who owns the parcel of land in the NW ... > Ken Snell, LLC. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Need advice about Proxy and IP
    ... being the beneficial owner. ... A "registered agent" for your LLC could be used, ... payment option. ...
    (alt.privacy)
  • Re: What say you, group?
    ... Owner and proprietor, Trollus Amongus, LLC ...
    (rec.music.artists.kiss)
  • Re: Piercing the Corporate Veil
    ... specifically how can an owner keep the entities separate? ... Your scenario is muddled. ... What LLC? ... And if he's the owner of a sole ...
    (misc.legal)
  • Re: How do you look?
    ... Owner and proprietor, Trollus Amongus, LLC ...
    (rec.music.artists.kiss)