By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 879 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Crosstab query help...

P: 55
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:
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM First(PRCJ830A.[Accrual Rate]) AS [FirstOfAccrual Rate]
  2. SELECT PRCJ830A.[Fund Number], IIf([Class] Is Null,0,[Class]) AS MultiClass
  3. FROM PRCJ830A
  4. GROUP BY PRCJ830A.[Fund Number], IIf([Class] Is Null,0,[Class])
  5. PIVOT PRCJ830A.Date;
Jan 14 '08 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,357
You'll need an intermediary query that will convert your dates to the string of your choice.
Jan 14 '08 #2

P: 55
Rabbit,

Will I need to do something like this?

Expand|Select|Wrap|Line Numbers
  1. UPDATE PRCJ830A_Crosstab SET PRCJ830A_Crosstab.[01/11/2008] = Replace([Accrual Yield])
  2. WHERE PRCJ830A.[01/11/2008] ="Accrual Yield"
  3.  
You'll need an intermediary query that will convert your dates to the string of your choice.
Jan 14 '08 #3

Rabbit
Expert Mod 10K+
P: 12,357
Not quite. You want a query BEFORE the crosstab that will create another column that translates the dates to the string that you want.
Jan 14 '08 #4

P: 55
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.
Jan 14 '08 #5

Rabbit
Expert Mod 10K+
P: 12,357
Expand|Select|Wrap|Line Numbers
  1. iif([Accrual Yield]=#1/11/2008#, "Accrual Yield", [Accrual Yield])
Jan 14 '08 #6

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

Expand|Select|Wrap|Line Numbers
  1. iif([Accrual Yield]=#1/11/2008#, "Accrual Yield", [Accrual Yield])
Jan 14 '08 #7

Rabbit
Expert Mod 10K+
P: 12,357
Not a problem, good luck.
Jan 14 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.