Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Question Unanswered: Consecutive Days

    Hello,

    I am new to the access world! So bare with my lack of knowledge.

    I have a table [SQI Scores]
    Columns:
    [Name],[Score], and [Date Scored].

    What I am trying to achieve is a list of [name] where [Score] is grater than or = to .85 4 or more consecutive times by [Date Scored].

    Any assistance would be greatly appreciated.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not 100% sure, but it could be something like:
    Code:
    SELECT b.Name
        FROM (
              SELECT SQI_Scores.Name, 
                     SQI_Scores.Score, 
                     SQI_Scores.Date_Scored, 
                     SQI_Scores.Name
                FROM SQI_Scores 
                    INNER JOIN (
                                SELECT SQI_Scores.Name, 
                                       SQI_Scores.Date_Scored, 
                                       SQI_Scores.Name
                                    FROM SQI_Scores
                                    WHERE SQI_Scores.Score>=0.85
                                    ORDER BY SQI_Scores.Date_Scored, 
                                             SQI_Scores.Name
                               ) AS a 
                    ON SQI_Scores.Name = a.Name
                WHERE DateDiff("d",[a].[date_scored],[sqi_scores].[Date_scored]) =1
             ) AS b
        GROUP BY b.Name
        HAVING COUNT(b.Name) >= 3;
    Have a nice day!

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    Quote Originally Posted by Sinndho View Post
    I'm not 100% sure, but it could be something like:
    Code:
    SELECT b.Name
        FROM (
              SELECT SQI_Scores.Name, 
                     SQI_Scores.Score, 
                     SQI_Scores.Date_Scored, 
                     SQI_Scores.Name
                FROM SQI_Scores 
                    INNER JOIN (
                                SELECT SQI_Scores.Name, 
                                       SQI_Scores.Date_Scored, 
                                       SQI_Scores.Name
                                    FROM SQI_Scores
                                    WHERE SQI_Scores.Score>=0.85
                                    ORDER BY SQI_Scores.Date_Scored, 
                                             SQI_Scores.Name
                               ) AS a 
                    ON SQI_Scores.Name = a.Name
                WHERE DateDiff("d",[a].[date_scored],[sqi_scores].[Date_scored]) =1
             ) AS b
        GROUP BY b.Name
        HAVING COUNT(b.Name) >= 3;
    Sinndho thank you for the response.

    Getting a Syntax error on the from clause, Also the days are not consecutive in a row they may be 1 or 2 per month. There may be months that have no scores at all. Any thoughts would be Helpful.

Posting Permissions

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