Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    Willamette Valley, Oregon
    Posts
    7

    Question Unanswered: Design Question - How detailed is too detailed?

    Howdy folks,

    Right now I'm making a database to manage information for several different mailing lists. I want to do this right so I'm not hand-tweaking a couple hundred letters next time I use the address information for a mail merge.

    The problem is that I have some addresses with company names (and some of those have attn: lines -- one or two people, not always with the same last name), while others are to individuals (one or two people at the same address -- again, not always with the same last name). Another monkey wrench in this mess is that a few people have titles at the beginning of their names (eg: Rev.), while others have them at the end (eg: Phd or Director).

    I want to keep this as simple as possible, but I'm threatening to shove the companies & individuals into different tables (I have about 400 entries).

    What I have for dealing wit the names is as follows:
    Company Name line 1
    Company Name line 2
    attn: (to add an attn line into mail merge)
    Person 1 Mr/Ms
    Person 1 First Name
    and (to add a second person with the same last name to the list)
    Person 1.1 Mr/Ms
    Person 1.1 First Name (has same last name as person 1)
    Person 1 Last Name
    Person 1 Title
    Person 2 Mr/Ms (Has different last name than person 1)
    Person 2 last name
    Person 2 Title

    Am I on the right track or am I blowing this way out of porportion?

    Thanks a bunch,
    Annie

  2. #2
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    I will suggest creating two tables

    tblCompany

    tblContacts

    with a one-to-many relationship

    once this is done you can just create a query to create the detail.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  3. #3
    Join Date
    Feb 2003
    Location
    Auckland, NZ
    Posts
    150
    I will suggest creating two tables

    tblCompany

    tblContacts

    with a one-to-many relationship

    once this is done you can just create a query to create the detail.
    JJ Kennedy
    Double J IT Solutions
    www.doublejit.co.nz

    VB 6, VB.NET, ASP.NET, MS SQL Server, MySQL, MS Access

  4. #4
    Join Date
    Aug 2003
    Location
    Willamette Valley, Oregon
    Posts
    7

    Smile

    Thanks for the suggestion -- I hadn't thought about splitting things that direction.

    Annie

  5. #5
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by annie_dragon
    Thanks for the suggestion -- I hadn't thought about splitting things that direction.

    Annie
    Other thing is that the typical name in a database should be stored as 5 fields. Because the person who takes over your will curse you for a long time if you don't.

    The fields should be:
    • PREFIX: (MR., Ms., Miss, Dr., Rev.) use a drop down with the ability to hand type
    • FIRST_NAME
    • MIDDLE_NAME/MIDDLE_INITIAL
    • LAST_NAME
    • SUFFIX: (Jr., Sr., PHD, DO) adain use a drop down with the ability to hand type.


    Then in your queries or report boxes the concantenation would be something like
    Adressee: trim(iif(PREFIX="" or isnull(PREFIX)," ",PREFIX) &" " & iif(FIRST_NAME="" or isnull(FIRST_NAME)," ",FIRST_NAME) & iif(MIDDLE_INITIAL<>"" and not isnull(MIDDLE_INITIAL), " " & MIDDLE_INITIAL & " " ," ") & LAST_NAME & iif(SUFFIX="" or isnull(SUFFIX)," "," " & SUFFIX))

    Also in the report you can do a suppress on the text boxes if they are empty by making the ".05" high with a Can Grow Property set to yes.

    Just some hints.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  6. #6
    Join Date
    Aug 2003
    Location
    Willamette Valley, Oregon
    Posts
    7

    Smile

    That helps quite a bit. You'd think that this kind of an example would be in the assistance books rather than the one field for name examples where you can't even sort by last name (like the list that I started out with).

    I'm assuming that I should be able to run a similar query to bunch the names together once I've grouped the people who are at one address so that I end up with "John and Betty Blue" instead of "John Blue and Betty Blue" for the addressee.

    Thanks for help as I learn how to muddle my way through this.

    Annie

  7. #7
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Talking

    Originally posted by annie_dragon
    That helps quite a bit. You'd think that this kind of an example would be in the assistance books rather than the one field for name examples where you can't even sort by last name (like the list that I started out with).

    I'm assuming that I should be able to run a similar query to bunch the names together once I've grouped the people who are at one address so that I end up with "John and Betty Blue" instead of "John Blue and Betty Blue" for the addressee.

    Thanks for help as I learn how to muddle my way through this.

    Annie
    This is important to note, even though the Access is/can act as a relational database, a large part of the documentation was written so that you have to figure out how to do the relational work yourself.

    As far as the John and Betty type query you may have to put some work into how you build indexes and referential columns, but it should be doable.

    I would highly suggest some non-M$ books on Access development.
    Do a search for access development at Amazon and take a look. I came back with 160 hits. My company coughed for a couple of Access and Office VBA/Development books and it increased our competence many fold.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •