Ms-access – Returning 1 Max Value when Some Counts Are Equal in Access

maxms access

I'm building a database that needs to return peak traffic data for Month-over-Month and Year-over-Year Comparisons in Access. We are trying to determine what our busiest Monday is, Tuesday, Wednesday, and so on in each month. The date needs to be attached to the return value. It should look like this. 

Sep     
Date    DOW Data
9/12    Mon 995
9/13    Tue 982
9/14    Wed 986
9/1     Thu 1004
9/2     Fri 1008
9/17    Sat 750
9/18    Sun 954

I'm trying to run a SQL query that counts all of the individual records for a day, sorts by Day of Week, finds the max, and then returns that value. We are running into two major problems though. I can get it to look like the table above without the Date attached by running this query.

SELECT DOW, MAX(Counts) AS Max
FROM
(
SELECT DOW, Start, Count(*) AS Counts
FROM Outbound
GROUP BY DOW, START
)
GROUP BY DOW;

*"DOW" is a number value signifying the day (1=Monday, etc…) and "Start"is the actual date.

It returns the below table.

DOW Max
1   995
2   982
3   986
4   1004
5   1009
6   750
7   954

This has everything I need but that date. However, the 'Max' Value is not unique, so I can't relate it back to an earlier table to get a date associated with this count, because there may be several that have that same value. I also can't use DISTINCT because the 'Start' Value is different on every record. We only need to return one date if there are several with the max value, because this is used more for MOM and YOY comparison, so inter-month comparison isn't important.

To sum it up, we can either get the right data, but without the date, or get the date, but too much data.

Any ideas?

Best Answer

When you join this result set (as a subquery?) back to the main table, why don't you do another GROUP BY and get the MAX(Start)?

Select MaxCount.DOW As DayNumberOfWeek, Max(Date) As LatestDate, MaxCount.Max
From
  (
  SELECT DOW, MAX(Counts) AS Max
  FROM
    (
    SELECT DOW, Start, Count(*) AS Counts
    FROM Outbound
    GROUP BY DOW, Start
    )
  GROUP BY DOW
  ) As MaxCount
  Inner Join 
    (
    SELECT DOW, Start, Count(*) As Counts
    FROM Outbound
    GROUP BY DOW, Start
    ) As DailyCount On MaxCount.DOW = DailyCount.DOW And MaxCount.Max = DailyCount.Counts
Group By MaxCount.DOW, MaxCount.Max;

Apologies if I made some syntax errors, I don't have Access handy right now.

Related Question