If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
View Null Results in Query
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.. |
#2
|
|||
|
|||
View Null Results in Query
StuJol wrote:
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. -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|