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

[davegri]

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.

I assume this is in response to post #12.


Well, that was an example just to show the current and prior year. When 2020 ends, it will automatically show 2020 as prior and 2021 as current. In your case of changing the date in the computer, I would NEVER do that to test anything, and would not trust any results from such a drastic step.
If you want to specify a specific current year, you would need to enter the year on a form and the form would save the year in a tempvar. Then the tempvar would be used in the query criteria instead of Year(Date()).

[Emmanuel]

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

SELECT "Income" as TType, * FROM [Income Table]
UNION ALL SELECT "Expense", * FROM [Expenses Table]

then to get opening balance you need (call this Qry2)

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)

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)

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)

Hello.
Please Qry1 to Qry3 worked.
Qry4 gave me an error which reads “ The number of columns in the two selected tables or queries of a union query do not match”

[Mickjav]

I don't keep the openning balance with the transactions data I use the Account Table

[CJ_London]

my mistake

qry2 should be

SELECT "Opening Balance" AS TranType, 0 AS TranPK, #01/01/2020# AS TranDate,"" AS TranDesc , Sum (Amount*iif(TType="Income",1,-1)) as TranAmount
FROM Qry1
WHERE [Date]<#01/01/2020#

[Emmanuel]

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

please I tried yours and it was going smoother But when I finished Qry4, it gave me an error

[CJ_London]

what is the error

@Mickjav - this is a rolling opening balance - at the beginning of a given year