Results 1 to 4 of 4

Thread: Query

  1. #1
    Join Date
    Mar 2008
    Posts
    8

    Unanswered: Query

    I have a field in my table Claim Quality Index which has file scores 3.0, 2.5, 2.0, 1.5, 1.0. I need to build a query that will give me the count of each of the scores above - I want it to show how many files were scored 3.0 in one column and how many files scored 2.5 in a separate column and so on. I also use the first column as my date of reinspection as I will need these counts by certain dates, quarter, yearly, or by audit. I use Between[Enter Beginning Date]and[Enter Ending Date] and choose the where as I do not need the dates to show I just need the data for that time frame.

    I have used various formulas but I cannot seem to get them to work. I will use this query to feed other reports I have built into the database. I need these counts in order to calculate the CQI score we give to an individual, team, office, or audit. I have tried various IIf statements i.e., IIf([Claim Quality Index]=3.0,Count[Claim Quality Index],"") I get an error - I have also tried the DCount statement but to no avail either.

    I hope someone can help me build this query or is the an SQL statement that could use?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try something like...
    Code:
    SELECT SUM(IIF([Claim Quality Index]=3.0,1,0)) AS Count_30
         , SUM(IIF([Claim Quality Index]=2.5,1,0)) AS Count_25
         , ...
    notice how the SUM of 1s and 0s produces the count
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    Posts
    8

    Query

    Thank you - how would I end the SQL statement

  4. #4
    Join Date
    Mar 2008
    Posts
    8
    Thank you it worked - all I had to do was add "" around each score - thanks for your help

Posting Permissions

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