Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Location
    Pueblo, Colorado, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Statistical Data Calcs on Form (Access2000)

    I sure need help. I'm trying to calculate school attendance statistics on a form and am having serious mind problems. I'm sure there's a simple solution that I have missed. Just in case you need to look at the database, I've taken the liberty of attaching it. Here goes the problem: I have a form (frmAttendance) using a subform based on a table (tblAttendance) to allow the user to input the date of class and the attendance of the student (present, excused, absent, etc.). That data is stored in tblAttendance. No problem so far. I have created another form (frmAttendanceStats) to display the statistics of each student. I am using a non-visible subform based on tblAttendance in the footer to capture the statistics (i.e., number/percentage of times present, etc.) for each student. That works too. Here's the problem. I have created another form (frmAttendanceStats List) to display the statistics for all the students in continuous forms. All the records have the same results as the first one, until I individually select each record which then calculates the expressions. I also tried this with report with the same results. How can I possibly capture the correct statistics for all the students without the hassle of selecting each one individually? This problem also prevents any kind of report printing with accurate results. Any suggestions?

    JodyR
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Statistical Data Calcs on Form (Access2000)

    Hi Jody,

    Unfortunately continuous forms and calculated expressions don't work very well together as you've discovered. In this case I think you may be able to make it work by putting the hidden subform into the detail section (I haven't tried it however). But I would approach the problem differently. I would use a crosstab query as the data source for the form or report. That lets you combine the attendance records into a single record for each student in a easy manner. I you haven't played with crosstabs, post back and we'll try to help you up the learning curve.
    Wendell

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Location
    Pueblo, Colorado, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistical Data Calcs on Form (Access2000)

    Oh Wendell, I guess that's why they call you Moderator and me Dummy! I can't believe I didn't try that myself (putting subform in Detail). I thought I had tried every conceivable combination of solutions. It works great. I tried crosstabs which worked like a charm until I came to the calculation part - again the problem. I am having another minor problem but will try to work it out on my own first. Thank you so much. <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23> Ever grateful, JodyR

  4. #4
    New Lounger
    Join Date
    Nov 2003
    Location
    Pueblo, Colorado, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistical Data Calcs on Form (Access2000)

    Hate to bother you again but I still need to consult a brain that isn't smoldering. I've tried to figure this one out but to no avail: In the same database (see previous attachment), in frmAttendanceStats, some of the records have #NUM! errors in the calculated fields. You can see this in Record 5 of that form. This doesn't occur in all the records just a few of them. Even after I move the subform to the detail section, it still happens. I've changed all of the datatypes and formats that I could think of - without solving the problem. Has this ever happened to you? I could understand if all the records had the same error but only a couple stumps me.

    I'd appreciate any thoughts. <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    JodyR

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Statistical Data Calcs on Form (Access2000)

    Are any of the values Null? That can certainly cause a Num# error when trying to do calculations.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Nov 2003
    Location
    Pueblo, Colorado, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistical Data Calcs on Form (Access2000)

    Thanks for getting back to me so quickly, Charlotte. I don't think the values are Null. The underlying data comes from Yes/No fields. I count the number of times each particular field is checked, i.e., 3 times Present, 5 times Absent, etc. Then I calculate the percentages of Present, Absent. This is all in the frmAttendanceStats. I also checked the calculations in the subform (frmAttendance subform) to see if there are any problems and there aren't. Logic tells me that the problem occurs when the main form calculates the data from the subform (but I could be way off). But the error doesn't occur with every record. If any values were Null wouldn't all the records in the set have the same #NUM! error? Only a couple are having that problem. It sure has me stymied.

    JodyR

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Statistical Data Calcs on Form (Access2000)

    For record # 5 (Elijah Johns), all Times ... fields are 0, so Total Times Possible is 0. The percentages are calculated by dividing Times ... by Total Times Possible, so for this record, you are dividing by 0. You can avoid this by testing if Total Times Possible is 0, for example for % Present:

    =IIf([Total Times Possible]=0,0,[Times Present]/[Total Times Possible])

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Statistical Data Calcs on Form (Access2000)

    It looks like Hans has nailed the problem - division by 0 will always cause the #NUM! error.
    Wendell

  9. #9
    New Lounger
    Join Date
    Nov 2003
    Location
    Pueblo, Colorado, USA
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Statistical Data Calcs on Form (Access2000)

    To Wendell, Hans and Charlotte: Thanks so much for your input and attempts to fix my problem. You were just thinking much too logically for what was wrong. I agreed with all of you that you can't divide by zero and you have to watch null values. However, what stymied me was that all the records were fed the same calculations with sporadic results. I went back to step one and looked at every expression and calculation. Wellllll, when I was adding the calculated controls to my form, I just copied and pasted, then changed the pertinent field names. You've got it, I have two calculations looking at the same field. As soon as I fixed that, everything worked perfectly. I was just in too big a hurry and didn't check my work thoroughly. Lessons learned. JodyR

Posting Permissions

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