Results 1 to 12 of 12
  1. #1
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16

    Can you count how many yes/no boxes are checkd yes?

    I am trying to create a qualification tracker that takes 53 tasks to achieve this qualification. I need to track it for 300+ users. I have a table already for user data and I am not sure the best way of going about tracking this qualification.



    I thought doing a table w/ a relationship to the user table through the name and then have the 53 quals listed with yes/no for each and then as the user completes a qual hit yes for each task they completed in the table. I don't know how to develop the query for that and I need help.

    Maybe I need to do a table with all the 53 qualifications and then have another with entries for each time the user completes one but then again I don't know how to put that together to give me a percentage of completion.

    In the end I would like to be able to see where each user is at in completing the qual with a percentage. I would also like it to be easy enough to update the qualifications for the user.

    Please help.

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Probably a better alternative (it will be easier to do calculations, and it allows optinally to save some additional characteristics like grouping, or level, or whatever for different qualifications) will be an additional table with all characteristics, and a table where you link various qualifications to user. Something like:
    tblUsers: UserID, UserForeName, UserLastName, ...;
    tblQualifications: QualificationID, QualificationText, ...;
    tblUserQualifications: UserQualificationID, UserID, QualificationID.

    You'll have a subform for user qualifications, based on tblUserQualifications, in users form. Forms are linked with UserID - this limits only qualifications linked to user to be displayed, and links any qualification added in subform to be linked to user active in main form automatically.

  3. #3
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16
    I have seen that recommended before but you make sound a lot clearer. The only question I have is how would I get the percentage they are qualified. If they completed 25 of the 53 tasks I would like it to say 47% and then be able to view a report that displays all the users and what percentage they are at completing the qualification?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Chevy757IT View Post
    The only question I have is how would I get the percentage they are qualified. If they completed 25 of the 53 tasks I would like it to say 47% and then be able to view a report that displays all the users and what percentage they are at completing the qualification?
    When all users must have all qualifications, then you need a query like
    Code:
    SELECT u.UserID, COUNT(uq.QualificationID)/(SELECT COUNT(QualificationID) FROM tblQualifications) AS Percentage
    FROM tblUsers u LEFT JOIN tblUserQualifications uq ON uq.UserID = u.UserID
    GROUP BY u.UserID
    When the list of required qualifications varies for users, the you need an additional table (e.g. tblRequiredQualifications: RequiredQualificationID, UserID, QualificationID) where you register qualifications required for every user. On fly, the query will be something like
    Code:
    SELECT u.UserID, COUNT(uq.QualificationID)/(SELECT COUNT(rq0.QualificationID) FROM tblRequiredQualifications rq0 WHERE rq0.UserID = u.UserID) AS Percentage
    FROM (tblUsers u LEFT JOIN tblUserQualifications ON uq.UserID = u.UserID) LEFT JOIN uqtblRequiredQualifications rq ON rq.UserID = uq.UserID AND rq.QualificationID = uq.QualificationID
    WHERE rq.QualificationID Is Not Null
    GROUP BY u.UserID

  5. #5
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16
    Do I input this in the expression builder or how would I put that in?

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Select SQL view after you open SQL designer. You can copy query string into SQL Designer window in SQL view, and you can edit the query string there directly.

  7. #7
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16
    You have been so helpful thanks but I am kind of confused on the SQL code.
    So this is what I have:
    Tables:
    Command_User this table has user data
    Qual_Item_List this table has a list of all the qualifications needed to obtain the qual
    Qual_Tracker this table has all the qualifications the user has completed

    Command_User:
    ID - Primary Key
    Last Name, First Name - Self explanatory

    Qual_Item_List:
    ID - Primary Key
    Qualification Item

    Qual_Tracker:
    ID - Primary Key
    Last Name, First Name - linked to the user table
    Qualification Item - Linked to the Qual_Item_List table.

    I created the form and the subform now I am working on the query with the code and it is not working. Do I need to redesign this?

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    In attachment is an example. For form you use DLookup formula, which calculates the percentage for active user in unbound text box. For e.g. reports you can use a saved query like one I added into example database.
    Attached Files Attached Files

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    If you stick with the non-normalized structure, use VBA to loop through a recordset of the one record and return count to query or textbox. Example of looping a recordset to look at yes/no fields https://stackoverflow.com/questions/...45662#55345662

    With a normalized structure, use report Sorting & Grouping features with aggregate calcs in textboxes in group and report header/footer sections. This allows display of raw data as well as summary calcs.

    Textbox in report footer: =Count(*)

    Then textbox in group footer section references the report footer textbox:
    =Count(*) / tbxReport * 100
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16
    I keep getting "your query does not include the specified expression as part of an aggregate function"

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What query? Post the attempted SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Chevy757IT is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Jan 2015
    Posts
    16
    NM I went back in today and it is working fine. I must have fat fingered something yesterday. ArviLaanemets and June7 Thank you sooo much with helping me with this!!!!!!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Count of Combo Boxes with data
    By Mask in forum Forms
    Replies: 1
    Last Post: 06-28-2017, 03:37 AM
  2. Count Checked boxes on report
    By BLFOSTER in forum Reports
    Replies: 2
    Last Post: 06-22-2015, 09:33 AM
  3. count records, add boxes
    By Mclaren in forum Reports
    Replies: 2
    Last Post: 04-28-2011, 03:27 AM
  4. How to count multiple text boxes
    By Brian62 in forum Reports
    Replies: 6
    Last Post: 11-10-2010, 03:22 PM
  5. help with totals for count text boxes
    By gacapp in forum Reports
    Replies: 10
    Last Post: 07-22-2009, 11:26 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums