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).