Results 1 to 14 of 14

Thread: [RESOLVED] Need query to get only last record of a customer from his/her total records ...

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Resolved [RESOLVED] Need query to get only last record of a customer from his/her total records ...

    Hi,

    I have a table "Reciepts_Table" in MS Access Database where I stored the information of received amount from all customers. I can extract all the records that customers paid to date in a report. But what I need is, instead of all records I only want to see the last record of each customer so that I can understand when the customer last paid.

    This is the query that I am using right now:

    Select CustomerName, ReceiptNumber, ReceiptDate, AmountReceived from Receipts_Table ORDER BY ReceiptNumber.

    How to modify the above query to get only the last record of each customer in a report.


    Please support.
    Attached Images Attached Images  

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,029

    Re: Need query to get only last record of a customer from his/her total records ...

    without testing try it with Last

    Code:
    Select Last(CustomerName, ReceiptNumber, ReceiptDate, AmountReceived from Receipts_Table ORDER BY ReceiptNumber)
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,296

    Re: Need query to get only last record of a customer from his/her total records ...

    Your Problem is Access since it doesn’t support row_number function.
    with row_number it’s easy
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,230

    Re: Need query to get only last record of a customer from his/her total records ...

    You've posted this same thread three times now, under two different accounts. I moved the first one to the most appropriate forum and deleted the second one as a duplicate and now here we are again. Maybe try not to waste moderators' time. As the other account was brand new, I'll now delete that thread and move this one. Please do better. Here is my reply to the first thread:

    I think this will still work with the receipt number as text. First create a result set containing just the customer name and the maximum receipt number for that name:
    Code:
    SELECT CustomerName, MAX(ReceiptNumber) AS MaxReceiptNumber
    FROM Receipts_Table
    GROUP BY CustomerName
    You can then join the original table with that on the receipt number:
    Code:
    SELECT r.*
    FROM Receipts_Table r
    INNER JOIN
    (
        SELECT CustomerName, MAX(ReceiptNumber) AS MaxReceiptNumber
        FROM Receipts_Table
        GROUP BY CustomerName
    ) A
    ON r.ReceiptNumber = A.MaxReceiptNumber
    ORDER BY r.ReceiptNumber
    I haven't tested that but I believe it should work as is.

  5. #5
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,230

    Re: Need query to get only last record of a customer from his/her total records ...

    Quote Originally Posted by ChrisE View Post
    without testing try it with Last

    Code:
    Select Last(CustomerName, ReceiptNumber, ReceiptDate, AmountReceived from Receipts_Table ORDER BY ReceiptNumber)
    Wouldn't that just get the one last record in the table, as opposed to the last record for each customer?

  6. #6
    Addicted Member
    Join Date
    Jan 2022
    Posts
    211

    Resolved Re: Need query to get only last record of a customer from his/her total records ...

    Quote Originally Posted by jmcilhinney View Post
    You've posted this same thread three times now, under two different accounts. I moved the first one to the most appropriate forum and deleted the second one as a duplicate and now here we are again. Maybe try not to waste moderators' time. As the other account was brand new, I'll now delete that thread and move this one. Please do better. Here is my reply to the first thread:

    I think this will still work with the receipt number as text. First create a result set containing just the customer name and the maximum receipt number for that name:
    Code:
    SELECT CustomerName, MAX(ReceiptNumber) AS MaxReceiptNumber
    FROM Receipts_Table
    GROUP BY CustomerName
    You can then join the original table with that on the receipt number:
    Code:
    SELECT r.*
    FROM Receipts_Table r
    INNER JOIN
    (
        SELECT CustomerName, MAX(ReceiptNumber) AS MaxReceiptNumber
        FROM Receipts_Table
        GROUP BY CustomerName
    ) A
    ON r.ReceiptNumber = A.MaxReceiptNumber
    ORDER BY r.ReceiptNumber
    I haven't tested that but I believe it should work as is.
    There is no better way to do it than this.

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,029

    Re: Need query to get only last record of a customer from his/her total records ...

    Quote Originally Posted by jmcilhinney View Post
    Wouldn't that just get the one last record in the table, as opposed to the last record for each customer?
    you are correct, the "Group By" part is missing
    this is based on sample Table
    Code:
    SELECT Last(Bestellungen.BestellNr) AS LetzterWertvonBestellNr, Bestellungen.Bestelldatum
    FROM Bestellungen
    GROUP BY Bestellungen.Bestelldatum
    ORDER BY Last(Bestellungen.BestellNr) DESC;
    Access Database does have that option -Last-
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,296

    Re: Need query to get only last record of a customer from his/her total records ...

    Quote Originally Posted by vbdotnut View Post
    There is no better way to do it than this.
    There is.
    with row_number function.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,230

    Re: Need query to get only last record of a customer from his/her total records ...

    Quote Originally Posted by Zvoni View Post
    There is.
    with row_number function.
    Using a function that doesn't exist in the database you're using is not easier than anything.

  10. #10
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,296

    Re: Need query to get only last record of a customer from his/her total records ...

    Quote Originally Posted by jmcilhinney View Post
    Using a function that doesn't exist in the database you're using is not easier than anything.
    Then choose a database that is up to date on functions etc.
    or choose to use constructs as above

    i don’t care one way or the other
    and i told OP his problem is using Access….
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Need query to get only last record of a customer from his/her total records ...

    Quote Originally Posted by jmcilhinney View Post
    You've posted this same thread three times now, under two different accounts. I moved the first one to the most appropriate forum and deleted the second one as a duplicate and now here we are again. Maybe try not to waste moderators' time. As the other account was brand new, I'll now delete that thread and move this one. Please do better. Here is my reply to the first thread:

    I think this will still work with the receipt number as text. First create a result set containing just the customer name and the maximum receipt number for that name:
    Code:
    SELECT CustomerName, MAX(ReceiptNumber) AS MaxReceiptNumber
    FROM Receipts_Table
    GROUP BY CustomerName
    You can then join the original table with that on the receipt number:
    Code:
    SELECT r.*
    FROM Receipts_Table r
    INNER JOIN
    (
        SELECT CustomerName, MAX(ReceiptNumber) AS MaxReceiptNumber
        FROM Receipts_Table
        GROUP BY CustomerName
    ) A
    ON r.ReceiptNumber = A.MaxReceiptNumber
    ORDER BY r.ReceiptNumber
    I haven't tested that but I believe it should work as is.

    Thanks to all for their support. The first of the code is working fine but the second part of the code throws an error.

    This is the code which I am using as per your advice.

    Code:
    "Select CUSTNO,CUSTNAME, MAX(RECEIPTNO) As MaxReceiptNumber
                                    From RECEIPTSTABLE
                                    Group By CUSTNO,CUSTNAME,
    
                                    SELECT r.*
                                    FROM RECEIPTSTABLE r
                                    INNER JOIN
                                    (
                                    SELECT CUSTNO,CUSTNAME, MAX(RECEIPTNO) AS MaxReceiptNumber
                                    FROM RECEIPTSTABLE
                                    GROUP BY CUSTNO,CUSTNAME
                                    ) A
                                    ON r.RECEIPTNO = A.MaxReceiptNumber
                                    ORDER BY r.RECEIPTNO"
    The error snapshot has been attached.

    The below code is working fine to retrieve the last record of CUSTNO & CUSTNAME based on RECEIPTNO. But the second part is required to get other information such as RECEIPTDATE & AMTRECEIVED from the RECEIPTSTABLE.

    Code:
    "Select CUSTNO,CUSTNAME, MAX(RECEIPTNO) As MaxReceiptNumber
                                    From RECEIPTSTABLE
                                    Group By CUSTNO,CUSTNAME"
    When I write the full code then the error pops-up.

    Please support.
    Attached Images Attached Images  

  12. #12
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,230

    Re: Need query to get only last record of a customer from his/her total records ...

    There's a reason that I posted two separate code snippets. They are not supposed to be combined into one Frankenstein query. The first snippet is already included in the second. It's the part inside the parentheses. The second snippet is the whole query. Combining the two by simply putting a comma between them makes no sense at all.

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Re: Need query to get only last record of a customer from his/her total records ...

    Quote Originally Posted by jmcilhinney View Post
    There's a reason that I posted two separate code snippets. They are not supposed to be combined into one Frankenstein query. The first snippet is already included in the second. It's the part inside the parentheses. The second snippet is the whole query. Combining the two by simply putting a comma between them makes no sense at all.

    Got it. The code is working fine now. Thanks a lot for your kind support.

  14. #14
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,173

    Re: Need query to get only last record of a customer from his/her total records ...

    Quote Originally Posted by jmcilhinney View Post
    Code:
    SELECT r.*
    FROM Receipts_Table r
    INNER JOIN
    (
        SELECT CustomerName, MAX(ReceiptNumber) AS MaxReceiptNumber
        FROM Receipts_Table
        GROUP BY CustomerName
    ) A
    ON r.ReceiptNumber = A.MaxReceiptNumber
    ORDER BY r.ReceiptNumber
    FWIW, here's a (slightly) shorter alternative:

    SELECT * FROM Receipts_Table
    Where ReceiptNumber In (SELECT MAX(ReceiptNumber) FROM Receipts_Table GROUP BY CustomerName)
    ORDER BY ReceiptNumber

    Olaf

Posting Permissions

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



Click Here to Expand Forum to Full Width