table/form design question

[default105]

I have a form with 10 check boxes that will be used by the users to select
which persons a task will be assigned to. I am having a problem laying out
the tables to follow 3NF. What I have is a table for the task orders, a
table for the task details and a table for the task crew. I have all set up
with primary and foreign key respectively. Right now I am using sql to
insert the crew name into the task crew table field [crewAssignment] when the
user clicks on the check box on the form. However this leaves a lack of
visual check when the form is reopened because it has no controlsource. I do
not know how to setup the form check box to look at multiple records to
populate the checkboxes appropriately that are bound to the primary key of
the task orders table. I have it set up this way so I did not have ten
fields with only 2 to 3 with values in then and null value in the others.
Any input would be greatly appreciated. Thank you very much in advance.

 

[Jeff Boyce]

A serious disadvantage to setting up a form with "one checkbox per
AssignedPerson" is that the number (and selection) of persons that might be
assigned can change over time. This approach would require intensive
maintenance each time there was such a change.

An alternate approach to showing "which folks are associated with this
record/task/..." would be to use paired listboxes. If you want an example
of how this looks and works, use the query wizard to create a new query. In
the process, you will be presented with a list(box) of possible fields, and
a list(box) of selected fields. You use the "arrows" to move fields
back/forth between the listboxes.

In your situation, your form would contain task-level information, and the
paired listboxes would indicate which person(s) have been assigned. The
simplicity of this design is that you can, by simply adding another person
to the person table (which feeds your "available persons" listbox), have the
new person show up as available to assign. No maintenance required!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[default105]

Thanks Jeff, sometimes the simpliest solution is the one that is overlooked.
I was definately making the solution harder than it needed to be. Sometimes
the K.I.S.S. (keep it simple stupid) method slaps us all in the face. Thanks
for your prompt reply.

A serious disadvantage to setting up a form with "one checkbox per
AssignedPerson" is that the number (and selection) of persons that might be
assigned can change over time. This approach would require intensive
maintenance each time there was such a change.

An alternate approach to showing "which folks are associated with this
record/task/..." would be to use paired listboxes. If you want an example
of how this looks and works, use the query wizard to create a new query. In
the process, you will be presented with a list(box) of possible fields, and
a list(box) of selected fields. You use the "arrows" to move fields
back/forth between the listboxes.

In your situation, your form would contain task-level information, and the
paired listboxes would indicate which person(s) have been assigned. The
simplicity of this design is that you can, by simply adding another person
to the person table (which feeds your "available persons" listbox), have the
new person show up as available to assign. No maintenance required!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with 10 check boxes that will be used by the users to select
which persons a task will be assigned to. I am having a problem laying
out
the tables to follow 3NF. What I have is a table for the task orders, a
table for the task details and a table for the task crew. I have all set
up
with primary and foreign key respectively. Right now I am using sql to
insert the crew name into the task crew table field [crewAssignment] when
the
user clicks on the check box on the form. However this leaves a lack of
visual check when the form is reopened because it has no controlsource. I
do
not know how to setup the form check box to look at multiple records to
populate the checkboxes appropriately that are bound to the primary key of
the task orders table. I have it set up this way so I did not have ten
fields with only 2 to 3 with values in then and null value in the others.
Any input would be greatly appreciated. Thank you very much in advance.

 

[Jeff Boyce]

I suspect that the paired listbox approach isn't particularly "simple", but
it's "relatively simple", given the alternatives!

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Thanks Jeff, sometimes the simpliest solution is the one that is overlooked.
I was definately making the solution harder than it needed to be. Sometimes
the K.I.S.S. (keep it simple stupid) method slaps us all in the face. Thanks
for your prompt reply.

A serious disadvantage to setting up a form with "one checkbox per
AssignedPerson" is that the number (and selection) of persons that might be
assigned can change over time. This approach would require intensive
maintenance each time there was such a change.

An alternate approach to showing "which folks are associated with this
record/task/..." would be to use paired listboxes. If you want an example
of how this looks and works, use the query wizard to create a new query. In
the process, you will be presented with a list(box) of possible fields, and
a list(box) of selected fields. You use the "arrows" to move fields
back/forth between the listboxes.

In your situation, your form would contain task-level information, and the
paired listboxes would indicate which person(s) have been assigned. The
simplicity of this design is that you can, by simply adding another person
to the person table (which feeds your "available persons" listbox), have the
new person show up as available to assign. No maintenance required!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a form with 10 check boxes that will be used by the users to select
which persons a task will be assigned to. I am having a problem laying
out
the tables to follow 3NF. What I have is a table for the task orders, a
table for the task details and a table for the task crew. I have all set
up
with primary and foreign key respectively. Right now I am using sql to
insert the crew name into the task crew table field [crewAssignment] when
the
user clicks on the check box on the form. However this leaves a lack of
visual check when the form is reopened because it has no controlsource. I
do
not know how to setup the form check box to look at multiple records to
populate the checkboxes appropriately that are bound to the primary key of
the task orders table. I have it set up this way so I did not have ten
fields with only 2 to 3 with values in then and null value in the others.
Any input would be greatly appreciated. Thank you very much in

advance.