Need Zero Values in Table For Graph

[TexMax007]

Hello All,



I am trying to create a graph of the monthly sales for a given customer of our organization.

I have a query that adds up the sales from our sales table and groups them by month (and obviously by customer).

However, if they didn't purchase anything in say... month 03 (March), there is no record created in the result.

The records look like this:

|Month Number|Total|Customer Number|

So, say I'm graphing the first 5 months of the year, but Customer A didn't buy anything in march, this is what I would get:

01 | $15.67 | A
02 | $12.22 | A
04 | $50.11 | A
05 | $23.56 | A

When I use this result in my smooth line graph, it graphs it nicely, but it is rather misleading.

The line between month 2 and 4 just goes straight from $12.22 to $50.11, rather than going back to $0 for month 3 (which is what I would prefer).

To sum it up:

I need a way to append the missing records to this result table for the months that don't have any sales.

Please not that this result table doesn't have just one customer, there are thousands. So, if there are 10 customers, I need 120 records in the final result (12 months for each of the 10 customers).

Please let me know if you have any suggestions. This has been bugging the heck out of me.

Thanks in advance for the assistance.

Max

[apr pillai]

I think the best and easy solution is to create a table (12 * 10 or for whatever number of customers in the output) Records with all blank or zeros as the case may be with Month and Customer Codes as Key.

You need a table with 12 month's blank records. Place the Customer List File (pick from the Report Query) and the Month's table (with blank records) in a new Make-Table Query and don't link them. It will give XXX * 12 records automatically where XXX is the number of customer codes in the report Query. Place the Customer Code and the Month Columns to the Query Output. Run it to create the Graph Table.

Create the actual output for the graph as a separate Table.

Link both tables on Customer Code and Month in an Update Query and update the Graph Table Fields from the output table.

[TexMax007]

Thanks for the help.

I didn't quite understand what you were saying, but I did eventually get it to do what I wanted to do.

For those interested:

I created a table with the dates I wanted to plot.
I did each month for the past two years in this format: yyyy mm (e.g. 2010 05 for May 2010).

I then made a query that selected each customer from my customers table and each date from my date table. Thereby making x*y rows for x customers and y dates.

Then I left joined my "CustomersAndDates" table with the results of a query that got all the sales for each customer and grouped by year and month.

This will give x*y rows now with a total sales column added. If there were no sales for that year month combination, it will be null. Nulls are graphed as zero, so it works for what I wanted.

So now when I graph by customer, it correctly puts a zero where there are no sales.