Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Report left to right not top to bottom

Discussion in 'Information Technology' started by Adam@nospam.com, Jul 28, 2006.

  1. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    UK British Sikh Report-2016 Breaking News Mar 24, 2016
    1984 The Story I Did Not Report In ’84 History of Sikhism Jun 28, 2015
    Opinion Top Green Beret Officer Forced to Resign Over Affair With WaPo Reporter Breaking News Jun 26, 2014
    United Sikhs New Report Shows Increasing Hate Attacks on Sikhs Sikh Organisations May 21, 2014
    Legal ACLU reports: Judge orders Sikh man, "Remove that rag, or Go to Jail! Breaking News Sep 27, 2013

  3. Kernow Girl

    Kernow Girl
    Expand Collapse
    Guest

    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
    >
    >
     
  4. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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
    > >
    > >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > >
    > > >

    >
     
  6. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > >
    > > > >

    > >
     
  7. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > > >
    > > > > >
    > > >

    >
     
  8. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > > > >
    > > > > > >
    > > > >

    > >
     
  9. Kernow Girl

    Kernow Girl
    Expand Collapse
    Guest

    Hi Adam - try this - create a field in the query that you are basing the
    crosstab on. In the field concatenate all the fields - or break down into 3
    'sets' of fields. In the crosstab use these created fields for your left
    side. If you want spaces between the fields do as [field]&" "&[field]. May
    need to break down if combined text and number fields. HTH - #Dika

    "Adam@nospam.com" wrote:

    > 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" <adamevans81@gmail.com> wrote in message
    > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > > > > >
    > > > > > > >
    > > > > >
    > > >

    >
    >
     
  10. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    news:1150120880.150823.269720@h76g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > > > > >
    > > > > > > >
    > > > > >
    > > >

    >
     
  11. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > news:1150120880.150823.269720@h76g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > > > > > >
    > > > > > > > >
    > > > > > >
    > > > >

    > >
     
  12. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    news:1150188239.936005.196300@u72g2000cwu.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > news:1150120880.150823.269720@h76g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > > > > > > >
    > > > > > > > > >
    > > > > > > >
    > > > > >
    > > >

    >
     
  13. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > news:1150188239.936005.196300@u72g2000cwu.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > news:1150120880.150823.269720@h76g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > >
    > > > > > >
    > > > >

    > >
     
  14. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > > news:1150188239.936005.196300@u72g2000cwu.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > news:1150120880.150823.269720@h76g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > > > > > > > > >
    > > > > > > > > > > >
    > > > > > > > > >
    > > > > > > >
    > > > > >
    > > >
     
  15. BruceM

    BruceM
    Expand Collapse
    Guest

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

    "Adam@nospam.com" <adamevans81@gmail.com> wrote in message
    news:1150197613.498169.206770@g10g2000cwb.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> > news:1150188239.936005.196300@u72g2000cwu.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> > > > news:1150120880.150823.269720@h76g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> > > > > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> > > > > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    >> > > > > > > > > > >
    >> > > > > > > > > > >
    >> > > > > > > > >
    >> > > > > > >
    >> > > > >
    >> > >

    >
     
  16. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > news:1150197613.498169.206770@g10g2000cwb.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > >> > news:1150188239.936005.196300@u72g2000cwu.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > >> > > > news:1150120880.150823.269720@h76g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > >> > > > > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > >> > > > > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > >> > > > > > > > > > >
    > >> > > > > > > > > > >
    > >> > > > > > > > >
    > >> > > > > > >
    > >> > > > >
    > >> > >

    > >
     
  17. BruceM

    BruceM
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    news:1150200986.990550.301690@y43g2000cwc.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> news:1150197613.498169.206770@g10g2000cwb.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> >> > news:1150188239.936005.196300@u72g2000cwu.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> >> > > > news:1150120880.150823.269720@h76g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    >> >> > > > > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in
    >> >> > > > > > > > message
    >> >> > > > > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    >> >> > > > > > > > > > >
    >> >> > > > > > > > > > >
    >> >> > > > > > > > >
    >> >> > > > > > >
    >> >> > > > >
    >> >> > >
    >> >

    >
     
  18. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    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" <adamevans81@gmail.com> wrote in message
    > news:1150188239.936005.196300@u72g2000cwu.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > news:1150120880.150823.269720@h76g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > > news:1150118416.326584.230600@j55g2000cwa.googlegroups.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" <adamevans81@gmail.com> wrote in message
    > > > > > > > news:1150116370.199385.39530@j55g2000cwa.googlegroups.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
    > > > > > > > > > >
    > > > > > > > > > >
    > > > > > > > >
    > > > > > >
    > > > >

    > >
     

Share This Page