Crosstab query help...

[eskelies]

Hello all,

I have a crosstab query that sets specific dates as field names. Is there an easy way to change the field name from date (numerical) to "Current date", but still give me the current dates information?

I am running access 2003 and below is my SQL code:

TRANSFORM First(PRCJ830A.[Accrual Rate]) AS [FirstOfAccrual Rate]
SELECT PRCJ830A.[Fund Number], IIf([Class] Is Null,0,[Class]) AS MultiClass
FROM PRCJ830A
GROUP BY PRCJ830A.[Fund Number], IIf([Class] Is Null,0,[Class])
PIVOT PRCJ830A.Date;

[Rabbit]

You'll need an intermediary query that will convert your dates to the string of your choice.

[eskelies]

Rabbit,

Will I need to do something like this?

UPDATE PRCJ830A_Crosstab SET PRCJ830A_Crosstab.[01/11/2008] = Replace([Accrual Yield])
WHERE PRCJ830A.[01/11/2008] ="Accrual Yield"
 

You'll need an intermediary query that will convert your dates to the string of your choice.

[Rabbit]

Not quite. You want a query BEFORE the crosstab that will create another column that translates the dates to the string that you want.

[eskelies]

In by doing this will it translate the date (ie. 1/14/2008...1/15/2008...etc.) into the string "Accrual Yield?"

Do you know where I might find an example?

Not quite. You want a query BEFORE the crosstab that will create another column that translates the dates to the string that you want.

[Rabbit]

iif([Accrual Yield]=#1/11/2008#, "Accrual Yield", [Accrual Yield])

[eskelies]

Okay...I'm an idiot. Thanks Rabbit for your help.

iif([Accrual Yield]=#1/11/2008#, "Accrual Yield", [Accrual Yield])

[Rabbit]

Not a problem, good luck.