Possible? Create form with variable transposed data

[Fireman Sam]

I have been asked to write a report in a certain way, but I can't get my head around how to do it in access (or in SQL and pulling the table into access). I am open to suggestions even if I need to re-write the tables and how the data is imported.

I have collected data that is in this format in a table.

Date Sum1 Count1 Sum2 Count2 Sum3 Count3 etc. to 8
01/01/09 50000 5 425100 6 512100 5
02/01/09 38000 11 560000 9 680000 7
03/01/09 51000 9 435100 7 532100 9
For any dates picked from a "to" and "from" date option on a form.

But I need to show it in this format in a report and on a form.

Date 01/01/09 02/01/09 03/01/09 etc (can grow/shrink - No of days chosen)
Sum1 50000 425100 512100 …
Count1 5 6 5
Sum1 38000 560000 680000 …
Count1 11 9 7
Sum1 51000 435100 532100 …
Count1 9 7 9
To 8 down

Two problems occur.
1) how to show the data when the dates can be for 1 week or 1 month or 2 months (the number of records would increased if transposed) ?
2) How to show records next to each other (in columns) rather than the normal row?

I have spent days looking throughout the net, I have even used the transpose http://support.microsoft.com/kb/202176 which puts the information the way I believe I need it for the form/report but I am limited to a set number of records and the names are in the first field of the record.

 

[gemma-the-husky]

its still tricky. but this will very quickly get too big for a report

i would be inclined to just dump it as a csv or xls, with the data formatted as they want.

i am not sure if there is a transpose function of some sort, ie a direct way to do it with a simple query

the other way is to generate a temporary table with the right no of columns, and populate that, which needs some coding