Field displays depending on another table field value

[David - Australia]

G'day from Australia,

I'm hoping some bright spark may be able to help me with this one. I'm
sure that it can be done, I've just hit a wall with it. So I'm opening
it up.

I'm storing student competencies for a given number of performance
criteria (which are stored in a separate reference table).
Students are rated competent or not (yes/no field) on up to 5
assessments - Task, Observation, Project, Practical Test, Exam for
each performance criteria.

But for any given performance criteria, an assessment may not be used
to assess competency so I don't want it to display on the data entry
form (with two subforms - student and performance criteria)

Example:
Competencies table:
StudentID(PK/FK), PerformanceCriteriaID(PK/FK), Task (Yes/No), Project
(Yes/No), Obs (Yes/No), PracTest (Yes/No), Exam (Yes/No)

Sample Data in the table:
StudentID, PerformanceCriteriaID, Task, Project, Obs, PracTest, Exam
5432, 16, -1, -1, -1, 0, -1
5432, 23, -1, -1, -1, -1, 0

In this example, the field PracTest for student 5432 contains a zero
(not because the student isn't competent on Performance Criteria 16,
but because a PracTest is not used to assess this competency). I
don't want a user to accidentally tick a checkbox for this performance
criteria for a PracTest if a PracTest is not used to assess the
Performance Criteria.

But student 5432 could have a zero recorded for the Exam assessment
on Performance Criteria 23 because they do not have competency and the
staff member has not ticked the checkbox (which is displayed in this
instance)
What I considered was a separate table that determined (using yes/no
fields) whether an assessment was to be displayed or not (for that
PerformnceCriteriaID), then somehow query this table when the record
was being displayed on the data entry form (which is displaying
continuous forms) and use the visible property for the field control.

It would be cool if there was a datatype that handled three values -
yes/no/not used.

Does anyone have any bright ideas that I could progress with?
I would be most grateful for some inkling on how to proceed.

I have a database if anyone needs a copy to see where I'm at so far.
Probably not wise for it to be posted with this message. So I assume
if anyone needs it they will tell me how to send it to them.

Cheers
David :-)

Brisbane
Australia

[Allen Browne]

Hi David.

Your conclusion suggests that you need a field that can contain 3 states:
Yes/No/Not used. You can do that in Access is you use a Number field (size
Integer), and interface it with a check box. Set the Triple State property
of the check box to Yes, and it now has 3 states: Yes (-1), No (0), and Not
Applicable (Null.) If you are using Windows XP, there may not be any visual
distinction between No and Null unless you uncheck the option under:
Tools | Options | Forms/Reports | Use Themed

But there's a more basic issue here: the data structure. One performance
criterion can have multiple assessments. This should be handled as 2 tables
with a one-to-many relation instead of as multiple check boxes in the one
table. (In typical competency-based training, an assessment may assess
multiple elements/outcomes, each with multiple criteria, so the structure
may actually be a bit more involve than just that.) Once you get the
structure right, you can then teach the database which actual
critiera/assessments are matched, and therefore which ones apply when an
actual assessment is submitted.

Once the data structure is correct, you can then worry about how to
interface it. That might end up being a form with a subform to handle the
related records.

If you want to continue with the approach you already have instead, the
triple state check box will allow you to use Null for N/A. Unlike zeros, the
nulls don't affect averages. And you could run some checks in
Form_BeforeUpdate to ensure that there's no 0 or -1 in a field that should
be null. While tha'ts possible, it is not structurally correct, and so it is
harder to write, maintain, and query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David - Australia" wrote in message
news:rg********************************@4ax.com...

G'day from Australia,

I'm hoping some bright spark may be able to help me with this one. I'm
sure that it can be done, I've just hit a wall with it. So I'm opening
it up.

I'm storing student competencies for a given number of performance
criteria (which are stored in a separate reference table).
Students are rated competent or not (yes/no field) on up to 5
assessments - Task, Observation, Project, Practical Test, Exam for
each performance criteria.

But for any given performance criteria, an assessment may not be used
to assess competency so I don't want it to display on the data entry
form (with two subforms - student and performance criteria)

Example:
Competencies table:
StudentID(PK/FK), PerformanceCriteriaID(PK/FK), Task (Yes/No), Project
(Yes/No), Obs (Yes/No), PracTest (Yes/No), Exam (Yes/No)

Sample Data in the table:
StudentID, PerformanceCriteriaID, Task, Project, Obs, PracTest, Exam
5432, 16, -1, -1, -1, 0, -1
5432, 23, -1, -1, -1, -1, 0

In this example, the field PracTest for student 5432 contains a zero
(not because the student isn't competent on Performance Criteria 16,
but because a PracTest is not used to assess this competency). I
don't want a user to accidentally tick a checkbox for this performance
criteria for a PracTest if a PracTest is not used to assess the
Performance Criteria.

But student 5432 could have a zero recorded for the Exam assessment
on Performance Criteria 23 because they do not have competency and the
staff member has not ticked the checkbox (which is displayed in this
instance)
What I considered was a separate table that determined (using yes/no
fields) whether an assessment was to be displayed or not (for that
PerformnceCriteriaID), then somehow query this table when the record
was being displayed on the data entry form (which is displaying
continuous forms) and use the visible property for the field control.

It would be cool if there was a datatype that handled three values -
yes/no/not used.

Does anyone have any bright ideas that I could progress with?
I would be most grateful for some inkling on how to proceed.

I have a database if anyone needs a copy to see where I'm at so far.
Probably not wise for it to be posted with this message. So I assume
if anyone needs it they will tell me how to send it to them.

Cheers
David :-)

Brisbane
Australia

[David - Australia]

Wow that was fast Allen,

I see what you mean about my structure. I might look more closely at
your suggestion since it appears to be more easily updateable, and if
this works for my subject, others may want to use it. We're currrently
using spreadsheets which are a severe nuisance.

For ease of data entry I wanted checkboxes to be used by staff, but I
think that's still achievable using your suggestion.

Thank you for you help.

David
Brisbane

On Sun, 11 Feb 2007 19:49:02 +0900, "Allen Browne"
<Al*********@SeeSig.Invalidwrote:

>Hi David.

Your conclusion suggests that you need a field that can contain 3 states:
Yes/No/Not used. You can do that in Access is you use a Number field (size
Integer), and interface it with a check box. Set the Triple State property
of the check box to Yes, and it now has 3 states: Yes (-1), No (0), and Not
Applicable (Null.) If you are using Windows XP, there may not be any visual
distinction between No and Null unless you uncheck the option under:
Tools | Options | Forms/Reports | Use Themed

But there's a more basic issue here: the data structure. One performance
criterion can have multiple assessments. This should be handled as 2 tables
with a one-to-many relation instead of as multiple check boxes in the one
table. (In typical competency-based training, an assessment may assess
multiple elements/outcomes, each with multiple criteria, so the structure
may actually be a bit more involve than just that.) Once you get the
structure right, you can then teach the database which actual
critiera/assessments are matched, and therefore which ones apply when an
actual assessment is submitted.

Once the data structure is correct, you can then worry about how to
interface it. That might end up being a form with a subform to handle the
related records.

If you want to continue with the approach you already have instead, the
triple state check box will allow you to use Null for N/A. Unlike zeros, the
nulls don't affect averages. And you could run some checks in
Form_BeforeUpdate to ensure that there's no 0 or -1 in a field that should
be null. While tha'ts possible, it is not structurally correct, and so it is
harder to write, maintain, and query.