M
Michael Malinsky
I'm starting a new post for this question since I apparently was unable to
clearly communicate my needs in my original post.
I have a paper financial type of report that is being prepared manually by
summarizing data from a trial balance. I'm trying to "automate" this
process by dropping the trial balance into an Access database. I am looking
for guidance on database schema and normalization for my tables on which I
can base queries to do what I want. My current thought process is for my
first table to be tblTrialBalance which will hold an accounting trial
balance. What I'm trying to do is create a mechanism that will group the
accounts into corresponding pages/lines of the paper report I already have.
Example:
tblTrialBalance
AccountID AccountDesc Balance
1000 Checking 1,000
1010 Money Mkt 2,000
2000 Accts. Pay -1,500
3000 Equity -500
4000 Revenue A -1,000
4010 Revenue B -250
5000 Expenses 250
The report I have, in a simple form, would have something like (note that in
reality, a page can have from 1 to 4 rows):
Page Page Desc Line LineDesc SubLineDesc
Column
1 Assets 1 Cash
1 Assets 1.1 Cash Checking
1
1 Assets 1.2 Cash Money Market
1
2 Liabilities 1 Payables
1
2 Liabilities 2 Equity
1
3 Inc. Stmt. 1 Revenue A
1
3 Inc. Stmt. 1 Revenue B
2
3 Inc. Stmt. 2 Expenses
1
So I envisioned tblGroups to look like the following and would then add
fields to tblTrialBalance to include GroupID and SubGroupID.
GroupID SubGroupID Page Line LineDesc SubLineDesc
Column
1 1 1 Cash
1 1.1 1 1.1 Cash
Checking 1
1 1.2 1 1.2 Cash
Money Market 1
2 2 1
Payables 1
3 2 2 Equity
1
4 3 1
Revenue A 1
5 3 1
Revenue B 2
6 3 2
Expenses 1
Hopefully this clears things up from my original post and someone can give
me some guidance on how to properly do what I'm trying to do.
TIA.
--
Michael J. Malinsky
Pittsburgh, PA
"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh
clearly communicate my needs in my original post.
I have a paper financial type of report that is being prepared manually by
summarizing data from a trial balance. I'm trying to "automate" this
process by dropping the trial balance into an Access database. I am looking
for guidance on database schema and normalization for my tables on which I
can base queries to do what I want. My current thought process is for my
first table to be tblTrialBalance which will hold an accounting trial
balance. What I'm trying to do is create a mechanism that will group the
accounts into corresponding pages/lines of the paper report I already have.
Example:
tblTrialBalance
AccountID AccountDesc Balance
1000 Checking 1,000
1010 Money Mkt 2,000
2000 Accts. Pay -1,500
3000 Equity -500
4000 Revenue A -1,000
4010 Revenue B -250
5000 Expenses 250
The report I have, in a simple form, would have something like (note that in
reality, a page can have from 1 to 4 rows):
Page Page Desc Line LineDesc SubLineDesc
Column
1 Assets 1 Cash
1 Assets 1.1 Cash Checking
1
1 Assets 1.2 Cash Money Market
1
2 Liabilities 1 Payables
1
2 Liabilities 2 Equity
1
3 Inc. Stmt. 1 Revenue A
1
3 Inc. Stmt. 1 Revenue B
2
3 Inc. Stmt. 2 Expenses
1
So I envisioned tblGroups to look like the following and would then add
fields to tblTrialBalance to include GroupID and SubGroupID.
GroupID SubGroupID Page Line LineDesc SubLineDesc
Column
1 1 1 Cash
1 1.1 1 1.1 Cash
Checking 1
1 1.2 1 1.2 Cash
Money Market 1
2 2 1
Payables 1
3 2 2 Equity
1
4 3 1
Revenue A 1
5 3 1
Revenue B 2
6 3 2
Expenses 1
Hopefully this clears things up from my original post and someone can give
me some guidance on how to properly do what I'm trying to do.
TIA.
--
Michael J. Malinsky
Pittsburgh, PA
"I am a bear of very little brain, and long
words bother me." -- AA Milne, Winnie the Pooh