Retrieve group records based on max value

[wireless]

Hello all,

Need a little help as the 100 or so permutations I've tried still have not yielded satisfactory results. We've built a table where arrive time based on offices are being tracked. We've been able to count the number of arrive times by day of week and by office.
What we would like to do is pull up the expected arrive time based on the time, the number of occurrences and the office name.
Here's what I've been able to do so far, but can't figure out how to pull the time based on a single office name and max number of occurrences(or if two entries for the same office are the same to pull the earliest time)

Here's the code:

SELECT First(TimeIn) AS FirstTimeIn, Max(CountOfRecordID) AS MaxCountOfRecordID, Max(Office_Name) AS LocationID
FROM RuntimeAVG
WHERE DayofWeek='Friday' AND CountOfRecordID<>1
GROUP BY TimeIn, Office_Name
ORDER BY Max(Office_Name), Max(CountOfRecordID) DESC , First(TimeIn);

Here's the results so far:

FirstTimeIn MaxCountOfRecordID LocationID
06:30 7 Office 1
06:45 3 Office 1
07:15 7 Office 2


07:00 6 Office 2
07:30 6 Office 2
09:30 2 Office 3
10:00 2 Office 3
07:30 3 Office 4
08:15 3 Office 4
07:45 2 Office 4
08:00 2 Office 4
08:30 2 Office 4
09:15 2 Office 4
10:45 2 Office 4
05:45 5 Office 5
06:00 2 Office 5

Any help is greatly appreciated.

[jzwp11]

Assuming that your original table was named RunTimeAvg with the following fields:

RunTimeAvg
-TimeIN
-DayOfWeek
-Office_Name

I would create the following query:

query name: qryCountOfTimeINWithinDayWithinOffice

SELECT RunTimeAvg.Office_Name, RunTimeAvg.DayOfWeek, RunTimeAvg.TimeIN, Count(RunTimeAvg.TimeIN) AS CountOfTimeIN
FROM RunTimeAvg
GROUP BY RunTimeAvg.Office_Name, RunTimeAvg.DayOfWeek, RunTimeAvg.TimeIN;

Then I would create the following query using the above query that gets earliest (minimum) time (having the greatest count i.e. max count) for each office for each day of the week.

query name: qryFinal
SELECT qryCountOfTimeINWithinDayWithinOffice.Office_Name, qryCountOfTimeINWithinDayWithinOffice.DayOfWeek, Min(qryCountOfTimeINWithinDayWithinOffice.TimeIN) AS MinOfTimeIN, Max(qryCountOfTimeINWithinDayWithinOffice.CountOfT imeIN) AS MaxOfCountOfTimeIN
FROM qryCountOfTimeINWithinDayWithinOffice
GROUP BY qryCountOfTimeINWithinDayWithinOffice.Office_Name, qryCountOfTimeINWithinDayWithinOffice.DayOfWeek;


I've attached a sample database.

[wireless]

First thank you very much for your help!
I should add that the table RuntimeAVG has a field CountOfRecordID which was complied from another table grouping all occurances by office in 15 minute intervals.
To make a little clearer for the above example query, In Office1 I want to pull the name and the 06:30 Time because of the MaxCountOfRecordID has the highest value(already counted occurances), for Office2 The 07:15, for Office3 since the number of MaxCountOfRecordID is the same for the two entries, the 09:30 Time since it occurs first.

I tried both of your queries and get different results because the TimeIN only has one record for each time based on the RuntimeAVG table. I realize I need to play with your queries a little more since a step has already been performed in counting records by time.

Thanks again for your help and look forward to any other thoughts you may have.

[wireless]

Spoke too soon, it does work but I'm trying to determine groupings of Time in 15 minute groups rather than specific Hour/minute. The time is recorded specific to minute then I'm creating the table RuntimeAVG from table Runtimes.
Code:
SELECT Format(DatePart("h",[DateTime]),"00") & ":" & Format(Int(DatePart("n",[DateTime])/15)*15,"00") AS TimeIn, Count(Runtimes.DateTime) AS CountOfRecordID, DeviceName AS Office_Name, DayofWeek INTO RuntimeAVG
FROM Runtimes
WHERE (Runtimes.DayofWeek)<>'Saturday' And (Runtimes.DayofWeek)<>'Sunday'
GROUP BY Format(DatePart("h",[DateTime]),"00") & ":" & Format(Int(DatePart("n",[DateTime])/15)*15,"00"), DeviceName, DayofWeek
ORDER BY DayofWeek, DeviceName;

Your method does indeed work as needed but I need to group into 15 minute clusters as above

[jzwp11]

Can you adapt what I have provided to meet your needs? If not, can you post a stripped down database with some data that does not have any sensitive info?

[wireless]

I'll see what I can pull out, there's about a year's worth of data so it may take some time to strip down the db. I've made a couple of changes to your suggestions, it's getting close thanks to your input.

(DeviceName, and OnTime substituted from the original table for the Office_Name/TimeIn I used as aliases)
Code
SELECT qryCountOfTimeINWithinDayWithinOffice.DeviceName, qryCountOfTimeINWithinDayWithinOffice.DayOfWeek, Min(qryCountOfTimeINWithinDayWithinOffice.OnTime) AS MinOfTimeIN, Max(qryCountOfTimeINWithinDayWithinOffice.CountOfT imeIN) AS MaxOfCountOfTimeIN
FROM qryCountOfTimeINWithinDayWithinOffice
WHERE (((qryCountOfTimeINWithinDayWithinOffice.DayOfWeek )<>'Saturday' And (qryCountOfTimeINWithinDayWithinOffice.DayOfWeek)< >'Sunday' And(qryCountOfTimeINWithinDayWithinOffice.DayOfWee k)='Friday')
GROUP BY qryCountOfTimeINWithinDayWithinOffice.DeviceName, qryCountOfTimeINWithinDayWithinOffice.DayOfWeek
ORDER BY qryCountOfTimeINWithinDayWithinOffice.DayOfWeek;

It's doing great pulling one office name and the max number of occurances by 15 minute groupings. Problem is it is pulling the Counts for the row with the max number of TimeIN but the earliest time in the grouping.
I tried changing the Min(qryCountOfTimeINWithinDayWithinOffice.OnTime) AS MinOfTimeIN to just qryCountOfTimeINWithinDayWithinOffice.OnTime and adding it to the GROUP BY but I receive multiple rows for each Office.
Results:
DeviceName DayOfWeek MinOfTimeIN MaxOfCountOfTimeIN
Office1 Friday 12:30 1
Office2 Friday 06:15 7 'should be 6:30 the TimeIn for Office2 MaxcountofTimeIn;'
Office3 Friday 07:00 7
Office4 Friday 08:00 2
Office5 Friday 06:00 6
Office6 Friday 04:45 7

The others all have the earliest time and not the time associated with the Office# and maxofcount.

[wireless]

Here's the stripped down db

[jzwp11]

I took a look at your database. First, you only need the runtime table, the other 2 tables look to repeat the data from runtime just in different ways that can be accomplished by queries. There is no need to store the same data again and again. I also noticed that within your runtime table you had fields that were not necessary such as day of the week and the TimeON. Both of these values can be extracted from the field called DATETIME using built-in functions.

I built a series of queries that rely on previous queries to finally get to what you are after (as I interpret your posts). I believe the key is the query called qryJoinOfMaxCountBackToGetTimes which joins the times of max count back to the query that has the detailed records that have the time. The two queries join via 3 fields.

I've attached the revised DB