Query help

  • Thread starter Thread starter jpBless
  • Start date Start date
J

jpBless

TimeEntered Amount

55.44 6.20am

60.99 6.25am

70.99 6.26AM

40.99 7.10AM

100.20 7.50AM

100.50 8.00AM

120.35 8.25AM

100.00 8.56AM

120.25 8.59AM

100.55 10.00AM

20.22 10.41AM



SQL Query to return 2 fields (time range and total)

TimeEntered Amt

6am-7.59AM 335.61

8am-9.59AM 441.11

10Am-11.59 120.77
 
First create a table TimeRanges:

TimeFrom TimeTo
06:00 07:59
08:00 09:59
10:00 11:59

Then join this to your main table in a query, group by the from and to times
and sum the amount:

SELECT TimeFrom, TimeTo,
SUM(Amount) AS TotalAmount
FROM TimeRanges INNER JOIN YourTable
ON (YourTable.TimeEntered BETWEEN
TimeRanges.TimeFrom AND TimeRanges.TimeTo)
GROUP BY TimeFrom, TimeTo;

Ken Sheridan
Stafford, England
 
Having just seen your other post, it looks like the TimeEntered values
contain differing dates. The values in the TimeRanges table would all have a
date element of 30 December 1899, which is 'day zero' in Access's date/time
implementation, so you'd need to force all your values to this date by means
of the TimeValue function:

SELECT TimeFrom, TimeTo,
SUM(Amount) AS TotalAmount
FROM TimeRanges INNER JOIN YourTable
ON (TIMEVALUE(YourTable.TimeEntered) BETWEEN
TimeRanges.TimeFrom AND TimeRanges.TimeTo)
GROUP BY TimeFrom, TimeTo;

Ken Sheridan
Stafford, England
 
Back
Top