Thread: Contact Management Database Help
04-17-13, 11:56 #1Registered User
- Join Date
- Apr 2013
Unanswered: Contact Management Database Help
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:
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.
04-18-13, 09:22 #2Grumpy old man (training)
Provided Answers: 10
- Join Date
- Sep 2006
- Surrey, UK
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:
- 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.
Beers earned: 1