Report left to right not top to bottom

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),
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
> > > > > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11242-report-left-right-not-top-bottom.html
> > > > > > > >
> > > > > > > >
> > > > > > > > "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
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > >
> > >
>

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Thanks Doug, what will this do? I presume this is SQL code to be used
in a query?

Please forgive my newbness!


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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
> 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),
> 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
> > > > > > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
> > > > > > > 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
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >

Sorry, I can see this now.

I will try this shortly and let you know how it goes! If I have a space
inbetween my field names, I know its a sin, should I use the square
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
brackets around the field name?


Adam@nospam.com wrote:
> Thanks Doug, what will this do? I presume this is SQL code to be used
> in a query?
>
> Please forgive my newbness!
>
>
> 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),
> > 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:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
> > > > >
> > > > > > 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
> > > > > > > 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
> > > > > > > > > > >
> > > > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > >
> > >

If a field name contains a space, you must surround it with brackets.

"Adam@nospam.com" wrote in message
news:1150197613.498169.206770@g10g2000cwb.googlegr oups.com...
> Sorry, I can see this now.
>
> I will try this shortly and let you know how it goes! If I have a space
> inbetween my field names, I know its a sin, should I use the square
> brackets around the field name?
>
>
> Adam@nospam.com wrote:
>> Thanks Doug, what will this do? I presume this is SQL code to be used
>> in a query?
>>
>> Please forgive my newbness!
>>
>>
>> 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),
>> > 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
>> > > > > > > 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
>> > > > > > > > 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.
>> > > > > > > > > > >
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
>> > > > > > > > > > > Does anybody know how I could do this?
>> > > > > > > > > > >
>> > > > > > > > > > > Many Thanks
>> > > > > > > > > > >
>> > > > > > > > > > > Adam
>> > > > > > > > > > >
>> > > > > > > > > > >
>> > > > > > > > >
>> > > > > > >
>> > > > >
>> > >
>

Hi,

I'm trying this now however its saying "Query input must contain at
least one table or query".

This error message appears when I run the Union query and when I try
and make a crosstab query from that Union query.


BruceM wrote:
> If a field name contains a space, you must surround it with brackets.
>
> "Adam@nospam.com" wrote in message
> news:1150197613.498169.206770@g10g2000cwb.googlegr oups.com...
> > Sorry, I can see this now.
> >
> > I will try this shortly and let you know how it goes! If I have a space
> > inbetween my field names, I know its a sin, should I use the square
> > brackets around the field name?
> >
> >
> > Adam@nospam.com wrote:
> >> Thanks Doug, what will this do? I presume this is SQL code to be used
> >> in a query?
> >>
> >> Please forgive my newbness!
> >>
> >>
> >> 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),
> >> > 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.
> >> > > > > >
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
> >> > > > > > 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
> >> > > > > > > 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
> >> > 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
> >> > > > > > > > > > >
> >> > > > > > > > > > >
> >> > > > > > > > >
> >> > > > > > >
> >> > > > >
> >> > >
> >

I have been following this thread, but only jumped in when I knew I had a
quick answer to a specific question. I thought it might save some time. I
will wait for Douglas to respond to the question about the query, as I am
not that familiar with union queries.

"Adam@nospam.com" wrote in message
news:1150200986.990550.301690@y43g2000cwc.googlegr oups.com...
> Hi,
>
> I'm trying this now however its saying "Query input must contain at
> least one table or query".
>
> This error message appears when I run the Union query and when I try
> and make a crosstab query from that Union query.
>
>
> BruceM wrote:
>> If a field name contains a space, you must surround it with brackets.
>>
>> "Adam@nospam.com" wrote in message
>> news:1150197613.498169.206770@g10g2000cwb.googlegr oups.com...
>> > Sorry, I can see this now.
>> >
>> > I will try this shortly and let you know how it goes! If I have a space
>> > inbetween my field names, I know its a sin, should I use the square
>> > brackets around the field name?
>> >
>> >
>> > Adam@nospam.com wrote:
>> >> Thanks Doug, what will this do? I presume this is SQL code to be used
>> >> in a query?
>> >>
>> >> Please forgive my newbness!
>> >>
>> >>
>> >> Douglas J Steele wrote:
>> >> > You can create a query that normalizes your data
>> >> >
>> >> > SELECT Team, "ICPresented" AS Category, [Date] AS MeasurementDate,
>> >> > ICPresented AS Measurement
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
>> >> > 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...
>> >> > >
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
>> >> > > 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),
>> >> > 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
>> >> > > > > > > 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
>> >> > > > > > > > > > >
>> >> > > > > > > > > > >
>> >> > > > > > > > >
>> >> > > > > > >
>> >> > > > >
>> >> > >
>> >
>

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),
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
> > > MeasurementDate as the Column heading (on the 3rd page of the wizard),
> 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...
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11242
> > > > >
> > > > > --
> > > > > 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
> > > > > > 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
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > >
> > > >
> >