I have collected data for a designated timeframe (Beginning Date and Ending Date) for Multiple Suspense Accounts. For discussion sake, let's say the reporting period is 1 week, though it could be different at times.
Additionally, I have collected data for 6 months prior to the Beginning Date for use in Part 2 below.
I have two tables. The first table contains Opening and Closing Suspense Account Balances based on the Beginning and Ending Dates the user enters in fields on the Main Menu. The second table is a details table showing all individual Transactions with the date the Transaction is Effective. This detail table contains the 6 months of data prior to the reporting Start and End Dates.
Part 1:
I am trying to create an Aging Report for a given timeframe. If, on the Starting Date, the account balance was $0 and anytime during that timeframe, amounts were either added or subtracted, how long was that cash in that account...even if the cash was deposited and withdrawn on the same day a 1 day timeframe would be identified.
Part 2:
On the Starting Date, if the Opening Balance was NOT $0, how long has THAT amount been in the account. This could be a single amount that was deposited or a number of Transactions that were deposited totaling the current Opening Balance; hence the additional 6 months previous data to be used to search for the aging of an existing balance that was NOT $0.
Any help would be appreciated and if additional information is needed or explained better, please do let me know.
Thank you in advance for your time and assistance.
Chip
Additionally, I have collected data for 6 months prior to the Beginning Date for use in Part 2 below.
I have two tables. The first table contains Opening and Closing Suspense Account Balances based on the Beginning and Ending Dates the user enters in fields on the Main Menu. The second table is a details table showing all individual Transactions with the date the Transaction is Effective. This detail table contains the 6 months of data prior to the reporting Start and End Dates.
Part 1:
I am trying to create an Aging Report for a given timeframe. If, on the Starting Date, the account balance was $0 and anytime during that timeframe, amounts were either added or subtracted, how long was that cash in that account...even if the cash was deposited and withdrawn on the same day a 1 day timeframe would be identified.
Part 2:
On the Starting Date, if the Opening Balance was NOT $0, how long has THAT amount been in the account. This could be a single amount that was deposited or a number of Transactions that were deposited totaling the current Opening Balance; hence the additional 6 months previous data to be used to search for the aging of an existing balance that was NOT $0.
Any help would be appreciated and if additional information is needed or explained better, please do let me know.
Thank you in advance for your time and assistance.
Chip