Monthly and YTD totals (1 Viewer)

I am trying to create a report that shows monthly totals and YTD totals. I also need to show monthly totals of jobs that were not approved.

Here's initial query:

From here, I am using a crosstab query to give me my monthly breakdown:

That all works fine on the initial report. I need to add a supreport that gives monthly and YTD totals on records in the initial query where Approved = "no". Here's my query for that:

Here's the query that counts the number of records that were not approved:

I said all of that to ask this question - the subreport data does not have data for every month in the year - for example, there are no records for December. I need to leave a place for December data for next year - right now I'm getting an error that the field doesn't exist. It doesn't, since it is a calculated field that is returning no value.

Any advice on the best way to handle this? I need it to just show zero for the months where there is no value returned from the calculation.

I appreciate and all insight. The attached screen shots should provide a visual illustration of what I am doing.

You can't create data where there is none....so make some. You will need a table that houses all the values you want to report on. Since you want to report on all months, you will need a table of those.

Then what you do is before you cross-tab, you include that table in your query. You LEFT JOIN from that table (e.g. Show all records from the month table) to whatever datasource you have now. You use the month from that new table and that will force it to show up. To convert the null value to 0, use NZ().

I wonder if it would work to use an if statement in the report control - that basically says if the field doesn't exist, display a zero.

plog said:

You can't create data where there is none....so make some. You will need a table that houses all the values you want to report on. Since you want to report on all months, you will need a table of those.

Then what you do is before you cross-tab, you include that table in your query. You LEFT JOIN from that table (e.g. Show all records from the month table) to whatever datasource you have now. You use the month from that new table and that will force it to show up. To convert the null value to 0, use NZ().

Click to expand...

I'm not sure I'm getting your full meaning. The monthly data is in a date format calculated field in the original query. If I put the months fields in a separate table, even with a join, how will this work when the field calculation in the original query returns a value other than null?

A JOIN matches data between two tables:

Table1
MonthName, SalesTotal
January, 18
March, 22
May, 7

Table2
MonthName
January
February
...
December

You link those tables via MonthName. If you use an INNER JOIN (show matching records) the resulting query will produce 3 records (the ones in Table1). If you use a LEFT JOIN (showing all from Table2) the resulting query will produce 12 records (one for each month). Of course, 9 of those will have null SalesTotal values. You convert those to 0 values via the NZ function.

Oh - ok. I see what you mean. I'll give that a try. Thanks so much for the additional explanation!