Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query to find last and second last entries (All)

    I have a history table with HistoryID (auton), HistoryDate and HistoryAmount. Each time a customer's payment changes (usually annually) I add a new entry for each customer.

    For a billing report I need to establish the difference between the latest amount and the previous (second last) amount so that I can work with the difference.

    I can get to the latest amount by using an aggregate function (last) in the query, but I don't know how to get at the previous amount. Can anyone help?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query to find last and second last entries (All)

    Hi David,

    You can probably use the second method ("Using code") described in ACC2000: Referring to a Field in the Previous Record or Next Record.

    Note: this will be slow if you have a large number of records.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query to find last and second last entries (All)

    Actually, you need to be careful. Using the Last function may give you the "last" amount, but that may not coincide with the latest date. You could do this with about 3 queries, but it would be messy and would involve subqueries as well.
    Charlotte

  4. #4
    Lounger
    Join Date
    Mar 2002
    Location
    St Albans, Hertfordshire, England
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to find last and second last entries (All)

    Can you not just run a simple query along the lines of:

    Create Table TempHistoryRecords
    Select Top 2 CustomerID, HistoryID, HistoryDate, HistoryAmount
    From tblRecords
    Where CustomerID in [Enter Customer ID]
    OrderBy HistoryID DESC;

    This would then return the last two records for a specified customer (change the where clause to make it more user friendly) which could then be interrogated using vba etc to move the two entries into a new table in a single row (i.e. transposing the record)...


    ...or am I missing a trick <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    Kind regards

  5. #5
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query to find last and second last entries (All)

    Thank you all for your comments. It looks like doing this by a query is out of the question. I've started to work up some code that will take Chris's suggested "top 2" as the SQL source of a recordset and then do the calculations in code, outputting the result to a temporary table.

    Charlotte, you are absolutely right. The search can't just be on the ID as this may be out of order. I solved this dilemma in a different query and the answer was to sort by date order.

    Agani many thanks

Posting Permissions

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