Query for time intervals 1

[ptrifile]

I have a table that holds the fields [type], [DayOfWeek] and [interval]. I have a query that groups on each of those fields. This all works fine but sometimes there is no data in the table for a particular 15 minute interval, there was just no order for that particular time period. What I would like to accomplish is if there is a missing interval have it added to the query with a count of 0, or blank and I can add it in excel or something later after export.

I would like to show all 96 intervals for each type and day of week. Here is an example but for simplicity sake, lets say i was only querying 1 particular hour:

Current query results

type1 Monday 1:00 5
type1 Monday 1:15 3
type1 Monday 1:30 8
type2 tuesday 1:00 8
type2 tuesday 1:15 1
type2 tuesday 1:45 5

Desired Results

type1 Monday 1:00 5
type1 Monday 1:15 3
type1 Monday 1:30 8
type1 Monday 1:45 0
type2 tuesday 1:00 8
type2 tuesday 1:15 1
type2 tuesday 1:30 0
type2 tuesday 1:45 5

So in summary, need to add any missing interval for type and day for an entire 24 hour period.

I hope this made some sort of sense to someone. Any help or suggestions on where to start would be appreciated.

Paul

 

[dhookom]

I would create a table of 96 records with each interval time and use it with an outer join to your current query results.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ptrifile]

Brilliant! Thanks, Duane!