Counting values in 1 hour time bins

[Squinado]

I’m using Access 2002.

I’m trying to create a query that will COUNT the number of values within 1
hour time bins (e.g. 00:00 to 00:59, 01:00 to 01:59 etc). The catch is that
I need to have every hour bin represented whether there is any value in it or
not. I believe that I need to create a separate table of the bins that I
want (include bin start-value & stop-values) and then bring this table into
my query and join it to the original table.

I was able to construct the make table query below to COUNT the values in
hour bins for which there were records, but I also need rows for all of the
zero values;

SELECT [All NWHI Detections].Species, [All NWHI Detections].Transmitter,
[All NWHI Detections].Island, [All NWHI Detections].Location,
DatePart("yyyy",[All NWHI Detections.Date]) AS [Year], DatePart("m",[All NWHI
Detections.Date]) AS [Month], [All NWHI Detections].Date, [All NWHI
Detections.Date]-[SharkTaggingSummaryTable.DateDeployed] AS
[TimeSinceTagged(days)], CDate2Julian([All NWHI Detections.Date]) AS [Julian
Date], DatePart("h",[All NWHI Detections.Time]) AS HourBin, Count([All NWHI
Detections].Time) AS TotalNumberofDetections INTO [GLM HourlyBins]
FROM SharkTaggingSummaryTable INNER JOIN [All NWHI Detections] ON
SharkTaggingSummaryTable.Transmitter = [All NWHI Detections].Transmitter
GROUP BY [All NWHI Detections].Species, [All NWHI Detections].Transmitter,
[All NWHI Detections].Island, [All NWHI Detections].Location,
DatePart("yyyy",[All NWHI Detections.Date]), DatePart("m",[All NWHI
Detections.Date]), [All NWHI Detections].Date, [All NWHI
Detections.Date]-[SharkTaggingSummaryTable.DateDeployed], CDate2Julian([All
NWHI Detections.Date]), DatePart("h",[All NWHI Detections.Time])
HAVING ((([All NWHI Detections].Species)="tiger"))
ORDER BY [All NWHI Detections].Transmitter;


Q1? Creating the hour bins table

This table will be very large because it will need to have 4 years worth of
hour bins for each transmitter (N=15) and location (N=12). This results in
6,307,200 hour bins! How can I write a query to create this table, or is
there a better way of doing this?

Q2? Bringing the hour bins table into the main query.

How do I include the hour bins table in the main query to get my final result.


Any help would be greatly appreciated! Thank you.