Group by Time

[Qaspec]

I have a table (tblRecords) with specific times (long time) for each record.
From a query I need to group the resulting records by hour military time
(0600, 0700, 0800....)

I've created another table named tblTime with 3 colums

Hour Start End
0600 6:00:00 AM 6:59:59 AM
0700 7:00:00 AM 7:59:59 AM
0800

and so on...

How do I tie the 2 tables together to have each record from tblRecords
assigned a Military hour?

 

[John Spencer]

You could just use the Hour function and not worry about the auxiliary table.

SELECT Hour(TimeField), Count(somefield) as countOfSomeField
FROM tblRecords
GROUP BY Hour(TimeField)

If you really feel that you have to do it with the tables joined then you can
only do this in SQL view with a non-equi (not equal) join.

SELECT tblRecords.*, tblTime.Hour
FROM tblRecords INNER JOIN tblTime
ON tblRecords.TheTime >= Start and tblRecords.TheTime <= End

You could use a cartesian join and use criteria in the where clause as an
alternative method.

SELECT tblRecords.*, tblTime.Hour
FROM tblRecords , tblTime
WHERE tblRecords.TheTime >= Start and tblRecords.TheTime <= End

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I have a table (tblRecords) with specific times (long time) for each record.
From a query I need to group the resulting records by hour military time
(0600, 0700, 0800....)

I've created another table named tblTime with 3 colums

Hour Start End
0600 6:00:00 AM 6:59:59 AM
0700 7:00:00 AM 7:59:59 AM
0800

and so on...

How do I tie the 2 tables together to have each record from tblRecords
assigned a Military hour?

SELECT tblHours.MilHour, tblRecords.*
FROM tblHours LEFT JOIN tblRecords
ON tblRecords.timefield >= tblHours.Start
And tblRecords.timefield <= tblHours.End

If you do not want to see hours where there are no records
in tblRecords, change the LEFT JOIN to INNER JOIN