B
Bill Unger
I have a query that selects a number of records. Each record is a person
and each person belongs to a single category ( A - F ). Additionally, each
person has a score associated with him/her numbering 20,30,or 40. 20 is
considered the best, 30 not as good, 40 bad. Here's an example:
Name Score Category
Bob 20 A
Sue 30 B
Jane 20 C
Walter 40 D
Marge 30 B
I need to summarize this data in such a way that I can view the total
numbers of scores per category and the total number of people per category.
But here is the rub: if a person scores 20, he should be counted in the
totals for 30 and 40 within the same category. That helps to answer the
question "how many people scored at least 40?" ( remember the scoring
comparison is reversed - if they scored 20, then they have also scored at
least a 30 and at least a 40 ). This logic flows for those scoring 30 (
they would be counted in the 30 and 40 totals ) and for those scoring 40 (
though they would just be counted in the 40 totals ). The final totals row
is simply a count of the # of people in a category, which will NOT be sum of
the rows of that category. Here is an example of this:
Category A Category B
Category C Category D
People Scoring 20: 1 0
1 0
People Scoring 30: 1 2
1 0
People Scoring 40: 1 2
1 1
Total for Category 1 2
1 1
So.... I have created a form that contains a bunch of text fields and is
driven by VBA code. When the form opens, the VBA code opens the query,
retrieves each row, performs a bunch of If...Then...Else statements and
calculates each total, then displays the results in the text fields. This
works fine. The form looks like the chart above, but each number is in a
text field.
The problem is that the above solution is very limiting! I cannot easily
create graphs or reports b/c the calculated data is displayed only on a
form. I have investigated pivot charts and tables, but can't seem to find a
solution due to the rub outlined above.
If anyone has any ideas or suggestions, I would be very much appreciative!
and each person belongs to a single category ( A - F ). Additionally, each
person has a score associated with him/her numbering 20,30,or 40. 20 is
considered the best, 30 not as good, 40 bad. Here's an example:
Name Score Category
Bob 20 A
Sue 30 B
Jane 20 C
Walter 40 D
Marge 30 B
I need to summarize this data in such a way that I can view the total
numbers of scores per category and the total number of people per category.
But here is the rub: if a person scores 20, he should be counted in the
totals for 30 and 40 within the same category. That helps to answer the
question "how many people scored at least 40?" ( remember the scoring
comparison is reversed - if they scored 20, then they have also scored at
least a 30 and at least a 40 ). This logic flows for those scoring 30 (
they would be counted in the 30 and 40 totals ) and for those scoring 40 (
though they would just be counted in the 40 totals ). The final totals row
is simply a count of the # of people in a category, which will NOT be sum of
the rows of that category. Here is an example of this:
Category A Category B
Category C Category D
People Scoring 20: 1 0
1 0
People Scoring 30: 1 2
1 0
People Scoring 40: 1 2
1 1
Total for Category 1 2
1 1
So.... I have created a form that contains a bunch of text fields and is
driven by VBA code. When the form opens, the VBA code opens the query,
retrieves each row, performs a bunch of If...Then...Else statements and
calculates each total, then displays the results in the text fields. This
works fine. The form looks like the chart above, but each number is in a
text field.
The problem is that the above solution is very limiting! I cannot easily
create graphs or reports b/c the calculated data is displayed only on a
form. I have investigated pivot charts and tables, but can't seem to find a
solution due to the rub outlined above.
If anyone has any ideas or suggestions, I would be very much appreciative!