Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Reports to Calculate Formulas

    Watch as I state my Version and SR number.. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    I am using O97, SR2. I would like to somehow report on some data I have in some fields... although I am familiar with fields in Excel, the concept of formulas in Access has escaped me.... Each of the fields in question are on the same form, and are primarily difference dates, how can I determine average times within different records for the field "date"?

    Similairly, how can I determine differences between the dates in two seperate fields? "date" and "datecompleted"?

    Any help would be greatly appreciated!

    Regards,
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Reports to Calculate Formulas

    Hi Drk,
    You can use the DateDiff function to calculate the difference between 2 dates. As for the average, you can use the Avg function to calculate an average. Just place the Avg field in the report or section footer to calculate the average of multiple records.

    Check the Access helpfiles for the specifics on these functions. <img src=/S/salute.gif border=0 alt=salute width=15 height=20>

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Reports to Calculate Formulas

    Excellent.. Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  4. #4
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Reports to Calculate Formulas

    Okay, I understand these functions, what I don't understand is how I can get an 'overall' picture.

    How can I get the average date difference for the entire table [clients] for the fields [maileddate] and [receiveddate]?
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Reports to Calculate Formulas

    Hi Drk,
    First, you'll want to include the Calculated date difference in the Detail section (invisible is ok).
    If you're calculating the overall average for the entire report, place the calcualted field in the report footer. If you're calculating the average for only one section, place the field in the section footer.

    They should automatically calculate the "overall" value for the respecive area (Report or section).

    P.S. What was that Version/SR again?!?! <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15>

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Reports to Calculate Formulas

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> I've got issues..

    First, there are holes my data.. Holy Data Batman!

    In some cases, there are no dates in either field. In these cases, I would like to exclude the data from the report.

    Now that I think about it, is there a way to calculate these values in a query? I can certainly display only items which contain ALL the data I need, while ignoring data with holes...

    The moral: It's hard to find a good data entry person these days... <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Thanks!
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Northern, California, USA
    Posts
    1,886
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Reports to Calculate Formulas

    Anyone? <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    <IMG SRC=http://www.wopr.com/w3tuserpics/Kel_sig.gif>
    Moderator:<font color=448800> Pix Place, Internet Explorer</font color=448800>
    <small>www.kvisions.com

  8. #8
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    London, Ontario, Canada
    Posts
    437
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Reports to Calculate Formulas

    Hey Drk, why not delve into the (Drk) realm of crosstab queries. You can list down columns and across rows showing averages, counts etc. I think it may solve your problem. Checkout Help and post back if we can be of more assistance. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

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