Counting occurrances in a chart

[hotplate]

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

 

[Guest]

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.

 

[hotplate]

Do you mean a table with every possible date from here until forever?
or 365 month day combinations? I am confused.

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

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

 

[Guest]

You ignored my question <quote>Do 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

Do you mean a table with every possible date from here until forever?
or 365 month day combinations? I am confused.

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

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

 

[hotplate]

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

You ignored my question <quote>Do 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

Do you mean a table with every possible date from here until forever?
or 365 month day combinations? I am confused.

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


:

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

 

[Guest]

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

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

You ignored my question <quote>Do 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

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


:

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