access db swap fields and records for report

[el_oso]

I have a database which records the performance of certain individuals which then groups up into their teams. As is traditional database we have one record for each person on each date. I need to generate a report that shows and individual/teams performance over the last 4 or 13 week period. The query is easy to set up to retrieve the right data. I can't get the layout right though. I need to swap the fields so they run down the page on the report with the date data values running accross the top of the page. I've tried using a crosstab query but these really are not my strong point. table1 is my start data with table2 my layout. It doesn't look so bad at the moment but there are over 20 different metrics and it just looks wrong going across the top of the page.
____________________
Duke 390
Previous: '05 XR125L | '96 XJ600S Diversion |'05 Suzuki GSXR1000 | '05 Honda CBR125-R | '97 YZF 600R Thundercat | '11 Honda CBR250
Car: Jeep Wrangler 4.0L

[Kickstart]

Hi

Is this an MS Access database?

With columns like that you pretty much land up needing to build the SQL dynamically. But if the dates are fixed in comparison to the current date then there are alternatives.

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[Frog]

Assuming you have excel, try using a pivot table. You can drag the fields wherever you need and group as required.

I've not used access with a pivot table, but you should be able to get excel to I'll the information from access, then there's an option to use that data to drive a pivot table.

Once you've got the pivot table, you can drag the field into wherever you need.
____________________
CBT 23/09/10 - Theory 19/03/11 - Mod1 19/04/11 - Mod2 06/05/11
Bikes: CBF125 (sold 30/10/10-25/09/12) - CB400 24/06/11 (broken) - ER6-f 25/09/2012

[el_oso]

yes access database.

I have tried messing about with the pivot tables but they/I can't do what i want using them.

Building custom SQL for each field sounds doable, but there would be something like 48 unions which doesn't sound like fun.

Having the fields run down a report instead of across the top doesn't sound like a ridiculous thing to be able to do so I don't know why it's so difficult.
____________________
Duke 390
Previous: '05 XR125L | '96 XJ600S Diversion |'05 Suzuki GSXR1000 | '05 Honda CBR125-R | '97 YZF 600R Thundercat | '11 Honda CBR250
Car: Jeep Wrangler 4.0L

[Kickstart]

Hi

Do you need this as a single query? Can you dynamically build it? is it 48 fixed dates ranges?

All the best

Keith
____________________
Traxpics, track day and racing photographs - Bimota Forum - Bike performance / thrust graphs for choosing gearing

[el_oso]

it doesn't need to be a single query, so long as I can fit the same fields on one report.

The current query has a WHERE clause in the SQL 'Where date() - 28', so that it returns only the last 4 weeks.

Since there are only a small amount dates reutrned with a lot of fields having numbers it looks wrong on the report.
____________________
Duke 390
Previous: '05 XR125L | '96 XJ600S Diversion |'05 Suzuki GSXR1000 | '05 Honda CBR125-R | '97 YZF 600R Thundercat | '11 Honda CBR250
Car: Jeep Wrangler 4.0L