Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Unanswered: How to Count these Records?

    The data looks like this:

    Entered On AD Greetings AD Friendliness AD Attentiveness AD Language Skills
    18-Mar-09 Poor Excellent Excellent Good
    18-Mar-09 Excellent Good Poor Good

    I have tried several ways of counting these records but failed to achieve what i want.

    What should i do to count the similar records?
    The results i want based on the above is like this
    Poor = 2 records
    Fair = 0 record
    Good = 3 records
    Excellent = 3 records

    Please help me with this. Thanks!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT rating
         , COUNT(*) AS record
      FROM (
           SELECT [Ad Greetings] AS rating
             FROM daTable
           UNION ALL
           SELECT [Ad Friendliness] AS rating
             FROM daTable
           UNION ALL
           SELECT [Ad Attentiveness] AS rating
             FROM daTable
           UNION ALL
           SELECT [Ad Language Skills] AS rating
             FROM daTable
           ) AS d
    GROUP
        BY rating
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    SELECT Feedback.[Entered On], Choose([Feedback].[Concierge Greetings and Friendliness],"Poor","Fair","Good","Excellent") AS [Concierge Greetings and Friendliness], Choose([Feedback].[FO Greetings and Friendliness],"Poor","Fair","Good","Excellent") AS [FO Greetings and Friendliness], Choose([Feedback].[Driver Greetings and Friendliness],"Poor","Fair","Good","Excellent") AS [Driver Greetings and Friendliness], Choose([Feedback].[AD Attentiveness],"Poor","Fair","Good","Excellent") AS [AD Attentiveness], Choose([Feedback].[AD Language Skills],"Poor","Fair","Good","Excellent")

    I use the above to convert the numbers to automatically select the ratings in query (Feedback by Contact Name).

    From here, when i used the following which you recommended, there said there is 'syntax error in FROM clause'. Is there anything else i should have changed?

    SELECT rating
    , COUNT(*) AS record
    FROM (
    SELECT [Concierge Greetings and Friendliness] AS rating
    FROM Feedback by Contact Name
    UNION ALL
    SELECT [FO Greetings and Friendliness] AS rating
    FROM Feedback by Contact Name
    UNION ALL
    SELECT [FO Greetings and Friendliness] AS rating
    FROM Feedback by Contact Name
    UNION ALL
    SELECT [AD Attentiveness] AS rating
    FROM Feedback by Contact Name
    ) AS d
    GROUP
    BY rating

    I am not a good at this. Hope you can help. Thanks!

  4. #4
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    FROM Feedback by Contact Name???

    I doubt that 'Feedback by Contact Name' is a table name?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  5. #5
    Join Date
    Mar 2009
    Posts
    5
    I was just reading your posts in 'Adding an extra field...' , very interesting. I was having the same problem as him but i used an alternative way to solve the problem.

    That's the Query name. Actually the table is name as Feedback and the records are entered in numbers like 1,2,3 and 4.

    From there, i have used the Choose function in Query to change the records to Poor, Fair, Good and Excellent. So this is done.

    But now, i need to calculate the records as i have illustrated above. So that i could create a chart.

    Any suggestion?

  6. #6
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    If your object name includes spaces, enclose it with square brackets...

    [Feedback by Contact Name]
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  7. #7
    Join Date
    Mar 2009
    Posts
    5
    You're EXCELLENT and so does r937!!! You just solved my problem. Thank you!

  8. #8
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    r937 should take all the credit!

    PS Try not to use spaces or invalid characters in your object names.

    I tend to recommend using a naming convention to avoid this and other pitfalls like accidentally using 'Reserved Words';

    This is a handy link;

    Microsoft Access Database Naming Conventions | Database Solutions for Microsoft Access | databasedev.co.uk

    Have fun out there...
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  9. #9
    Join Date
    Mar 2009
    Posts
    5
    SELECT d.Rating, Count(*) AS [Arrival and Departure]
    FROM (SELECT [Concierge Greetings and Friendliness] AS Rating
    FROM [Feedback by Contact Name]
    UNION ALL
    SELECT [FO Greetings and Friendliness] AS Rating
    FROM [Feedback by Contact Name]
    UNION ALL
    SELECT [AD Attentiveness] AS Rating
    FROM [Feedback by Contact Name]
    UNION ALL
    SELECT [AD Language Skills] AS Rating
    FROM [Feedback by Contact Name]
    UNION ALL
    SELECT [Driver Greetings and Friendliness] AS Rating
    FROM [Feedback by Contact Name]
    UNION ALL
    SELECT [AD Problem Solving Ability] AS Rating
    FROM [Feedback by Contact Name]
    UNION ALL
    SELECT [AD Efficiency] AS Rating
    FROM [Feedback by Contact Name]
    UNION ALL
    SELECT [AD Overall Experience] AS Rating
    FROM [Feedback by Contact Name]
    ) AS d
    GROUP BY d.Rating;

    In the end, i have this and it works. The results,

    Rating Arrival and Departure
    Excellent 5
    Fair 2
    Good 7
    Poor 2

    What should i do if i want to add additional field?

    Rating Arrival and Departure Food and Beverage
    Excellent 5 6
    Fair 2 5
    Good 7 4
    Poor 2 3 (sample)

    How should i continue with the SQL? Thanks in advance!

Posting Permissions

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