Results 1 to 4 of 4
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: How To Create Total Count of 3 Fields in a Report

    Hi All,

    I have a query with 4 fields but I want to create Chart Report to get the Total Count of 3 types of data in the following order:

    1- Department ( i mean listed by Department order)
    2- Then ServiceType ( this would be the count of servicetype in a specific period. For example, how many times our CleaningService was used in December, How many times TeachingService was used in December by each department.

    To summarise, the chart should read look like:

    Department= Housing
    ServiceType = CleaningService = Then it should have total count of This Particular ServiceType

    We want this to be ranked by High to Low number. I mean Highest Count of Service Top.

    I would really appreciate if you could please help on what type of Query I should create and what type of report I should choose.

    Thanking you all in advance.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    assuming this data is in the samne table :

    1. create a query in design mode - at the top click the summation symbol

    2. bring down the fileds department and service type
    3. using drop down below Department choose group by
    4. using drop down below service type choose count
    5. click the red exclamation at the top.
    Dale Houston, TX

  3. #3
    Join Date
    May 2006
    Posts
    386
    Hi Axsprog,
    Thank you for your response. I am sorry I forgot to mention that there are many clients in the BookingTable which has all the data in it, and at first selection we will need to choose a clientID i.e. 1, 2 or 100 whatever their ID, and then Start Date and End Date and the report should then prodcue a Report Chart showing the following something like this:

    ClientName: ABC Company Ltd.
    Department: Finance Department. ServiceType: Cleaning - Number of Times used this service = 20
    Department: Finance Department. ServiceType: Teaching- Number of Times used this service = 55


    by the way, this the design of my query in sql view:

    SELECT Booking.ClientID, Booking.DateOfJob, Booking.DeptOrTypeofCase, Count(Booking.Language1ID) AS CountOfLanguage1ID
    FROM Booking
    GROUP BY Booking.ClientID, Booking.DateOfJob, Booking.DeptOrTypeofCase
    HAVING (((Booking.ClientID)=[Enter First Client Acc] And (Booking.ClientID)=[Start End Client Acc]) AND ((Booking.DateOfJob)=[Enter Start Date] And (Booking.DateOfJob)=[Enter End Date]))
    ORDER BY Booking.DeptOrTypeofCase;
    The report can be in any design as long as it shows the name of ClientID. Department and Number of Services Used by Each Department.
    Last edited by Emal; 01-04-13 at 06:05.
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    my question is then - what are you looking for - you are getting count of one of those fieldds - is this not the field you are counting the instances of?

    the qury looks fine so I am a bit confused.
    Dale Houston, TX

Posting Permissions

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