Fixed headings names in a crosstab with dynamic columns

[maddak]

Hello,

I'm trying to run a query which fetches only the last 2 years of data for a given region from a table with several years worth of data (there are year, region, sector and rank columns among others). The region is passed into the query from a combobox from Form1.

The first problem was that some regions have up-to-date data and some not so much e.g. for Europe the "last 2 years" mean 2012,2013 for Asia its 2011,2012. In order to deal with this I've created a crosstab query which works well except for one thing - because the columns are dynamic (dependant on the region) the column headings change as well.

And here comes my question, how can i fix the column names to be e.g."Current Year" and "Prior Year" independent of the query fetching 2012,2013 or 2009,2010? I've tied different things with PIVOT... IN ... but with no luck.

Thanks in advance for your help.

Here's the sql for the query:

PARAMETERS [Forms]![Form1]![cmbRegion] Text ( 255 );
TRANSFORM min(DataTable.Rank)
SELECT DataTable.Region, DataTable.RegionalSector, 
FROM DataTable 
WHERE (((DataTable.Region)=Forms!Form1!cmbRegion))
              AND (DataTable.ResultsYear =
                           (SELECT Max(DataTable.ResultsYear)-1 FROM DataTable
                           WHERE (((DataTable.Region)=[Forms]![Form1]![cmbRegion]))) 
                      OR DataTable.ResultsYear =
                           (SELECT Max(DataTable.ResultsYear) FROM DataTable
                           WHERE (((DataTable.Region)=[Forms]![Form1]![cmbRegion])))   
))
GROUP BY DataTable.Region, DataTable.RegionalSector, 
PIVOT DataTable.ResultsYear;

 

[vbaInet]

Let's see what you tried with PIVOT IN.

 

[maddak]

I've tried the IIF eventually getting to this form:

PIVOT IIF(DataTable.ResultsYear= [various code here nothing worked],"CurrentYear","PreviousYear") IN ("CurrentYear","PreviousYear")

where various code means:

(SELECT Max(DataTable.ResultsYear) FROM DataTable WHERE (((DataTable.Region)=[Forms]![Form1]![cmbRegion])))

or Max (access doesn't allow it)
or

[FONT=Arial]DMax("ResultsYear","DataTable","Region"= & """[Forms]![Form1]![cmbRegion]"""") [/FONT]
[FONT=Arial]DMax("ResultsYear","DataTable",[Region]= [Forms]![Form1]![cmbRegion]) [/FONT]

 

[CJ_London]

I'm surprised your subquery is giving you the result you want - you need to alias the subquery table i.e.

DataTable.ResultsYear =
                           (SELECT Max(DataTable.ResultsYear)-1 FROM DataTable [COLOR=red]AS T
[/COLOR]                           WHERE (((DataTable.Region)=[Forms]![Form1]![cmbRegion])))

You are also running the subquery twice so this may be more efficient

 DataTable.ResultsYear >=
                           (SELECT Max(DataTable.ResultsYear)-1 FROM DataTable [COLOR=red]AS T
[/COLOR]                           WHERE (((DataTable.Region)=[Forms]![Form1]![cmbRegion])))

If you want column headings of "Current Year" and "Prior Year" you need to create another calculated column as your Xtab column which will derive this for you. Something like

PIVOT iif(resultsyear=(SELECT Max(ResultsYear) FROM DataTable AS T WHERE DataTable.Region=[Forms]![Form1]![cmbRegion]),"Current Year","Prior Year")

However this is a subquery which unfortunately won't work for XTabs but you can use dmax instead

PIVOT IIf([resultsyear]=DMax("resultsyear","DataTable","Region=[Forms]![Form1]![cmbRegion]"),"Current Year","Prior Year")

 

[CJ_London]

see more posting has gone on

your dmax is not quite correct try

DMax("ResultsYear","DataTable","Region=" & [Forms]![Form1]![cmbRegion])

If region is text then

DMax("ResultsYear","DataTable","Region='" & [Forms]![Form1]![cmbRegion] & "'")

 

[maddak]

Hi CJ,

Thank You a lot for your help, the DMax you provided

PIVOT IIf([resultsyear]=DMax("resultsyear","DataTable","Region=[Forms]![Form1]![cmbRegion]"),"Current Year","Prior Year")

did work, I've tried something similar earlier but clearly did it wrong.

BTW, if not too much to ask, how does aliasing the table help in this particular situation?

Also thanks for pointing out the unecessary double subquery.

Cheers,

 

[CJ_London]

how does aliasing the table help in this particular situation

I was being a bit too cautious - I always alias as a matter of course but you need to alias subquery tables when the subquery references the main query which also contains that table otherwise you get a wrong result.

 

[vbaInet]

...otherwise the engine doesn't know what field you're referring to. The main query or the subquery.