Count function of a Min function

[MTSPEER]

Hello,

I want to find how many days there are of the oldest date in my table. The table name is tbl_Queue and the field name is Contract_Date. I'm not sure how to do this. I tried in the query design view for the field name:

Count(Min(Format([tbl_Queue].[Contract_Date], "mm/dd/yyyy")))

But it didn't work. Your help would be appreciated. Thanks

[pbaldy]

Shot in the dark:

SELECT TOP 1 [Contract_Date], Count(*) As HowMany
FROM [tbl_Queue]
GROUP BY [Contract_Date]
ORDER BY [Contract_Date]

[MTSPEER]

I'm trying to create this in query design view and I want to get the latest date of each of each building which is a field..and how many times that date is in there for each building

[JoeM]

Try this:

SELECT tbl_Queue.Building, Max(tbl_Queue.Contract_Date) AS Max_Date, Count(tbl_Queue.Contract_Date) AS Total_Count
FROM tbl_Queue
INNER JOIN
(SELECT tbl_Queue.Building, Max(tbl_Queue.Contract_Date) AS Max_Date
FROM tbl_Queue
GROUP BY tbl_Queue.Building) as tmp
ON tbl_Queue.Building=tmp.Building AND tbl_Queue.Contract_Date=tmp.Max_Date
GROUP BY tbl_Queue.Building;