Results 1 to 5 of 5
  1. #1
    james bond is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2020
    Posts
    5

    Ageing report by query

    am attaching a screenshot of query i have made
    i want to make an ageing report so that if i can mention a date range from to
    it shows days greater than 30/60/90 days with opening balance.
    now in this query the result comes in column form due to which i cannot get total of the same bill number ( it is showing bifurcated) i want it to show as the image this is for one party i want for all or one as required please advice how can i make the report


    the sql of query is


    SELECT Account_Transaction.Ledger_Code, Account_Transaction.Amount, Account_Transaction.Vou_Date, Account_Transaction.DrCr, Customer_Master.First_Name, Customer_Master.Ac_No, Ledger_Master.op_bal, Account_Transaction.amountcr, Account_Transaction.inst_no, Sum(Account_Transaction.amountcr) AS [bill amount]
    FROM Customer_Master INNER JOIN (Account_Transaction INNER JOIN Ledger_Master ON Account_Transaction.Ledger_Code = Ledger_Master.lg_code) ON Customer_Master.Customer_Code = Ledger_Master.alias_code


    GROUP BY Account_Transaction.Ledger_Code, Account_Transaction.Amount, Account_Transaction.Vou_Date, Account_Transaction.DrCr, Customer_Master.First_Name, Customer_Master.Ac_No, Ledger_Master.op_bal, Account_Transaction.amountcr, Account_Transaction.inst_no
    HAVING (((Account_Transaction.Ledger_Code)>90))
    ORDER BY Customer_Master.Ac_No;Click image for larger version. 

Name:	Screenshot 2020-07-10 at 11.55.42 AM.png 
Views:	18 
Size:	46.4 KB 
ID:	42398Click image for larger version. 

Name:	IMG-20200710-WA0006.jpg 
Views:	18 
Size:	36.8 KB 
ID:	42399Click image for larger version. 

Name:	Screenshot 2020-07-10 at 11.55.42 AM.png 
Views:	18 
Size:	46.4 KB 
ID:	42398Click image for larger version. 

Name:	IMG-20200710-WA0006.jpg 
Views:	18 
Size:	36.8 KB 
ID:	42399

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Q1, would pull the data in the date range, and calc the day count: Days.
    Q2, would use Q1 and build the buckets:

    Select id, iif([days]>=0 and [days]<=30,"30 days","") as 30d, iif([days]>=31 and [days]<=60,"60 days","") as 60d

  3. #3
    james bond is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2020
    Posts
    5
    sir thank you but please please I am new can I give you data to help and advice me please I have mention all relation just 1 word will help can you help
    I can't understand buckets or data range I would relay be obliged

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a form, say fMyForm. on the form are 2 text boxes for the dates: txtStartDate, txtEndDate
    this is used in Q1 to pull data from what is in these boxes:

    Q1= select *, DateDiff("d",[date1],[date2] as Days from table where [datefeld] between forms!
    fMyForm!txtStartDate and
    forms!fMyForm!txtEndDate

    so Q1 will pull data in the date range listed AND calculate the #days.
    DateDiff is the function used to calculate the # days between 2 dates.


    Q2 then takes the DAYS calculated in Q1 and creates 'buckets' (fields) of 30d, 60d, 90d.
    Q2: select *,
    iif([days]>=0 and [days]<=30,"30 days","") as 30d, iif([days]>=31 and [days]<=60,"60 days","") as 60d
    from Q1


  5. #5
    james bond is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Mar 2020
    Posts
    5
    thanks thanks thanks one more thing same invoice number has six amounts as 3 each are for dr and 3 for cr
    1 is basic other are taxes I want to add all and they are in rows how do I add them so that I get 1 invoice number with total amount of dr only
    also any way I can save these queries tables I get in the mdb file as some name

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

Similar Threads

  1. Replies: 8
    Last Post: 08-15-2019, 02:49 PM
  2. Replies: 1
    Last Post: 10-08-2015, 10:59 AM
  3. Replies: 1
    Last Post: 02-27-2014, 07:59 PM
  4. Replies: 3
    Last Post: 03-11-2013, 05:11 PM
  5. Replies: 3
    Last Post: 05-21-2010, 03:57 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