Complex Sort in Access Report

[agsjjs]

I have been working on a complex sort in Access Reports using Sorting and Grouping and I cannot get it to sort and group in the order I need.

Here is what I am trying to accomplish.
1. Group by Order_Number ascending and sum the amounts in the group footer using a calculated field.
Group Header: No
Group Footer: Yes
Group On: Each Value
Group Interval: 4
Keep Together: Whole Group
2. Then sort the line items of the grouped order numbers by Date ascending.
Group Header: No
Group Footer: No
Group On: Each Value
Group Interval: 1
Keep Together: No
3. Number 1 and 2 work fine. However, the last item is the challenge. I am then trying to sort the entire group of orders (keeping the line items chronological) by the earliest line item date in the group.
Here is an example.

3566 4/30/2003 11:30AM
3566 5/1/2003 8:00AM
3566 5/2/2003 2:00PM

2985 5/1/2003 4:40PM
2985 5/2/2003 8:00AM
2985 5/3/2003 3:30PM

3300 5/2/2003 8:00AM
3300 5/2/2003 4:35PM
3300 5/4/2003 12:00PM

If anyone can help, it is much appreciated!

 

[hymn]

If you are getting your source for the report through a Query try sort ascending onthe required field

Hope this helps
Hymn

 

[AccessAce]

For the record source of the report, create a field that is Order_By_Date: (SELECT Min(Date) FROM Orders WHERE Order_Number = [Order_Number])

Then do your first grouping on Order_By_Date. Everything else should stay the same.

 

[agsjjs]

AccessAce. Your trip worked like a charm!
Thank you so much!