the way i have things set up has to be wrong. here's the lowdown.
I have a database that manages the accounting of scholarships. The main table is set up like so:
tblAccounts
[p]AccountsId
AccountNumber
FamsNumber
Account Title
BeginningBalance
Income
Expenses
Balance
the way the accounts work is a little confusing, so i will try and clarify. Most scholarships have one account number and one fams number and are for one of the many campuses at the university. But the proverbial gum in the works is this: Some scholarships are shared between multiple campuses. this in turn requires, per scholarship, multiple fams numbers(for each campus) but still only one account number.
Example:
Title - Bob Smith Scholarship for Accounting
Account Number - 654321
Fams Number - 12345
Title - Bob Smith Scholarship for Engineering
Account Number - 654321
Fams Number - 67890
all of this wouldn't be such a pain but the financial information is updated automatically. When the scholarships are updated the account number is used to place the financial data with the appropriate scholarship, being that it is unique. if i put all the scholarships in one table, when the data is imported it puts the same financial amount in multiple scholarships instead of dividing the money between the campuses.
What it does now:
Title - Bob Smith Scholarship for Accounting
Account Number - 654321
Fams Number - 12345
Beginning Balance - $0
Income - $10,000
Expenses - $5,000
Balance - $0
Title - Bob Smith Scholarship for Engineering
Account Number - 654321
Fams Number - 67890
Beginning Balance - $0
Income - $10,000
Expenses - $5,000
Balance - $0
It should look like this:
Title - Bob Smith Scholarship for Accounting
Account Number - 654321
Fams Number - 12345
Beginning Balance - $0
Income - $5,000
Expenses - $2,500
Balance - $2,500
Title - Bob Smith Scholarship for Engineering
Account Number - 654321
Fams Number - 67890
Beginning Balance - $0
Income - $5,000
Expenses - $2,500
Balance - $2,500
to further complicate matters, the way the money is divided between shared scholarships is not always the same. some might be split three ways equally(33% 33% 33%) while others may be split 20% 20% 60%.
how can i, when imported, get the financial data to be distributed between the scholarships the way it is supposed to be. i am sure i need some other table(s) to get this working buut i can't figure out the structure. if anyone has done something similar or just plain udnerstands what the heck i am trying to do, please help!
jerry.
I have a database that manages the accounting of scholarships. The main table is set up like so:
tblAccounts
[p]AccountsId
AccountNumber
FamsNumber
Account Title
BeginningBalance
Income
Expenses
Balance
the way the accounts work is a little confusing, so i will try and clarify. Most scholarships have one account number and one fams number and are for one of the many campuses at the university. But the proverbial gum in the works is this: Some scholarships are shared between multiple campuses. this in turn requires, per scholarship, multiple fams numbers(for each campus) but still only one account number.
Example:
Title - Bob Smith Scholarship for Accounting
Account Number - 654321
Fams Number - 12345
Title - Bob Smith Scholarship for Engineering
Account Number - 654321
Fams Number - 67890
all of this wouldn't be such a pain but the financial information is updated automatically. When the scholarships are updated the account number is used to place the financial data with the appropriate scholarship, being that it is unique. if i put all the scholarships in one table, when the data is imported it puts the same financial amount in multiple scholarships instead of dividing the money between the campuses.
What it does now:
Title - Bob Smith Scholarship for Accounting
Account Number - 654321
Fams Number - 12345
Beginning Balance - $0
Income - $10,000
Expenses - $5,000
Balance - $0
Title - Bob Smith Scholarship for Engineering
Account Number - 654321
Fams Number - 67890
Beginning Balance - $0
Income - $10,000
Expenses - $5,000
Balance - $0
It should look like this:
Title - Bob Smith Scholarship for Accounting
Account Number - 654321
Fams Number - 12345
Beginning Balance - $0
Income - $5,000
Expenses - $2,500
Balance - $2,500
Title - Bob Smith Scholarship for Engineering
Account Number - 654321
Fams Number - 67890
Beginning Balance - $0
Income - $5,000
Expenses - $2,500
Balance - $2,500
to further complicate matters, the way the money is divided between shared scholarships is not always the same. some might be split three ways equally(33% 33% 33%) while others may be split 20% 20% 60%.
how can i, when imported, get the financial data to be distributed between the scholarships the way it is supposed to be. i am sure i need some other table(s) to get this working buut i can't figure out the structure. if anyone has done something similar or just plain udnerstands what the heck i am trying to do, please help!
jerry.
