Notices

Great community. Great ideas.

Welcome to ASP/Free, a community dedicated to helping beginners and professionals alike in improving their knowledge of Microsoft's development and administration technologies. Sign up today to gain access to the combined insight of tens of thousands of members.

Thread: **HELP** - I'm new to access - Points & Percentages

Results 1 to 8 of 8
Share This Thread →
  1. #1
    LSSGreenBelt is offline Registered User
    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0

    **HELP** - I'm new to access - Points & Percentages

    Hi all!

    I have set up a Access Database to be used as a Quality Monitoring Form for Call Center Auditors.

    I have a list of questions with the answers in drop down boxes, dependent on the answer selected, a score will be generated. The scores are then totaled up to give an over all score.

    I have 3 questions.

    1) How do I also generated a percentage score that will be displayed on the Form but also saved into my table?

    2) Some questions can be answered with 'N/A' which obviously has '0' points attached. If N/A is selected, how to I remove the 'maximum points possibly awarded for that questions' from the percentage?

    3) On my table, the points shown in each field are cumulative, however I would like to view the points scored for each individual question and then a total.

    Sorry if this is not clear - any help would be VERY gratefully received!!

  2. #2
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    There's really not enough information to go on here. We know absolutely nothing about your data structure or how your DB is set up. Having said that, you should read the stickies at the top of the forum they have some great information on how to properly set up a DB. Some things I can tell you from your questions.
    1.) DO NOT save a calculated value in your table. This is just asking for trouble. Only do calculations as you need them for display purposes.
    2.) No idea, we would need to see your DB to help. Post it to a file sharing site and link it so that we can take a look. (remove any confidential information and compact and repair it before loading.)
    3.) A cumulative column would seem to indicate that this a calculated field. refer to #1. You should be saving the response of each individual question and response seperately then doing the calculations to add them up only as you need to.

  3. #3
    LSSGreenBelt is offline Registered User
    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    Thank you for your response.

    I will 'TRY' to explain in more detail.

    I have 3 tables set up

    1) The Form table, where all the information is stored.

    2) I have a 'Category' Table with my unique ID.

    3) I then have the Criteria table with 3 columns
    - The possible answers that appear in the drop down boxes of the form.
    - The Question ID from the Questions Table
    - Points - How many points you get dependent on the answer given.

    For example -

    Question/Category
    1 How old are you?
    2 What is your eye color?

    Answer/Criteria Question ID Points
    Under 21 1 5
    22 - 35 1 10
    Over 35 1 15
    Blue 2 5
    Green 2 10
    Brown 2 15



    My combo boxes on my forms have a row source of...

    SELECT [QualityCriT].[ID], [QualityCriT].[CriteriaName], [QualityCriT].[Points] FROM QualityCriT WHERE CategoryID=1 ORDER BY [Points];

    I then have a event procedure 'After Update' to 'Calculate Points' in my 'Points' box.

    Private Sub CalculatePoints()

    Points = 0
    Points = Points + Val(Nz(CatageCmb.Column(2)))
    Points = Points + Val(Nz(CateyecolorCmb.Column(2)))

    Private Sub CatageCmb_AfterUpdate()

    CalculatePoints

    etc....

    In my Form Table I currently see this....

    Name Contact Age Eye Color Points
    Emma xxxxxx 10 20 20
    Sam xxxxxx 10 15 15

    I would like to be able to see this....

    Name Contact Age Eye Color Points %
    Emma xxxxxx 10 10 20 66%
    Sam xxxxxx 10 5 15 50%

    Once the score is calculated once, it will not changed.

    These scores will remain in the database and a report will be pulled for us to monitor performance and progression.

    Does this help or hinder??????

    I will try to upload what I have already....

  4. #4
    June7's Avatar
    June7 is offline Moderator
    Join Date
    Apr 2009
    Location
    The Great Land
    Posts
    4,618
    Rep Power
    649
    I agree with meratigoerr.

    Enter raw data and do summary calcs as needed. A report object would be ideal for this. Allows display of detail records and summary calcs (including percentages) in report header/footer sections.

    Percentage of what - apparently highest possible total points.

    Is age and eye color real data or is that just dummy example?

    The Form table does not appear to be normalized structure. How many reponse columns are there?
    Last edited by June7; January 31st, 2013 at 08:00 PM.
    I like to know if my suggestion helped you. One way to let me know is by clicking star at bottom left corner of post.
    Debug!Debug!Debug!http://www.cpearson.com/excel/debug.htm

  5. #5
    LSSGreenBelt is offline Registered User
    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    Ok - i think I am getting my head around this now - thank you!

    No - the age/eye is just dummy data.

    It is a large QM sheet - roughly 20 questions with 4 possible responses for each.

    The issue I am having is dealing with the N/A responses.

    For example - if a certain question is 'N/A' - i need to deduct the entire question out of the end score so that they will have 0 out of 0 rather than 0 out of 30 (for argument sake) so that it doesn't skew there overall score and makes the QM process as fair as possible.

    I would like the end users of this not to have to do any calculations themselves - it's currently set up so that they can only use the user friendly form.

  6. #6
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    How are the actual results of your data stored. If you're storing them in a properly normalized table it should look somehting like this: (response of 0 indicates an N/A)

    ID : QuestionID : QuestionResult : TesteeID : MaxPoints

    With data like this:
    ID QuestionID QuestionResult TesteeID MaxPoints
    1 1 1 1 3
    2 2 1 1 3
    3 3 2 1 3
    4 4 3 1 3
    5 5 2 1 3
    6 6 1 1 3
    7 7 2 1 3
    8 8 2 1 3
    9 9 1 1 3
    10 10 3 1 3
    11 11 2 1 3
    12 12 2 1 3
    13 13 2 1 3
    14 14 2 1 3
    15 15 1 1 3
    16 16 0 1 3
    17 17 0 1 3
    18 18 1 1 3
    19 19 3 1 3
    20 20 2 1 3

    the query:
    Code:
    SELECT Sum(Table1.QuestionResult) AS SumOfQuestionResult, Table1.TesteeID, Sum(IIf([QuestionResult]=0,0,[MaxPoints])) AS TotalPoints
    FROM Table1
    GROUP BY Table1.TesteeID;
    Would yeild the results:
    TesteeID SumOfQuestionResult TotalPoints
    1 33 54

    Then use another query to calculate your percentages.

    Unfortunely I don't know enough about your table structurs to know if this will help you. If you do have values stored horizontally instead of Vertically, then you have a real challenge on your hand and should consider redesigning your tables before moving on any further.

  7. #7
    LSSGreenBelt is offline Registered User
    Join Date
    Jan 2013
    Posts
    4
    Rep Power
    0
    Oh no - it's horizontal!

    Thank you all - looks like i'm going to be starting again!

    Valuable lesson learnt!!

  8. #8
    meratigoerr's Avatar
    meratigoerr is offline Contributing User
    Join Date
    Jun 2011
    Location
    South Jersey
    Posts
    544
    Rep Power
    58
    Vertical storage of data over horizontal is usually refered to as Normilaztion (sp?). This is how data is stored in an access database versus an excel spread sheet. while it possible to store data horizontally in a database it will cause you great headaches and pain and should be avoided. Changing your tables to a more normilized setup, you will notice it becomes a lot easier to do many of the tasks you're asking about.

Share This Thread →

Become Part of This Conversation

Join NowFor Free!

Similar Threads

  1. Percentages
    By jonyBravo in forum ASP Development
    Replies: 4
    Last Post: July 4th, 2007, 02:15 PM
  2. 'Evil twin' Wi-Fi access points proliferate (InfoWorld)
    By RSS_News_User in forum Technology News
    Replies: 0
    Last Post: April 25th, 2007, 09:03 AM
  3. Asp and percentages
    By ghost06 in forum ASP Development
    Replies: 9
    Last Post: February 9th, 2007, 04:50 AM
  4. Decimal points in access and ASP
    By id123 in forum ASP Development
    Replies: 1
    Last Post: November 1st, 2005, 09:19 AM
  5. calculating percentages
    By markoc in forum ASP Development
    Replies: 1
    Last Post: February 15th, 2005, 10:21 AM

ASP Free Advertisers and Affiliates