# Formula

1. 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. 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. 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. 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. 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. Hold on till some one comes along. Am no good with reports.

Thanks

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

Thank you

8. Competent Performer
Windows XP Access 2003
Join Date
Jul 2011
Posts
338
MitziBostwick

Dear Ray67,

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

https://www.accessforums.net/queries/formula-24766-newpost.html

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

Thank You

9. 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.

10. 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

11. 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)

12. 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. 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)

14. 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. Read my previous post again. The suggested expression for January returns the value you say is correct.

Page 1 of 4 1234 Last

#### 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