Relate Records with Checkboxes

[brentus]

I volunteered to help build a database to help the teachers at my
daughter's pre-school generate progress reports. It did not seem to be
a big deal at the time, but I am very stuck.

Requirements: Generate a multi-page progress report for each student
that shows the picture and description of the skills which that student
has mastered. The school sent me .jpg's of each skill (which also
contains the description). What I would like to do is have an Access
database in which the teachers can view a form that has the student
name and checkboxes for each skill. That way I can have a report that
will print the skill pictures for each student based on what the
teachers have checked off in the form.

I started by building 2 tables in Access:
1)tblStudents and
2)tblSkills which contains the fields SkillName, SkillCategory, and
SkillPicture (OLE Object Data Type)

But this is where I get stuck. I tend to think I relate the two
tables, so that I can 1)show all the tblSkill records as checkboxes and
2) have Access generate a related record if a skill is checked. But I
am not sure how to do this.

Also, I cannot help but wonder if there is an easier way to design this
project.

Thanks,
Brent

 

[John Vinson]

I started by building 2 tables in Access:
1)tblStudents and
2)tblSkills which contains the fields SkillName, SkillCategory, and
SkillPicture (OLE Object Data Type)

But this is where I get stuck. I tend to think I relate the two
tables, so that I can 1)show all the tblSkill records as checkboxes and
2) have Access generate a related record if a skill is checked. But I
am not sure how to do this.

You need a *third table*: tblStudentSkills, with fields for the
StudentID (not name, names aren't unique) and SkillName. For each
skill that a student attains you would add a new record to this table.

Rather than checkboxes - which would require a certain amount of VBA
code - you might want to use a Subform with a combo box allowing the
person entering data to select the skill by name.

Also, I cannot help but wonder if there is an easier way to design this
project.

I'd recommend against storing the pictures in an OLE Object field.
Access is HORRIBLY inefficient at handling picture data. It's usually
better to store the path and filename of the external image file, and
display it using code.

If you like the checkboxes approach anyway, post back - it can be done
but it's more work for you as the developer.

John W. Vinson[MVP]

 

[brentus]

Thanks John.

I would like to take a look at checkbox approach. Could you just give
me an overview of the process?

Brent