Sorting a group by a field other than the grouping field

[tylerg11]

I have a tricky scenario here that I am trying to handle with a report, but I'm really hitting a wall. I will try to explain what's going on as simply as I can.



The recordset of the report contains a list of orders, each with a company name, $ amount, and order date. The goal is to create a report that groups by company (showing only the company name once), with the total $ value, and the max of the order date. This is all simple enough, and best done on the query side, because Access does not handle the "grouping on one field, sorting on another at the same level" functionality. So at first I did this in the query, and on the report I just added a sort to the $ field.

Further, I need to be able to allow the user to specify a date range for the "ungrouped" recordset that contains each order date. I can't do this if I am grouping to get a max date on the query side. So now I had to change that recordset to not group by date. Now I would be forced to do this on the report side. I am basically trying to grouping on the Company field, while sorting by the $ amount. Really running into a wall. Don't know if this is possible. Any help would be greatly appreciated.

Thanks.

[Parsonshere]

I use a action query or series of queries to create a temporary table recordsource that is built when the report is run, using docmd.runsql in a module. Other than that, can u maybe leave the detail section out of the report, and just use the group headers or footers. I don't think you can accomplish the goal that way, but might think that through.