blank columns in cross tab

[Jymoz]

i have separate cross tabs for each group, which is a group of ID numbers. In these cross tabs i am working out a summary of sales for each year, with years going from 2006 to 2009.

My problems comes when there is no data for a certain year for an id number. It compacts the cross tab columns and doesnt include the year that it wasnt there. So say that there was an ID for only 2008 and 2009. Since there is no 2006/2007 data it only shows the two columns whichs puts it in line with the wrong years.

So what i need to do is be able to insert a blank column where there is no data for that id. So for the above example blank columns for the years 2006 abd 2007 so that the 08/09 data is in line with the rest. Can this be done? I'm guessing it may be a formula that i put in the columns or something as i can't find any options in the cross tab expert.

Any help will be very appreciated, thanks.

 

[Endre]

See my reply in the following thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=176256

Hope this helps...

 

[Jymoz]

See my reply in the following thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=176256

Hope this helps...

Hey Endre,

I'm guessing you mean your second post about entering the values in yourself. This sounds like it would be a perfect solution but a quick question (probably a stupid one too) how do you open the cross tab in design view to be able to type in the values? I can only seem to find the expert option. I'm running crystal reports 9 if that helps.

Else if that isnt possible in this version where would i put that VBA code and adapt it to show the years in the headers etc?

Thanks for your help

 

[Endre]

Open up MS Access and display the main list of Queries. Right Click on the x-tab query and select [Design View]. You should see your query screen split in two - the top half shows tables and queries that make up your x-tab, and the bottom half shows how you want fields displayed and filtered etc. Click anywhere in the top half of the screen in the empty areas (i.e. not on a table or query). If the properties box is not already open then from the main menu choose View > Properties. (Or right click and choose properties). You should now have a properties input box open with the heading "Query Properties". The third option down is "Column Headings" where you want to fix your values.

Attched are 2 screen dumps to help if required.