Hi.
As a newbie to Access I have received tonnes of GREAT advice on building tables in the "tables and relationships" forum. I'm now in the process of building forms and subforms, etc.
My basic database requirements and table info is listed below.
I have already built the basic "ADD" forms (Add New Insurance Company, Add New Broker, etc) and am now working on the main "working" form.
What I'm looking for (or at least envisioning) is a main form that will allow a user to search for/select a broker from the <tblBrokerInfo> table with a confirmation once the correct broker record is found. Once "confirmed", the focus is shifted to the subform that allows the user to select the Insurance Company (located in the <tblCompanyInfo> table that the selected broker's had sale(s) for. This should then populate the subform with that company's commission rates that are stored in the same table.
Once this occurs, the user will enter the selected Broker's "FYC" info (dollar amount that the broker earns as a base commission depending on the Insurance company they had a sale for( the one we just selected))...please see info below).
Once the FYC is entered, either a "Calculate Commission" button or something like that is pushed and the Broker's commission(s) for that company is calculated and stored in <tblBrokerMonthlyData>. The user should then have the option of selecting another Insurance Company that the same Broker had sale(s) for that month or to select a new Broker.
Just a quick footnote: My intentions for this question is not to have someone create/code the form/subform for me (although if someone wishes to, then by all means please do..<smiling>), but to help me get started by doing some hand-holding.
Many thanks for any and all help and/or suggestions. You folks are the best!!
Please see below for details.
Mark
______________________
Scope of the project:
There're approximately 1000 independent brokers who sell either traditional insurance (life, health, etc) OR "money" stuff (mutual funds, stocks, etc) for (as of now) approximately 20 different insurance companies. Our company acts as a middle-man between the independent brokers and the insurance companies. We have to calculate each broker's commission based on the amount of the sale (the broker's FYC, which would be entered for each broker each time there's a sale) and the broker's commission percentage rate (For traditional insurance this is a static rate that changes relatively infrequently but for "money" sales, this number is either calculated using the same formula as the traditional insurance OR is an amount that has to be entered by an end-user). Our company get's a cut of the total commission using a "total bonus rate" (this is a percentage as well). Both types of "sales" (traditional and money sales) have different rate calculations.
For example,
Broker John Doe sells a life insurance policy this month (to a client while representing insurance company A resulting in $1000.00 in premiums) and shares of a hot stock (to a client while representing insurance company B resulting in premiums of another $1000.00). John Doe's FYC (First Year Commission - his base commission)is $100.00. (an amount determined by our company) John Doe's commission would be calculated in the following manner:
Company A: Sells $1000.00 in life insurance premiums.
$100.00 (FYC) * 170% (broker's percentage) = $170.00
(amount John Doe receives from the $1000.00 premium)
Company B: Sells $1000.00 in stock premiums.
$100.00 (FYC) * 170% = $170.00 OR an amount that has to
be manually entered depending on the insurance company
Our company receives a "cut" of the commission in this way:
Depending on the insurance company, the rate is a
percentage and can be 185%, 200%, etc. Thus,
our cut is:
FYC * (200% (depends on the ins. company) - broker's %)
$100.00 * (200% - 170%) = $100.00 * 30% = $30.00.
For each broker, these commissions must be tracked for EACH insurance company on a monthly basis as well as a Year-To-Date.
The data collected for John Doe each month is:
His name
His commission rate.
His monthly commission for each company he had sales.
His total monthly commission for all companies.
His total monthly FYCs (base commissions).
His total FYC for the YTD.
Our company's monthly commissions generated by that broker.
Our company's YTD commissions generated by that broker.
--------------------------
I have three tables as follows:
tblBrokerInfo
brkrID - Primary Key (links to tblBrokerMonthlyData)
brkrLN - Broker's Last Name
brkrFN - Broker's First Name
brkrCompanyName - name of company if no ind broker
brkrOffLoc - Location of broker's office
brkrORRateA - Broker's commission rate (in %)
brkrORRateIA - Broker's commission rate (if sale was
with a single, specific company)
tblBrokerMonthlyData
BrokerID - links with <tblBrokerInfo.brkrID> field
CompanyID - links with <tblCompanyInfo.CompanyID> field
BrokerFYC - Broker's base commission rate (can change)
BrokerORMS - Broker's commission (Money Sales)
BrokerORLS - Broker's commission (Traditional Sales)
FHORLS - Our Company's commission on the broker's sale
(Traditional Sales)
FHORMS - Our company's commission on the broker's sale
(Money Sales)
Month - Month in which the sale was recorded
Year - Year in which the sale was recorded.
tblCompanyInfo
CompanyID - Primary Key - links to
tblBrokerMonthlySales.CompanyID>
CmpnyName - Name of the Insurance Company that the
broker represented for the sale.
TotalORRateLS - commission rate (in %) that our company
receives from this company for each
traditional sale.
TotalORRateMS - same as above for money sales.
As a newbie to Access I have received tonnes of GREAT advice on building tables in the "tables and relationships" forum. I'm now in the process of building forms and subforms, etc.
My basic database requirements and table info is listed below.
I have already built the basic "ADD" forms (Add New Insurance Company, Add New Broker, etc) and am now working on the main "working" form.
What I'm looking for (or at least envisioning) is a main form that will allow a user to search for/select a broker from the <tblBrokerInfo> table with a confirmation once the correct broker record is found. Once "confirmed", the focus is shifted to the subform that allows the user to select the Insurance Company (located in the <tblCompanyInfo> table that the selected broker's had sale(s) for. This should then populate the subform with that company's commission rates that are stored in the same table.
Once this occurs, the user will enter the selected Broker's "FYC" info (dollar amount that the broker earns as a base commission depending on the Insurance company they had a sale for( the one we just selected))...please see info below).
Once the FYC is entered, either a "Calculate Commission" button or something like that is pushed and the Broker's commission(s) for that company is calculated and stored in <tblBrokerMonthlyData>. The user should then have the option of selecting another Insurance Company that the same Broker had sale(s) for that month or to select a new Broker.
Just a quick footnote: My intentions for this question is not to have someone create/code the form/subform for me (although if someone wishes to, then by all means please do..<smiling>), but to help me get started by doing some hand-holding.
Many thanks for any and all help and/or suggestions. You folks are the best!!
Please see below for details.
Mark
______________________
Scope of the project:
There're approximately 1000 independent brokers who sell either traditional insurance (life, health, etc) OR "money" stuff (mutual funds, stocks, etc) for (as of now) approximately 20 different insurance companies. Our company acts as a middle-man between the independent brokers and the insurance companies. We have to calculate each broker's commission based on the amount of the sale (the broker's FYC, which would be entered for each broker each time there's a sale) and the broker's commission percentage rate (For traditional insurance this is a static rate that changes relatively infrequently but for "money" sales, this number is either calculated using the same formula as the traditional insurance OR is an amount that has to be entered by an end-user). Our company get's a cut of the total commission using a "total bonus rate" (this is a percentage as well). Both types of "sales" (traditional and money sales) have different rate calculations.
For example,
Broker John Doe sells a life insurance policy this month (to a client while representing insurance company A resulting in $1000.00 in premiums) and shares of a hot stock (to a client while representing insurance company B resulting in premiums of another $1000.00). John Doe's FYC (First Year Commission - his base commission)is $100.00. (an amount determined by our company) John Doe's commission would be calculated in the following manner:
Company A: Sells $1000.00 in life insurance premiums.
$100.00 (FYC) * 170% (broker's percentage) = $170.00
(amount John Doe receives from the $1000.00 premium)
Company B: Sells $1000.00 in stock premiums.
$100.00 (FYC) * 170% = $170.00 OR an amount that has to
be manually entered depending on the insurance company
Our company receives a "cut" of the commission in this way:
Depending on the insurance company, the rate is a
percentage and can be 185%, 200%, etc. Thus,
our cut is:
FYC * (200% (depends on the ins. company) - broker's %)
$100.00 * (200% - 170%) = $100.00 * 30% = $30.00.
For each broker, these commissions must be tracked for EACH insurance company on a monthly basis as well as a Year-To-Date.
The data collected for John Doe each month is:
His name
His commission rate.
His monthly commission for each company he had sales.
His total monthly commission for all companies.
His total monthly FYCs (base commissions).
His total FYC for the YTD.
Our company's monthly commissions generated by that broker.
Our company's YTD commissions generated by that broker.
--------------------------
I have three tables as follows:
tblBrokerInfo
brkrID - Primary Key (links to tblBrokerMonthlyData)
brkrLN - Broker's Last Name
brkrFN - Broker's First Name
brkrCompanyName - name of company if no ind broker
brkrOffLoc - Location of broker's office
brkrORRateA - Broker's commission rate (in %)
brkrORRateIA - Broker's commission rate (if sale was
with a single, specific company)
tblBrokerMonthlyData
BrokerID - links with <tblBrokerInfo.brkrID> field
CompanyID - links with <tblCompanyInfo.CompanyID> field
BrokerFYC - Broker's base commission rate (can change)
BrokerORMS - Broker's commission (Money Sales)
BrokerORLS - Broker's commission (Traditional Sales)
FHORLS - Our Company's commission on the broker's sale
(Traditional Sales)
FHORMS - Our company's commission on the broker's sale
(Money Sales)
Month - Month in which the sale was recorded
Year - Year in which the sale was recorded.
tblCompanyInfo
CompanyID - Primary Key - links to
tblBrokerMonthlySales.CompanyID>
CmpnyName - Name of the Insurance Company that the
broker represented for the sale.
TotalORRateLS - commission rate (in %) that our company
receives from this company for each
traditional sale.
TotalORRateMS - same as above for money sales.
