Creating a Contact Database

[EricTheRed]

Hello all!

Thanks for taking the time to read this post, and for any help you may be able to offer.

A bit of background to begin: I'm a student working on a volunteer basis for a community organization. This organization deals with hundreds (if not thousands!) of contacts, however the information is currently spread across dozens of personal address books. My job is to create an Access database to store all of this information electronically.

While this initially struck me as a relatively simple task, it has turned out to be far more complicated than I orginally envisioned. All compounded by the fact that I have no experience with Access, exluding what I've tried to teach myself over the past few days. All I'm looking for is some advice, and perhaps pointers in the right direction; I'm more than willing to fill in the gaps myself. Without further ado, I'll just explain what I have so far:

Each person has basic information asociated with them:

• contact ID (autonumber)
• first name
• last name
• title of courtesy
• four "optional" text fields to be used at the discretion of the organization, with possible values to be drawn from another table
• two sets of contact information (primary and secondary)

Both sets of contact information (14 fields of data) are currently stored in the same record with everything else, and are differentiated by a postfixed "P" or "S". Should these be stored in a different table, linked by the contact ID? Each set of contact info also has a type [one of: home, work, other] that is stored as a number.

I would like to also have a series of notes associated with each person, a note being simply a text comment and the date it was added to the record. How should I store this? I'm thinking another table, with simply fields for ContactID, Date, and Comment.

Also associated with each person are "contact reminders": small notes to remind staff to contact the person. Each contact reminder consists of four elements: the person doing the contacting (one of the authorized people the list defined in another table), the date to contact, the subject, and whether an email reminder should be sent to staff. I was thinking of a similar table set up for the notes. Is this a good idea?

And (please bear with me!) I have one last question. A separate table will also be used to store company contact information. Is it possible to use another set of autonumbers for company IDs, and then store associated "notes" in one single notes table, or would the numbers conflict with the personal contacts?

Wow. That was rather long-winded. Thank you immensely for anyone who read the whole thing! And thanks in advance for any help/comments/advice you may be able to offer!

 

[cdacey]

Eric,

It doesn't seem too bad.

Set up your base tables first.

Set up a primary contact table with and Contact# field (Usually auto number) with other contact info fields. The you can set up other related tables with other contact info by simplying adding the Contact# field (Use the lookup option under field type and select the Contact # in the base contact table) This will automaticaly link the two tables by the Contact # and make them " One record". Repeat the proces with any other tables you think are needed and include a similar Contact# filed in each table joined to the original.

In regard to you reminder table, you would first need to set up a staff info table with names of employees who need to be reminded. Then set up a reminder table and create a [staffname] field that looks up the staff name field in the staff table. Add the fields needed for the reminder info and add a [reminderdate] field that is the date you want to be reminded on. You will then need to create a query from the reminder table which has a criteria in the [reminderdate] field of <=Date(). In this way the query will only show records where the [reminderdate] is <= to todays date. You could copy this query and paste to create more specific queres for each staff member (add a staffmembers name under the criteria in the staffname filed). You could then create forms from each query to allow each staff member to view their individual reminder list.

Anyway, you get the idea. Since your new to access, start with your base tables, then build your queries, then you can build forms for the end-user. I like using the design view for creating tables, but use the wizards for queries and forms.

Good luck.

 

[Autoeng]

For reference there is a contact database wizard packaged with Access. Select Start (on the desktop), New Office Document, Database and Contact Management.

 

[EricTheRed]

Thank you for the prompt and helpful replies. It's certainly given me a place to start, which is more than I had several hours earlier. Though I am certain more questions are forthcoming as I piece this together.

Thanks again!