Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: Grouping records

    Hi! I need to create a simple query but it seems I'm having troubles. I have a list of bank statements which are characterized by the ID of the account, the corresponding month and year and an ID of the bank statement. I simply would like to find the ID of the last (only the last) bank statement for each account. I tried something like this, but it seems it's not correct:

    Code:
    SELECT [IDAccount], MAX(CDate([Month] & "/" & [Year])), [IDBankStatement]
    FROM tbl
    GROUP BY [IDAccount]
    Unfortunately Access complains stating that [IDBankStatement] is not part of an aggregate function. I would like to insert the ID of the bank statement whose date is the one selected by MAX. Any idea how I can do this? I thought I can use a join afterwards on the IDAccount and the other values, but I would prefer something simpler if possible.
    Any idea?
    Thanks!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    SELECT [IDAccount], MAX(CDate([Month] & "/" & [Year])), [IDBankStatement]
    FROM tbl
    GROUP BY [IDAccount], [IDBankStatement]
    should work

    mind you I'd have expected the max statement to be max ([year] & [month])

    I'm expecting you to have problems if you persist in using reserved words in Access
    I'm also suspicious that you have a clunky table design if you are storing the month and year separately
    List of reserved words in Access 2002 and in later versions of Access
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2009
    Posts
    16
    Well, I already tried that way, but I see this is not what I need... By grouping for both IDAccount and IDBankStatement I get that more than one statement is selected for each account... I need only one statement for each account, which has to be the more recent one.
    Thanks for the answer!

  4. #4
    Join Date
    Sep 2009
    Posts
    16
    I solved using a join, but I suppose a better way can be found...

Posting Permissions

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