Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Crosstab Query X 2

    I am trying to get specific output to a report. Here is what I'm working with:

    Table:

    pr_ppClientName..........teBill................... .teHours....HoursType
    Sears...........................Billable.......... .........14...............1
    Sears...........................Billable.......... .........11...............1
    Sears.........................Non-Billable...............11...............1
    Sears.........................Billable............ ...........5................2
    Sears.........................Non-Billable................5................2
    Sears.........................Non-Billable................5................2


    Report:

    Client.....Bill1.....NonBill1.....Bill2....NonBill 2
    Sears.......25..........11..........5...........10

    Where it sums the total for each different type of hours within the detail section of the report. I tried using a crosstab but was unable to get it to work. (In reality I have about 10 different types of billing codes)

    Maybe there is some type of sumif function that can be used on a report? I prefer not to use any Dsum type functions as they will bring the report to a crawl.

    Thanks for taking a look.

    Norm

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select pr_ppClientName As Client,
    SUM(IIF(teBill = 'Billable' AND HoursType = 1), teHours, 0) As Bill1,
    SUM(IIF(teBill = 'Non-Billable' AND HoursType = 1), teHours, 0) As NonBill1,
    SUM(IIF(teBill = 'Billable' AND HoursType = 2), teHours, 0) As Bill2,
    SUM(IIF(teBill = 'Non-Billable' AND HoursType = 2), teHours, 0) As NonBill2
    from table
    group by pr_ppClientName
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Posts
    100
    Originally posted by r123456
    Select pr_ppClientName As Client,
    SUM(IIF(teBill = 'Billable' AND HoursType = 1), teHours, 0) As Bill1,
    SUM(IIF(teBill = 'Non-Billable' AND HoursType = 1), teHours, 0) As NonBill1,
    SUM(IIF(teBill = 'Billable' AND HoursType = 2), teHours, 0) As Bill2,
    SUM(IIF(teBill = 'Non-Billable' AND HoursType = 2), teHours, 0) As NonBill2
    from table
    group by pr_ppClientName
    Thanks so much. It was exactly what I needed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •