Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2017
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Report showing transactions for 15, 30, 60 days on the report.

    Hi,

    I am working on a comprehensive report where we are importing csv data into access and then report generation is done for 15, 30, 60 days.

    I managed to get individual reports by creating queries filtering data on the basis of 15, 30, 60 days separately.

    But the next required operation is to get a single report showing all 15, 30, 60 days transactions and then grand total on the basis of ID as primary key.

    Can anyone please suggest any specific query or method to make things simple and easy please.

    I am not too much expert so please suggest accordingly.

    regards,

  2. #2
    New Lounger
    Join Date
    Oct 2009
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Since you already have the 3 reports written just use a 4th to do the totaling and the other 3 as sub reports.

  3. #3
    New Lounger
    Join Date
    Sep 2017
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by baderms View Post
    Since you already have the 3 reports written just use a 4th to do the totaling and the other 3 as sub reports.
    That was what my initial plan too, but whats required is to :

    Create a single report showing 3 separate columns of same data for 3 different date ranges like total amount payable for last 15 days, next column showing total amount payable for last 30 days, next column showing total amount payable for last 60 days etc.

    The formula for individual report I used was :

    >=DateAdd("d",-15,Date()) ... >=DateAdd("d",-30,Date()) .... >=DateAdd("d",-60,Date()) .... these formulas show individual records but I need to put the total of each 15, 30 and 60 days amount payable data to the final report under 3 separate columns.

    Don't know how to achieve this ... please help accordingly.

    regards,
    Umar Farooq

  4. #4
    5 Star Lounger Lugh's Avatar
    Join Date
    Jun 2010
    Location
    Indy
    Posts
    848
    Thanks
    211
    Thanked 108 Times in 95 Posts
    Quote Originally Posted by rjumar View Post
    I am not too much expert
    Same here

    My approach is to use Access for verifying and storing data, and Excel for manipulating and presenting it. If that sounds ok to you, my untested thoughts are:

    Bring the 3 existing query results into 3 separate sheets in a workbook—and a 4th if necessary for the grand total you mention.
    Then consolidate the data into a new summary or overall sheet.

    Pivot Table

    If you're familiar with pivots:
    I'm not sure about the degree of flexibility with custom date ranges like yours, but I'd be very surprised if it's not possible to do 15-30-60. I use the built in M-Q-Y date summary functions myself, which work great.

    Make just one Access query which supplies the date.
    Bring it into Excel as a pivot, set up the date ranges, and you should have what you need.
    Lugh.
    ~
    Dell Alienware Aurora R6 (new 2017)
    Windows 10 Home x64 1703; Office 365 x32
    GeForce GTX 1060; 16GB DDR4 2400
    256G SSD, 1TB HD

  5. #5
    New Lounger
    Join Date
    Sep 2017
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    rjumar, may I suggest that you use Sum(IIF..... in your queries and DateDiff to set up the time periods.
    Set up the query in design mode and and make sure you click "Totals" in the ribbon (its a greek sigma symbol)

    for 15 days the expression will be something like this in the "field" row of the query. Make sure you also select Expression in the GroupBy field. Here I am assuming you have something like a transaction date and a total owing per customer.
    Total at 15 days:Sum(IIF([Transdate] Between (DateDiff("d",-15,Date())) And Date(), [TotalOwing],0)
    For 30 days, just copy the above into the adjacent field and change -15 to -30....similarly for 60. And change the first bit to read "Total at 30 days:" and "Total at 60 days:"
    rjumar.PNG
    This should give you a working query. Then you need to design a report based on this query. You can set that up reasonably easily in my view, but if you need a hand, let me know.
    Last edited by Raoul; 2017-09-08 at 04:49.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •