Query to change Data layout

[waffleguy]

Hi there,
I have a warehouse table that lists a stock code with the last 12 periords closing balances in a record.

I would like to write a query that just shows each stock code with only one month per record and has the openning and closing balance for that month.(the closing balance of one period is the openning balance of the next period).

So the Table currently looks like this

StkCode....Whouse....12.....11......10.....09......08.....07
1111.........TZ..........100....200....300...400.....500...600

I would like it like:

StkCode....Whouse....Period....open....close
1111.........TZ..........12.........200.....100
1111.........TZ..........11.........300.....200
1111.........TZ..........10.........400.....300
1111.........TZ..........09.........500.....400
1111.........TZ..........08.........600.....500

I haven't worked with access for years and can see how to do this in my head - i just can't figure out what to write. Any help would be greatly appreciated.

 

[Moniker]

You're talking about Crosstab queries. Search for "crosstab" in here, in Access Help, and Google if you're still stuck.

 

[waffleguy]

Thanks for the tip - I am familiar with crosstabs - but a crosstab will not do what I want... I wanted to solve this with one query, but just ended up running a seperate append query for each month and put the records in a new table.

 

[sportsguy]

waffle,

if its all numeric data, write the query as follows:

Select StockCode, Whouse, Iif([Period]=12,[Close],0) AS Period12, Iif([Period]=11,[Close],0) AS Period11, etc. ;

then create a second query that will group and total the first query. .

Select StockCode, Whouse, Sum([Period12]) as 12, Sum([Period11]) as 11, etc
Group by StockCode, Whouse;

This query creates the same functionality as a cross tab, manually in two queries.

sportsguy