Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a query that groups a table by client id, showing counts for each client id. See attached screenshot for query specs.

    I would like to add another row to the results of this query that shows the total for all client ids.

    How would I do this?

    Thanks.
    Attached Images Attached Images
    Carol W.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    That sort of thing is best done using a report rather than a query - adding a row to a query is rather cumbersome. In this case you could probably do it with a Union query, but to get it to appear at the bottom would involve lots of tricks. Reports have the ability to sum the count of intClientType pretty easily.
    Wendell

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You beat me to the punch Wendell
    The Short Answer is You Cannot in Access using the Query Designer.
    It is a feature available in SQL Server but NOT Access.

    There are a couple of ways round this

    1. Send the results of this query to a Tablular Form or Report
    then put a SUM(countOfIntClientType) into the Report/Form Footer


    2. In Order to do this in a Single Query you need to resort to a UNION Query.
    And this means Writing the SQL Script
    In the query window you need to use View SQL

    It would, from your example look a bit like this.

    However I cannot see all the correct field, calculation and table name details so this is an outline...

    Code:
    SELECT    YourIIF(Function) AS [WhatEverYouWantToCallIt],Count(intClientType) As [Client Types]
    FROM   	tblCombinedClientetc 
    WHERE 	dteDateServiced Between #StartDate# And #EndDate# 
    GROUP BY YourIIF(Function)
    
    UNION ALL
    
    SELECT "TOTAL ALL" AS [Grand Total], Count(*) As [Total Types]
    FROM   	tblCombinedClientetc 
    WHERE 	dteDateServiced Between #StartDate# And #EndDate#
    GROUP BY "TOTAL ALL"
    NOTE in The Above, StartDate and EndDate are in Fixed date Format
    and must be in the form #mm/dd/yyyy#

    eg. 15th Jan 2010 would be #01/15/2010#

    If you are using Parameters then the WHERELine Would be

    WHERE dteDateServiced Between [Start Date] And [End Date] in both parts

    Make sure you spell them the same!


    You can play around with Labels after the AS and also the "Text"
    Andrew

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Or by having a completely separate query that counts without the Group By.
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for all the suggestions. I knew I could do this in a report but I wanted to try it in a query just because I wanted to try it in a query.

    AndrewKKWalker, your Union suggestion worked! Thanks.

    Here is the final version of the SQL:

    SELECT IIf([intClientType]=1,"CCSD",IIf([intClientType]=2,"CCSD Bags",IIf([intClientType]=3,"HOPE",IIf([intClientType]=4,"HOPE Bags","Other")))) AS Expr1, Count([tblCombined client data].intClientType) AS CountOfintClientType
    FROM [tblCombined client data]
    WHERE ((([tblCombined client data].dtDateServiced) Between [Start Date] And [End Date]))
    GROUP BY IIf([intClientType]=1,"CCSD",IIf([intClientType]=2,"CCSD Bags",IIf([intClientType]=3,"HOPE",IIf([intClientType]=4,"HOPE Bags","Other"))))

    UNION ALL SELECT "Grand Total" AS Expr1, Count([tblCombined client data].intClientType) AS CountOfintClientType
    FROM [tblCombined client data]
    WHERE ((([tblCombined client data].dtDateServiced) Between [Start Date] And [End Date]));

    Attached is a screenshot of the results.

    Thanks again!
    Attached Images Attached Images
    Carol W.

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    You could make the headings better by changing the Expr and Automatic Access names

    Code:
    SELECT IIf([intClientType]=1,"CCSD",IIf([intClientType]=2,"CCSD Bags",IIf([intClientType]=3,"HOPE",IIf([intClientType]=4,"HOPE Bags","Other")))) AS [Client Type], 
    Count([tblCombined client data].intClientType) AS [Clients]
    FROM [tblCombined client data]
    WHERE ((([tblCombined client data].dtDateServiced) Between [Start Date] And [End Date]))
    GROUP BY IIf([intClientType]=1,"CCSD",IIf([intClientType]=2,"CCSD Bags",IIf([intClientType]=3,"HOPE",IIf([intClientType]=4,"HOPE Bags","Other"))))
    
    UNION ALL SELECT "Grand Total" AS GrandTotal, Count([tblCombined client data].intClientType) AS [Clients]
    FROM [tblCombined client data]
    WHERE ((([tblCombined client data].dtDateServiced) Between [Start Date] And [End Date]));

    IF You have a numerical lookup like this you can also use Choose with IIF to make it simpler

    eg


    SELECT IIf([intClientType] Between 1 and 4,Choose("CCSD","CCSD Bags","HOPE","HOPE Bags","Other") AS [Client Type]

    Andrew

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again, Andrew. I've tidied up the column headings.
    Carol W.

  8. #8
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again, Andrew. I've tidied up the column headings.
    Carol W.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again, Andrew. I've tidied up the column headings.
    Carol W.

  10. #10
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again, Andrew. I've tidied up the column headings.
    Carol W.

  11. #11
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again, Andrew. I've tidied up the column headings.
    Carol W.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again, Andrew. I've tidied up the column headings.
    Carol W.

  13. #13
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again, Andrew. I've tidied up the column headings.
    Carol W.

  14. #14
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Nevada, USA
    Posts
    207
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again, Andrew. I've tidied up the column headings.
    Carol W.

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    You could also use an additional table to hold the intClienttype and an associated description.

    Then you have the nice option of adding/deleting from this table and you dont have to modify the query on new ClientTypes or ClientTypes that are no longer applicable.

    Your query then becomes:

    SELECT ct.ClientTypeDesc AS TypeDesc, Count(ct.intClientType) AS CountOfintClientType
    FROM [tblCombined client data] AS cd INNER JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
    WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]))
    GROUP BY ct.ClientTypeDesc

    UNION ALL
    SELECT "Other" AS Expr1, Count(cd.intClientType) AS CountOfintClientType
    FROM [tblCombined client data] AS cd LEFT JOIN tblClientTypes AS ct ON cd.intClientType = ct.intClientType
    WHERE (((cd.dtDateServiced) Between [Start Date] And [End Date]) AND ((ct.intClientType) Is Null))

    UNION ALL
    SELECT "Grand Total" AS TypeDesc, Count(cd.intClientType) AS CountOfintClientType
    FROM [tblCombined client data] AS cd
    WHERE cd.dtDateServiced Between [Start Date] And [End Date];

    I have included my test database.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

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