Query to Show oldest and newest date (1 Viewer)

[Number11]

Hi,

So i need to bring back the results to show the oldest order and the newest order date with a count on total orders for a customer fields i wish to include are:


Customer_ID
Order Date

Need to show:

Customer_ID ¦ Count Of Orders ¦ First Order Date ¦ Last Order Date
123 ¦ 4 ¦ 01/04/2022 ¦ 01/01/2023

would love some help

 

[XPS35]

Create an aggregate query. Look here for more info.

 

[CJ_London]

in an aggregate query use min and max rather than last and first

 

[The_Doc_Man]

Look up aggregate queries, which allows you to use the MIN, MAX, and COUNT functions in the same query. HOWEVER, one question is whether the dates are in true "Date" format, or are they date-strings, i.e. TEXT mode... because the MIN and MAX (oldest and youngest) functions will do the wrong thing if the dates are text fields. A couple of words of wisdom: Long term, you do less typing if you don't embed spaces in field names - because not only do you save the Spacebar keystroke, but you won't need the [] around the names. And don't be afraid to use a query as a data source. Queries are your friends.

If everything you need is in your Orders table, it MIGHT look like this:

SELECT CustID, COUNT( * ) As OrderCount, MIN( [Order Date] ) As EarliestOrder, MAX( [Order Date] ) As LatestOrder
FROM Orders GROUP BY CustID ;

If you create this query as a permanent name then you can use the query later to look up the information you want. That lookup could take the form of a JOIN (recommended) or in VBA code, a DLookup with the CustID in the criteria text. Using the JOIN would be an example of layered queries, which are perfectly legal with Access.