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)