Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272

    Difficulty in finding opening and closing balances in my access database report.

    I have two MS Access database tables, one is for income and the other for expenses.

    Income table details


    DATE, TYPE OF INCOME, AMOUNT

    Expenses Table details
    DATE, NARRATION, AMOUNT

    It is a church database and a such the type of data for the income are

    Tithes, Offering, Fund raising, Seed sowing

    Have been able to query both income and expenditure in order to get the

    Sum of all Income - Sum of all Expenditure = Balance (in my Income & Expenditure Account)
    This was successfully for the year 2019.

    I will like the balance to carry forward to 2020 so it will add up to the income in 2020 and expenditure deducted accordingly.

    And the cycle continues.

    Have tried all I could to do this. Kindly help me out.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    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. #3
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Sorry but I forgot to add that I have posted it in the above website. I need it urgently so it skipped me. Apologies

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I did a forum search on keywords report opening balance. Here is one https://www.accessforums.net/showthread.php?t=75748
    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. #5
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Please I have tried to relate other people’s solution to mine to see how I can solve it but am not making any progress.
    As I said earlier, am a beginner and don’t know much wit reference to access vba codes

  6. #6
    CarlettoFed is online now Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255

    Risposta

    If you attach the file, devoid of any sensitive data, having the structure of your database available, perhaps it is easier to help you.

  7. #7
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    How do I attach a file here please?

  8. #8
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Please here is the database for your consideration
    Attached Files Attached Files

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Don't have time to look at db right now. Link I provided demonstrates use of DSum(). Did you try? Why doesn't it work for you? What exactly have you tried?
    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. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it would be significantly easier if your income and expenditure data were in one table - either using positive/negative to indicate which type, or a separate field. You would also make you life a lot easier by not having spaces or non alphanumeric characters in table/field/query/form/report names and not using reserved words as field names (Date). You also have some weird relationships - ID field of income table related to the date field in the expenses table for example.

    You haven't populated the Balance field in either table - I would remove it, storing calculate values such as this is a) a very bad idea and b) impossible to maintain with your current setup

    As it is you will need quite a complex query setup but suggest you try this

    Qry1 - to combine data
    Code:
    SELECT "Income" as TType, * FROM [Income Table]
    UNION ALL SELECT "Expense", * FROM [Expenses Table]
    then to get opening balance you need (call this Qry2)

    Code:
    SELECT "Opening Balance" AS TranType, , 0 AS TranPK, #01/01/2020# AS TranDate , Sum (Amount*iif(TType="Income",1,-1)) as TranAmount
    FROM Qry1
    WHERE [Date]<#01/01/2020#
    Now get all the transactions on or after 1st Jan (call this Qry3)

    Code:
    SELECT TType, ID, [Date], [Type of Income], Amount*iif(TType="Income",1,-1) as TranAmount
    FROM Qry1
    WHERE [Date]>=#01/01/2020#
    Finally combine these last two and set an order (Qry4)

    Code:
    SELECT * FROM Qry2
    UNION ALL SELECT * FROM Qry3
    ORDER BY TranDate, TranPK
    Those are the individual steps you need to go through. Check each one as you go. You can combine the queries once you are happy it is working as expected. This code has been freetyped, so there may be typo's. If your code doesn't work, provide the code you actually used and explain what the problem is (you get an error? - what's the error, you get a wrong result? what result is your data based on, what did you get what did you expect to get)

  11. #11
    hci_jon is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Montgomery, IL
    Posts
    9
    Having a balance forward for today is simply the sum of all income in the prior year minus all expenses.

    This should work by definition. Your logic there is solid. Is there a particular form or report where it is not doing what you expect? Maybe the form or report you are using is filtering records by year? If so, we need to know how you get to the form or report you want to modify so we can look it up in your database and then explain what needs to be done.

  12. #12
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Here's a report example showing bal/fwd.
    Click image for larger version. 

Name:	emanrpt .png 
Views:	35 
Size:	16.6 KB 
ID:	40624


    Database below is bare bones, with income and expense combined into one table. Shows how to get the report above.
    You don't need a separate series of queries for each type of income/expense.

    Church-emmanuel-davegri-v02.zip
    Last edited by davegri; 01-08-2020 at 11:03 AM.

  13. #13
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Thanks for the feedback. I tried testing yours and I realized it works perfectly for 2020. But when you change the computer’s date to say 2021 to assume that is the current year, you realize the current balance from 2020 isn’t what was carried forward to the year 2021.

  14. #14
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by hci_jon View Post
    Having a balance forward for today is simply the sum of all income in the prior year minus all expenses.

    This should work by definition. Your logic there is solid. Is there a particular form or report where it is not doing what you expect? Maybe the form or report you are using is filtering records by year? If so, we need to know how you get to the form or report you want to modify so we can look it up in your database and then explain what needs to be done.


    hanks for the feedback. I tried testing yours and I realized it works perfectly for 2020. But when you change the computer’s date to say 2021 to assume that is the current year, you realize the current balance from 2020 isn’t what was carried forward to the year 2021.

  15. #15
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by Ajax View Post
    it would be significantly easier if your income and expenditure data were in one table - either using positive/negative to indicate which type, or a separate field. You would also make you life a lot easier by not having spaces or non alphanumeric characters in table/field/query/form/report names and not using reserved words as field names (Date). You also have some weird relationships - ID field of income table related to the date field in the expenses table for example.

    You haven't populated the Balance field in either table - I would remove it, storing calculate values such as this is a) a very bad idea and b) impossible to maintain with your current setup

    As it is you will need quite a complex query setup but suggest you try this

    Qry1 - to combine data
    Code:
    SELECT "Income" as TType, * FROM [Income Table]
    UNION ALL SELECT "Expense", * FROM [Expenses Table]
    then to get opening balance you need (call this Qry2)

    Code:
    SELECT "Opening Balance" AS TranType, , 0 AS TranPK, #01/01/2020# AS TranDate , Sum (Amount*iif(TType="Income",1,-1)) as TranAmount
    FROM Qry1
    WHERE [Date]<#01/01/2020#
    Now get all the transactions on or after 1st Jan (call this Qry3)

    Code:
    SELECT TType, ID, [Date], [Type of Income], Amount*iif(TType="Income",1,-1) as TranAmount
    FROM Qry1
    WHERE [Date]>=#01/01/2020#
    Finally combine these last two and set an order (Qry4)

    Code:
    SELECT * FROM Qry2
    UNION ALL SELECT * FROM Qry3
    ORDER BY TranDate, TranPK
    Those are the individual steps you need to go through. Check each one as you go. You can combine the queries once you are happy it is working as expected. This code has been freetyped, so there may be typo's. If your code doesn't work, provide the code you actually used and explain what the problem is (you get an error? - what's the error, you get a wrong result? what result is your data based on, what did you get what did you expect to get)


    Thanks for the feedback. Please let me try and get back to you

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Time-series database for monthly loan balances
    By rlmax in forum Database Design
    Replies: 6
    Last Post: 03-08-2017, 09:15 AM
  2. Replies: 4
    Last Post: 08-11-2015, 03:12 PM
  3. Opening/closing Anything in Access is slow!
    By cboshdave in forum Access
    Replies: 1
    Last Post: 06-02-2015, 02:27 PM
  4. Replies: 3
    Last Post: 06-05-2012, 01:47 PM
  5. Closing 1 form and opening another
    By mulefeathers in forum Forms
    Replies: 13
    Last Post: 12-08-2011, 04:04 PM

Tags for this Thread

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