Results 1 to 5 of 5
  1. #1
    philozelda is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    2

    Help with displaying last value

    Hi everyone - so I have a table that I'm trying to query against to produce certain results. I'm having a bit of trouble so I'm not clear about the right solution.



    Here is my table:

    Cust ID As of Month-Year Amt Period
    XYZ-001 1-OCT-18 .0860 4Q 2018
    XYZ-001 1-NOV-18 .0839 4Q 2018
    XYZ-001 1-DEC-18 .0719 4Q 2018
    XYZ-001 1-JAN-19 .0711 1Q 2019
    XYZ-001 1-FEB-19 .1040 1Q 2019
    XYZ-001 1-MAR-19 .1161 1Q 2019












    What I'm attempting to do is show the last Amt for each period. So if I was able to get the results I'm looking for, it would like this:

    Cust ID As of Month-Year Amt Period
    XYZ-001 1-DEC-18 .0719 4Q 2018
    XYZ-001 1-MAR-19 .1161 1Q 2019






    Can anyone help me with this solution?

    Thank you.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    are your month-year and period columns date datetypes or text? If they are data datatypes makes it easier to find the 'max' date within a quarter. Also does you table contain a primary key - again makes it easier

  4. #4
    philozelda is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Apr 2020
    Posts
    2
    Quote Originally Posted by Ajax View Post
    are your month-year and period columns date datetypes or text? If they are data datatypes makes it easier to find the 'max' date within a quarter. Also does you table contain a primary key - again makes it easier
    Hi Ajax - month-year is a date type but Period is text, although Period can be ignored.

    That said, do you have any suggestions? I tried Allen Browne's method but I couldn't get it work right. I'm not getting the desired output.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    adapt this sql as I don't use spaces or non alphanumeric characters in field names

    Code:
    SELECT myTable.* 
    FROM mytable 
        INNER JOIN (SELECT CustID, Max(MonthYear) as maxDate
                           FROM myTable
                           GROUP BY format(monthYear,"q\Q yyyy")) AS T
        ON myTable.CustID=T.CustID AND myTable.monthYear=T.maxDate

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

Similar Threads

  1. Displaying 0's
    By O.92 in forum Queries
    Replies: 1
    Last Post: 12-14-2015, 04:16 PM
  2. Sub Report Not displaying
    By tstoneami in forum Reports
    Replies: 7
    Last Post: 08-28-2013, 04:29 PM
  3. Displaying name
    By Kivan in forum Programming
    Replies: 2
    Last Post: 08-27-2012, 07:48 AM
  4. Not Displaying a : or ,
    By hawkins in forum Forms
    Replies: 9
    Last Post: 08-12-2011, 11:39 AM
  5. Displaying Value and not ID
    By jonny in forum Access
    Replies: 3
    Last Post: 10-19-2009, 08:21 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