Pass variables through different levels

[Joost de Vries]

Hey,

I've got a problem with a report I'm working on. I'm calculating some group
totals by using the 'running sum' property and they sum up perfectly. The
problem is that I would like to use the total value in some other part of the
report (to calculate another group total). Everytime I refer to the field
containing the summed value I only get the value from the first record.

Schematic:

Grouping level 1: Employee
Numer of items checked
field: total_items =[sum_items_checked]
Number of errors found
field: total_errors =[sum_errors]
Total quality
field: total_quality =1-([sum_errors]/[sum_items_checked])

Grouping level 2: Case
Number of items checked
field: case_items =[numOfItems]
Number of errors
field: case_errors =[numOfErrors]
Case total items
field: sum_items_checked =[numOfItems] (Running Sum = "groups")
Case total errors
field: sum_errors =[numOfErrors] (Running Sum = "groups")

numOfItems is a value stored in the DB, numOfErrors is a value based on
another field with running sum in the case detail section.

I hope you're able to help me figure this one out because I think I've tried
about everyting I know..

 

[Allen Browne]

Yes, this is a timing problem: the value that Access has accumulated at the
time when it calls this total is not the final sum. You will therefore need
to take a different approach.

Would it be feasible to get the total via DSum() or DLookup() or possibly
even with a subquery in the RecordSource of the report?

 

[Joost de Vries]

Allen,

I've actually used one of you tips from your website to solve this problem.
By using the print event to sum all groups everything works perfectly. I
wanted to limit the use of DSum, Dcount or DLookup because it just doesn't
'feel' right to use DB lookup functions within a SQL-generated report.

Here's what I've done:

1st level totals (for case group, based on detail):
Using Duane Hookom's tip for the 'running sum' property.
2nd level totals (for employee group, based on case totals):
In the VB declarations:
dim total_checked As Integer
dim total_errors As Integer
In VB: Employee group header, On_Print:
total_checked = 0
total_errors = 0
In VB: Case group footer, On_Print:
total_checked = total_checked + Me.sum_checked
total_errors = total_errors+ Me.sum_errors
In VB: Employee group footer, On_Print:
Me.field_total_checked = total_checked
Me.field_total_errors = total_errors

Thanks for helping out. Your site is an extremely useful reference.

----------------
Joost de Vries
R&D Student

Yes, this is a timing problem: the value that Access has accumulated at the
time when it calls this total is not the final sum. You will therefore need
to take a different approach.

Would it be feasible to get the total via DSum() or DLookup() or possibly
even with a subquery in the RecordSource of the report?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hey,

I've got a problem with a report I'm working on. I'm calculating some
group
totals by using the 'running sum' property and they sum up perfectly. The
problem is that I would like to use the total value in some other part of
the
report (to calculate another group total). Everytime I refer to the field
containing the summed value I only get the value from the first record.

Schematic:

Grouping level 1: Employee
Numer of items checked
field: total_items =[sum_items_checked]
Number of errors found
field: total_errors =[sum_errors]
Total quality
field: total_quality =1-([sum_errors]/[sum_items_checked])

Grouping level 2: Case
Number of items checked
field: case_items =[numOfItems]
Number of errors
field: case_errors =[numOfErrors]
Case total items
field: sum_items_checked =[numOfItems] (Running Sum = "groups")
Case total errors
field: sum_errors =[numOfErrors] (Running Sum = "groups")

numOfItems is a value stored in the DB, numOfErrors is a value based on
another field with running sum in the case detail section.

I hope you're able to help me figure this one out because I think I've
tried
about everyting I know..