Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62

    Unanswered: Getting a total in the table

    Hi,
    I'm new to access and need some advice.
    I have a form with scores for a test,at the bottom of the form i need the total of all the different questions added up. I've done the unbound text box that pulls from the individual score boxes and the "=[q1]+[q2]" etcin the control source field, and it works, however i need to include the result on the table with all individual question scores. How can i do this.
    I'm sure your rolling your eyes, and its probably right in front of my face but......
    Any help would be much appretiated
    Brownie

  2. #2
    Join Date
    May 2004
    Posts
    65

    Recording a total

    From my understanding of your problem:

    Question 1: Score ##
    Question 2: Score ##
    ...

    You currently record the score in:

    ScoreTotal: Q1+Q2

    You now require that score total to be stored with the current recipient?

    You probably have a table like
    testrecipientno, testno, q1
    testrecipientno, testno, q2
    ...

    You could have a separate table (say 'results') that records scores such as:

    recipientno, testno, totalscore

    Once a user finishes the test: on the close form event add code like:
    docmd.runsql "Insert Into(...results - recipientno, testno, totalscorevalues ...)"

    (You will need to check exact code requirements)


    Hope that helps

  3. #3
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    Thanks gavinfm,
    My situation is i have a form patients fill out ( dementia in the elderly, it measures how far along they are) for every answer they get a score. The forms are hard copy and then data entered. i have it set up where the scores for the questions are put into a number field(named q1, q1, q3 etc. ), at the bottom of the form i have an unbound text box with =[q1]+[q2]+[q3] coded in. A "Total" box, it adds the questions fine but its not recorded in the coresponding table, how do i get the results into a column in the table. I'm sorry for the newbie question but there isn't a begginer forum.
    If you could help it would be great.
    Thanks
    Brownie

  4. #4
    Join Date
    May 2004
    Posts
    65
    Don't apologise... We're all newbies... some a just crawling others walking.

    Because the record is unbound it is not automatically related to an underlying table.

    You need code to tell it where to put your total.

    1st question: Where do you want to put the testtotal - In the same table or another one.

    2nd question: Are you familiar with [event procedure] coding using Visual basic?

  5. #5
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    Thanks,
    I Want the "Total" in the same table.
    Not yet very familiar with vb, learning as i go.
    How do i relate it to the table.
    Thanks agian
    Brownie

  6. #6
    Join Date
    May 2004
    Posts
    65

    Try this!

    Your table should look like this:

    recno,patientno,q1,q2,q3,q4,...,total.

    On your form:

    q1: [#]
    q2: [#]
    q3: [#]
    q4: [#]
    (Where # is the number value you type in)

    total: [##]

    Take out the controlsource line that will say something like: =q1+q2...
    and make it point to the total field in the table (by using the drop down list)

    If you get the properties box up for the last question and go to the events tab and click in the 'after update' line. A box appears on the end with three dots in it. Click on that and select code builder.

    Type in the line between the lines that automatically appear:

    total = q1+q2+q3

    Hold down the Alt key and hit Q: This returns you to the form.

    Try it out and see if it works.

    I'll explain what you have done:

    the question scores and total appear on the same record in the table.
    Therefore after all the question results are in you then update the total using the above code.

    You have told access that after the last question is updated you want access to calculate the total and place it in the field on the form called total which is bound to the underlying table.

    See how you go!

  7. #7
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    thanks gavinfm, but still no joy
    i get where your coming from, it seems lke it should work but i'm getting "0"
    iv'e been looking at an old db someone else was using, they had an invisible unbound box with the =[q1]+[q2]+[q3] assigned to it. but it doesn't show up in the table and i don't know how he/she got the result from the unbound text box to the "total" box?????
    Thanks again mate
    Brownie

  8. #8
    Join Date
    Sep 2004
    Location
    Bahrain
    Posts
    21
    I assumed that you have a field named "TOTAL" in the table, and an unbound textbox in the form named CALC_TOTAL (which means calculated total).

    in the form design place this field TOTAL and make it un visible

    the CALC_TOTAL has the experession =[q1]+[q2]+...

    place an event on the last quastion grade on_exit and type the following
    [TOTAL]=[CALC_TOTAL]

    or

    place an event on the form after update and type the same code

  9. #9
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62
    Don't want to be a pain but still getting a 0 in the totals column and on the page, iv'e uploaded the table and form if you can look at it and try to see what i'm doing wrong.
    i don't want someone to "just fix it" i need to learn this
    Thanks for the patience.
    Brownie
    Attached Files Attached Files
    Last edited by Brownie13; 09-18-04 at 09:54.

  10. #10
    Join Date
    May 2004
    Posts
    65

    This works!

    There were a couple of errors:

    T6 in your table was a text field - the addition was not working because you were adding numbers and text.

    The addition on the form was also not working because access cant add 'null' or totally empty fields. I fixed this by making the default value of each score 0.

    I then made the event procedure of each totals 'after update' event = the total of all questions.

    You do not require the other field.

    You requested that I dont just fix it so have a look at the probfixed database and change your original - that way you will see the problems.

    Regards

    Gavin
    Attached Files Attached Files

  11. #11
    Join Date
    Sep 2004
    Location
    Atlanta, GA
    Posts
    62

    gavinfm is a star

    Cheers mate, works great!
    One question in the code it says
    xTOTAL = t1 + t2 + t3 + t4 + t5 + t6 + t7 + t8 + t9 + t10 + t11

    What is the "x" in xtotal?
    Thanks ALOT
    Brownie

  12. #12
    Join Date
    May 2004
    Posts
    65

    Just making sure I dont confuse access

    Access accepts almost any word as a field name. However it can be confusing as to what is a reserved word and what is not. So I never use words like: Total, sum, average, database etc...

    In preference I would use ScoreTotal, TotSum, ScoreAverage, CurrDatabase...

    Because your table already had the field as Total I didn't change that... Instead I changed the text field on the form to xTotal, that way the form was able to point directly to that field and I knew I wasn't going to have any other frustrating issues that using 'Total' may have caused.

    At the end of the day it probably didn't matter... I just eliminated the possibility.

    Regards

    Gavin

Posting Permissions

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