Results 1 to 4 of 4
  1. #1
    Join Date
    May 2008

    Unanswered: Choosing Maximum

    I have a bit of a problem. I'm trying to put together a query in Access 97 and I can't get my head around it.

    Suppose you have three tables:

    tblOne (OneID)
    tblTwo(TwoID, OneID, ThreeID, Name)
    tblThree(ThreeID, Type, Date)

    tblOne to tblTwo is a one-to-many. tblThree to tblTwo is a one-to-many. So in effect, tblOne to tblThree is many-to-many.

    What I'm trying to get is the "Names" from tblTwo which correspond to the maximum dates of tblThree of a certain "Type", using outer joins. Sorry if this explanation is bad.

    So suppose I do this query:

    SELECT tblOne.OneID, tblTwo.Name FROM
    tblTwo ON tblTwo.OneID = tblOne.OneID
    tblThree ON tblTwo.ThreeID = tblTwo.ThreeID
    tblThree.Type = 1;
    Now, if a tblOne.OneID correspond to more than one tblThree of that type (by joining through tblTwo), I want to just display the latest one by using the tblThree.Date. But I need to preserve the joins because there may not be any of the tblThree.Type at all for that tblOne.

    Really sorry for this explanation, it's the best I can do for now. Just ask if you need clarification. Thanks everyone.

    Edit: I understand that the above query won't actually work in Access97. Just wanted to keep it generic.
    Last edited by addlebrains; 05-28-08 at 22:24.

  2. #2
    Join Date
    Apr 2004
    metro Detroit
    Create a group by query (qryMaxDate) using tblThree. Group by the type and pull the max date.

    Create another query with all three tables and qryMaxDate. Link qryMaxDate to tblThree by the type and date (that's not really the name of your field, right).

  3. #3
    Join Date
    May 2008
    Thanks. I've been talking to other people and the consensus seems to be to create an external query. Thanks.

    And no, my actual tables have real names, in fact my query is much bigger. Just wanted to get the idea first.

  4. #4
    Join Date
    Nov 2007
    Adelaide, South Australia
    Good consensus
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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