Trying to do a Report on Amount Owed by different Pay Grades for different Tasks. (1 Viewer)

Hello everyone, I've been tearing my hair out over this report.

The main table I use is a Timesheet, with columns like 'Start Date' and 'End Date'. I calculate the 'Duration' using DateDiff(). There are also Charge Rates, linked to the Grade of the person recording the time. They can be either Partner/Office Holder, Manager or Assistant/Support. Finally, there is SIP Field, which breaks down the kinds of tasks recorded, e.g. dealing with creditors, trading, realisation of assets, etc.

The Report needs to present the following things for a particular Case over a particular Date Range:

The Total duration of time spent in each SIP Field for the Grade of Partners/Office Holders.

The Total duration of time spent in each SIP Field for the Grade of Managers.

The Total duration of time spent in each SIP Field for the Grade of Assistant/Support.

The Average Charge Rate by the Grades for each SIP Field.

The Total TimeCost (Charge Rate * Duration) for each SIP Field.

This needs to all be generated by a simple Case and Date Range Form, with a 'Create Report' button.


I've experienced a range of problems, including extortionately inflated totals and the report wizard refusing to combine multiple, related Queries.

Can you demonstrate your issue with data? Provide 2 sets:

A. Starting sample data from your tables. Include table/field names and enough data to cover all cases.

B. Expected results of A. Show what you hope to end up with when you feed in the data from A.

plog said:

Can you demonstrate your issue with data? Provide 2 sets:

A. Starting sample data from your tables. Include table/field names and enough data to cover all cases.

B. Expected results of A. Show what you hope to end up with when you feed in the data from A.

Click to expand...

Thankyou, for getting back to me.

A. I have attached some sample data from the Table 'Timesheet' View attachment SampleData.xlsx .

I have also attached the Tables 'Task Reference' View attachment Task Reference.xlsx and 'Charge Table' View attachment Charge Table.xlsx .

'Fee Earner' joins Timesheet to Charge Table, allowing us to know the 'Charge Rate' and 'Grade' for each record. 'Task' joins Timesheet to Task Reference, allowing us to know the SIP Field for each 'Task'.

B. On the Report, I would like the data to look like this Model Report View attachment Model Report.xlsx I have attached. As you can see, the data is all grouped on the left by SIP Field, with this Model having tasks in Fields 1, 3 and 4.

The next three columns are Durations, corresponding to Grade, displaying total time spent doing tasks in each SIP Field by Fee Earners who are Office Holders/Partners, then Managers, and then in Assistant/Support roles.

Next, mean average Charge Rate is shown calculated for each SIP Field.

Finally, the Time Cost is calculated from the Sum of the three Durations * Average Charge Rate.

Thanks for uploading the Excel data.
However, it would make it easier for us to assist you if you upload the relevant sections of your database.

Thanks

Thanks for the sample data. Now I need some explanation. Let's focus on the first row of ModelReport.xls:

SIPField, OfficeHolder/Partner, Manager, Assistant/Support, ChargeRate, TimeCost
1 - Administration & Planning, 180, 90, 230, 150, 75000

1. How do you get the 180 under OfficeHolder/Partner? Using the data in TimeSheet I get 675 records with a duration total of 9667 minutes (~161 hours). Where's 180 coming from?

2. Where does the 150 for Charge Rate come from? Over those same 675 records I get an average of 414 for the Charge rate.