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

    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

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  3. #18
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    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

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  5. #20
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    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

  6. #21
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Use the NZ() function??

  7. #22
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    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

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  9. #24
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    For the month of January provider had no charges (Provider was out).

  10. #25
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  11. #26
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    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

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  13. #28
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    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.

  14. #29
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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.

  15. #30
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    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

Page 2 of 4 FirstFirst 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