databasedev.co.uk - database solutions and downloads for microsoft access

Using the MAX() function in Microsoft Access:

The SQL Max( ) function retrieves the largest value in a specified column of a table or query. This can be useful when you need a fast way to find specific data in your database. For example, you may need to find the most recent Order (by Order Date) placed by a Customer from the Orders table.

If we have the following Microsoft Access tables:

tblCustomers

lngCustomerID	strCustomerFirstName	strCustomerLastName
1	Bill	Smith
2	Jennifer	Lee
3	Danny	Jones
4	Tracy	Robins

tblOrders

lngOrderID	lngCustomerID	strProductName	intOrderQuantity	dteOrderDate
1	1	Access 2000 Bible	2	01/01/2004
2	1	Excel 2002 Bible	1	10/05/2004
3	2	Excel 2002 Bible	1	21/08/2004
4	2	Word 2000 For Beginners	2	07/09/2004
5	3	Active Server Pages 3.0	4	15/05/2004
6	3	PowerPoint 97 Bible	1	18/06/2000
7	3	Database Design for Mere Mortals	5	26/03/2004
8	4	HTML for Beginners	1	27/08/2003
9	4	CCS Bible	2	10/03/2004

As you can see, One Customer can place Many Orders. Therefore each customer may have many OrderDates.

With our scenario - "For example, you may need to find the most recent Order (by Order Date) placed by a Customer from the Orders table"

What we will initially need to do is to create a query that contains the Orders table, to allow us to Group the Orders By Customer and to find the Max(OrderDate) per Customer. The following shows the query design which uses the Totals Query Option:

The Totals query used to find the Latest (Max) Order Date by Customer

We have Grouped this query by CustomerID and have used the Max() function to find the latest OrderDate:

SELECT tblOrders.lngCustomerID, Max(tblOrders.dteOrderDate) AS MaxOfdteOrderDate
FROM tblOrders
GROUP BY tblOrders.lngCustomerID;

What this gives us are the results below. It shows the latest OrderDate of each individual Customer:

The results of finding the latest OrderDate for each individual Customer using the Max() function

Now to enable us to find out the name of each Customer and their latest OrderDate, we need to create a further query that uses the Customer table and the query that we have created above. The design of the new query is shown below:

Using the Max() query as part of the new query to find the Customer's latest OrderDate

The query joins the Customers table with the query (qryMaxOrderDate) using the lngCustomerID field that is contained in both recordsets.

The SQL of the query looks like the following:

SELECT tblCustomers.lngCustomerID, tblCustomers.strCustomerFirstName,
tblCustomers.strCustomerLastName, qryMaxOrderDate.MaxOfdteOrderDate
FROM tblCustomers INNER JOIN qryMaxOrderDate ON
tblCustomers.lngCustomerID = qryMaxOrderDate.lngCustomerID;

This will now give us the Customer details and show the date of the latest Order that they placed as shown in the query results below:

The query that joins the Customer information with the query that finds the latest OrderDate

This process, using the Max() function or alternatively, the Min() function, can be used to find the largest or smallest numbers (for example when creating Order Numbers) or for finding the largest or smallest Order Totals.