Results 1 to 5 of 5

Thread: Query Grouping

  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Query Grouping

    Hello all,

    I am trying to combine a list of items in a query. Here is the raw data:

    pr_ppClientName..........teBill...............SumO fteHours
    Sears.........................Non-Billable...............11
    Sears.........................Billable............ ...........5
    Sears.........................Non-Billable...............24
    Sears.........................Billable............ ...........10

    Here is the result I am shooting for:

    pr_ppClientName.............Billable.......Non-Billable
    Sears.................................35.......... 15

    The reason I want it to combine these two billing types in one line is the data will ultimately end up in a report and I don't want the report to look like this (which is what I seem to be getting):

    pr_ppClientName.............................Billab le.......Non-Billable
    Sears............................................. ..35...................
    Sears............................................. ......................15

    Thanks for the help.

    Norm

  2. #2
    Join Date
    Feb 2004
    Location
    Dublin, Ireland
    Posts
    212

    Re: Query Grouping

    Originally posted by norm801
    Hello all,

    I am trying to combine a list of items in a query. Here is the raw data:

    pr_ppClientName..........teBill...............SumO fteHours
    Sears.........................Non-Billable...............11
    Sears.........................Billable............ ...........5
    Sears.........................Non-Billable...............24
    Sears.........................Billable............ ...........10

    Here is the result I am shooting for:

    pr_ppClientName.............Billable.......Non-Billable
    Sears.................................35.......... 15

    The reason I want it to combine these two billing types in one line is the data will ultimately end up in a report and I don't want the report to look like this (which is what I seem to be getting):

    pr_ppClientName.............................Billab le.......Non-Billable
    Sears............................................. ..35...................
    Sears............................................. ......................15

    Thanks for the help.

    Norm
    You can use TRANSFORM in your select, which can transform data to
    format you need,
    but I realy don't rember syntax becouse it's too long time I've last used MS Access. Try to find it in some manual with example.

    good luck :-)

  3. #3
    Join Date
    Feb 2004
    Location
    Washington
    Posts
    49
    Select Sum(TeHour), pr_ppClientName, teBill as ‘Billable, 0 as ‘Non-Billable’
    From tablename
    Where teBill = ‘Billable’
    Group by pr_ppClientName, teBill

    Union All

    Select Sum(TeHour), pr_ppClientName, 0 ‘Billable, teBill as ‘Non-Billable’
    From tablename
    Where teBill = ‘Non-Billable’
    Group by pr_ppClientName, teBill


    For a report - have a column for the ‘Billable' and a column for the 'Non-Billable' fields. In the group footer, total the 'Billable' and the 'Non-Billable' columns.
    Cathy

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    What you need is a Cross Tab Query. You'll want to group the client field and the bill field and then sum on the hours. Then when you make it a Cross Tab Query a CrossTab field will appear. For the Client field make it the Row Header, for the Bill field make it the Column Header and for the Sum of the Hours make it the Value. This is all n Query design. If it is easier then here is a sample SQL:

    TRANSFORM Sum(Temp.Hours) AS SumOfHours
    SELECT Temp.Client
    FROM Temp
    GROUP BY Temp.Client
    PIVOT Temp.Bill

    Change the table name and the field names

  5. #5
    Join Date
    Jan 2004
    Posts
    100
    Huge help!

    Thanks a bunch.

    Norm

Posting Permissions

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