Check boxes.

[radink]

Im creating a contact database for our marketing person. Most of it I
can handle, but I have one issue. A contact can belong to one or more
organizations in the architectural world, ie, AIA, AAMA, etc...

What I want to do is have a check box for each of these orgs. Then our
marketing person can check them off for each one they belong to. I
have made a table for the list of orgs. Is this something easy to do,
or is there a better way?

Thanks!

Mark

 

[Golfinray]

In your table, go to design view and add the fields for each organization.
Make them data type yes/no. If your form is based on the table, simply bring
those into your form as checkboxes and label them, AIA, etc. If your form is
based off a query made from the table, same thing. Your field list will
contain those fields and you can drop them on your form. They will be stored
in your table as 0's and -1's. Yes is stored as a -1, no as a 0.

 

[Arvin Meyer [MVP]]

In a properly designed database, you would use a subtable, which is a
many-side table to your contacts table. In Access 2007, you can use a
multi-value field, but this is extremely poor design and not usable by any
other DBMS.

 

[Klatuu]

Do not use check boxes and multiple fields in the contact table for this.
To do so is creating a spreadsheet in Access. Excel would be a better tool
if you do it that way.
What you need is an Organization table.
The layout should be:

tblOrgs
OrgID - Autonumber PK
OrgShortName - Text. Used for the Acronym every organaization has
OrgFullName - Text. The full name of the organization.
and any other info you need specific to the organization

Since a contact can belong to one or more orgs, you need a ContactOrg table
to resolve the many to many between contacts and orgs. All it really needs
is a field for the contact's primary key and the organization's primary key:

tblContactOrg
ContactID - Foreign Key to Contact table
OrdID - Foreign key to Org table

The combination of the two should be the primary key.

 

[Larry Linson]

You have just denormalized the table design. For shame. That table will be
difficult to use when its data is needed in the future.

Larry