dcsimg
Results 1 to 8 of 8

Thread: [RESOLVED] ACCESS SQL problem - MAX() selection of newest 'Update' from Transaction list

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Resolved [RESOLVED] ACCESS SQL problem - MAX() selection of newest 'Update' from Transaction list

    Esteemed Forum Participants and Lurkers:
    ================================
    Microsoft Access 2000 (9.0.3821 SR-1)

    I am trying hard to understand and use Access, but it is eluding me. I have distilled my question as simply as possible ... my actual target is a bit more complex ... but here is my major hurdle right now ...

    I have 2 tables ... a table ('Accounts') listing the Accounts and a table ('Updates') of Update Transaction records. I need to generate a query that yields only the most recent Update Transaction for each account along with related information. I am having trouble understanding how to do the subquery to pull the specific desired data from both tables for each record into the query.

    This WORKS! Yields List of ALL transactions grouped by Account ID
    Code:
    SELECT Updates.TransDate, Updates.Balance, Accounts.OrgName, Updates.AcctID, Accounts.Owner
    FROM Accounts INNER JOIN Updates ON Accounts.AcctID = Updates.AcctID
    GROUP BY Updates.AcctID;
    This WORKS! Yields a subquery of ONLY THE MOST RECENT Update Transaction for each account.
    Code:
    SELECT Updates.AcctID, Max(Updates.TransDate) AS Updated
    FROM Updates
    GROUP BY Updates.AcctID;
    I found this style on a website but I can't get it to work ... it yields the following error:
    Syntax error . in query expression 'Accounts.AcctID = Updates.AcctID
    (SELECT Updates.AcctID, Max(Updates.TransDate)
    FROM Updates
    GROUP BY Updates.AcctID AS UpdateAmount)'.
    Code:
    SELECT Updates.TransDate, Updates.Balance, Accounts.OrgName, Updates.AcctID, 
    Accounts.Owner 
    FROM Accounts INNER JOIN Updates ON Accounts.AcctID = Updates.AcctID
        (SELECT Updates.AcctID, Max(Updates.TransDate)
         FROM Updates
         GROUP BY Updates.AcctID AS UpdateAmount)
    WHERE Updates.AcctID = UpdateAmount.AcctID
    AND Updates.AcctID = UpdateAmount.TransDate;
    I have searched for quite a few hours and have found some information on putting the subquery in an INNER JOIN, but I can't get that to work either. I actually have several more columns to add to the query from the 'Accounts' table, and I would like to simplify that linkage as much as possible.

    The entire Access database with a little data is attached as a .txt file ... change it to .mdb for access. The Table Diagram is also attached. I would sincerely appreciate any and all comments, suggestions, and assistance in getting this to work. Thank you!
    Attached Images Attached Images  
    Attached Files Attached Files
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,409

    Re: ACCESS SQL problem - MAX() selection of newest 'Update' from Transaction list

    There are two mistakes in the syntax you tried.

    The first is that AS UpdateAmount should be after the closing bracket rather than before it, and the other is that you should treat the sub-query as a table (thus use Join etc).

    Try this:
    Code:
    SELECT Updates.TransDate, Updates.Balance, Accounts.OrgName, Updates.AcctID, 
    Accounts.Owner 
    FROM (Accounts 
    INNER JOIN Updates ON Accounts.AcctID = Updates.AcctID)
    INNER JOIN 
        (SELECT Updates.AcctID, Max(Updates.TransDate)
         FROM Updates
         GROUP BY Updates.AcctID) AS UpdateAmount ON Updates.AcctID = UpdateAmount.AcctID
    WHERE Updates.AcctID = UpdateAmount.TransDate;
    (two quirks for Access: unusual brackets needed when multiple joins, and only one condition per join - hence still using a Where clause)

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: ACCESS SQL problem - MAX() selection of newest 'Update' from Transaction list

    Thanks for the detailed explanations Si. I think I'm beginning to see the process ...

    Create something like a virtual temporary table aliased UpdateAmount that contains only the records from the 'Updates' table that are needed, and join that virtual table and the other two real tables.

    It doesn't quite work yet ... the last line of your code is not possible ...
    "Updates.AcctID = UpdateAmount.TransDate"
    ... so I replaced "TransDate" in that line with "AcctID". That ran, but basically returned ALL records based on the 'Updates' table without disqualifying any of the 'older' update records that aren't the "MAX()" records.

    Then I replaced it to read:
    "WHERE Updates.TransDate = UpdateAmount.TransDate;"
    ... and it pops up a modal dialog box: "Enter Parameter Value" with "UpdateAmount.TransDate" and a text entry box.

    So, it is close, but not operational yet. Looking at the JOIN of the 'virtual table' UpdateAmount ...
    "AS UpdateAmount ON Updates.AcctID = UpdateAmount.AcctID"
    ... AcctID in the virtual table is unique (ONE) while AcctID in the Updates table is MANY, so I think that may be why the dialog box is popping up asking for an additional qualifier to make the selection unique ???
    EDIT: Can't do following because it would lose the 'AcctID' for the GROUP BY !!!
    I'm going to play with using the TransID field in the Updates table instead of the AcctID field since that will definitely be unique and make a one-to-one relationship between the virtual table and the Updates table. Is that a good guess?

    EDIT: I think there has to be ...
    "Updates.TransDate = UpdateAmount.TransDate AND Updates.AcctID = UpdateAmount.AcctID"
    ... somewhere to qualify the correct Updates.Balance record.

    I'll let you know what happens. Thank you for your gracious assistance.
    Last edited by Webtest; Mar 4th, 2011 at 06:27 PM. Reason: Duhhhh ... GROUP BY ...
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  4. #4

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: ACCESS SQL problem - MAX() selection of newest 'Update' from Transaction list

    I actually got this working, thanks to the "Design View" (as opposed to the SQL view), but I had to create 2 queries to do it.

    The first query just builds the (virtual) table of Date/Account pairs only for the most recent update for each account:

    qryTopDate:
    Code:
    SELECT Max(u.TransDate) AS Updated, u.AcctID
    FROM Updates AS u
    GROUP BY u.AcctID;
    The second query joins the first query and the Updates table and yields the desired records ... the account ID and the Balance only for the most recent update:

    qryUpdates:
    Code:
    SELECT u.TransDate, u.AcctID, u.Balance
    FROM Updates AS u INNER JOIN qryTopDate 
    ON (u.TransDate = qryTopDate.Updated) AND (u.AcctID = qryTopDate.AcctID);
    As you can see, there are TWO fields linking the tables by virtue of the AND statement. Of course, some genius will figure out how to put all of this into a single query and that will be wonderful!
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,409

    Re: ACCESS SQL problem - MAX() selection of newest 'Update' from Transaction list

    That looks good.

    Quote Originally Posted by Webtest View Post
    It doesn't quite work yet ... the last line of your code is not possible ...
    Oops, I wasn't paying enough attention there! I just copied it directly from your Where clause, and forgot to check the fields.

    Then I replaced it to read:
    "WHERE Updates.TransDate = UpdateAmount.TransDate;"
    ... and it pops up a modal dialog box: "Enter Parameter Value" with "UpdateAmount.TransDate" and a text entry box.
    That was the right thing to do, the reason for the dialog box is that it doesn't know what UpdateAmount.TransDate is supposed to be, which you can correct by giving an alias:
    Code:
     Max(Updates.TransDate) as TransDate
    ..or a better alias such as Updated.


    This should bring it to the equivalent of what you have come up with.

    Based on previous experience I doubt you will be able to use 2 conditions in the join once there are more than 2 tables, but it is worth a try.

  6. #6

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: ACCESS SQL problem - MAX() selection of newest 'Update' from Transaction list

    Si ...
    Thank you very much for your interest and your assistance. Your solution works PERFECTLY! Sorry about my error in my initial posting ... it is very understandable that you would copy it and I wouldn't have expected anything else.

    I also should have figured out aliasing the MAX() result, because I've seen before that columns resulting from functions get assigned different names. Be assured that I will carefully go over your code in great detail so that I understand all of it.

    Anyway, Thanks again ... you get my vote as you are indeed a "super moderator". One of these days I'll figure out how to get a bottle of "Old Engine Oil" to you!
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

  7. #7
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,409

    Re: [RESOLVED] ACCESS SQL problem - MAX() selection of newest 'Update' from Transacti

    I'm happy to help, especially with someone who is putting in the effort to find solutions themselves.

  8. #8

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

    Re: [RESOLVED] ACCESS SQL problem - MAX() selection of newest 'Update' from Transacti

    The working FINAL SOLUTION many thanks to si_the_geek !!!
    Code:
    SELECT Updates.TransDate, Updates.Balance, Accounts.OrgName, Updates.AcctID, 
    Accounts.Owner 
    FROM (Accounts 
    INNER JOIN Updates ON Accounts.AcctID = Updates.AcctID)
    INNER JOIN 
        (SELECT Updates.AcctID, Max(Updates.TransDate) as Updated
         FROM Updates
         GROUP BY Updates.AcctID) AS UpdateAmount 
         ON Updates.AcctID = UpdateAmount.AcctID
    WHERE Updates.TransDate = UpdateAmount.Updated;
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width