P
Peter
Hello everyone,
I need to check the balance of an account during reporting periods (13
x 4 weeks throughout the year)
I have devised a query to do this.
But first the build up:
(ALL AMOUNTS ARE FICTIONAL !)
I have a table called Balances 103090. This table holds the balances
at the beginning of each period of this particular account,
the amounts accumulated onto it by category as well as the amounts
that were discharged from it to other accounts,
also in totals per category. This should, if entered and calculated
correctly, lead to the proper new balance at the end of each period.
The query fields put into my query refer to the following fields from
tables and other queries:
[Balances 103090].[Period]
[Balances 103090].[Balance]
[Q t10 Invoices per Period].[Invoices cumulated]
[Q t30 Discharges per period].[Discharges cumulated]
The Q txx entities metioned above are queries that sum up the amounts
per item and have been proven correct.
The table [Balances 103090] holds these amounts:
P00 ? 0,00
P01 ? 105.000,00
P02 ? 117.000,00
The starting balance of this account was actually ? 105.000,00, but I
figured
that this could accomplished by charging it to the account in period
P00.
This way, at the "end of period 0" the balance would be the actual
starting balance.
To complete the example I give you these amounts and the period in
which they were booked.
Period Invoices cumulated Discharges cumulated
P01 ? 65.000,00 ? 53.000,00
If all went as expected, the calculation should have been as follows:
At the beginning of + Invoices -/- Discharges = New Balance
P00 ? 0,00 + ? 105.000,00 -/- ? 0,00 =
? 105.000,00
P01 ? 105.000,00 + ? 65.000,00 -/- ? 53.000,00 = ?
117.000,00
This is what I see on screen when I run the query:
Period Balance Invoices Discharged New Balance
P00 ? 0,00 + ? 65.000,00 -/- ? 53.000,00 = ?
12.000,00
P00 ? 0,00 + ? 105.000,00 -/- ? 53.000,00 = ?
52.000,00
P01 ? 105.000,00 + ? 65.000,00 -/- ? 53.000,00 = ?
117.000,00
P01 ? 105.000,00 + ? 105.000,00 -/- ? 53.000,00 = ?
157.000,00
P01 ? 117.000,00 + ? 65.000,00 -/- ? 53.000,00 = ?
129.000,00
P02 ? 117.000,00 + ? 105.000,00 -/- ? 53.000,00 = ?
169.000,00
As you can see amounts are shifted among periods, and,
amounts that were booked in period P01 are repeatedly mentioned
as also being booked in the other period.
I hope I have been clear in my explanation
Regards,
Peter
I need to check the balance of an account during reporting periods (13
x 4 weeks throughout the year)
I have devised a query to do this.
But first the build up:
(ALL AMOUNTS ARE FICTIONAL !)
I have a table called Balances 103090. This table holds the balances
at the beginning of each period of this particular account,
the amounts accumulated onto it by category as well as the amounts
that were discharged from it to other accounts,
also in totals per category. This should, if entered and calculated
correctly, lead to the proper new balance at the end of each period.
The query fields put into my query refer to the following fields from
tables and other queries:
[Balances 103090].[Period]
[Balances 103090].[Balance]
[Q t10 Invoices per Period].[Invoices cumulated]
[Q t30 Discharges per period].[Discharges cumulated]
The Q txx entities metioned above are queries that sum up the amounts
per item and have been proven correct.
The table [Balances 103090] holds these amounts:
P00 ? 0,00
P01 ? 105.000,00
P02 ? 117.000,00
The starting balance of this account was actually ? 105.000,00, but I
figured
that this could accomplished by charging it to the account in period
P00.
This way, at the "end of period 0" the balance would be the actual
starting balance.
To complete the example I give you these amounts and the period in
which they were booked.
Period Invoices cumulated Discharges cumulated
P01 ? 65.000,00 ? 53.000,00
If all went as expected, the calculation should have been as follows:
At the beginning of + Invoices -/- Discharges = New Balance
P00 ? 0,00 + ? 105.000,00 -/- ? 0,00 =
? 105.000,00
P01 ? 105.000,00 + ? 65.000,00 -/- ? 53.000,00 = ?
117.000,00
This is what I see on screen when I run the query:
Period Balance Invoices Discharged New Balance
P00 ? 0,00 + ? 65.000,00 -/- ? 53.000,00 = ?
12.000,00
P00 ? 0,00 + ? 105.000,00 -/- ? 53.000,00 = ?
52.000,00
P01 ? 105.000,00 + ? 65.000,00 -/- ? 53.000,00 = ?
117.000,00
P01 ? 105.000,00 + ? 105.000,00 -/- ? 53.000,00 = ?
157.000,00
P01 ? 117.000,00 + ? 65.000,00 -/- ? 53.000,00 = ?
129.000,00
P02 ? 117.000,00 + ? 105.000,00 -/- ? 53.000,00 = ?
169.000,00
As you can see amounts are shifted among periods, and,
amounts that were booked in period P01 are repeatedly mentioned
as also being booked in the other period.
I hope I have been clear in my explanation
Regards,
Peter