Newbie question: contacts database for address labels

[laurynosaurus]

Help! I've been asked to create a contacts database in Access and
what seemed like a simple assignment at first has turned into a
nightmare.

The company wants two separate sets of contacts: one for general
announcements, and one for holiday cards. There's a lot of overlap
between the contacts on each list, so I'm thinking I might want a
single table with yes/no fields for "general" and "holiday" -- but I
don't know for sure.

Also, each contact record is associated with one or more employees at
the company, and I need to be able to return matching records for each
employee, sometimes for a single employee, sometimes for all of them.
Right now I just have four fields for entering the initials of each
employee associated with the contact/record, but this seems like a
really inefficient way of doing things.

Should I be running queries to get the records I need? If so, is
there a good place to start learning about queries?

 

[Golfinray]

WoW! The learning curve on Access is quite steep. I think what you may want
to do is to build a set of good contact lists in Outlook, or even in Excel.
That would save you a lot of time trying to learn a complicated piece of
software. If you must proceed in Access, you would need to build tables,
probably with one table being contact type and one for contacts. You could
then query against those tables to pull the contacts you need. You do have a
nightmare on your hands. Good luck.

 

[John Spencer]

You are correct. You only want one list of contacts with all the
information about contacts.

You can go with the checkboxes for General and Holiday, but you can run into
a problem. What if someone (pointy-haired boss) decides they want another
group of contacts VIP, and another ExtremeCustomers, and another and another
....

So, you are better off adding another table called ContactGroups in which
you store the a reference to the primary key of the Contacts Table and a
group to which the contact is associated. Every time a contact gets
associated with another group, you add a record to the ContactGroups table.

You would do the same thing for Contacts and there associated employees. A
two field table with ContactID and EmployeeID as your fields.
One record for each Contact plus associated employee.

So right now you have 4 or 5 tables
Contacts
Employees
ContactGroups
ContactAssociates
and perhaps Groups (holding all the groups you want to use in contact
groups.

You build queries using the two (or more tables) to get the information you
want.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[laurynosaurus]

I've created a separate table called ContactGroups with the fields
GroupID, GroupName and GroupDescription, with GroupID (an AutoNumber)
as the primary key. Because there are only three groups a contact can
be in--General, Holiday, and both--I just have those three records in
ContactGroups. (I figure if pointy-haired boss wants to create more
contact groups, he can hire an actual professional to do it!) I now
have a GroupID field in my Contacts Table which corresponds to the
GroupID field in the ContactGroups table.

That's easy enough, I guess, but I'm stumped on sorting by Employee.
Each contact record has between 1 and 4 employees, so my Contacts
Table has four fields (which means some are left blank, which I
realize is not a good design). I can't retrieve all the records
matching Employee A this way, and in all my searching I can't figure
out the trick to doing this right.

Again, sorry for putting all these uneducated questions to you.

 

[John Spencer]

Basically you need another table with two fields

EmployeeContacts
ContactID
EmployeeID

Then for each combination of employee and contact you will add one record to
this table. Lets say that Contact Albert works with employees Jack, Bob,
and Janice

So you would have a record in Contacts
ContactID: 1
FirstName: Alfred

Records in employees
EmployeeID : FirstName
1: Jack
15: Bob
22: Janice

And in the EmployeeContacts table
ContactID : EmployeeID
1 : 1
1 : 15
1 : 22

Then if you want all contacts for Jack you would join Contacts to the
Employee contacts table and return records with employeeContacts.EmployeeID
= 1. Of course, much simpler would be to join all three tables in one
query and search for Jack.

Getting the data into the tables would probably be done with a Form based on
Contacts table that had a continuous subform based on the Employee contacts
table.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I've created a separate table called ContactGroups with the fields
GroupID, GroupName and GroupDescription, with GroupID (an AutoNumber)
as the primary key. Because there are only three groups a contact can
be in--General, Holiday, and both--I just have those three records in
ContactGroups. (I figure if pointy-haired boss wants to create more
contact groups, he can hire an actual professional to do it!) I now
have a GroupID field in my Contacts Table which corresponds to the
GroupID field in the ContactGroups table.

That's easy enough, I guess, but I'm stumped on sorting by Employee.
Each contact record has between 1 and 4 employees, so my Contacts
Table has four fields (which means some are left blank, which I
realize is not a good design). I can't retrieve all the records
matching Employee A this way, and in all my searching I can't figure
out the trick to doing this right.

Again, sorry for putting all these uneducated questions to you.

 

[laurynosaurus]

John--wow, thank you so much for taking the time to help me with
this. I followed the instructions in the post and I'm pretty sure
I've got everything I need now. I can't tell you how much I
appreciate it!