Access query to return a list of customers (Distict) with their most recent transaction date

Posted on 2011-10-17
1 Endorsement
Last Modified: 2012-05-12
I need to contruct a query that returns just one record for each customer (that has made a purchase) with the date of their last purchase.

So I have a transaction table with the customer ID so from this table I need to return just the most recent instance of each Customer ID along with the transaction date. I'll then join this to the customer table to get names and stuff.

The query is used for a report which then filters the date range of the transactions.

What is the fastest method to return this data set?
Question by:kentgorrell
    LVL 24

    Expert Comment

    Well the best is to keep some date with the  entries. Have you done that?

    if not do you have some autowert value field in the table? If yes just use  e.g
    DMax([autoIdField]); ....

    Author Comment

    As I said, the Transaction table has Customer_ID and Transaction_Date.

    To clarify, I want a list that includes just one record for each customer - the most recent.

    of course if I don't include the date then SELECT DISTINCT will work but I do need the date and it must be the most recent.

    I can work the join to the customer table out myself I just need the SQL for the Transation Table to return just the most recent record for each customer.
    LVL 8

    Expert Comment

    Following is a MS-SQL query :
    Select Customer_ID, MAX(Transaction_Date) FROM YourTable GROUP BY Customer_ID
    LVL 24

    Accepted Solution

    Then you can use Dmax on the Transaction date or the MAX SQL function on the Transaction Date.
    SELECT CustomerData.CustomerID, Max([LastModified]) AS LastDate
    FROM CustomerData
    Group by CustomerID;


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now