Page 1 of 4 1234 LastLast
Results 1 to 15 of 54
  1. #1
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338

    Formula

    Hello


    I need help with this formula. I have the following table
    Provider Name Year Month Charges Payments
    John 2009 January 14,300 11,000
    John 2009 February 24,000 10,700
    Smith 2009 January 7,000 3,00
    Smith 2009 February 15,000 8,000

    The following formula i have in excel. =SUM(January Payments +February Payments)/(January Charges+February Charges). How can i does this in access.


    Thank you

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Just check out if below gives some guidelines :
    Code:
    SELECT 
        myTable.ProvideName, 
        Sum(myTable.Payments) AS SumOfPayments, 
        Sum(myTable.Charges) AS SumOfCharges, 
        Sum(myTable.Payments)/Sum(myTable.Charges) AS TheDivision
    FROM 
        myTable
    GROUP BY 
        myTable.ProvideName;
    Thanks

  3. #3
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Thank you for your reply it work great. Another question The formula for each month is different. For march i would need to include Jan Feb and march totals =SUM(January Payments +February Payments+March Payments )/(January Charges+February Charges+March Charges). For April =SUM(January Payments +February Payments+March Payments+ April Payments )/(January Charges+February Charges+March Charges+ April Charges). Can this be done???

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Yes, it can be done.
    Check out 2 things :
    1) Keyword - "Running Total"
    2) Your Year & Month need to be stored in Date Format, else you need to convert them to the same.

    Thanks

  5. #5
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Recyan

    Hello
    I need help with another formula. I have a subreport on a main report that is linked by Provider name and Year. I have this formula for March =+[R_Gross A/R Ending Balance subreport].Report.March/((R_RheumVarianceTotalCharges.Report.January+R_Rhe umVarianceTotalCharges.Report.February+R_RheumVari anceTotalCharges.Report.March)/90), which gives me how many gross days are outstanding in A/R. Now for January and February i would need it to pull totals from the prior year. For January i would need to pull numbers from November 2011, December 2011 and January 2012. How can i go about this????

    Thank You

  6. #6
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Hold on till some one comes along. Am no good with reports.

    Thanks

  7. #7
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Can any one help??????

    Thank you

  8. #8
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    MitziBostwick

    I received this message:

    Dear Ray67,


    MitziBostwickhas just replied to a thread you have subscribed to entitled - Formula - in theQueries forum of Microsoft Access Forums.

    Thisthread is located at:
    https://www.accessforums.net/queries/formula-24766-newpost.html

    I can't find the thread that you replied to.

    Thank You

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    Really need to know more about report structure. Is it grouping and sorting by provider and year? Is it showing just annual summary?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Here is the db. This is the name of the report R_RheumVarianceGrossAR. If you go to page 2 Gross Days Outstanding in A/R you will see January and February missing number. It's here where i would need to get numbers from 2010. Right now the report is sowing all of the years. The users would run the report by year.


    Thank you
    Attached Files Attached Files

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    Try:

    =+[R_Gross A/R Ending Balance subreport].[Report].[January]/((DLookUp("[November]","Q_Gross A/R Ending Balance","[Year]=" & [Year]-1)+DLookUp("[December]","Q_Gross A/R Ending Balance","[Year]=" & [Year]-1)+[R_RheumVarianceTotalCharges].[Report].[January])/90)

    =+[R_Gross A/R Ending Balance subreport].[Report].[February]/((DLookUp("[December]","Q_Gross A/R Ending Balance","[Year]=" & [Year]-1)+[R_RheumVarianceTotalCharges].[Report].[January]+[R_RheumVarianceTotalCharges].[Report].[February])/90)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    June7

    Thank you for your reply. I try it but its not working right. For January i'm getting 41.55 but it should be 92.42 days.

    Thank You

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    Ooops, referenced wrong query in the DLookup.

    =+[R_Gross A/R Ending Balance subreport].[Report].[January]/((DLookUp("[November]","[Q_RheumVarianceTotalCharges]","[Year]=" & [Year]-1)+DLookUp("[December]","[Q_RheumVarianceTotalCharges]","[Year]=" & [Year]-1)+[R_RheumVarianceTotalCharges].[Report].[January])/90)

    =+[R_Gross A/R Ending Balance subreport].[Report].[February]/((DLookUp("[December]","[Q_RheumVarianceTotalCharges]","[Year]=" & [Year]-1)+[R_RheumVarianceTotalCharges].[Report].[January]+[R_RheumVarianceTotalCharges].[Report].[February])/90)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    You are using the wrong name for the query. It should come from Q_RheumVarianceTotalCharges. Why are you using query to get the numbers??? Something else is wrong not sure what. This is what i have for march formula =+[R_Gross A/R Ending Balance subreport].Report.March/((R_RheumVarianceTotalCharges.Report.January+R_Rhe umVarianceTotalCharges.Report.February+R_RheumVari anceTotalCharges.Report.March)/90)

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,358
    Read my previous post again. The suggested expression for January returns the value you say is correct.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Formula field
    By vidplaylist in forum Access
    Replies: 1
    Last Post: 12-07-2011, 04:37 PM
  2. Formula mod help
    By jcaptchaos2 in forum Access
    Replies: 2
    Last Post: 04-25-2011, 02:55 PM
  3. SQL Formula to Sum Itself
    By Scorpio11 in forum Access
    Replies: 1
    Last Post: 02-19-2011, 11:22 AM
  4. Formula for form
    By chanoc24 in forum Forms
    Replies: 3
    Last Post: 08-18-2010, 10:40 PM
  5. Formula
    By hschrunk in forum Access
    Replies: 3
    Last Post: 05-18-2010, 01:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums