Results 1 to 7 of 7
  1. #1
    enilc is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    14

    Most recent interaction

    Hello



    I thought this seemed fairly straightforward, but it's giving me fits and driving me a little crazy.

    Data as such (tablename=clientnotes)


    client type date
    smith email 20Jan2020
    jones phone 2Feb2020
    smith phone 2Feb2020
    smith phone 19Mar2020
    jones email 14Apr2020
    wilson phone 14Apr2020
    wilson email 4Oct2020
    smith email 6Nov2020


    I am attempting to create a query that returns the last date each client was contacted via phone


    The query using the above data would return:
    smith phone 19Mar2020
    jones phone 2Feb2020
    wilson phone 14Apr2020


    The criteria set to 'phone' is straightforward. I've various iterations of using a "Totals" query in query designer and set Total to 'Last' for date or set Total to 'Last' for client.


    SQL looks like:


    SELECT ClientNotes.client, Last(ClientNotes.[Date]) AS [LastOfDate], ClientNotes.[Type]
    FROM ClientNotes
    GROUP BY ClientNotes.client, ClientNotes.[Type]
    HAVING (((ClientNotes.[Type])="phone"))
    ORDER BY ClientNotes.client;

    I tried messing with TOP 1 but that ends up only returning a single client, not all clients. I feel like I'm over-thinking it and have confused myself.

    I really appreciate any assistance.

    Steve

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Use max() not last()
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    enilc is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    14
    I appreciate the quick response.
    Unfortunately, that does not seem to work. It is still returning every instance of 'phone' contact type. Using the sample data, I am getting:

    client type Maxofdate
    jones phone 2Feb2020
    smith phone 2Feb2020
    smith phone 19Mar2020
    wilson phone 14Apr2020

    In my real data, I have 596 clients. So the most rows I could/should possibly get returned is 596. But I am getting every single 'phone' contact type (over 1200) rather than just the most recent.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    That is because you are also grouping by type? Though you are only specifying phone. Is that all you get?, as I normally use single quotes for text.
    Remove type from the query and see if that fixes it, if you get more than phone.
    Date is also a reseved word.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Here is something that works for me with my data
    Code:
    SELECT Transactions.Description, Max(Transactions.TransactionDate) AS MaxOfTransactionDate, Transactions.Category
    FROM Transactions
    GROUP BY Transactions.Description, Transactions.Category
    HAVING (((Transactions.Category)='SF OUT:Payment'));
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    Last(ClientNotes.[Date]) AS [LastOfDate], ClientNotes.[Type]
    Be aware that "Date" and "Type" are reserved words in Access and shouldn't be used for object names. "Date" is also a built in function. Plus they are not very descriptive.
    Maybe change to "ContactDate" and "ContactType"...


    You might look at TOP n records per group (Provided by Allen Browne); this has helped me in the past.

  7. #7
    enilc is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2020
    Posts
    14
    Thanks again for your attention.
    Firstly, apologies that my quick sample data creation was messy. I’m not using ‘date’…was some careless shorthand on my part.
    Next, after looking at your snippet and switching from “last” to “max” s you suggested, i believe i had an issue with parentheticals that was causing my two criteria to act as an ‘or’ rather than an ‘and’ set of criteria.
    Very cloudy on my part, and i very much appreciate the help from you and your code.

    Thanks
    Steve

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access<-->Excel Interaction Problem
    By Ellpee in forum Programming
    Replies: 5
    Last Post: 09-15-2018, 06:58 PM
  2. Replies: 3
    Last Post: 05-23-2016, 10:31 AM
  3. Interaction between Excel and Access 2010
    By mojeime in forum Access
    Replies: 1
    Last Post: 09-26-2013, 05:33 AM
  4. Replies: 9
    Last Post: 05-16-2012, 10:17 AM
  5. Form / Switchbox interaction
    By oleBucky in forum Forms
    Replies: 6
    Last Post: 03-22-2011, 10:58 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums