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:
lngCustomerID | strCustomerFirstName | strCustomerLastName |
---|---|---|
1 | Bill | Smith |
2 | Jennifer | Lee |
3 | Danny | Jones |
4 | Tracy | Robins |
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.