Multiple Checkbox Values Recorded in 1 Field

B

Bfunk

I am a semi-frequent MS Access user that sticks to the basics, however
I need to find out how I can store multiple checkbox values on a form
into 1 field.

Example - I would like to record a generic "Scope of Work" for
construction projects by selecting from the following values(these are
not the only values)(Projects may include some of the values on one
particular project, but may be different on other projects; Not all
projects are the same):

Demolition (Check); Asbestos Abatement (Check); New VCT Flooring
(Check)

Let's say -

Project 1 has Demolition & New VCT Flooring
Project 2 has Asbestos Abatement Only

Can this be done?
 
K

KARL DEWEY

how I can store multiple checkbox values on a form into 1 field.
You can not.

Each checkbox will be associated with a field. If you were selecting only
one item then you could use an Option Group on a form bound to one field.

Rather than multiple checkboxes and an equal number of fields you can create
a record for each selection. You would have your job table and then
one-to-many relation to your project parts. This would use a form for
project and subform for the parts. The subform in datasheet view have a
listbox to select the part type.
 
J

John Vinson

I am a semi-frequent MS Access user that sticks to the basics, however
I need to find out how I can store multiple checkbox values on a form
into 1 field.

You can't, you shouldn't, and you don't need to.
Example - I would like to record a generic "Scope of Work" for
construction projects by selecting from the following values(these are
not the only values)(Projects may include some of the values on one
particular project, but may be different on other projects; Not all
projects are the same):

Demolition (Check); Asbestos Abatement (Check); New VCT Flooring
(Check)

Let's say -

Project 1 has Demolition & New VCT Flooring
Project 2 has Asbestos Abatement Only

Can this be done?

Yes, by using Microsoft Access as it was designed - as a relational
database. You need THREE tables:

Projects
ProjectID
<fields about the project as a whole>

Steps
StepID
Step <e.g. Demolition, New VCT flooring, Replacement Roof, ...

ProjectScope
ProjectID <link to Projects>
StepID <link to Steps>
<information about this step for this project, e.g. date started,
date completed, special issues, comments, ...>


John W. Vinson[MVP]
 
B

Bfunk

Well...I have a project table including, but not limited to, the
Project ID, Description, Status, scope, etc...

The decription field is a short description such as Combine 2
classrooms to make 1 large.

I need the scope field to describe the work required to complete this
job in steps:

Example Classroom Renovation Scope:

1. Asbestos Abatement
2. Demo
3. Framing
4. Rough-Ins (Electrical, Mechanical, etc...)
5. Drywall
6. Flooring
7. Finishes

My problem is that all classroom projects might not have this same
scope. Should I be making a table with a Step ID, the Step (Asbestos)?

Not sure I understand what I should relate this to? Or if it will help
me? Please keep in mind that every project is different in one way or
another.

PLEASE HELP! Thanks.
 
B

Bfunk

Wait I think I may understand...

Jamie said:
The problem with this kind of data structure is that you are assuming
each 'step' is independent. Say roof replacement on a demolition job is
an absurdity. If both 'roof replacement' and 'demolition' were columns
of the same row (i.e. in the same table), writing the constraint would
be easy:

(roof_replacement IS NULL AND demolition IS NULL
OR roof_replacement IS NOT NULL AND demolition IS NOT NULL)

Would you know how to write the constraint to make this combination
illegal with your structure, let alone the OP?


Again, there could be business rules such as roof replacement cannot
commence before asbestos abatement has been completed. With these
values in the same columns of different rows in the same table, do you
think such constraints would be easy to define?

I guess I'm saying that, while you could be on the right lines, I think
OP has not provided enough detail to prescribe as something explicit
as, 'You need THREE tables'.

Jamie.

--
 
R

Rick Brandt

Bfunk said:
Well...I have a project table including, but not limited to, the
Project ID, Description, Status, scope, etc...

The decription field is a short description such as Combine 2
classrooms to make 1 large.

I need the scope field to describe the work required to complete this
job in steps:

Example Classroom Renovation Scope:

1. Asbestos Abatement
2. Demo
3. Framing
4. Rough-Ins (Electrical, Mechanical, etc...)
5. Drywall
6. Flooring
7. Finishes

My problem is that all classroom projects might not have this same
scope. Should I be making a table with a Step ID, the Step
(Asbestos)?

Not sure I understand what I should relate this to? Or if it will
help me? Please keep in mind that every project is different in one
way or another.

PLEASE HELP! Thanks.

Think of the classic one-to-many relationship, the Sales Order. Each order can
have one or more line-items associated with it so there are two related tables,
one for the Order Header and one for Order Line Items.

Such data is normally presented with a form/subform combination with the subform
being a continuous view or datasheet so that all the line items are displayed
when looking at a particular order.

You have the same relational requirements. Each project can have one or more
scope steps. Therefore you need two tables and a form/subform to properly deal
with the data.

Make sense?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top