Crosstab column issues

[solomon]

I am running croostab queries over a huge a mount of data and require only data that falls within a certain period. This period changes throughout the year (funny that) and the columns created by the crosstab in one period might not be the same in the next.

My Problem;

Other queries run off of the crosstab and use the fields created to go on and further produce reports. If the current period does not contain a column that was included within last periods crosstab then the queries fall over as it cannot find a column it is looking for.

I have thought about creating a field in a query that will populate if the crosstab creates the same one and will be zero if it does not.

Example;

Period 1 - crosstab creates columns 1,2,3,4 and other queries use these

Period 2 - crosstab creates columns 1,3,5,7. The queries fallover as they were looking for columns 2 & 4

 

[namliam]

You can use the "In" part to ensure that all periods are allways there

Easiest way of doing it is to go to SQL view
find your pivot line
PIVOT YourColumnField

and simply add In (....) to it like so:

PIVOT YourColumnField In (1,2,3,4,....)

Hope this helps.

Regards

 

[solomon]

Star

You are a star!!

Thanks, that works great!!

 

[Myriad_Rocker]

You can use the "In" part to ensure that all periods are allways there

Easiest way of doing it is to go to SQL view
find your pivot line
PIVOT YourColumnField

and simply add In (....) to it like so:

PIVOT YourColumnField In (1,2,3,4,....)

Hope this helps.

Regards

I'm bumping this because it's an excellent reply to a problem I've been having all day.

 

[namliam]

Wow a 3+ year question providing an answer... Cool

Kudos for using the search system and thank you for leaving a message