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.