Oh my words... I got this working.

It looks awesome! You are a legend my friend!


Douglas J Steele wrote:
> You can create a query that normalizes your data
>
> SELECT Team, "ICPresented" AS Category, [Date] AS MeasurementDate,
> ICPresented AS Measurement
> UNION
> SELECT Team, "ICAnswered" AS Category, [Date] AS MeasurementDate, ICAnswered
> AS Measurement
> UNION
> SELECT Team, "PCA20" AS Category, [Date] AS MeasurementDate, PCA20 AS
> Measurement
> UNION
> SELECT Team, "PCA40" AS Category, [Date] AS MeasurementDate, PCA40 AS
> Measurement
>
> then use the approach I suggested.
>
> BTW, Date is not a good choice for a field name in Access (nor, for that
> matter, for anything else). Date is a reserved word, and using it for your
> own purposes can lead to all sorts of problems. If you cannot (or will not)
> change the field name, at least use square brackets around it, as in my
> example above.
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no e-mails, please!)
>
>
> "Adam@nospam.com" wrote in message
> news:1150188239.936005.196300@u72g2000cwu.googlegr oups.com...
> > Hi,
> >
> > No this is my fault, I haven't explained this very well at all.
> >
> > I have a table with data as below:
> >
> > Team ICPresented ICAnswered PCA20 PCA40 Date
> > A 1235 1100 60% 70% Mar-06
> > B 1446 1300 65% 72% Mar-06
> > C 1289 1201 55% 61% Mar-06
> > D 1564 1498 61% 70% Mar-06
> > A 1531 1475 63% 68% Apr-06
> > B 1101 1012 54% 61% Apr-06
> > C 2101 1897 59% 64% Apr-06
> > D 1905 1806 63% 71% Apr-06
> >
> > So I would like a report to then look like the below:
> >
> > Team A Mar-06 Apr-06
> > ICPresented 1235 1531
> > ICAnswered 1100 1475
> > PCA20 60% 63%
> > PCA40 70% 68%
> >
> > Team B
> > ICPresented 1446 1101
> > ICAnswered 1300 1012
> > PCA20 65% 54%
> > PCA40 72% 61%
> >
> > And so on etc...
> >
> > The months across the top would cover from Jan to Dec and there would
> > be more categories going down on the left.
> >
> >
> > Many Thanks for your continued help!
> >
> > Adam
> >
> >
> >
> > Douglas J Steele wrote:
> >
> > > Perhaps I don't understand your issue.
> > >
> > > Assuming you've got a table along the lines of:
> > >
> > > Category
> > > MeaurementDate
> > > Measurement
> > >
> > > you'd select Category as the Row heading (on the 2nd page of the
> wizard),
> > > MeasurementDate as the Column heading (on the 3rd page of the wizard),
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11242-report-left-right-not-top-bottom.html
> then
> > > Measurement on the 4th page of the wizard. Since you've only got one
> value
> > > to calculate, you can use Avg, First, Last, Max, Min or Sum as the
> function.
> > >
> > >
> > > --
> > > Doug Steele, Microsoft Access MVP
> > > http://I.Am/DougSteele
> > > (no e-mails, please!)
> > >
> > >
> > > "Adam@nospam.com" wrote in message
> > > news:1150120880.150823.269720@h76g2000cwa.googlegr oups.com...
> > > > Thats what I need but how do i do more than 2 categories??
> > > >
> > > >
> > > >
> > > > Douglas J Steele wrote:
> > > >
> > > > > What I understand is that you wanted months across the top, and 20
> > > different
> > > > > categories along the left. At the intersection of each category and
> each
> > > > > month, you want a value that will represent the measure for that
> > > category
> > > > > for that month.
> > > > >
> > > > > That's a cross-tab query...
> > > > >
> > > > > --
> > > > > Doug Steele, Microsoft Access MVP
> > > > > http://I.Am/DougSteele
> > > > > (no e-mails, please!)
> > > > >
> > > > >
> > > > > "Adam@nospam.com" wrote in message
> > > > > news:1150118416.326584.230600@j55g2000cwa.googlegr oups.com...
> > > > > > Hi Doug,
> > > > > >
> > > > > > Well there's around 20 fields that I'd like to line up on the
> left,
> > > > > > however a crosstab query, as far as I know, only lets you look at
> a
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
> > > > > > maximum of 2.
> > > > > >
> > > > > >
> > > > > >
> > > > > > Douglas J Steele wrote:
> > > > > >
> > > > > > > Based on your original post, it certainly sounds as though a
> > > cross-tab
> > > > > query
> > > > > > > should fit the bill.
> > > > > > >
> > > > > > > What did you leave out of that original post?
> > > > > > >
> > > > > > > --
> > > > > > > Doug Steele, Microsoft Access MVP
> > > > > > > http://I.Am/DougSteele
> > > > > > > (no e-mails, please!)
> > > > > > >
> > > > > > >
> > > > > > > "Adam@nospam.com" wrote in message
> > > > > > > news:1150116370.199385.39530@j55g2000cwa.googlegro ups.com...
> > > > > > > > The crosstab wouldn't be sufficient though, as I have around
> 20
> > > fields
> > > > > > > > I want to include on the page.
> > > > > > > >
> > > > > > > >
> > > > > > > > Kernow Girl wrote:
> > > > > > > >
> > > > > > > > > Hi Adam - it sounds like what you want is a Cross-Tab Query.
> The
> > > > > Help
> > > > > > > screen
> > > > > > > > > says this:
> > > > > > > > > You use crosstab queries to calculate and restructure data
> for
> > > > > easier
> > > > > > > > > analysis of your data. Crosstab queries calculate a sum,
> > > average,
> > > > > count,
> > > > > > > or
> > > > > > > > > other type of total for data that is grouped by two types of
> > > > > > > information -
> > > > > > > > > one down the left side of the datasheet and another across
> the
> > > top.
> > > > > > > > > HTH - Dika
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > "Adam@nospam.com" wrote:
> > > > > > > > >
> > > > > > > > > > Hi All,
> > > > > > > > > >
> > > > > > > > > > I'm having to do a big database where I am reporting on
> > > various
> > > > > > > > > > departments over yearly periods, where there performance
> is
> > > split
> > > > > > > > > > monthly and measured in the output report.
> > > > > > > > > >
> > > > > > > > > > I have various fields, including a month field.
> > > > > > > > > >
> > > > > > > > > > What I would like is to have a report output whereby the
> > > fields go
> > > > > > > down
> > > > > > > > > > the left, the months run across the top of the page (jan
> to
> > > dec)
> > > > > and
> > > > > > > > > > the measures were then shown below that.
> > > > > > > > > >
> > > > > > > > > > I cannot see a way of doing this.
> > > > > > > > > >
> > > > > > > > > > Does anybody know how I could do this?
> > > > > > > > > >
> > > > > > > > > > Many Thanks
> > > > > > > > > >
> > > > > > > > > > Adam
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >