Ms-access – MAX and MIN for each car model

Check out chapter 3 of

The Data Model Resource Book Volume 1 by Silverston

It has a pricing model that is very flexible

A very brief summary:

It's kinda complicated...

(*) means this column references another table

PriceComponent

id
fromDate
thruDate null
price null
percent null
geographicBoundary* [pizza costs more outside of city]
partyType* [surcharge to seniors]
productCategory* [discount for CRT monitors]
quantityBreak* [discount for high volume]
orderValue* [discount for big order]
saleType* [surcharge for retail]
currency* [discount for Canadians]
productFeature* 
product*

BasePrice : PriceComponent

DiscountComponent : PriceComponent

SurchargeComponent : PriceComponnent

ManufacturersSuggestedRetailPrice : PriceComponent

Charge : PriceComponent

OneTimeCharge : Charge

RecurringCharge : Charge

UtilizationCharge : Charge

So, the price of something is a set of PriceComponents, which can be either an actual price amount or a percentage. You can have a component of the final price based on geographical location, the type of sale (retail vs wholesale), the value of the order and so on.

Not sure if I understand your requirements correctly. This query will return, for every organization, the minimum start date and the latest membership_subcategory_id (when the order is by start):

SELECT    organisation_id, 
            MIN(`start`) AS 
          first_start_date,
            ( SELECT    m2.membership_subcategory_id
              FROM      membership AS m2
              WHERE     m2.organisation_id = m.organisation_id
              ORDER BY  m2.`start` DESC
                LIMIT 1
            ) AS 
          last_membership_subcategory_id
FROM      membership AS m
GROUP BY  organisation_id ;

The subquery will be executed as many times as the number of different organizations in the table, so efficiency depends on that. An index on (organisation_id, start, membership_subcategory_id) would help minimizing the subquery execution time.

Using a derived table:

SELECT    gm.organisation_id, 
          gm.first_start_date,
          m.membership_subcategory_id AS last_membership_subcategory_id,
          m.*                           --- whatever other data you want
                                        --- from the latest `start` date
FROM      ( SELECT    organisation_id, 
                      MIN(`start`) AS first_start_date,
                      MAX(`start`) AS last_start_date
            FROM      membership
            GROUP BY  organisation_id 
          ) AS gm 
    JOIN
          membership AS m
              ON  m.organisation_id = gm.organisation_id
              AND m.`start` = gm.last_start_date
  ;