selecting data coded with yes/no checkboxes

[Linty Fresh]

Hi, all

OK, so here's the situation. I'm doing a database for a company. We've got specific departments that can do specific activities. The activities are the same across all departments (i.e.--The activities "Email Notification", "Fund Drives", "Car Washes" apply to the Marketing Department, Accounting Department, and Human Resources Department.).

I want to list each department and the activities the department is participating in.

I've got a main form with the name of the department and a subform with the activities. The datatype of each activity is a yes/no, and I have it formatted as a checkbox on the subform. The tables are linked by a primary key/foreign key 1-to-many relationship.

It looks kind of like this.

Department Name: Accounting
__________________________
Activities:

_ Email Notification

x Fund Drives

_ Car Wash

__________________________

where _ indicates an unchecked checkbox and x indicates a checked checkbox.

My problem is this: I need to create a query that chooses each department along with the selected values while excluding the unselected values so that I can base a report on this. The report might look like:

Department Name: Accounting
Activities:
Fund Drives.

I'm trying to create a query which retrieves these selected values. Am I barking up the wrong tree with yes/no checkboxes or am I just not getting the SQL or query wizard right?

Thanks so much for your help. My department thanks you too!

 

[KevinM]

You don't need Yes/No fields.

Basically what you have is Many Depts to Many Activities.

Your tables should read something like this....

Table: Depts
Fields: DeptID(pk), DeptName

Table:Activities
Fields: ActivityID(pk), ActivityDescription

Table: DeptsAndActivities
Fields: DeptID(pk), ActivityID(pk)

HTH

 

[Linty Fresh]

Yeesh, thanks Kevin (Sometimes I'm such a user!).

I have another question, then. Is there an easy way to rig data entry so that the user can avoid entering the wrong information, or even avoid typing altogether? I'm thinking of a setup which would allow the user to enter "Car Wash" instead of "Carwash". I'd use a combo box, but there are up to 20 different activities which the department can be involved in. Would a scheme like two listboxes with add and remove keys be the best way to handle this?

Thanks.

 

[KevinM]

A combobox on a form would be ideal.
Just set it to Limit To List = Yes in it's properties and you can't go wrong.

Use the combo wizard to create it. Include the ID field in the first column and have it set to hidden. The wizard will default to this.

Note on the last table.

You only add a deptid and activityID if THAT dept is involved with THAT activity. So if a particular dept is involved with 20 activities then you will have 20 records consisting of that dept's ID and each activityID.

Before you do anything create the relationships between the tables and enforce ref integrity with cascading deletes & updates ticked.