Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2016
    Posts
    1

    Unanswered: Total Query: Getting records for latest dates only with multiple fields

    Hello all, new to this access thing -- I've inherited someone else's WIP database. I've searched and came close but no cigar on resolving my issue.

    I've attached a screenshot of what I'm seeing:

    Anyway, I'm trying to pull only the record of the latest last_invoiced_date field record for each main_pn. The source query I'm pulling this from has multiple last_invoice dates for each main_pn (based on other few other criteria beforehand).

    Now, this works if I use the totals query, and use "group by" in main_pn, and "max" on last_invoiced_date and leave it at only those two fields (red highlight on the screenshot). However, as soon as I bring in rest of the fields (uom, avgOfUnit_cost, AvgOflead_time), then it also returns multiple records for each main_pn of these rest of the tables (e.g. for each of the different leadTime, for example).

    What I want to do is once I find a main_pn, grab the single last_invoiced_date for that main_pn, but still show what the associated data is for rest of these fields (uom, avgOfUnit_cost, AvgOfLeadTime).

    How can I achieve this?
    Attached Thumbnails Attached Thumbnails screenshot.png  

  2. #2
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    180
    What value you have in uom Column? Try using First or Max instead of Group By in uom column.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

Posting Permissions

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