Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Location
    India
    Posts
    16

    Exclamation Unanswered: Help in Database Design - MS Access

    Hi folks,
    This may be simple for most of you.
    I need to store the phone numbers of each customer. A customer can have more than one phone, as well as diff types of phone (mobile.Landline Residence, Landline office). Can someone show me how best to capture this info in the tables.
    There is no restriction on the number of phones a person can have in each category.

    There is an additional problem. I need to display the info collected in a form without wasting much space in the form.
    Thanks in advance

    Yes we can go for a drink if the problem is solved...

    Database: MS Access 2003

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so lets think the problem through
    a customer may have more than one phone number, they may have more than one phone number of a specific type
    there is no restriction on the number of phones in each category.

    lets look at that in detail (ignoring the presentation issue for now)

    so effectively you have a one to many relationship between a customer and phone numbers, arguably you also have a one to many relationship between phone types and phone numbers

    of course you could just stuff a series of phone numbers into a text box, seperated by a vbcrlf, vbnewline constant or IIRC good old chr$(10) & chr$(13). but thats an ugly way of doing it. you loose control of the data integrity, its harder to enforce data validation and data typing (ie how would you know what type of phone it is.

    so Id sugest you have a sub table which identifies what customer, optionally) what phone type and the phone number

    if you do identify the phone type then have another table which identifies the phone type (say L: Landline; S: Satellite; M: Mobile etc......)

    in presentation terms consider using a subform
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2007
    Location
    India
    Posts
    16
    Thanks for the lightning reply
    something like this
    CustPhones
    custID
    phoneNo
    phoneType

    Database: MS Access 2003

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, but more like:

    CustPhones
    CustID
    PhoneType
    PhoneNo

    PhoneTypes
    PhoneType (PKF)
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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