2 subtotals from one report

[Randy]

Access 2000. I have an employee database with employee hours worked, miles
driven, expenses,date etc. I need a report for payroll reporting purposes.
The employee pay period is every two weeks. Sun. of the first week to Sat.
of the next week (14 days). I can very easily create a report showing each
day worked and a Sum of hours, expenses etc. using a between statement in
the query showing the correct date range of the pay period. I have been
asked to create a report that will have a subtotal for the first week, let
say 42.1 hours, and a subtotal for the second week lets say 40.0 hours and a
grand total of both weeks.(82.1) This is so overtime can be properly
accounted for. My table has a date field for the day worked. Any ideas.
Thanks for any help.

 

[Pat Hartman \(MVP\)]

If the detail records go into the report, you can add a grouping option on
date and choose week as the period. You can then add a footer with a sum
for the week. If you have already summed the data in the query, you'll need
a separate report to get to the detail you need.

 

[Randy]

Thanks Pat, your suggestions is what I needed. It worked except the dates
in my report are no longer sorted in ascending order even though the sort
order is set to ascending order. The underlying query is also set to
ascending oder. The query does sort correctly. Do you have any solutions
to this? Thanks, Randy

 

[Pat Hartman \(MVP\)]

Sorting is handled by the report so remove any order by clauses in your
queries. There is no need to sort the recordset twice. Make sure the
sorting and grouping options are properly set.

If your dates are not in "logical" order, you may have formatted them in the
query. Formatting turns a date into a string and it will then be sorted as
strings are by evaluating the characters left to right. This will cause
anomalies like October (month 10) sorting before February (month 2) or if
you formatted to alpha names, April will be the first month of the year.