Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    1

    Unanswered: Contact Management Database Help

    Hi all,

    I am well versed in Excel/VBA design, but I'm relatively inexperienced with Access, so I'm hoping someone here can point me in the right direction.

    I am working on a project to create a contact management database to house several hundred clients to store some basic info (name, company, email address, etc). I started with the Microsoft template, as it has most of the functionality I need:
    Desktop contacts - Templates - Office.com

    The biggest addition I need to make is to allow us to track what subscriptions each email address has - one email address can have several subscriptions. I've been messing around, and modified the form to allow checkboxes that store data in the Contacts table, like so:
    Form modification
    Contacts table

    But now the Contact List page lists multiple records for each subscription I add, and I'm getting an error whenever I modify an existing subscription:
    Two subscriptions, two rows
    Error: The field is too small to accept the amount of data

    Any advice? Did I even set this up correctly conceptually? Any help is appreciated.

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    The problem is that you are storing multiple values in one field. This is a big no-no, as it makes it difficult to use such fields in queries to find or amend data (as you have found).
    For this kind of relationship, you require n+1 tables, where n = number of tables holding records to be linked. In this example, you need three tables:
    • Contacts
    • Subscriptions
    • The intersection between them

    If you try a search (on this forum or on Google) for "intersection table", you'll find all you ever wanted to know (and more) on the subject.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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