Results 1 to 15 of 15

Thread: Calculations

  1. #1
    Join Date
    Oct 2005
    Posts
    55

    Unanswered: Calculations

    Hi its been a while for me and once again I have a problem.

    I created a database in access 2000 that uses combo boxes. The response in a combo box is either 'Yes' or 'No'.

    Say my question is Do you use the main road every day?, and say 4 persons selected 'Yes' and 5 selected 'No' I want to be able to calculate how much said 'Yes'.

    How do I do this?

    Thanks much for the help.

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    I assume in your table you have a field for the question number and one for response. Create a query with question number and response as your fields. Then select from the menu at the top View then Totals. Now in the question number field check that the total row say Group by. In the response column change the total row to Count and then add "Yes" to the criteria row of the response column. This will give a total of responses with "yes" for each question. To give a total for a selected question then add the question number to criteria row in the question number column.

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You could simply sum the yes/no column.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    You can make a query, whose SQL is something like that
    PHP Code:
    SELECT Count([id]) AS YesCount
       FROM YourTableName
         WHERE yesOrNoField 
    'Yes'
    OR

    PHP Code:
    SELECT Count([id]) AS YesCount INTO aTempTableName
       FROM YourTableName
         WHERE yesOrNoField 
    'Yes'

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or an iif in the query
    select IIf([mycolumnname]="YES",1,0) AS mycolumnnameyes from mytable

    BTW, in my books you should have used a radio button for yes/no in the user interface rather than a combo/list or checkbox
    Im hoping you have stored the results as boolean values not as text
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    in my books you should have used a radio button for yes/no in the user interface rather than a ... checkbox
    Why?? I only ever use radios for mutually exclusive selections and checkboxes for all other yes/no selections. Your book is wrong
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    hmmm lets see

    perhaps we should revisit UI 101

    the OP was using a combo to display YES or NO.. for some reason perverse to them, so they want to display the answers (for all I know they may have used the abomination that is a lookup value in the table definition)

    if the answer is either YES or NO, then the answers are mutually exclusive... so either a radio button/option group or single check box is valid

    the user wants to display both answers, then (in my books) a radio/option group button is the 'correct' UI paradigm, as the check box doesn't explicitly say yes or no..

    using a check box works if the OP wants a single response where tick means yes and no tick means no......
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    hmmm lets see

    perhaps we should revisit UI 101
    http://www.germes-online.com/direct/...87/Handbag.jpg

    BTW - Healdem's code is ideal as you can calculate % very easily with that.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    I prefer.....
    Attached Thumbnails Attached Thumbnails argentinanwall.jpg  
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2005
    Posts
    55
    Thank much people!
    However I did try what you said Poppa Smurf and its not working!

    When I try to run the query a message is generated which says 'Data type mismatch in criteria expression'.

    What have I done wrong?

    Can you take me through step by step.

    Thanks very very very much.

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    data type mismatch usually occurs when you have used a literal value in a where clause.
    it means that you haven't supplied the right type of data to sql engine

    most commonly its where you have mixed up number and string paramters

    eg
    where mynumericcolumn="1.23" 'will fail as you have supplied a string
    where mystringcolumn=1.23 ' will fail as you have supplied a number
    a string column should be encapsulated in quote marks, you can use " or '
    as numeric column musn't be encapsulated in quote marks

    say you have two varaibles
    dim mytextvar as string
    dim mynumvar as single
    mytextvar = "abc"
    mynumvar = 1.23

    strSQL = "select blah from mytable where mystringcolumn = " & chr$(34) & mytextvar & chr$(34) & " and mynumericcolumn=" & mynumvar
    ,,what you should see if you debug/pause the code is
    select blah from mytable where mystringcolumn = "abc" and mynumericcolumn=1.23

    .. the chr$(34) inserts a double quote mark either side of the mytextvar

    if you try to use a string then you will get a mismatch, even if the strign representation is numeric, as the compiler is thick.. it cannot and willnot do the translation for you.

    don't be tempted to store numbers in in a string representation.. the classic for this is dates.. store dates as dattime values.
    the other is say an item number as sting/text, and then complain that the numbers are out of sequence (eg 1,10,100,11,12...19,2,20.. etc).. in reality the computer sees theses as "1","10","100","11","12"..."19","2","20"
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    My mistake in my earlier post. You need to add an extra column for Response with a where condition and "YES" as the criteria. In the previous email you would receive a Data Mismatch as the Count is a number and the Criteria is a String. I have attached a sample database that shows the count query for All yes responses. Also, a count query for Yes responses a specific question.
    Attached Files Attached Files

  13. #13
    Join Date
    Oct 2005
    Posts
    55
    Hi Poppa Smurf I got the attached file however it not opening for me. A message is given 'Database is in an unrecognized format'.At present I am building the database in access 2000 and I would really like to see what you have sent. Can you help by sending a version that I would be able to view.

    Thanks very very much.

  14. #14
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Attached is a database in Access 2000 format.
    Attached Files Attached Files

  15. #15
    Join Date
    Oct 2005
    Posts
    55
    Thanks much it did open and I understand now!

    Thanks much Poppa Smurf.

Posting Permissions

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