Access Report Formating Issue

[mintz87]

i have attached doc that helps explains want i am trying to do. i am trying to format a report to look like a format i did in excel by using a pivot table.

thanks

[ADezii]

The only option that I can see is an 'Unbound', pre-formatted Reports with a mountain of code to 'Pull' the data in.

[nico5038]

With some query tricking you could get this done.
It would be easier to have all months besides one another and to have the average on top of bottom, but it can be done like:
1) Create a query selecting the details by Year/IndicationFirstLastYearHalf /Month, a dummy Avg field holding "1" and a 01-06 field for the crosstab, based on month - 6 when IndicationFirstLastYearHalf is "Last half".
2) Create a query to calculate the average with the same fields as the above one and have the Year set to Max - 1 and a dummy Avg field holding "0"
3) Create a UNION to JOIN all rows
4) Create the Crosstable query based on the one from step 3 and use the tricked month as the column header and the tricked year as the row header.

Getting the idea ?

Nic;o)

[ADezii]

That is a rather novel and ingenious approach, Nico. Just for curiosity, have you actually implemented it in some form to test it?

[nico5038]

Hi ADezii,

The basic "tricks" in this solution I've used multiple times.

In general I try to manipulate as much as possible the special fields in queries to get the final data available for a cross table query.

I'm however rather lazy, so for a test I would require a test database with some data. But as I don't want to spoil the "Eureka" feeling of the questioner, I start in general with some directions. Getting something working is always my greatest satisfaction and I hope for most questioner here too.

Nic;o)