Results 1 to 4 of 4

Thread: SQL Count Query

  1. #1
    Join Date
    Jun 2009
    Posts
    12

    Unanswered: SQL Count Query

    Hello All,

    I am attempting to create a summary table in Microsoft Access from a table.

    I want the table to have three columns

    Lawson ID, Uabs, Adherence.

    I want the query to count the number of Uabs and Adherence and insert the data along with the Lawson into a another table.

    I have tried several count statements, nested queries, etc but am having no luck.

    Here is what the data looks like.

    Lawson Date Name Occurence Letter Comments
    120486
    121393 2/6/2009 Alexander, Jeffrey Adherence Late-16 mins
    121393 1/24/2009 Alexander, Jeffrey Adherence Late- 5 mins
    121393 5/16/2009 Alexander, Jeffrey Adherence Late- 1 hr 28 mins
    121393 5/8/2009 Alexander, Jeffrey Adherence Late- 4 mins
    121393 2/13/2009 Alexander, Jeffrey Adherence Late- 4 mins from break
    121393 5/30/2009 Alexander, Jeffrey Adherence Left Early- 2 hr 45 mins
    121393 6/13/2009 Alexander, Jeffrey Adherence Written - Adherence Written re-issued
    121393 4/23/2009 Alexander, Jeffrey Adherence Late- 5 mins from break
    121393 1/19/2009 Alexander, Jeffrey Uabs Car trouble
    121393 3/15/2009 Alexander, Jeffrey Uabs Sick
    121393 2/14/2009 Alexander, Jeffrey Adherence Late- 4 mins from lunch
    121393 3/27/2009 Alexander, Jeffrey Adherence Late- 10 mins
    121393 1/13/2009 Alexander, Jeffrey Written - Adherence
    101156 3/8/2009 Antao, Francine Adherence Left Early
    101156 3/15/2009 Antao, Francine Uabs
    101156 3/22/2009 Antao, Francine Adherence 2 hrs 59 mins late
    101156 3/31/2009 Antao, Francine Adherence Left Early
    101156 4/26/2009 Antao, Francine Uabs
    101156 5/3/2009 Antao, Francine Adherence Left Early
    101156 2/1/2009 Antao, Francine Uabs

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    tlugdon, First, if you have a Microsoft Access question, you need to post in the Access forum. This one is for SQL Server.

    Second, I can show you some SQL that will work but may not be in Access format. I use the CASE expression but I think the IIF function will do the same thing.
    Code:
    SELECT Lawson_ID
         , SUM(CASE 
                   WHEN [Occurence Letter Comments] LIKE '%Uabs%' 
                                    THEN 1 
                                    ELSE 0
               END
              ) AS UAB_CNT
         , SUM(CASE 
                   WHEN [Occurence Letter Comments] LIKE '%Adherence%' 
                                    THEN 1 
                                    ELSE 0
               END
              ) AS Adherence_CNT
    FROM table-name
    GROUP BY Lawson_ID

  3. #3
    Join Date
    Jun 2009
    Posts
    12
    Sorry bout that... I didn't see the Access thread, and I was writing the query in SQL so I figured this was the best place.

    Thanks for the reply I will give it a shot, although I had already tried a query similar to that.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you ask really nice one of us moderator types might just move the thread to the MS-Access forum for you.

    MS-Access actually has several nifty tools for doing just this kind of transformation.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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