Query to transpose date

[Mark]

I have a huge database of stock market data. It is a list of daily prices.
Hypothetical eg

Code Date Price
ABC 01-Jan-1980 $2.10
ABC 02-Jan-1980 $2.11
ABC 03-Jan-1980 $2.09
etc to
ABC 01-Jan-2006 $22.00

(though of course the above is not aligned)

ACE 01-Jan-1980 $12.40
ACE 02-Jan-1980 $12.45
ACE 03-Jan-1980 $12.30
etc to
ACE 01-Jan-2006 $88.00

And so forth for all firms listed.

--------------------------------------

I want to write a query (or whatever) that will transpose (to use an Excel
term) the data by year so that it becomes:

01-Jan-1980 02-Jan-1980 03-Jan-1980 etc
ABC $2.10 $2.11 $2.09 etc
ACE $12.40 $12.45 $12.30 etc

And so forth for all firms listed (though of course the above is not
aligned)

I have the year worked out by using the Text function Right,2. How can I
transpose the data?

Many thanks
Mark

 

[pietlinden]

tried a crosstab query yet?

 

[Mark]

Thanks.
No, I've never used it. How should I set it up?

Mark

 

[Mark]

Hi

I found the following which seems to answer my question

http://groups.google.com/group/micr...q=crosstab&rnum=8&hl=en&#doc_bc77f55cebd921bb

 

[Mark]

I gave it a go following

http://groups.google.com/group/micr...q=crosstab&rnum=8&hl=en&#doc_bc77f55cebd921bb

But I'm getting the message:
You tried to execute a query that does not include the specified expression
Close as part of an aggregate function.

I used the wizard and I added an expression to Sum Close, but that has not
fixed the problem.

Any help would be appreciated

Thanks Mark