Question Data analysis using Cross tab (1 Viewer)

Lanason · Dec 5, 2018

I have a bunch of data and I want to create totals per month.
When I use the cross tab it works fine BUT if there is a month with no records it misses the month out rather than reporting Zero. Is there an easy way of including a zero?

theDBguy · Dec 5, 2018

Hi Adrian,

You can include all the months by specifying them in the Column Headings property.

isladogs · Dec 5, 2018

Depending on how you calculate your totals, you may need to use e.g. Nz(Totalsfield,0). Without that it may just be blank (null)

Pat Hartman · Dec 6, 2018

If you are using a single calendar year, you can use the technique suggested by theDBGuy. If you need to display more than twelve months or a rolling 12 months with some months in one year and others in the next year, you can't because year will be required as part of the column name.

Tell us the situation you have and we'll continue with other solutions if necessary.

Lanason · Dec 6, 2018

I am actually using rolling 12 months in the output

isladogs · Dec 6, 2018

In that case you need dynamic column headers.
Have a look at the method i use to deal with that.
For example https://www.access-programmers.co.uk/forums/showthread.php?t=294548
The idea is transferrable to any crosstab report without fixed headings

Question Data analysis using Cross tab (1 Viewer)

Lanason

Registered User.

theDBguy

I’m here to help

isladogs

MVP / VIP

Pat Hartman

Super Moderator

Lanason

Registered User.

isladogs

MVP / VIP

Similar threads

Users who are viewing this thread