Page 4 of 4 FirstFirst 1234
Results 46 to 54 of 54
  1. #46
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Sorry . I want to make a new report. The current one is by provider. I want the new report by year. So all providers would be on one page.


    For category Accounts Receivables>90days and Gross Days outstanding in A/R i would need to show those 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 July 2012 i would display valve for July 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


  2. #47
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Now I am remembering this discussion on converting the month name to month number started back in post 26. Refer to the queries suggested in post 27. Calculate another field in the UNION query to create a date value: CDate([Month] & " 1 " & [Year]) As VarDate
    Remember, the calc must be in each SELECT line.

    If every year had December as the last month of data, this would not be needed but as you pointed out, the current year is of course still accumulating data and the last month changes. To avoid having to manual adjust the query every month, need a date field to apply criteria on.

    The calculated date field provides a date value that can be used in subsequent queries, such as the crosstab query of post 27. Can now have a WHERE clause that uses the constructed date field to retrieve the record that has the latest date:
    WHERE (((Query1.VarDate)=DMax("VarDate","Query1","[Year]=" & [Year])))

    What are the formulas for the percentage calcs?
    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. #48
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Thank you for your reply. I did what you suggested in post 27 and it worked great. What i'm missing is the formula for percentage. I don't have a formulas. In one of the post you suggested to do a running sum. I try doing the runnnig sum but it didn't work.

  4. #49
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Think I suggested the running sum before understanding you wanted only the last month data.

    Just describe the calculation involved in arriving at the percentage and days figures. Maybe I can figure out the necessary expression.

    I am not understanding how the calcs in posts 32 and 33 apply to this new report.

    Hope you are aware that basing report on crosstab query can require that you modify the report as the data accumulates and report periods change? Crosstab query is very dynamic and stabilizing it so report will perpetually run without edits can be difficult. Review https://www.accessforums.net/reports...ery-26806.html
    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. #50
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Ok so here is the calculation

    Jan =Jan2011 Gross A/R Ending Balance/((November 2010 Total Charges + December2010 Total Charges + January2011 Total Charges)/90)

    Feb =Feb2011 Gross A/R Ending Balance/((December2010 Total Charges+ January2011 Total Charges + February 2011 Total Charges)/90)

  6. #51
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    After some concentrated effort on the issue and testing various approaches, my conclusion is to produce that data by provider by year based on the last month will not be easy because have to take into consideration the partial current year. Will require a full date value or at least month number to facilitate the extract and even then it will be complicated. I suggest you build a query of T_RheumVariance with the date and month number calculated and use that query as data source for subsequent queries. The left to right arrangement on report adds more complication. I see a possible solution using DMax and DLookup but domain aggregate functions can slow down queries, especially in large datasets. This has become more of a challenge than expected and you might be on your own now but will try to find time to revisit this in next week.
    Last edited by June7; 08-04-2012 at 10:03 AM.
    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.

  7. #52
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Thank You for your time spend issue. How about doing it in a report ???

  8. #53
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Still complicated. The data will need some manipulation before can even get think about report. The first manipulation is a query to calculate the full date and month number values so they can be available for any subsequent manipulation. Some very long and complex expressions using DMax and DLookup might generate the data but I am not sure at this point. Another approach might require VBA code to write a custom function to generate the data. Again, just not sure. As stated, this has turned into a significant challenge. VBA approach must consider that VBA doesn't work with UNION queries, at least not the one time I tried. I had to redesign a process to elminate UNION query so VBA code would work.
    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. #54
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Attached is a db that demonstrates what I described about how difficult it would be to manipulate your data to produce this report.

    There are 3 reports.

    Report 0 is an attempt to have a report/subreport with both based on crosstab queries. Apparently this is not possible because the report will open but triggers errors I don't understand and can't fix and the subreport does not show data.

    Report 1 is based on crosstab that attempts to include the same data presented in the two crosstabs of Report 0. It will probably crash Access.

    Report 2 does not use crosstab. It will open after about 5 minutes.

    I don't use crosstabs in my db and this was quite an educational experience.
    Attached Files Attached Files
    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 4 of 4 FirstFirst 1234
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