Cross-tab Table

[ocp&mcsd]

Dear all,

I have a small problem.....
I hope u 2 help me solving it.....
I cross tab query, with this column:
PIVOT Format([Date],"Short Date")
But as we all know that Format function returns variant(strin), So how can we reorder that column by date...
Sorry for bothering you....

Thanks & Regards

 

[mpb.vu2]

You may try as follows:

TRANSFORM Sum([Table1].[valu]) AS SumOfvalu
SELECT [Table1].[id], Sum([Table1].[valu]) AS [Total Of valu]
FROM Table1
GROUP BY [Table1].[id]
PIVOT [Table1].[date] In ("1-Jan-06","2-Jan-06");

etc. etc...

 

[ocp&mcsd]

Thank you very much...
I am a beginer in Access , can you give me more explanation...

 

[Rich]

You shouldn't have a field named Date, it's a reserved word in Access, and why are you formatting the Date to short date in the query?

 

[ocp&mcsd]

Thank you all,
I want the date to be ordered from 5/11/2005 to 4/10/2006:

TRANSFORM Sum([Shares Balance].[x]) AS [SumOfx]
SELECT [Shares Balance].[y] AS Expr1
FROM [Shares Balance]
GROUP BY [Shares Balance].[y]
PIVOT [Shares Balance].date In ("5/11/2005","4/10/2006");


HOW?
Sorry again for bothering you....

 

[mpb.vu2]

Are you doing this just to get the idea ?

You have to put all the dates as ("11-May-05", "12-May-05", ...... , "10-Apr-06")

 

[ocp&mcsd]

Thank u very much mr. mpb.vu2,
Can I use BETWEEN ... AND ...
How?
Because, I have 120 dates...
Imagine how difficult it will be...
Thanks & Regards

Are you doing this just to get the idea ?

You have to put all the dates as ("11-May-05", "12-May-05", ...... , "10-Apr-06")