Aggregate Query Question

  • Thread starter Thread starter Marcin Peciak
  • Start date Start date
M

Marcin Peciak

Hi,

I have a table called "tblOrders", with has following field (example):

Order_Num | Created_Date | Customer_ID |
-----------------------------------------------------
1-22132 | 02/05/2009 | 1-AX23 |

....

I've added additional column to calculate number of days between
"Created_Date" and today, so I have a query that does something like this:

Counter: DateDiff("d", Created_Date, Date())

What I need is a select those rows from the table where Counter is min/max -
so, if I have two (or more) same records for the customer, I want to pick up
the most recent one (where Counter is min) or the oldest one - where Counter
is max.

When I tried to build queries, I was getting error messages saying that I
cannot aggragete min/max function.

Any suggestions?

Many thanks,
Marcin
 
You lost me on the Min/Max. I guess you both the Min and Max.
Use two queries unless you know subqueries.
Qry_Min_Max --
SELECT Customer_ID, Min([Created_Date]) AS MinDate, Max([Created_Date]) AS
MaxDate
FROM tblOrders
GROUP BY Customer_ID;

SELECT Customer_ID, [Created_Date], Order_Num
FROM tblOrders INNER JOIN Qry_Min_Max ON tblOrders.Customer_ID =
Qry_Min_Max.Customer_ID
WHERE [Created_Date] = [MinDate] OR [Created_Date] = [MaxDate];
 
Back
Top