Sum Query on Date and Time Fields

  • Thread starter Thread starter Pat
  • Start date Start date
P

Pat

I am trying to do the following:

I have a query that produces all my work orders for the next 7 days.
Sorted on the date.

This table contains WorkDate, WorkTime, NumMen, NumTrucks ...
I am attempting to get a summary report for the dispatch desk so he
will know how many men and trucks he will need at what time in the
next 7 days. I need to total the men and trucks by the time an date.

Records
12/02/03 08:00 AM 8 2
12/02/03 08:00 AM 1 1
12/02/03 08:00 AM 3 2
12/02/03 09:00 AM 2 1
12/05/03 07:00 AM 5 1
12/05/03 07:00 AM 6 2
12/05/03 07:00 AM 4 3
12/07/03 09:00 AM 10 2
12/07/03 09:00 AM 14 3
12/07/03 09:30 AM 7 2

I am trying to get this:
Date Time TotalMenNeeded TotalTrucksNeeded
12/02/03 08:00 AM 12 5
09:00 AM 2 1

12/05/03 07:00 AM 15 6

12/07/03 09:00 AM 24 5
09:30 AM 7 2

Any help would be appreciated.
 
Looks like a simple totals query to me.

SELECT WorkDate, WorkTime, Sum(NumMen) as CountMen, Sum(NumTrucks) as CountTrucks
FROM YourTableName
WHERE WorkDate Between Date() and Date()+7
GROUP BY WorkDate, WorkTime

That will return the data, if you want is printed nicely, you will need to use a
report to do so.

The query will NOT hide repeating dates and give you blank lines between the dates
 
Back
Top