View Null Results in Query

[StuJol]

i have an access 2003 query

SELECT [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData
Table Filtered By 60 Min Trip].State,
DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])) AS
[Date], TimeSerial(Hour([Date/Time*]),Minute([Date/Time*]),0) AS TimePeriod,
Count(*) AS Total
FROM [AlarmData Table Filtered By 60 Min Trip]
GROUP BY [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData
Table Filtered By 60 Min Trip].State,
DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])),
TimeSerial(Hour([Date/Time*]),Minute([Date/Time*]),0)
HAVING ((([AlarmData Table Filtered By 60 Min Trip].[Event Type])="ALARM")
AND (([AlarmData Table Filtered By 60 Min Trip].State)="ACT/UNACK"));

which displays my data as

Event Type State Date TimePeriod Total
ALARM ACT/UNACK 29/12/2009 11:31:00 2
ALARM ACT/UNACK 29/12/2009 11:32:00 1
ALARM ACT/UNACK 29/12/2009 11:33:00 1
ALARM ACT/UNACK 29/12/2009 11:36:00 1
ALARM ACT/UNACK 29/12/2009 11:42:00 1
ALARM ACT/UNACK 29/12/2009 11:47:00 2
ALARM ACT/UNACK 29/12/2009 11:48:00 3
ALARM ACT/UNACK 29/12/2009 11:53:00 1
ALARM ACT/UNACK 29/12/2009 11:57:00 1
ALARM ACT/UNACK 29/12/2009 11:59:00 1

it counts the number of entries per minute. all works well and i have a nice
pivotchart to display data. the problem i have is on the pivot chart, the
time line has 1 minute sections missing, obviously if theres no data it cant
display. can i force the query or pivot chart to display null results so i
get something like..

ALARM ACT/UNACK 29/12/2009 11:53:00 1
ALARM ACT/UNACK 29/12/2009 11:54:00 0
ALARM ACT/UNACK 29/12/2009 11:55:00 0
ALARM ACT/UNACK 29/12/2009 11:56:00 0
ALARM ACT/UNACK 29/12/2009 11:57:00 1
ALARM ACT/UNACK 29/12/2009 11:58:00 0
ALARM ACT/UNACK 29/12/2009 11:59:00 1

thanks to anyone who looks at this..

 

[Marshall Barton]

i have an access 2003 query

SELECT [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData
Table Filtered By 60 Min Trip].State,
DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])) AS
[Date], TimeSerial(Hour([Date/Time*]),Minute([Date/Time*]),0) AS TimePeriod,
Count(*) AS Total
FROM [AlarmData Table Filtered By 60 Min Trip]
GROUP BY [AlarmData Table Filtered By 60 Min Trip].[Event Type], [AlarmData
Table Filtered By 60 Min Trip].State,
DateSerial(Year([Date/Time*]),Month([Date/Time*]),Day([Date/Time*])),
TimeSerial(Hour([Date/Time*]),Minute([Date/Time*]),0)
HAVING ((([AlarmData Table Filtered By 60 Min Trip].[Event Type])="ALARM")
AND (([AlarmData Table Filtered By 60 Min Trip].State)="ACT/UNACK"));

which displays my data as

Event Type State Date TimePeriod Total
ALARM ACT/UNACK 29/12/2009 11:31:00 2
ALARM ACT/UNACK 29/12/2009 11:32:00 1
ALARM ACT/UNACK 29/12/2009 11:33:00 1
ALARM ACT/UNACK 29/12/2009 11:36:00 1
ALARM ACT/UNACK 29/12/2009 11:42:00 1
ALARM ACT/UNACK 29/12/2009 11:47:00 2
ALARM ACT/UNACK 29/12/2009 11:48:00 3
ALARM ACT/UNACK 29/12/2009 11:53:00 1
ALARM ACT/UNACK 29/12/2009 11:57:00 1
ALARM ACT/UNACK 29/12/2009 11:59:00 1

it counts the number of entries per minute. all works well and i have a nice
pivotchart to display data. the problem i have is on the pivot chart, the
time line has 1 minute sections missing, obviously if theres no data it cant
display. can i force the query or pivot chart to display null results so i
get something like..

ALARM ACT/UNACK 29/12/2009 11:53:00 1
ALARM ACT/UNACK 29/12/2009 11:54:00 0
ALARM ACT/UNACK 29/12/2009 11:55:00 0
ALARM ACT/UNACK 29/12/2009 11:56:00 0
ALARM ACT/UNACK 29/12/2009 11:57:00 1
ALARM ACT/UNACK 29/12/2009 11:58:00 0
ALARM ACT/UNACK 29/12/2009 11:59:00 1

In order to display data that does not exist, you need
another table with a record for each item that does not have
a data record in your table.

That means that you would have to create a table with at
least one column with the time for each minute in the day.
Then you can use an outer join om the minutes field from
this new table to the above query. I don't see how you can
create the Event Type or State values out of thin air though
unless you put them into the new table too.