Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 54
  1. #31
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Forget previous suggestions. Here is another, simpler, approach to convert the month name to month number:


    Month([Month] & "/1/" & [Year])

    Use the expression in query to construct a field MonthNum.

    To find the latest month for a year:

    DMax("MonthNum","queryname","[Year]=" & [Year])

    The DMax can be criteria under the MonthNum field.

    I don't have your file with me now to test this. Maybe this weekend. Let me know if you figure it out before then.

    BTW, Month and Year are reserved words. Best not to use reserved words as field names. If you do, must enclose in []. Same applies if spaces or special characters are used.
    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.

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

    I was able to convert month name to month number. Before i do the rest i need help with one part.
    You helped me with this code before =+[R_Gross A/R Ending Balance subreport].Report.January/((DLookUp("[November]","[Q_RheumVarianceTotalCharges]"," [Provider Name]='" & [Provider Name] & "' And [Year]=" & [Year]-1)+DLookUp("[December]","[Q_RheumVarianceTotalCharges]"," [Provider Name]='" & [Provider Name] & "' And [Year]=" & [Year]-1)+R_RheumVarianceTotalCharges.Report.January)/90).

    How can i do this formula in a query????

    Thank You

  3. #33
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Does this Formula look right??

    YTD Gross Collection Rate: Sum(IIf([Payments]=0,1,[Payments])/IIf([Total Charges]=0,1,[Total Charges]))

  4. #34
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    In what query do you want that first formula? It probably would be exactly the same.

    Where are you using the second formula? Does it produce the result you expect?
    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. #35
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    I would want it in a new query.

    For the second formula I’m using a new query. It's not producing the result that I expected.

    I'm putting both of these query in a new one, because these are the one's I would need it to give me valve as of the last date.


    Thank You.

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

    Sum(IIf([Payments]=0,1,[Payments])/IIf([Total Charges]=0,1,[Total Charges])) This formula is working right. The problem is, that's not the right formula. For every month i need to include all prior months. For February this would be the formula =(JanuaryPayments+ February Payments)/ (January Total Charges+ February Total Charges) For March =(JanuaryPayments+ February Payments + March Payments )/ (January Total Charges+ February Total Charges+ March Total charges)
    How can i do this in query????

    Thank You

  7. #37
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    You want the monthly data to run left to right like on the existing R_RheumVarianceGrossAR report?

    Will probably need nested query/subquery (or DSum expressions) with T_RheumVariance table and another crosstab to rotate the results. Year and month number will be needed as criteria.

    Review: http://allenbrowne.com/subquery-01.html
    Last edited by June7; 07-31-2012 at 09:42 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.

  8. #38
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    Whats wrong with this formula??

    YTD: DSum("[Total Charges]","T_RheumVariance","[Month]< = '" & [MyMonth] & "'")

    i'm getting #error.

  9. #39
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Is [Month] the month name field from the table? What is [MyMonth]? Cannot use < operator on month names. January is not < April.
    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. #40
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    I guess everything was wrong with the formula. [Month] is the name field from the table. [MyMonth] is what i used to convert to number. I changed [MyMonth] to MonthNum: Month([Month] & "/1/" & [Year]). This is the new formula YTD: DSum("[Total Charges]","T_RheumVariance","[Month]>= '" & [MonthNum] & "'") it’s giving me the sum of all. I would like it to give me a running sum by year and provider. Here is the sql view

    SELECT T_RheumVariance.Year, T_RheumVariance.[Provider Name], Month([Month] & "/1/" & [Year]) AS MonthNum, Sum(T_RheumVariance.[Total Charges]) AS [SumOfTotal Charges], DSum("[Total Charges]","T_RheumVariance","[Month]>= '" & [MonthNum] & "'") AS YTD
    FROM T_RheumVariance
    GROUP BY T_RheumVariance.Year, T_RheumVariance.[Provider Name], Month([Month] & "/1/" & [Year])
    ORDER BY T_RheumVariance.Year;


    Thank you

  11. #41
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Still don't see how that can work. You are taking a text field ([Month]) and applying number criteria ([MonthNum]). The [Month] field has month names (January, February, etc) and [MonthNum] has numbers (1, 2, etc). Comparing apples to oranges. Consider:

    DSum("[Total Charges]", "T_RheumVariance", "[Year]=" & [Year] & " AND [MonthNum]<= " & [MonthNum]) AS YTD

    However, I have been testing GROUP BY with the DSum and although it looks good it is not working. I was not entirely sure it would when I initially suggested it as possibility. Access crashes and have to quit the app to get out of the query execution.

    Try the DSum in textboxes on report:

    =DSum("[Total Charges]", "T_RheumVariance", "[Year]=" & [Year] & " AND [MonthNum]<= " & Month("Jan 1 " & [Year])
    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. #42
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    =DSum("[Total Charges]", "T_RheumVariance", "[Year]=" & [Year] & " AND [MonthNum]<= " & Month("Jan 1 " & [Year])

    Didn't work. I'm getting error #Error.

  13. #43
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Sorry, instead of T_RheumVariance table, need to use a query that includes MonthNum constructed field. Think I did say this would all be easier if there was a full date field in T_RheumVariance table. Oh, and also need criteria for the provider.
    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. #44
    Ray67 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    338
    I guess this is not going to work in a query. Can i do this in a Report and call the last month value?
    This is the formula .
    =+[R_Gross A/R Ending Balance subreport].Report.January/((DLookUp("[November]","[Q_RheumVarianceTotalCharges]"," [Provider Name]='" & [Provider Name] & "' And [Year]=" & [Year]-1)+DLookUp("[December]","[Q_RheumVarianceTotalCharges]"," [Provider Name]='" & [Provider Name] & "' And [Year]=" & [Year]-1)+R_RheumVarianceTotalCharges.Report.January)/90).

    Thank you

  15. #45
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Post 41 suggested doing in report.

    You are showing a formula that is already working on report. Why? I thought the DSum was something new you needed but if that summary YTD data is already presented on the report, why the DSum?. Are you wanting to replace with the DSum? Will not be able to capture the November and December data for the January calculation.

    Clarify what you really want to do and I will look at your db later.
    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 3 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