Results 1 to 15 of 15
  1. #1
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149

    Age Analysis on Customer Statement

    Almost finished with my Customer statement report ad I've ht a snag. Please see image for query that contains all the fields for a certain customer.



    Click image for larger version. 

Name:	qury.JPG 
Views:	20 
Size:	56.7 KB 
ID:	35615

    I'm trying to calculate the 30 days field to have it show the sum of all the records that is 30 - 59 days from the start date. The start date and end date is selected on a form which opens the report.

    I have read about Switch function and IIF function but have no idea how they work or how to use them.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make Q1 to pull all your data and include days from start in the range of your dates on the form:
    select *, DateDiff("d",[StartDate],Date()) as DaysFromStart from table where [startDate] between forms!myForm!txtStartDate and forms!myForm!txtEndDate

    make Q2 using Q1 and use the range:
    select * from Q1 where Days between 30 and 59

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    See if this helps http://sfmagazine.com/post-entry/jun...ts-receivable/

    Calculate DueDate in query: TransDate + 30

    Use WHERE argument of OpenReport.

    DoCmd.OpenReport "report name", , , "DueDate BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
    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.

  4. #4
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    I already have a form called StatementsReportF with 2 text boxes for the dates. txtStartDate and txtEndDate.

    My SQL knowledge is very poor so I honestly have no idea what the code is you gave there

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Whom are you replying to?

    Use query design grid to build SQL. Create calculated field. This is basic Access functionality. Switch to SQLView to see resultant SQL statement. This is how I learned SQL.
    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.

  6. #6
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    The current section of transaction work fine. So all I have to do sil is the 30 days, 60 days and 90+ days sections. For this I have created 3 textboxes on the report itself that is unbound. The image in the first message is the query I am using to pul ALL the transactions for a specifed customer so i need to know how to get it to work out the totals for 30,60 and 90+ ays

  7. #7
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Sorry June, was replying to Ranman

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Do you want to show age as of current date?

    If today's date is between TransDate and TransDate + 30 then the record is less than 30 days old, similarly for 60 days and 90 days.

    Switch(Date() BETWEEN TransDate AND TransDate + 30, "30", Date() BETWEEN TransDate AND TransDate + 60, "60", Date() BETWEEN TransDate AND TransDate + 90, "90", True "<90")

    How do you filter out records that have been paid?

    Aging an account can be far more complicated. Must take into consideration partial payments, discounts, refunds, credits, interest. Difference of debits and credits gives account balance and if balance owed then need to determine which of the most recent debits would not be fully paid and how old and which part of balance is what age.
    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. #9
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    The Query I am using contains ALL the records of that cutomer from invoices, credits to payments. My thought was that it would calculate it by taking all the invoices and subtracting all the payments nd all the credits to give the amount left over outstanding........

    I have bow tried modifying my Query to add a coloumn for Days overdue and AmountDue.
    Days overdue calculated the difference between the TransDate and the StartDate of the current Statement.
    AmountDue is just an Alias for Amount

    Im trying to add n coloumn called Due30: with Due30: IIf( [DaysOverDue] between 1 And 30,[AmountDue],0) but I keep getting an error whn saving it.
    Click image for larger version. 

Name:	error.JPG 
Views:	14 
Size:	19.8 KB 
ID:	35616

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Post the full attempted query SQL statement. Switch to SQLView and copy/paste.
    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. #11
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Got it to work....it works on Due30: IIf(([DaysOverDue] Between 1 And 30),[AmountDue],0)

    stupid brackets LOL

  12. #12
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Got the Age Analysis to work now - YAY!

    But new problem - sigh

    In the detail section of my report, I want the report to display all the records from the Query that falls between the start date and end date of the selection form only. At the moment it shows every single record...

    What would you suggest to fix this?

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    That's weird because it worked as you had it for me; xID: IIf([ID] Between 9 And 11,[Amount],0)

    Filter your report, or modify the query to only return the records that satisfy the criteria?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    I tried criteria one on the Query and then it shows the records in that range but then the problem comes in with the 30 days and 60 days parts....it calculates the age analysis on the query records (which is only from 1 Sept to 30 Sept) and not everything as It should.

    Could I use a subreport with a seperate query for the details?

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Maybe you need to calculate a group identifier and use that to group/sort records on report.

    DueGrp: Switch([DaysOverDue] < 30, "<30", [DaysOverDue] < 60, "30-60", [DaysOverDue] < 90, "60-90", True, ">90")
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-06-2016, 07:05 PM
  2. Relationship Analysis
    By Alexieoo in forum Database Design
    Replies: 67
    Last Post: 10-26-2015, 12:51 PM
  3. Trend Analysis
    By TimC in forum Queries
    Replies: 2
    Last Post: 01-05-2015, 11:58 AM
  4. Replies: 6
    Last Post: 12-11-2013, 09:27 PM
  5. Replies: 1
    Last Post: 12-24-2011, 08:48 AM

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