Access sorting by 'revision date' of 'individual widget' by customer.

Question

I am trying to run a query with Access. Each customer may have multiple revisions of the same 'widget'. Not all customers require the same revision, e.g. - CustomerA was entered in column 'ID' as 10, 13, and 34 - 34 contains the data I want to see without seeing entry 10 and 13 - each 'ID' has the date it was entered and the same 'widget#' - so 10, 13, and 34 show 'Entry#''widget42''rev#''customerA''date entered'. Likewise, 'CustomerC' only has 1 entry. (The 1st revision is usually left blank, i.e. - some do not say Rev. 0, the cell is just blank.) So, 'customerC' would look like this - 'Entry#''widget42''rev# (may be blank)''customerC''date entered'. What I want is to only see the latest revision for each customer for any one widget, all customers on one page with all their latest revised widget (no repeat widgets per customer).

'Entry#'--'widget#'--'rev#'--'customer#'--'date entered'

'10'------'widget42'-' '--'Brazil'-----'08/19/1999'

'13'------'widget42'-'Rev 1'-'Brazil'-----'05/08/2001'

'20'------'widget5'--' '--'Ireland'----'09/12/2001'

'26'------'widget6'--' '--'Brazil'-----'12/01/2001'

'30'------'widget5'--'Rev 1'-'Ireland'----'10/30/2003'

'33'------'widget42'-' '--'Ireland'----'11/16/2005'

'34'------'widget42'-'Rev 2'-'Brazil'-----'05/14/2006'

'43'------'widget23'-' '--'Peru'-------'06/16/2006'

'54'------'widget6'--' '--'Ireland'----'06/17/2006'

WHAT I WOULD LIKE TO SEE ---------------------------- POSSIBLY SORTED BY CUSTOMER ----- AFTER RETURNING ONLY LATEST REVISION NUMBER

'Entry#'--'widget#'--'rev#'--'customer#'--'date entered'

'20'------'widget5'--' '--'Ireland'----'09/12/2001'

'26'------'widget6'--' '--'Brazil'-----'12/01/2001'

'30'------'widget5'--'Rev 1'-'Ireland'----'10/30/2003'

'33'------'widget42'-' '--'Ireland'----'11/16/2005'

'34'------'widget42'-'Rev 2'-'Brazil'-----'05/14/2006'

'43'------'widget23'-' '--'Peru'-------'06/16/2006'

'54'------'widget6'--' '--'Ireland'----'06/17/2006'

Answer 1

You could base your report on a query, say:

SELECT w.entry, w.widget, w.revx, 
       w.customer, w.date_entered 
FROM
   (SELECT entry, widget, Nz([rev],"rev0") AS revx, 
           customer, date_entered
    FROM widgets)  w 
INNER JOIN (SELECT customer, widget, Max(Nz(rev,"rev0")) As revx 
            FROM widgets 
            GROUP BY customer, widget)  AS a 
ON (w.widget = a.widget) AND (w.customer = a.customer) AND (w.revx=a.revx)

Answer 2

Some clarification to the question above since I can't fit all this in a comment.
Here's what the data looks like:

ID | TTS Version  |  Customer  |  Review Date  |  Product  |  Formula|

55 | 01           |  Brazil    |10/9/2012      |Liner      |  50312
54 | 00           |  Brazil    |3/7/2012       |Liner      |  50312  
9  | 01           |  Canada    |8/5/2008       |PETS       |  50077
61 | 02           |  Canada    |1/6/2015       |PETS       |  50004
40 | 02           |  Canada    |10/18/2010     |PETS       |  50077

Here's the result I want:

ID | TTS Version  |  Customer  |  Review Date  |  Product  |  Formula|

55 | 01           |  Brazil    |10/9/2012      |Liner      |  50312
61 | 02           |  Canada    |1/6/2015       |PETS       |  50004
40 | 02           |  Canada    |10/18/2010     |PETS       |  50077

I want to see the most current version for each formula for each customer all in the same report. This is in a MS Access 2002 database.