Exporting data from Access to CSV in particular format

[billelev]

Hi there,

My question does not concern the exporting of data to CSV so much as the format of the exported data. The data to be exported consists of a time series of prices for a number of assets. I can easily bring up a query of the data I want to export in the following way:

SELECT Date, Asset, AssetPrices
FROM tblPrices;
 

returning:

[Date],[Asset],[AssetPrice]
date_1,asset_1,price_1    
date_2,asset_1,price_2
date_3,asset_1,price_3
etc....
date_1,asset_2,price_1    
date_2,asset_2,price_2
date_3,asset_2,price_3
etc....
date_1,asset_3,price_1    
date_2,asset_3,price_2
date_3,asset_3,price_3
etc....
 

However, in the CSV format I would like the prices to be aligned in the following way:

"Date","Asset_1","Asset_2","Asset_3"
date_1,price_1, price_1, price_1
date_2,price_2, price_2, price_2
date_3,price_3, price_3, price_3
etc....
 

So, taking a 1-dimensional data structure within Access and exporting it as a 2-dimensional data structure. Does anyone know how to do this? Two possibilities might be to (1) create a data structure in Access to then export to CSV or (2) export each asset's data series at a time, appending successive columns of data within the CSV file.

Any help greatly appreciated.

[nico5038]

For this you need a cross table query.
Place the fields like:
Date, Asset, AssetPrices
in the query editor and change the query type to cross-table.
Now change the GroupBy under the AssetPrices into "Max"
Finally set the fields in the "combo line" for the cross table like:
Rowheader, ColumnHeader and Value

Nic;o)

[billelev]

Perfect! Thanks, Nic.

I've been looking for this kind of formatting in Access (obviously not very hard) for about a year!