Help with subtotal first record in report

[leslina76]

I’m so stumpted I’m having a breakdown.



Problem: I have an access report that is grouped by Estimator and for all the records grouped to that estimator, I have totals in the estimator footer. Most of these are straight totals =sum([billings]) etc. etc. but I have 1 total that show only the first record as their total = first([field name]). This is where the problem is. I need to add subtotal for each estimator in the report footer which works fine for the straight total, but not for the field as a first records. I need to subtotal the first record for each estimator. Example in field 2 . Please help

[rpeare]

So what you really want is to only show, on the subtotal line, the most recent dollar amount for that person. In terms of Access this is not the FIRST record, it is the dollar amount in for the record that has the MAXIMUM date.

I don't know the structure of your database, but I'm assuming this report is running off a query, what I would do is make a sub query that has the maximum date for each person for the period you're interested in, then link that to their dollar total for that maximum date, then link that back to the query running your report, then you can display the sum of that dollar total for the maximum date for that person in your report.