Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2009
    Posts
    50

    Unanswered: need all rows where less than 50% of columns are true

    I have a table that consits of 63 bool columns. I would like to know if there is a way to query only rows where less than 50% of the columns are true?

    From another thread in this forum I got a reply:
    Code:
    SELECT emp_id
         , result
      FROM ( SELECT emp_id
                  , CASE WHEN column1 IS TRUE THEN 1 ELSE 0 END +
                    CASE WHEN column2 IS TRUE THEN 1 ELSE 0 END +
                    CASE WHEN column3 IS TRUE THEN 1 ELSE 0 END +
                    ...
                    CASE WHEN column63 IS TRUE THEN 1 ELSE 0 END AS result
              FROM Skillset ) AS d
     WHERE result < 32
    However, CASE dose not work in Access. What I'm looking for is a way to accomplish this with a SWITCH statement.
    I need to report on all the employees who have less than 50% of their skill set certifications completed.
    Thanks
    Jim

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use IIF instead of CASE
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    This would usually be the part where I strongly suggest you redesign your tables for normalization so you wouldn't have to do this kind of thing, but I see Rudy already made the suggestion in the "other" thread...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In a query, you can use Rudy's suggestion with the IIF as an expression.

    ExpressionColumn1: IIF([Column1]=True,1,0)

    and so on for the other columns.

    Then base another query off of this one which sums ExpressionColumn1, ExpressionColumn2, etc... and any other calculations or criteria needed.

    Ideally though you could also write a function in a module which returns the counts or a true/false value if the counts exceed or are less than 50&#37;.
    Last edited by pkstormy; 05-03-10 at 19:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    I am submitting a solution that handles your problem with code and the addition of one field to your table. In my example, the last field in the table is named CompletedPerCent and the data type is double. This field stores the percent of skill sets completed, and is updated by a function in a query.

    Code in Module1 builds a query that includes a function (also in the module). I found that I could not rely on a stored query because the function would occasionally vanish from the query.

    My table has 25 Yes/No columns, so you would therefore edit the line Const FieldCount = 25 to read 63 to be in sync with your 63 columns.

    Use Macro1 to run the procedure; this will bring the query results into view.

    Any improvements or criticisms are welcome. Database is attached.
    Jerry
    Code:
    Function Query_SkillSets()
    Dim db As Database, rs As Recordset
    Dim qdf As QueryDef
    Dim strSQL As String, qryName As String, tblName As String
    
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("SkillSets", dbOpenSnapshot)
    
    tblName = "SkillSets"
    qryName = "LessThan50PC"
    
    For Each qdf In db.QueryDefs
      Select Case qdf.Name
        Case qryName
            DoCmd.DeleteObject acQuery, qdf.Name
        Case Else
            'Do nothing
      End Select
    Next qdf
    
    strSQL = "SELECT " & tblName & ".ID, " & tblName & ".CompletedPerCent, " & _
        "[CompletedPerCent]*25 AS SkillSetCount, GET_IDS() AS Expr1 " & _
        "FROM " & tblName & _
        " WHERE (((" & tblName & ".CompletedPerCent)<0.5)); "
    
    Set qdf = db.CreateQueryDef(qryName, strSQL)
    
    DoCmd.OpenQuery qryName, acViewNormal, acQuery
    
    Set rs = Nothing
    Set db = Nothing
    
    End Function
    
    Function Get_IDS()
    Const FieldCount = 25
    Dim db As Database
    Dim rst As Recordset
    Dim FieldName As Field
    Dim i As Integer, j As Integer, rCount As Integer
    Dim sqlText As String
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SkillSets", dbOpenTable)
    rCount = rst.recordCount
    
    For i = 1 To rCount
        j = 0
        For Each FieldName In rst.Fields
            If FieldName.Value = True Then j = j + 1
            If FieldName.Name = "CompletedPerCent" Then
                rst.Edit
                rst!CompletedPerCent = (j / FieldCount)
                rst.Update
            End If
        Next FieldName
        rst.MoveNext
    Next i
    
    Set rst = Nothing
    Set db = Nothing
    
    End Function
    Attached Files Attached Files

  6. #6
    Join Date
    Dec 2009
    Posts
    50
    Question? How can I redesign the table for normalization?
    If I undersatand it correctly, I would have 3 columns:

    Emp_ID
    SkillName
    Completed

    And then I would have 63 rows for Each Emp_ID. Instead of 1 row for each Emp_ID w/ 63 columns of Named Skills that have or have not been completed. Is this correct?

    The program that I'm writing relieghs on the named columns for updating the employee record.

    Here is the table:
    Code:
    Table = Emp_SkillSet 
    EmpID 
    WinStraightPB 
    WinPBAdm60 
    WinPBAdm601 
    WinPB60106 
    WinUp48To60 
    WinUp60To601 
    WinUpTo60106 
    WinMechFileChange 
    WinPBeXpress 
    LinStraightPB 
    LinPBAdm60 
    LinPBAdm601 
    LinPB60106 
    LinUp48To60 
    LinUp60To601 
    LinUpTo60106 
    LinMechFileChange 
    LinPBeXpress 
    MisRealVu2x 
    MisRealVu60x 
    MisWebVu502Tom 
    MisWebVu60x 
    MisWebVu601x 
    MisPBeXpressCisco 
    GatFileServ405 
    GatMic405 
    GatMic60 
    GatTCP405 
    GatTCPNet405 
    GatTCP60 
    GatTCPNet60 
    SpecInstallCallPBFE 
    SpecInstallCarlson 
    SpecInstallGalaxy 
    SpecInstallIHGCut 
    TrainPBADM50 
    TrainRealVu20 
    TrainPBADMReal60 
    TrainPBADMReal601 
    TrainWebVu50 
    TrainWebVu60 
    TrainWebVu601 
    TrainPB60106 
    TrainWebCon 
    SkillAspenTerm 
    SkillDataMan 
    SkillDevTran101 
    SkillDevTran201 
    SkillDiagTrouble 
    SKillFlexicomSup 
    SkillGalaxySup 
    SkillPanasonicSup 
    SkillMonerisCert 
    SkillBasicLogReading 
    SkillComLogReading 
    SkillSTELogReading 
    SkillNetSendIIS 
    SkillHeatDoc 
    SkillClarify 
    Tier1CertReq 
    Tier1CertComplete 
    Tier2CertReq 
    Tier2CertComplete 
    LastUpdate
    So I guess my question is How and Why? How would I redisign this table and Why is it necessary? Just curious and looking for knowledge for the future.

    Thanks
    Jim

  7. #7
    Join Date
    Dec 2009
    Posts
    50
    Sorry, cant get the IIF to work. Im getting a syntax error in join operation. How do you add more than one IIF statement and do I need to name the columns or use column1, column2 ?
    Not sure how to get this going.
    Thanks
    Jim

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You don't need to use iif - Access Booleans are -1 and 0 so just add them and take the ABS (you will get a negative number).

    I agree 100&#37; with Teddy. Google first normal form and pay special regard to repeating Groups - this is a special case of repeating groups problems. One reason why is the very basis of this thread - querying repeating groups is a PITA. The solution to this query in a normalised design is trivial.

    Also, your normalised design would only require two columns - only record the skills that are completed. This will also ensure you don't have other integrity issues, for example when you add a new skill.

Posting Permissions

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