Solved

Commissions Matrix

Posted on 2011-09-28
8
450 Views
Last Modified: 2013-11-29
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
0
Comment
Question by:pdvsa
  • 4
  • 3
8 Comments
 
LVL 16

Accepted Solution

by:
Sheils earned 500 total points
ID: 36719397
Yes you can add that to a table.My suggestion would be to have two tables:

tblTerms
 fldTermID (Autonumber, primary key)
fldTerm (text)

tblCommission
fldCommisionID (Autonumber, Primary key)
fldBankID (number, foreign key)
fldTermID (number, foreign key)
fldComission
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 36719476
I'm a little confused...

What is the *exact* output you are requiring?
0
 

Author Closing Comment

by:pdvsa
ID: 36814281
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
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:pdvsa
ID: 36814286
I was wondering if within the table, the validation rule could somehow be used as it does have >, < and some other interesting operators.  

0
 
LVL 16

Expert Comment

by:Sheils
ID: 36818074
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=tblCommission .fldTermID
where fldTermStart<4 AND fldTermEnd<6

This will allow you to quickly find the best commision that meets this criteria.


 
0
 

Author Comment

by:pdvsa
ID: 36818900
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
0
 
LVL 16

Expert Comment

by:Sheils
ID: 36893617
pdvs,

The Access equivalent to vlookup is DLookup. Syntax:

Dlookup("LookupField", "Table", "Criteria")
0
 

Author Comment

by:pdvsa
ID: 36894342
ahhh...ok thanks ...will work on this.  
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to insert parameter value in table 2 24
Use after update to change dates 2 18
Microaoft Access on a SBS 2011 Server 4 21
query sort by digit 5 12
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question