pdvsa
asked on
Commissions Matrix
Experts,
I am wanting to put a Banks commission schedule inside Access.
The pricing is based off a term in years.
The terms is entered in another table : tblLetterofCredit
Example Commission Structure
Bank 1
Up to 3 years .6%
3-5 years .75%
Bank 2
Up to 4 years .6%
4-5 years .75%
I am not certain if the term parameters can be somehow written into a table or maybe it can be simplified quite easily.
Thank you
I am wanting to put a Banks commission schedule inside Access.
The pricing is based off a term in years.
The terms is entered in another table : tblLetterofCredit
Example Commission Structure
Bank 1
Up to 3 years .6%
3-5 years .75%
Bank 2
Up to 4 years .6%
4-5 years .75%
I am not certain if the term parameters can be somehow written into a table or maybe it can be simplified quite easily.
Thank you
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sb9: I think I can work with that. I dont completely understand it but I think once I make the tables and test it I will.
thanks
thanks
ASKER
I was wondering if within the table, the validation rule could somehow be used as it does have >, < and some other interesting operators.
pdvsa,
The short answer is yes but depending on which field you are referring validation may not be practical noting that most of the fields are text or autonumber.
In the structure that I have provided tblTerm acts like a lookup table. I would expect that to be a relatively short list (1-2,1-3,1-5,2-3,2-4,2-5,3 -4,3-5 ...). You can get more functionality by changing the structure of tblTerm to the following:
tblTerm
fldTermID (Autonumber, primary key)
fldTermStart (Number)
fldTermEnd (Number)
So the table (minus fldTermID) will look like
1 | 2
1 | 3
1 | 4
1 | 5
1 | 6
1 | 7
1 | 8
1 | 9
2 | 3
2 | 4
2 | 5
2 | 6
2 | 7
2 | 8
2 | 9
3 | 4
3 | 5
3 | 6
3 | 7
3 | 8
3 | 9
4 | 5
4 | 6
4 | 7
4 | 8
4 | 9
Then you can use validation to ensure that termend is greater than termstart.
The row source for fldTermID in tblCommission will be:
Select fldTermID, [fldTermStart] & " - " & [fldTermEnd] & " years" As Term
The beauty of this approach is that it will allow you to compare commissions that fall within a certain period. For example if you want to find the terms that start at less than 4 years and does not require to extend to more than 6 years you would use the following query:
Select fldComission, fldTermID
FROM tblTerms INNER JOIN tblCommission ON tblTerms.fldTermID=tblComm ission .fldTermID
where fldTermStart<4 AND fldTermEnd<6
This will allow you to quickly find the best commision that meets this criteria.
The short answer is yes but depending on which field you are referring validation may not be practical noting that most of the fields are text or autonumber.
In the structure that I have provided tblTerm acts like a lookup table. I would expect that to be a relatively short list (1-2,1-3,1-5,2-3,2-4,2-5,3
tblTerm
fldTermID (Autonumber, primary key)
fldTermStart (Number)
fldTermEnd (Number)
So the table (minus fldTermID) will look like
1 | 2
1 | 3
1 | 4
1 | 5
1 | 6
1 | 7
1 | 8
1 | 9
2 | 3
2 | 4
2 | 5
2 | 6
2 | 7
2 | 8
2 | 9
3 | 4
3 | 5
3 | 6
3 | 7
3 | 8
3 | 9
4 | 5
4 | 6
4 | 7
4 | 8
4 | 9
Then you can use validation to ensure that termend is greater than termstart.
The row source for fldTermID in tblCommission will be:
Select fldTermID, [fldTermStart] & " - " & [fldTermEnd] & " years" As Term
The beauty of this approach is that it will allow you to compare commissions that fall within a certain period. For example if you want to find the terms that start at less than 4 years and does not require to extend to more than 6 years you would use the following query:
Select fldComission, fldTermID
FROM tblTerms INNER JOIN tblCommission ON tblTerms.fldTermID=tblComm
where fldTermStart<4 AND fldTermEnd<6
This will allow you to quickly find the best commision that meets this criteria.
ASKER
sb9,
I was thinking that I could maybe use Vlookup for this.
I know that Vlookup could be used a simple solution to this but in Excel.
I am not certain how a Vlookup could be used to lookup in a table ini Acces.
Please take a look at the Excel sheet.
There are 2 fields colored green with the Vlookup formula that references a named range which is sorted ascending. Sorted Ascending is what makes it work.
let me know what you think about that approach. thank you
Vlookup.xls
I was thinking that I could maybe use Vlookup for this.
I know that Vlookup could be used a simple solution to this but in Excel.
I am not certain how a Vlookup could be used to lookup in a table ini Acces.
Please take a look at the Excel sheet.
There are 2 fields colored green with the Vlookup formula that references a named range which is sorted ascending. Sorted Ascending is what makes it work.
let me know what you think about that approach. thank you
Vlookup.xls
pdvs,
The Access equivalent to vlookup is DLookup. Syntax:
Dlookup("LookupField", "Table", "Criteria")
The Access equivalent to vlookup is DLookup. Syntax:
Dlookup("LookupField", "Table", "Criteria")
ASKER
ahhh...ok thanks ...will work on this.
What is the *exact* output you are requiring?