Combining nested Access Queries into Single SQL Statement

I am trying to group multiple nested Access Queries into a single SQL statement for returning data. When I first built this tracking database, I was not familiar with SQL at all, so I built hard queries into Access. I am rebuilding a lot of VBA to streamline and I would prefer to use SQL in most instances instead of the saved queries. I have been able to do most things, but this problem is particularly difficult for me.

The database records hours of usage on a piece of equipment, coded for the type of usage and serialized by the equipment serial, into a "Raw_Data" table. I need to add up all of the hours on each day, for each serial number, then get the most hours put into any piece of equipment for that day, and add all of those together for a period of time (month, week, etc.). Previously, I had done this with a series of five (5) saved queries and just pulled from the top query.

The five SQL statements are below, from lowest, to highest:

RunHoursInit

SELECT Raw_Data.Serial, Raw_Data.LogDate, Sum(Raw_Data.Hrs) AS SumOfHrs, Raw_Data.UsageCode
FROM Raw_Data
GROUP BY Raw_Data.Serial, Raw_Data.LogDate, Raw_Data.UsageCode
HAVING (((Raw_Data.UsageCode)='1' Or (Raw_Data.UsageCode)='2' Or (Raw_Data.UsageCode)='3'
    Or (Raw_Data.UsageCode)='4' Or (Raw_Data.UsageCode)='5' Or (Raw_Data.UsageCode)='6'
    Or (Raw_Data.UsageCode)='7' Or (Raw_Data.UsageCode)='EMPTY'));

RunHours

SELECT RunHoursInit.Serial, RunHoursInit.LogDate, Sum(RunHoursInit.SumOfHrs) AS SumOfHrs
FROM RunHoursInit
GROUP BY RunHoursInit.Serial, RunHoursInit.LogDate;

RunHoursDay

SELECT RunHours.LogDate, Max(RunHours.SumOfHrs) AS MaxOfSumOfHrs
FROM RunHours
GROUP BY RunHours.LogDate;

RunHoursRange

SELECT RunHoursDay.LogDate, Sum(RunHoursDay.MaxOfSumOfHrs) AS SumOfMaxOfSumOfHrs
FROM RunHoursDay
GROUP BY RunHoursDay.LogDate
HAVING (((RunHoursDay.LogDate) Between PubStartDate() And PubEndDate()));

RunHoursRangeTotal

SELECT Sum(RunHoursRange.SumOfMaxOfSumOfHrs) AS TotalHrs
FROM RunHoursRange;

I have tried multiple SQL statements to try to get to this same level but have been unable to get it to sum up correctly. Nesting sums has been difficult, and I am not sure I am doing it right, or if it is even the best way to go about it. Should I stick with the saved queries? Or should I keep working to use SQL to improve the operation of the database?

Any help anyone can provide would be much appreciated!

Solution

If you want nested SQL, consider:

SELECT Sum(SumOfMaxOfSumOfHrs) AS TotalHrs FROM 
     (SELECT LogDate, Sum(MaxOfSumOfHrs) AS SumOfMaxOfSumOfHrs FROM 
          (SELECT LogDate, Max(SumOfHrs) AS MaxOfSumOfHrs FROM 
                (SELECT Serial, LogDate, Sum(SumOfHrs) AS SumOfHrs FROM 
                      (SELECT Serial, LogDate, Sum(Hrs) AS SumOfHrs, UsageCode FROMRaw_Data 
                       GROUP BY Serial, LogDate, UsageCode
                       HAVING UsageCode IN('1','2','3','4','5','6','7','EMPTY')) AS RunHoursInit 
                 GROUP BY Serial, LogDate) AS RunHours 
           GROUP BY LogDate) AS RunHoursDay 
      GROUP BY LogDate
      HAVING LogDate Between PubStartDate() And PubEndDate()) AS RunHoursRange;

Note use of IN() instead of multiple OR operators.
Quite possibly the two HAVING clauses should be WHERE clauses to filter records before aggregating. I am not sure it would make any difference in this case.