Cross Tab (Transform) query: how can I get alaise for column names

[Clipper2000]

Here is my simple example TRANSFORM query:

TRANSFORM sum(Table.Amount) as Amount
SELECT table.field1, table.field2, etc ...
From Table INNER JOIN TheOtherTable
GROUP BY Table.field1, Table.field2
PIVOT ([Period] & [Year])

This query will return 2 columns with the names:

92004 (meaning September 2004)
102004 (meaning October 2004)

I would like the column names to be:

Current Month
Prior Month

When I modify the pivot clause to read:

PIVOT ([Period] & [Year]) IN ("Current Month", "Prior Month")

I get the column names to read correctly, but no amounts showed up.

I look forward to hear your answer on this intersting question.

 

[lespaul]

adding the column names to the query doesn't work?

TRANSFORM sum(Table.Amount) as Amount
SELECT table.field1 As "Current Month", table.field2 As "Prior Month", etc ...
From Table INNER JOIN TheOtherTable
GROUP BY Table.field1, Table.field2
PIVOT ([Period] & [Year])

Leslie

 

[Clipper2000]

Unfortunately no. The issue here is the fields in the PIVOT clause, not the SELECT clause or the TRANSFORM clause.

 

[MichaelRed]

The classi soloution is to have / create and additional recordset with the field names and their alaiases for use in the XTab. Join on the actual field names, use the Alais names in the output.





MichaelRed

 

[Clipper2000]

Hi MichaelRed,

Yes I know that creating an additional recordset that points to the TRANSFORM query will work. Given that the PIVOT clause in this new recordset is no longer an issue, I can now have any alaise name I like. But I was wondering if there is a way to do everything without using another recordset.

 

[MichaelRed]

If (and ONLY if) the results set field collection will NEVER change, you can use the columns headings property of hte XTab, However this is well covered / discussed in the ubiquitous {F1} aka H E L P, which I always assume the 'professional' programmer (member of these fora) has thoroughly reviewed before posting here in.





MichaelRed

 

[Clipper2000]

Hi MichaelRed,

Thank you for your care. I am sorry that I have omitted some information in my original post.

The reason I ask my question is: the result set is send to an Access report where I need to have a group total on the Current and Prior field. Because the current field and prior field read: 102004, 92004 (ie, numeric), the Group totals don't work properly, it returns funny answers. However, as soon as I manually change them to a string value, ie, 102004a, it worked.

That is why I can't use 2 queries due to the changing periods. Now I can code something to make it work but perhaps there is another obvious easier way.

 

[Clipper2000]

I FOUND THE SOLUTION !

I changed the PIVOT clause to include a text character such as below, the columns are now text based, not numeric based, and it worked !


PIVOT [Period] & [Year] & "a";

 

[oharab]

Rather than having the extra character, could you not use
PIVOT cstr([Period] & [Year]);

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------
Want to get great answers to your Tek-Tips questions? Have a look at F

 

[Clipper2000]

Hi Ben,

That was the solution I need, thanks. Funny I never thought of it given that I have several cstr functions in the TRANSFORM query itself!