How to sort query results by sorted maxValue of related query

[leonardokroeger]

Hello everybody,
I'm new to this forum and - most importantly new to Access (2010).
I'm trying to sort a query in a slightly more sophisticated way, and I'm not having much success.

This is the scenario:

I want clients from tblClients to appear in a query in a certain order.

We call those clients and the call time is recorded in a call table, tblCalls. If there was no call for a client, there is no respective record in the calls table.
Next we want to be able to call first those clients which have their most recent call as the most unrecent call of all other clients.

How do I sort the clients table to have those clients listed first in a suitable query? (And of course the never called clients even before them)?

I want to be able then to build a form upon that query that shows up clients' details record by record in that order. This form must be updateble.

Client 1
Client 2
Client 3


Call 1 Client 1 on 01/01
Call 2 Client 1 on 02/01
Call 3 Client 1 on 03/01
Call 4 Client 2 on 04/01
Call 5 Client 2 on 05/01
Call 6 Client 2 on 06/01



Query should display:
Client 3 (no call)
Client 1 (last call = his maxValue or most recent call on 03/01/)
Client 2 (last call = his maxValue or most recent call on 06/01/ > maxValue or most recent call Client 1)

I'm kind of desperate... any try envolving totals in this or a related query renders it "not updateble" - including forms that I built upon the query.

What am I doing wrong? Any links or explanations? Google doesn't help me anymore...

Thanks a lot!

This is the farest I got. Using the following query to sort a query including all clients' details makes the second one not updateble:

SELECT T1.FKClient, T1.Calltime
FROM tblCalls AS T1
WHERE (((T1.Calltime)=(SELECT MAX(Calltime)
   FROM tblCalls AS T2
   WHERE T2.FKClients = T1.FKClients)))
ORDER BY T1.Data;

 

[Beetle]

The following query might return the results you want, but would not be updateable;

SELECT DISTINCT tblClients.ClientID, tblClients.ClientName, Nz(DMax("CallTime","tblCalls","ClientID=" & [tblClients].[ClientID]),"") AS LastCall
FROM tblClients LEFT JOIN tblCalls ON tblClients.ClientID = tblCalls.ClientID
ORDER BY Nz(DMax("CallTime","tblCalls","ClientID=" & [tblClients].[ClientID]),"");

I don't believe there would be any way to get those results in an updateable query because all the methods you might use to return the desired results (a Totals query, a Distinct predicate, a sub query, a domain aggregate function) will render the query (or at least some of it's fields) read only.

 

[leonardokroeger]

Thank you, Beetle.
This really is yet another way to do the same, but you helped me a lot however.
Now that it's clear my idea can't simply be realized I searched for another solution of my problem.

I am using your query for sorting the clients and having a form where they appear in this order. Then I open a new form where I let the IDs match and which is updateble. My problem is solved.

Thanks a lot again.