Voltron
Defender of the universe
- Local time
- Yesterday, 18:04
- Joined
- Jul 9, 2009
- Messages
- 77
Calculated field issue dealing with other queries
Here's the deal. In my continuing quest to replicate my company's fee structure in an Access database (to create a query to spit out the information they need in the format they need it in) I have run across numerous exceptions which have drastically complicated things. I have done my best to get all of the calculations for specific fees/exceptions in the same query, but I fear I can't do this with the following problem.
There are a could of clients that have their fees split between 2 separate plans. That's all fine and dandy except for the fact that they are based off of percentages of the total assets in the plans. More specifically:
(Fee*4)*(Total Plan Assets of first plan/(Total Plan Assets of first plan+Total Plan Assets of the second plan))
The 4 represents the number of quarters in the year. The fee is based off of a quarterly amount. The Fee is a hard-coded number that will be taken from a table. Total Plan Assets is a calculated field in another query.
My question is this...Is there anyway to get this to work for all of the separate instances of this throughout our client base or am I going to have to create queries to handle this for each and every client? I know exactly how to do the latter, but really want to avoid it because it is tedious and not easy for others to understand if they aren't the ones working with the database on a daily basis.
Any advice will be greatly appreciated.
Here's the deal. In my continuing quest to replicate my company's fee structure in an Access database (to create a query to spit out the information they need in the format they need it in) I have run across numerous exceptions which have drastically complicated things. I have done my best to get all of the calculations for specific fees/exceptions in the same query, but I fear I can't do this with the following problem.
There are a could of clients that have their fees split between 2 separate plans. That's all fine and dandy except for the fact that they are based off of percentages of the total assets in the plans. More specifically:
(Fee*4)*(Total Plan Assets of first plan/(Total Plan Assets of first plan+Total Plan Assets of the second plan))
The 4 represents the number of quarters in the year. The fee is based off of a quarterly amount. The Fee is a hard-coded number that will be taken from a table. Total Plan Assets is a calculated field in another query.
My question is this...Is there anyway to get this to work for all of the separate instances of this throughout our client base or am I going to have to create queries to handle this for each and every client? I know exactly how to do the latter, but really want to avoid it because it is tedious and not easy for others to understand if they aren't the ones working with the database on a daily basis.
Any advice will be greatly appreciated.
Last edited:
