Grouping without sorting in a report

[Robway]

I'm using Access 97.

In a report I want to group by "AccessionNo" (an expression joining two fields in the underlying query), but not sort on this. The sorting is needed on "EventDate" (another expression in a text box on the report combining the min and max EventDate values). AccessionItems have variable numbers of EventDates, and the order they appear on the report should be chronological. I can get them to sort beautifully by AccessionNo, or by EventDate, but never with both. I only want each AccessionNo to appear once.

If I group on EventDate first, every date shows, followed by its AccessionNo (frequently multiples). If I group first on AccessioNo I can't get them into chronological order.

The report draws other material from four tables.

I'm not at all literate in programming or even writing expressions, so please be tolerant.

 

[hermanlaksko]

Try using the sorting/grouping icon and use group header in this section this should fix your sorting problem.

Under properties/format of your field AccessionNo or EventDate select Hide dublicates

 

[Robway]

Thanks Herman,

I've tried both these things and they don't seem to help. I could be missing something simple - so often the way, but I've played with them over and over. That's why I'm getting so frustrated.

If I group on AccessionNo and create a group header it looks great, but the records are sorted in AccessionNo order, which I don't want. The dates format perfectly, but not in chronological order as AccessionNo has got in first with the sorting.

If I group first on EventDate I get the right order, but I get every date in the database and the AccessionNo's are repeated over and over. I can't hide duplicates because even if they appear to be duplicates, they're not - they relate to different AccessionNo's. And I don't want every one - just min and max to indicate the range per AccessionItem.

The tables these two fields come from have a many/many relationship which have a link table inbetween to create two one/many relationships. The report actually draws data from four linked tables, but this is the only bit that won't work.