Any help would be greatly appreciated.

[gusc999]

Good Afternoon,

I’m trying to figure out how to create query that will group the times by half hour increment and count them by those increments, in the [RHPRTT] field (military time). The query statement below was the one I used to generate this query below.

Any help would be greatly appreciated.

SELECT PIRF_TRKEXTF.RHRTE, PIRF_TRKEXTF.RHIMPD, PIRF_TRKEXTF.RHIMPT, PIRF_TRKEXTF.RHPRTD, PIRF_TRKEXTF.RHPRTT, PIRF_TRKEXTF.TRUSER
FROM PIRF_TRKEXTF
WHERE (((PIRF_TRKEXTF.RHPRTD)=[20080131]));

RHRTE RHIMPD RHIMPT RHPRTD RHPRTT
301 20080304 164750 20080304 165808
302 20080304 164750 20080304 165829
303 20080304 164750 20080304 165855
304 20080304 164750 20080304 170414
305 20080304 171622 20080304 171948
306 20080304 171622 20080304 172041
307 20080304 174345 20080304 174640
308 20080304 174345 20080304 174810
309 20080304 174345 20080304 174828
310 20080304 174345 20080304 174904
311 20080304 181956 20080304 182658
312 20080304 181956 20080304 183114
313 20080304 181956 20080304 183216
314 20080304 181956 20080304 183331
315 20080304 181956 20080304 183745
316 20080304 181956 20080304 184117
317 20080304 182536 20080304 184250
318 20080304 182536 20080304 184929
319 20080304 183705 20080304 185009
320 20080304 183705 20080304 185103

 

[dhookom]

Please provide just the column containing the values you want to convert and the values to convert to.

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

I am sure there is an easier way:

SELECT Int([RHPRTT]/10000)+((Int(Right([RHPRTT],4)/1000)>=3)*-5)/10 AS HalfHour, Count(Int([RHPRTT]/10000)+((Int(Right([RHPRTT],4)/1000)>=3)*-5)/10) AS CountH
FROM t
GROUP BY Int([RHPRTT]/10000)+((Int(Right([RHPRTT],4)/1000)>=3)*-5)/10;

 

[gusc999]

Is the "Int" supposed to be substituted for a field name?
I ran the sql statement but it didn't work...

 

[gusc999]

Dhookom,

The field that I'm addressing is the "RHPRTT" as far as the values I'm not sure...

 

[Remou]

In what way did it not work?

 

[dhookom]

gusc999,
Just give us two column of sample data including the current value and the new calculated value. Should the "half hour increment" round up/down or truncate?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If this is a one time shot then Remou's approach is the way to go. If you are manipulating these intervals a lot you may want to consider making an interval table. It will provide a lot of flexibility, but it will require you to populate the 48 rows.

tblIncrements
  strDescription
  dtmStart
  dtmEnd
  lngStart
  lngEnd
strDescription	dtmStart	dtmEnd	lngStart	lngEnd
1200 - 1229	12:00:00 PM	12:29:00 PM	120000	122900
1600 - 1630	4:00:00 PM	4:29:59 PM	160000	162959
1630 - 1700	4:30:00 PM	4:59:59 PM	163000	165959
1700 - 1730	5:00:00 PM	5:29:59 PM	170000	172959
1730 - 1800	5:30:00 PM	5:59:59 PM	173000	175959

[code]

then I can make a lot of easy queries
[code]
SELECT tblTime.RHPRTT, tblIncrement.strDescription
FROM tblTime, tblIncrement
WHERE (((tblTime.RHPRTT)>=[lngStart] And (tblTime.RHPRTT)<=[lngEnd]));

and my output
RHPRTT	strDescription
160100	1600 - 1630
160200	1600 - 1630
160300	1600 - 1630
163001	1630 - 1700
164500	1630 - 1700
170101	1700 - 1730
170200	1700 - 1730
171500	1700 - 1730

or

SELECT tblIncrement.strDescription, Count(tblIncrement.strDescription) AS CountOfstrDescription
FROM tblTime, tblIncrement
WHERE (((tblTime.RHPRTT)>=[lngStart] And (tblTime.RHPRTT)<=[lngEnd]))
GROUP BY tblIncrement.strDescription;

and output

strDescription	CountOfstrDescription
1600 - 1630	3
1630 - 1700	2
1700 - 1730	3

I say this from experience and this approach may save a lot of headaches if you are doing a lot of interval manipulation.