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

    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
    Kerala, India
    What value you have in uom Column? Try using First or Max instead of Group By in uom column. (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