lookup tables 1

[istone]

Hi everyone,
I was told to do lookup tables for the following but I do not know how to do them. I need help because I never done a lookup table before.
I have a table1 like this:

tbl1

PlanID PlanName Assets BP
CREST CRESTYYYYY 2,345,000
GLC GLCXXXXX 5,000,045
TYS TYSYYYYY 1,234,987
Ene EnergyXXX 4,567,876
JHC JHCXXXXXX 3,456,231
Guard GuardYYYY 34,000,000
CBAH CBAHYYYYY 19,000,043
SVM SVMXXXXX 14,345,000

What I would like to accomplish is to calculate the BP
(basis Point) for all the plans. There are about 150 plans in the table. The formula to get the BP for all the plans is Asset*5/10000 (which I know how to do)except for the follwoing plans the formula will different.

For Plan CREST it will be like this:
0 to 1,999,999 bp will be 30/10000
2,000,000 to 2,999,999 bp will be 20/10000
3,000,000 to 9,999,999 bp will be 16/10000
10,000,000 or more bp will be 10/10000
if Crest total asset = 2,345,000 then
2,000,000 * 30/10000
345,000 * 20/10000

For plan GLC
0 to 5,000,000 bp will be 40/10000
5,000,000 to 7,500,000 bp will be 30/10000
7,500,000 to 10,000,000 bp will be 20/10000
10,000,000 or more 16/10000
For plan SVM
0 to 5,000,000 bp will be 20/10000
5,000,000 to 7,500,000 bp will be 15/10000
7,500,000 to 10,000,000 bp will be 10/10000
10,000,000 or more 5/10000
For Plan JHC
0 to 30,000,000 bp will be 10/10000
30,000,000 to 40,000,000 bp will be 8/10000
40,000,000 to 50,000,000 bp will be 6/10000
50,000,000 or more 5/10000

Do I do an if formula or lookup tables which I need help with.
I hope my question is clear, please let me know if you have any questions.

I appreciate any help you would provide me and thanks in advance.

 

[dhookom]

I would expect the ranges and rates to change over time. Consider creating a lookup table with a structure and records like:
[tt]
tblPlanRanges
PlanID MinAsset MaxAsset BPRate
======== =========== ========= ======
CREST 0 2000000 0.003
CREST 2000000 3000000 0.002
...
GLC 0 5000000 0.004
GLC 5000000 7500000 0.003
...[/tt]
You could then write a function that accepts arguments of the PlanID and Assets value and returns the BP.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[istone]

that's what I was told to do, but I have no idea what function to write. If you can please help, I really appreciate it.
And thank you so much for your quick rely.

 

[dhookom]

You could create a public function in a module. Save the module with a name like "basBusinessCalcs". The function will be available any where as
BP: GetBP([PlanID],[Assets])
You may need to account for larger numbers that might not be addressed by your maximum assets.

Public Function GetBP(strPlanID As String, lngAssets As Long) As Double
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim lngRemainingAssets As Long
    Dim lngCountedAssets As Long
    Dim dblReturnValue As Double
    
    Set db = CurrentDb
    strSQL = "SELECT MinAsset, MaxAsset, MaxAsset-MinAsset As LngRange, BPRate " & _
            "FROM tblPlanRanges " & _
            "WHERE PlanID =""" & strPlanID & """ " & _
            "ORDER BY MinAsset"
    Set rs = db.OpenRecordset(strSQL)
    lngRemainingAssets = lngAssets
    With rs
        .MoveFirst
        Do While lngRemainingAssets > 0
            If lngRemainingAssets > .Fields("LngRange") Then
                dblReturnValue = dblReturnValue + .Fields("BPRate") * .Fields("LngRange")
                lngRemainingAssets = lngRemainingAssets - .Fields("LngRange")
             Else
                dblReturnValue = dblReturnValue + .Fields("BPRate") * lngRemainingAssets
                lngRemainingAssets = 0
            End If
            .MoveNext
        Loop
        .Close
    End With
    GetBP = dblReturnValue
    Set rs = Nothing
    Set db = Nothing
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[istone]

I'll work on it and I'll let you know the outcome.
Again thanks sooooo much for your help.

 

[dhookom]

Make sure if this works that you reply back to the public News Group where you asked the same question. It's nice to know when threads have been resolved.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[istone]

ok, I am stuck.
I created the following table and created the module called
basBusinessCalcs. I am not sure what to do from here.
what do you mean by: "The function will be available any where as BP: GetBP([PlanID],[Assets])"
Also how do I call the module?
Sorry but I am not a vba expert...

here is the table I created

PlanID MinAsset MaxAsset BPRate
CBAH 0.00 10,000,000.00 0.0025
CBAH 10,000,000.00 15,000,000.00 0.00125
CREST 0.00 2,000,000.00 0.0075
CREST 2,000,000.00 3,000,000.00 0.005
CREST 3,000,000.00 10,000,000.00 0.004
CREST 10,000,000.00 0.0025
ENERGY 3,000,000.00 0.00125
ENERGY 0.00 3,000,000.00 0.0025
GLC 0.00 5,000,000.00 0.01
GLC 5,000,000.00 7,500,000.00 0.0075
GLC 7,500,000.00 10,000,000.00 0.005
GLC 10,000,000.00 0.004
GLCB 0.00 5,000,000.00 0.01
GLCB 5,000,000.00 7,500,000.00 0.0075
GLCB 7,500,000.00 10,000,000.00 0.005
GLCB 10,000,000.00 0.004
GLNQ 0.00 5,000,000.00 0.01
GLNQ 5,000,000.00 7,500,000.00 0.0075
GLNQ 7,500,000.00 10,000,000.00 0.005
GLNQ 10,000,000.00 0.004
GUARD 0.00 15,000,000.00 0.005
GUARD 15,000,000.00 25,000,000.00 0.0025
GUARD 25,000,000.00 0.00125
JHC 0.00 30,000,000.00 0.0025
JHC 30,000,000.00 40,000,000.00 0.002
JHC 40,000,000.00 50,000,000.00 0.0015
JHC 50,000,000.00 0.00125
SSSH 0.00 5,000,000.00 0.00925
SSSH 5,000,000.00 7,500,000.00 0.00825
SSSH 7,500,000.00 10,000,000.00 0.00725
SSSH 10,000,000.00 0.00625
SVM 0.00 5,000,000.00 0.005
SVM 5,000,000.00 7,500,000.00 0.00375
SVM 7,500,000.00 10,000,000.00 0.0025
SVM 10,000,000.00 0.00125

 

[istone]

Yes, I will reply there and post the answer.
Thanks

 

[dhookom]

Did you paste the function into the module and then save it? I recommend while in the module design, select Debug->Compile... to check the code. If you get an error on the DAO.... code, you will need to set a Reference in the Microsoft DAO object library.

Once you get the code to compile, you can use the function where ever you need to calculate the BP. For instance, you could add a column to your query:
BP: GetBP([PlanID],[Assets])
This assumes your query tables contain the fields PlanID and Assets.

You could also add a text box to a report and set its control source to:
=GetBP([PlanID],[Assets])
Again, this assumes you have the fields in the report's record source query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[istone]

I am sorry dhookom I am still having trouble with it.
I paste the function into the module and then save it.
I also Debug it.
I set the reference
I added BP: GetBP([PlanID],[Assets])to my query. I get the follwing error:
Run-time error '13'
type mismatch

Also the follwoing is highlighted in the code:
set db = currentDb

What am I doing wrong?
Thanks

 

[dhookom]

What do you mean by "set the reference"? While in the design of the module, select Tools->References and make sure you have checked "Microsoft DAO ..."

The code also expects PlanID to be text and Assets to be numeric.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[istone]

Yes I checked : Microsoft DAO 2.5 Object Library
PlanID is Text and Assets is number

I am still getting the same error message.
Here is my query:

SELECT [tblGeneral Fees].PlanID, [tblGeneral Fees].Assets, [tblGeneral Fees].PLAN, GetBP([PlanID],[Assets]) AS BP
FROM [tblGeneral Fees];
thanks

 

[dhookom]

What version of Access are you using? The 2.5 DAO library is from a much old version. I would expect to see 3.X in more recent versions.

Do you have any records with null values of PlanID or Assets? If so, try
SELECT [tblGeneral Fees].PlanID, [tblGeneral Fees].Assets, [tblGeneral Fees].PLAN, GetBP([PlanID],[Assets]) AS BP
FROM [tblGeneral Fees]
WHERE PlanID Is Not Null And Assets Is Not Null;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[istone]

I did change do DAO 3.6 and I dont have any blank records. however, I am still getting a different error message:

run-time error'3021'
No current record

The query stops and I click ok to go to the next record.

 

[dhookom]

Apparently you might have higher Assets than your tblPlanRanges accounts for. You might want to change your code to

Public Function GetBP(strPlanID As String, lngAssets As Long) As Double
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim lngRemainingAssets As Long
    Dim lngCountedAssets As Long
    Dim dblReturnValue As Double
    
    Set db = CurrentDb
    strSQL = "SELECT MinAsset, MaxAsset, MaxAsset-MinAsset As LngRange, BPRate " & _
            "FROM tblPlanRanges " & _
            "WHERE PlanID =""" & strPlanID & """ " & _
            "ORDER BY MinAsset"
    Set rs = db.OpenRecordset(strSQL)
    lngRemainingAssets = lngAssets
    With rs
        .MoveFirst
        Do While lngRemainingAssets > 0 And Not .EOF
            If lngRemainingAssets > .Fields("LngRange") Then
                dblReturnValue = dblReturnValue + .Fields("BPRate") * .Fields("LngRange")
                lngRemainingAssets = lngRemainingAssets - .Fields("LngRange")
             Else
                dblReturnValue = dblReturnValue + .Fields("BPRate") * lngRemainingAssets
                lngRemainingAssets = 0
            End If
            .MoveNext

        Loop
        .Close
    End With
    GetBP = dblReturnValue
    Set rs = Nothing
    Set db = Nothing
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[istone]

Same error. The error message box stops at each record until I click end and then it moves to the next record showing the word error in Column BP.

Also in the code the word .MoveFirst is highlighted.
Thanks again

 

[istone]

I am sorry to drive you crazy. Your code worked beautifuly. The probem was in my query not your code. My query included all plan and I am only interted in doing the formula for some not all plans. Here is my final query now:

SELECT [tblGeneral Fees].PlanID, [tblGeneral Fees].Assets, [tblGeneral Fees].PLAN, GetBP([PlanID],[Assets]) AS BP
FROM [tblGeneral Fees]
WHERE ((([tblGeneral Fees].PlanID)="CBAH" Or ([tblGeneral Fees].PlanID)="CREST" Or ([tblGeneral Fees].PlanID)="ENERGY" Or ([tblGeneral Fees].PlanID)="GLC" Or ([tblGeneral Fees].PlanID)="GLCB" Or ([tblGeneral Fees].PlanID)="GLNQ" Or ([tblGeneral Fees].PlanID)="GUARD" Or ([tblGeneral Fees].PlanID)="JHC" Or ([tblGeneral Fees].PlanID)="SSSH" Or ([tblGeneral Fees].PlanID)="SVM") AND (([tblGeneral Fees].Assets) Is Not Null));

Now what I have to do is make this query an append query. I'll add it to the Main table.

I really appreciate your help, you've been worderful and patient.

 

[dhookom]

You should be able to simplify by using something like:

SELECT [tblGeneral Fees].PlanID, [tblGeneral Fees].Assets, [tblGeneral Fees].PLAN, GetBP([PlanID],[Assets]) AS BP
FROM [tblGeneral Fees]
WHERE [tblGeneral Fees].PlanID IN ("CBAH" , "CREST", "ENERGY" , "GLC", "GLCB" , "GLNQ" , "GUARD" , "JHC" , "SSSH" , "SVM") AND [tblGeneral Fees].Assets Is Not Null;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]