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
|
|||
|
|||
Counting occurrances in a chart
Hi,
I am making a chart using a trend line format. I am counting the number of occurrances per day. Days with no occurrances should give me a zero, but instead it is like it has no value instead. How do make the graph put a zero for the value instead of nothing? Here is the query for the graph: SELECT (Format([DateInsp],"Short Date")) AS Expr1, Count(qryLineChart.ID) AS CountOfID FROM qryLineChart GROUP BY (Format([DateInsp],"Short Date")), (Int([DateInsp])) ORDER BY (Int([DateInsp])); James |
#2
|
|||
|
|||
Counting occurrances in a chart
Do the dates even show up? If not, you must have a table of all possible
dates (or query) with a LEFT or RIGHT JOIN to display all the dates between a range. Use Nz() with your count to return a 0 if necessary. -- Duane Hookom Microsoft Access MVP "hotplate" wrote: Hi, I am making a chart using a trend line format. I am counting the number of occurrances per day. Days with no occurrances should give me a zero, but instead it is like it has no value instead. How do make the graph put a zero for the value instead of nothing? Here is the query for the graph: SELECT (Format([DateInsp],"Short Date")) AS Expr1, Count(qryLineChart.ID) AS CountOfID FROM qryLineChart GROUP BY (Format([DateInsp],"Short Date")), (Int([DateInsp])) ORDER BY (Int([DateInsp])); James |
#3
|
|||
|
|||
Counting occurrances in a chart
Do you mean a table with every possible date from here until forever?
or 365 month day combinations? I am confused. Duane Hookom wrote: Do the dates even show up? If not, you must have a table of all possible dates (or query) with a LEFT or RIGHT JOIN to display all the dates between a range. Use Nz() with your count to return a 0 if necessary. -- Duane Hookom Microsoft Access MVP "hotplate" wrote: Hi, I am making a chart using a trend line format. I am counting the number of occurrances per day. Days with no occurrances should give me a zero, but instead it is like it has no value instead. How do make the graph put a zero for the value instead of nothing? Here is the query for the graph: SELECT (Format([DateInsp],"Short Date")) AS Expr1, Count(qryLineChart.ID) AS CountOfID FROM qryLineChart GROUP BY (Format([DateInsp],"Short Date")), (Int([DateInsp])) ORDER BY (Int([DateInsp])); James |
#4
|
|||
|
|||
Counting occurrances in a chart
You ignored my question quoteDo the dates even show up?/quote. Does your
chart use every possible date from here until forever? Notice I also suggested using a query. For instance if you have a table [tblNums] with a single numeric field [Num] and values 0 - 9, you can create a query that returns all dates from today for 1000 days with this SQL: SELECT [tblNums].[Num]*100+[tblNums_1].[Num]*10+[tblNums_2].[Num]+Date() AS TheDate FROM tblNums, tblNums AS tblNums_1, tblNums AS tblNums_2 ORDER BY 1; -- Duane Hookom Microsoft Access MVP "hotplate" wrote: Do you mean a table with every possible date from here until forever? or 365 month day combinations? I am confused. Duane Hookom wrote: Do the dates even show up? If not, you must have a table of all possible dates (or query) with a LEFT or RIGHT JOIN to display all the dates between a range. Use Nz() with your count to return a 0 if necessary. -- Duane Hookom Microsoft Access MVP "hotplate" wrote: Hi, I am making a chart using a trend line format. I am counting the number of occurrances per day. Days with no occurrances should give me a zero, but instead it is like it has no value instead. How do make the graph put a zero for the value instead of nothing? Here is the query for the graph: SELECT (Format([DateInsp],"Short Date")) AS Expr1, Count(qryLineChart.ID) AS CountOfID FROM qryLineChart GROUP BY (Format([DateInsp],"Short Date")), (Int([DateInsp])) ORDER BY (Int([DateInsp])); James |
#5
|
|||
|
|||
Counting occurrances in a chart
Thanks for your reply,
My graph will start on the earliest date in the month with a record, and end on the lastest day in the month with a record and all the days in between will show up, however I would like all the days to show up in the month starting from 1 even if there is no record for that day. I am probably going to have to create a table with possible dates as you suggested. This database may be used for a very long time (the one I replaced was in use 10 years). James Duane Hookom wrote: You ignored my question quoteDo the dates even show up?/quote. Does your chart use every possible date from here until forever? Notice I also suggested using a query. For instance if you have a table [tblNums] with a single numeric field [Num] and values 0 - 9, you can create a query that returns all dates from today for 1000 days with this SQL: SELECT [tblNums].[Num]*100+[tblNums_1].[Num]*10+[tblNums_2].[Num]+Date() AS TheDate FROM tblNums, tblNums AS tblNums_1, tblNums AS tblNums_2 ORDER BY 1; -- Duane Hookom Microsoft Access MVP "hotplate" wrote: Do you mean a table with every possible date from here until forever? or 365 month day combinations? I am confused. Duane Hookom wrote: Do the dates even show up? If not, you must have a table of all possible dates (or query) with a LEFT or RIGHT JOIN to display all the dates between a range. Use Nz() with your count to return a 0 if necessary. -- Duane Hookom Microsoft Access MVP "hotplate" wrote: Hi, I am making a chart using a trend line format. I am counting the number of occurrances per day. Days with no occurrances should give me a zero, but instead it is like it has no value instead. How do make the graph put a zero for the value instead of nothing? Here is the query for the graph: SELECT (Format([DateInsp],"Short Date")) AS Expr1, Count(qryLineChart.ID) AS CountOfID FROM qryLineChart GROUP BY (Format([DateInsp],"Short Date")), (Int([DateInsp])) ORDER BY (Int([DateInsp])); James |
#6
|
|||
|
|||
Counting occurrances in a chart
You can either create the actual table of dates or use the Cartesian query
method that I described previously. You could add the table tblNums in to the query again to get 10000 dates. -- Duane Hookom Microsoft Access MVP "hotplate" wrote: Thanks for your reply, My graph will start on the earliest date in the month with a record, and end on the lastest day in the month with a record and all the days in between will show up, however I would like all the days to show up in the month starting from 1 even if there is no record for that day. I am probably going to have to create a table with possible dates as you suggested. This database may be used for a very long time (the one I replaced was in use 10 years). James Duane Hookom wrote: You ignored my question quoteDo the dates even show up?/quote. Does your chart use every possible date from here until forever? Notice I also suggested using a query. For instance if you have a table [tblNums] with a single numeric field [Num] and values 0 - 9, you can create a query that returns all dates from today for 1000 days with this SQL: SELECT [tblNums].[Num]*100+[tblNums_1].[Num]*10+[tblNums_2].[Num]+Date() AS TheDate FROM tblNums, tblNums AS tblNums_1, tblNums AS tblNums_2 ORDER BY 1; -- Duane Hookom Microsoft Access MVP "hotplate" wrote: Do you mean a table with every possible date from here until forever? or 365 month day combinations? I am confused. Duane Hookom wrote: Do the dates even show up? If not, you must have a table of all possible dates (or query) with a LEFT or RIGHT JOIN to display all the dates between a range. Use Nz() with your count to return a 0 if necessary. -- Duane Hookom Microsoft Access MVP "hotplate" wrote: Hi, I am making a chart using a trend line format. I am counting the number of occurrances per day. Days with no occurrances should give me a zero, but instead it is like it has no value instead. How do make the graph put a zero for the value instead of nothing? Here is the query for the graph: SELECT (Format([DateInsp],"Short Date")) AS Expr1, Count(qryLineChart.ID) AS CountOfID FROM qryLineChart GROUP BY (Format([DateInsp],"Short Date")), (Int([DateInsp])) ORDER BY (Int([DateInsp])); James |
Thread Tools | |
Display Modes | |
|
|