How to use MAX() on a subquery result?

You don't need the subquery that finds the maximum value.
Instead, ; you just need the first row after having ordered the rows:

select * from (  select     membership.mem_desc,    membership.mem_max_rentals,    membership_history.mem_type,          count(membership_history.MEM_TYPE) as membership_count  from membership_history  JOIN membership ON membership.mem_type = membership_history.mem_type  group by (membership_history.mem_type,membership.mem_desc,membership.mem_max_rentals)  ORDER BY 4 DESC  -- Added this line) gWHERE ROWNUM = 1. -- Added this line

This is all good SQL.

The best way to find a column value using the max primary key column value is:

SELECT .... from tablenameWHERE ... AND  (SELECT max(primary key name) FROM tablename WHERE .... ) = primary_key_name

this example will return a scalar value.

I have had a similar issue within an Access Query ... based on some of the comments in this thread, are we to understand that the ACCESS MAX function should only be used on/with Primary Key Fields? ... basically, I have a table that defines Addresses relating to an Entity, BUT, with a slight twist; 1) once saved, no record is allowed to be deleted or changed (meaning an Entity could have multiple address Records), 2) any current Entity Address can either have; HOME (1 Record) or HOME and MAIL (2 records) ... each Address record has an (ID) (autonumber Primary KEY) a HOME ID (HID) matching the HOME record's (ID) (HID is NOT a Primary KEY and is NOT unique) and an Entity ID (EID) ... so ... when I attempt to use a sub-query to get the max(HID) value for a given Entity, I only want the current Address returned ... what I found by using MAX in a sub-query is it either fails to run because ACCESS incorrectly thinks it has too many matching records for the sub-query ... or ... it incorrectly returns rows it shouldn't be returning.

The only way I found to get around this issue was to replace the max sub-query with a Max Function which returns the MAX HID Value for an Entity (the function uses the sub-query statement with DAO logic within VBA)

Here is the HOME sub-query which is part of the main Query (the main query needs to return one row per entity as; EID, Home.Address, Mail.Address)

Select *From tbAddresses As tba1Where tba1.aType = "Home"  And tba1.HID = (Select MAX(tba2.HID)                       From tbAddresses As tba2                      Where tba1.EID = tba2.EID)

The main query only works properly when the Where clauses both Home and Mail (not shown) are replaced with the function below. If the MAX sub-query is included as above, it doesn't work.

So if the MAX function requires a PRIMARY KEY field to work, this might explain why my queries are failing, although, that would sound like a major limitation.

Where tba1.HID = fnGetMaxHID(tba1.EID) 

Here is some test data which should only return 3 rows

ID    HID    EID   aType  Address 1      1    100   Home   Blah 1 2      2    101   Home   Blah 2 3      2    101   Mail   PO Box Blah 0 4      4    102   Home   Blah 3 5      5    101   Home   Blah 4

One last note, several versions of Access Pro; 2002, 2003, 2016 were tested and all produce the same results. So this issue is either an inherent quirk of the max function or some kind of bug which has possibly been ignored or gone unnoticed? ... the function works as a workaround for me but may not work for others so would be nice if the MAX function was clarified.