Hi All

I have a query that returns data like this - where *** is a memo recording a conversation with a client.

Client Date Note
A 21/1/12 ***
B 16/3/11 ***
A 1/4/11 ***
B 12/5/12 ***

I base a report on the query that groups the data to give this:-

Client A 1/4/11 ***
21/1/12 ***

Client B 16/3/11 ***
12/5/12 ***

But I really want to order the Clients so that the client with the latest date in their date stream comes first & then the client with the next latest date & so on. So Client B should rank before Client A because the latest date in Client B’s note stream 12/5/12 is later than the latest date in Client A’s note stream 21/1/12.

I’ve played & can’t make it happen. How do I do this please?

TIA

Ian

In the sorting and grouping option have you tried setting the first sorting option (no grouping) to the call date descending and then have it group by client name?

If that works then I imagine your only issue would be that the calls would appear in reverse order, if thats an issue you need to add another field to your query with the 'Max' call date, this could be done with DMax (but would be very slow) or with some creative grouping on the queries its based off. Once you sort out that field then sort the data by the Max date descending then the call date ascending and it should work... I think

Can you not make an Order By on the Date field, ei:

Hi John

I have to group on the client - obviously - & the report design won't let me put a sort on date above this group on the client.

Regards

Ian

Later: Now I've succeeded in doing that but I now get a report that for each day gives the conversations for that day broken down by client & then again for the next day & so on.

What I really want is all the conversations for client A in one continuous list & then for client B but the client order to be determined by the order of the date of the most recent conversation with each client.

Its this I can't achieve.

Hi arnelgp

I can indeed order the query data on descending date but this is over-ridden by the sorting & grouping settings in the report.

The report is grouped on the client as it has to be & then sorted on date.

I guess the answer must lie in additional fields in the query that I can then group on in the report but I haven't solved it yet!

Any more thoughts would be welcome!

Regards

Ian

I have just double checked and you can (at least in Access 03) specify a sort field on a report first with no grouping and then set another field under with a group header/footer. I'm not sure if that then still groups the data from the first option but just doesn't show any group headers etc but if you use the DMax or equivalent I suggested that should work in combination.

Hi John

I think you Dmax idea may be the way to go. I'll test - watch this space!!

Ian

It will slow things down alot mate, you might want to look at grouping your query instead. If you create a group query that gets groups on client name and Max Date then join that query to the existing one so you have the Max date field it should run alot quicker.

Hi John

Cracked it! DMax was the way to go.

Once I had remembered that Dmax returns text & I used Cdate to get a date it was simple.

Many thanks to you both for your interest & your help!

Regards

Ian

Cool, happy to help