June7
Thank you yes it did return correct valve. I have multiple providers so the first provider valve come back correct but for the rest it does not. I want to be able to run the report by provider and all provider.
Thank you
June7
Thank you yes it did return correct valve. I have multiple providers so the first provider valve come back correct but for the rest it does not. I want to be able to run the report by provider and all provider.
Thank you
Each DLookup will have to include criteria for the provider.
=+[R_Gross A/R Ending Balance subreport].[Report].[February]/((DLookUp("[December]","[Q_RheumVarianceTotalCharges]", "[Provider Name]='" & [Provider Name] & "' And [Year]=" & [Year]-1)+[R_RheumVarianceTotalCharges].[Report].[January]+[R_RheumVarianceTotalCharges].[Report].[February])/90)
If there is possibility of more than one provider with same name (like John Smith), include provider ID in queries and use it for criteria.
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.
June7
Thank you that worked great. I try trasnfer this to excel with all provider but i get a error OVERFLOW. Is it because it's too much data???
Thank you
Don't know, never encountered that. I tested export of the report and did not get error.
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.
I found it on page 73 there is null values for January. How can i go about fixing the null values. There is O charges and O payments for January, so for YTD Gross collection rate and YTD Net Collection Rate i'm getting #Num! error.
Thank you
Use the NZ() function??
Thank you for your reply ssanfu. I never really used the Nz Function.
How would i be able to use it with this formula =[R_RheumVarianceTotalPayments subreport].Report.January/R_RheumVarianceTotalCharges.Report.January
Thank you
Calculation with null should return null. Division by 0 will error. Why would zero get passed as the divisor?
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.
For the month of January provider had no charges (Provider was out).
If there are no records for a provider/year/month the aggregate value will be null. Note that Jan-Oct 2009 months are all null in the example db you provided. The only month with a zero charge is Feb 2010. If this Charges field was left null the calculation will result in null, however, since zero is entered, it must be dealt with in the formula.
Nz will not deal with the division by zero issue. An IIf can.
=[R_RheumVarianceTotalPayments subreport].Report.January/IIf(R_RheumVarianceTotalCharges.Report.January=0, null, R_RheumVarianceTotalCharges.Report.January)
or
=[R_RheumVarianceTotalPayments subreport].Report.January/IIf(R_RheumVarianceTotalCharges.Report.January=0, 1, R_RheumVarianceTotalCharges.Report.January)
Last edited by June7; 07-24-2012 at 05:48 PM.
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.
The second one worked. Thank you very much
one more thing. I want to show all providers together by year. My question is how I can show valve for last month only. So for 2011 it would give me the valve of December 2011.
Joe Smith Charges 2011 300 400 Payments 2011 100 100 Adjustments 50 50 Gross A/R Ending Balance 150 250 (Based on Decemver 2011 Joe Smith Charges 2012 500 800 Payments 2012 200 200 Adjustments 50 50 Gross A/R Ending Balance 250 550 (Based on July 2012
To 'rotate' your data with queries, try:
Query1
SELECT [Month], [Year], T_DocInfo.[Provider Name], "Total Charges" As Category, [Total Charges] As Data FROM (SELECT T_RheumVariance.*, T_DocInfo.[Provider Name] FROM T_DocInfo INNER JOIN T_RheumVariance ON T_DocInfo.ID = T_RheumVariance.[Provider Name])
UNION SELECT [Month], [Year], T_DocInfo.[Provider Name], "Payments", [Payments] FROM (SELECT T_RheumVariance.*, T_DocInfo.[Provider Name] FROM T_DocInfo INNER JOIN T_RheumVariance ON T_DocInfo.ID = T_RheumVariance.[Provider Name])
UNION SELECT [Month], [Year], T_DocInfo.[Provider Name], "All/Adj", [Allowance/ Adjustments] FROM (SELECT T_RheumVariance.*, T_DocInfo.[Provider Name] FROM T_DocInfo INNER JOIN T_RheumVariance ON T_DocInfo.ID = T_RheumVariance.[Provider Name])
UNION SELECT [Month], [Year], T_DocInfo.[Provider Name], "Gross A/R Beginning", [Gross A/R Beginning Balance] FROM (SELECT T_RheumVariance.*, T_DocInfo.[Provider Name] FROM T_DocInfo INNER JOIN T_RheumVariance ON T_DocInfo.ID = T_RheumVariance.[Provider Name])
UNION SELECT [Month], [Year], T_DocInfo.[Provider Name], "Gross A/R Ending", [Gross A/R Beginning Balance]+[Total Charges]-Payments-[Allowance/ Adjustments] FROM (SELECT T_RheumVariance.*, T_DocInfo.[Provider Name] FROM T_DocInfo INNER JOIN T_RheumVariance ON T_DocInfo.ID = T_RheumVariance.[Provider Name]);
Query2
TRANSFORM Sum(Query1.[Data]) AS [SumOfData]
SELECT Query1.Year, Query1.Category
FROM Query1
GROUP BY Query1.Year, Query1.Category
PIVOT Query1.[Provider Name];
There is no wizard for UNION query, must type in the SQL View of query designer.
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.
Thank you very much. they worked great.
For category Accounts Receivables>90days and Gross Days outstanding in A/R i would need to show those valve. But i would only need to show the valve based on the last month. So for example for 2011 i would display valve from December 2011. For 2012 since the numbers are up to June 2012 i would display valve for June 2012.
2011 Category Doe Smith Gross A/R Beginning $ 20,000.00 $ 13,936.74 Total Charges $ 10,000.00 $ 20,000.00 Payments $ 5,000.00 $ 10,000.00 All/Adj $ 2,000.00 $ 3,000.00 Gross A/R Ending $ 23,000.00 $ 20,936.74 Accounts Receivables>90days 80% 83% Based on December 2011 Gross Days outstanding in A/R 40 50 2012 Category Doe Smith Gross A/R Beginning $ 20,000.00 $ 13,936.74 Total Charges $ 10,000.00 $ 20,000.00 Payments $ 5,000.00 $ 10,000.00 All/Adj $ 2,000.00 $ 3,000.00 Gross A/R Ending $ 23,000.00 $ 20,936.74 Accounts Receivables>90days 80% 83% Based on June 2012 Gross Days outstanding in A/R 40 50
Last edited by June7; 07-26-2012 at 01:02 AM.
It's easy to include month of December as criteria in the crosstab. Problem arises when the year isn't complete and December is not available. You are saving the month name instead of number in table. If the number was available the filter criteria could use DMax function to determine the last month for a year. I really think you would be better off having the month and date as a single value in a date/time field. The day part could always be the 1st. Then date manipulation functions could be used to extract the various date parts when needed. Alternatives: a table of months to lookup the month number; or a public custom function that can be called to convert the month name to number; or use intrinsic Switch function - Switch([Month]="January",1,[Month]="February",2, ...)
Why are you showing same data for both years?
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.
I really don't want to change month and date as a single value because i have all the other reports build. Where would i use the switch function??? I'm thinking of going with a table of months. The DMax function i would use it in reports????
I'm showing data for both years same just as an example. Bad example Sorry
Thank you