Results 1 to 6 of 6

Thread: Last call

  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have 2 tables tblClients and CallsClients.I have a query with a field CallDate.
    SELECT [TblClients].[ClientID], [CallsClients].[CallDate]
    FROM TblClients INNER JOIN CallsClients ON [TblClients].[ClientID]=[CallsClients].[ClientID];

    Naturally when a client is called 2 times, the query shows these calls in 2 rows.is it possible to make a query showing only the last call ? i could also name the field LastCall too
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The usual way of accomplishing that is to use the Max function on the call date to identify what the transaction is in a query, and then join to that query using the tblClients - of course you want to make sure the ClientID is included in the GroupBY query.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    I'd do it this way:

    SELECT [TblClients].[ClientID], sq.[LastCallDate]
    FROM TblClients LEFT JOIN (SELECT ClientID, Max(CallDate) as LastCallDate FROM CallsClients GROUP BY ClientID) as sq ON [TblClients].[ClientID]=sq.[ClientID]

    This gives you the option of returning all clients, whether or not a call was made. If you only want to return clients that have a LastCallDate, add "WHERE LastCallDate Is Not Null" to the SQL statement.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for your reply.I got the following error with the sql:"The Microsoft Jet database cannot find the input table or query "select ClientID,Max(CallDate) as LastCallDate From CallsClients GROUP by ClientID".Make sure it exists and its name is spelled properly". Would you help ? It seems i am not doing something properly

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Have a look at the attachment. It does it both ways.
    Attached Files Attached Files
    Regards
    John



  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much ! The query is perfect and i thank you very much for that !!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •