E
Emelina Bumsquash
I have a table that has combo boxes to select activities carried out during a
session. Each group of activities has a maximum number that be carried out
per visit i.e. you can have a maxmimum of 3 assessments carried out as well
as a maximum of 2 interventions and 4 written tests etc. etc.
There are MANY of these groups of activities. Each participant has records
for up to 16 weeks so to get around the field number limitations in access, a
new row is created for each ‘week’ of participation to be able to accommodate
the number of activity fields that must be available for each session.
One of the activities i.e. assessments described above, has 8 possible
selections. So, there is a wide selection of ‘type of assessment’(coded 1-8)
and a maximum of 3 ‘assessments’ that can be carried out in one session.
The data is stored in the table as follows:
Study No. wk no. Mon_Assess1 Mon_Assess2 Mon_Assess3, Tues Assess1 etc
34 1 2 3 7
6
34 2 4
1
34 3 1 5
2
34 4
5
These values are entered into the table via a user friendly form with combo
boxes. The list of selections in the combo boxes show the ‘types of
assessment’ i.e. 1-8. and there are 3 combo boxes per visit to reflect that
there can be up to 3 ‘types of assessments’ in any given session.
I need to tally up the number of a given assessment code used across the
weeks. So, for example I want to tally up the number of code ‘3’ assessments
carried out. However, you could potentially have a 3 in any of the assessment
fields over all of the 16 weeks of data entry.
I know it’s not usually recommended to store values that are calculated in
an underlying table but in this case, it’s really difficult to tie up
everything unless we get a tally, since the given assessment code could be
anywhere!
Is there a way to run a calculation which does the following:
Identifies any instance of the value ‘3’ across a number of fields i.e.
Mon_Assess1, Mon_Assess2, Mon_Assess3, Tues_Assess1, Tues_Assess2,
Tues_Assess3 and so on.
Tallies up the number of occurrences of this number and returns it in a
given field i.e. ‘Tally of Assessment Code 3’ but ONLY tallies those with the
same study number. i.e. there will be 16 rows of data for a given study
number, given the 16 weeks of participation and each of these rows can
contain ‘3’s in the assessment fields across the week. As I say, the result
of having multiple rows per patient is not good practice but was the only way
to get around the limitations in Access.
Is it possible to do this tally calculation? If anyone can help I’d be most
appreciative. Feel free to advise on a better way to record the data in the
first place though! This method has been used because of the complexity i.e.
too many fields to have one row per patient, multiple potential codes to be
used and multiple ‘maximums’ of a given group of codes there could be during
any one visit.
session. Each group of activities has a maximum number that be carried out
per visit i.e. you can have a maxmimum of 3 assessments carried out as well
as a maximum of 2 interventions and 4 written tests etc. etc.
There are MANY of these groups of activities. Each participant has records
for up to 16 weeks so to get around the field number limitations in access, a
new row is created for each ‘week’ of participation to be able to accommodate
the number of activity fields that must be available for each session.
One of the activities i.e. assessments described above, has 8 possible
selections. So, there is a wide selection of ‘type of assessment’(coded 1-8)
and a maximum of 3 ‘assessments’ that can be carried out in one session.
The data is stored in the table as follows:
Study No. wk no. Mon_Assess1 Mon_Assess2 Mon_Assess3, Tues Assess1 etc
34 1 2 3 7
6
34 2 4
1
34 3 1 5
2
34 4
5
These values are entered into the table via a user friendly form with combo
boxes. The list of selections in the combo boxes show the ‘types of
assessment’ i.e. 1-8. and there are 3 combo boxes per visit to reflect that
there can be up to 3 ‘types of assessments’ in any given session.
I need to tally up the number of a given assessment code used across the
weeks. So, for example I want to tally up the number of code ‘3’ assessments
carried out. However, you could potentially have a 3 in any of the assessment
fields over all of the 16 weeks of data entry.
I know it’s not usually recommended to store values that are calculated in
an underlying table but in this case, it’s really difficult to tie up
everything unless we get a tally, since the given assessment code could be
anywhere!
Is there a way to run a calculation which does the following:
Identifies any instance of the value ‘3’ across a number of fields i.e.
Mon_Assess1, Mon_Assess2, Mon_Assess3, Tues_Assess1, Tues_Assess2,
Tues_Assess3 and so on.
Tallies up the number of occurrences of this number and returns it in a
given field i.e. ‘Tally of Assessment Code 3’ but ONLY tallies those with the
same study number. i.e. there will be 16 rows of data for a given study
number, given the 16 weeks of participation and each of these rows can
contain ‘3’s in the assessment fields across the week. As I say, the result
of having multiple rows per patient is not good practice but was the only way
to get around the limitations in Access.
Is it possible to do this tally calculation? If anyone can help I’d be most
appreciative. Feel free to advise on a better way to record the data in the
first place though! This method has been used because of the complexity i.e.
too many fields to have one row per patient, multiple potential codes to be
used and multiple ‘maximums’ of a given group of codes there could be during
any one visit.