Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Build query (Access2003)

    Have a table of leadtime dates with sales order numbers and multiple dates per sales order. I want to query this table and produce only one entry for each sales order# with the longest leadtime ship date. I have attached an excel version of a table and query result for it would produce 3 lines displaying rows 4, 5, 8.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Build query (Access2003)

    Have a look at the attachment. It uses 2 queries.

    Query1 finds the max ShipDate for each Order.
    Query2 then joins that back to the table to add in the other fields.
    Attached Files Attached Files
    Regards
    John



  3. #3
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Build query (Access2003)

    Thank you, this performed the function needed. It did leave the query "read only" when I tried to open it as a record set with some of my other code. I turned it into a Make Table query and was able to open the result.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Build query (Access2003)

    Yes any query that uses a Totals query is always read only. So if you count, or sum, or find the Max or Min etc.
    Regards
    John



  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Build query (Access2003)

    Here is another version that is not read only. This uses 3 queries, and the third one has a subquery, and depends on the table having a key field that uniqely identifies each record.
    Attached Files Attached Files
    Regards
    John



Posting Permissions

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