Counting fluctutation over time

[jill]

I am working on a query to get a line chart to plot the
fluctuation in customer's responses I receive each month.
I get these responses throughout the month on one out of
seven products. What I am looking to do is plot the each
product as its own line and show the volume of responses
we get on each product a month. So far I have been able to
work on a query that shows "0" when we received no
responses on a product but I can not account in the query
for the fact that at the beginning of tracking these
responses we had a couple months where we just did not
received any responses, which is causing my graph to look
a bit off jumping months from January to March with out
February showing up on the x-axis. Note that in the
database the first response received occurred in December
of 2002, so I also want December to be present in the
query. Right now my query for the chart looks like this:
1) (Year([Response Date])*12+Month([ResponseDate])-1)
2) (Format([Response Date],"mmm"" '""yy"))
3) Count: Nz((Count([Product/Service])),0)
4) Product/Service
Any suggestions on how to work the query so that the
months show up in sequence (with no month being left out
of the x-axis) would be really appreciated.

 

[[MVP] S. Clark]

Most likely an outer join will solve the problem.

If not, consider using a temp table. Populate the table with all of the
data needed, force in extra data that doesn't get added the first time, as
well as any other last minute scrubbing that the data may require. Then run
the chart from either this table, or a query based on this table.

I use this technique for reports/charts often. It really saves on debugging
time, as you don't have to rerun every query to test the report/chart.


--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-