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 2 1 Excel 2002 Bible 1 3 2 Excel 2002 Bible 1 4 2 Word 2000 For Beginners 2 5 3 Active Server Pages 3.0 4 6 3 PowerPoint 97 Bible 1 7 3 Database Design for Mere Mortals 5 8 4 HTML for Beginners 1 9 4 CCS Bible 2 As you can see, One Customer can place Many Orders. Therefore each customer may have many OrderDates.

Jun 7, 2017 - UtterAccess is the premier Microsoft Access wiki and help forum. Have questions or feedback about Office VBA or this documentation? This MSAccess tutorial explains how to use the Access Avg function with syntax and examples. The Microsoft Access Avg function returns the average.

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. Foto payudara gambar payudara. 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.