Showing volume over time

  • Thread starter Thread starter Jill
  • Start date Start date
J

Jill

I have been trying to make a graph to chart the number of
people cancelling a service each month (and have all the
months of the year appear on the x-axis even if there
are "0" cancellations). The problem I have run into is
that some months I have no response and on the chart the
months on the x-axis will jump from from January to March
becuase there is nothing in February. How can I play with
the query for this chart to make it show all the months of
the year and the volume of reponse, even when there are
none. Below is what I have in the field listing from the
query...

1) (Year([Cancellation Date])*12+Month([Cancellation
Date])-1)
2) Date of Cancellation: (Format([Cancellation
3) Date],"mmm"" '""yy"))
4) [Product/Service]
Count: Nz((Count([Product/Service])),0)
 
This worked for me:
Create a table called Iota with long integer field Iota. Create as many
records as you need for the monthly intervals that you need, with the first
record being 1 and if you enter 100 records, the last record being 100.
Record 2 would be 2, Record 3 would be 3, etc...

Create this query, and call it qCancellationMinMax:
SELECT Min(DateSerial(Year([Cancellation Date]),Month([Cancellation
Date]),1)) AS MinDate, Max(DateSerial(Year([Cancellation
Date]),Month([Cancellation Date]),1)) AS MaxDate
FROM tblCancellation;

Create this query and call it qCancellationAllDates:
SELECT DateAdd("m",[Iota],[MinDate]) AS AllDates
FROM qCancellationMinMax, Iota
WHERE (((DateAdd("m",[Iota],[MinDate]))<[MaxDate]));

Create this query and call it qCancellationUnion:
SELECT
1 AS ID, [Cancellation Date] from tblCancellation
UNION ALL Select 0 AS ID, AllDates from qCancellationAllDates;

Then run this query:
SELECT (Year([Cancellation Date])*12+Month([Cancellation Date])-1) AS ID,
(Format([Cancellation Date],"mmm"" '""yy")) AS [Date of Cancellation],
Sum(qCancellationUnion.ID) AS [Product/Service]
FROM qCancellationUnion
GROUP BY (Year([Cancellation Date])*12+Month([Cancellation Date])-1),
(Format([Cancellation Date],"mmm"" '""yy"));
 
Back
Top