Lookup table?

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

There are several ways.

Result = DLookup("Asset", "tbl1", "[PlanId]=" &
Forms![FormName]![PlanYouAreLookingFor])

If you know VBA and some SQL you can do it that way also. If you have to
look through the entire record the above method can be time consumming from a
programmer's point of view and sloppy, but it works.

Dim db As dao.Database

Set db = CurrentDb

Set rstT = db.OpenRecordset("SELECT * FROM YourTable")

The * represents selecting all the fields in the table.

From here look up how to go through each field. There are two methods, the
syntax will take a little getting use to if you are not familiar with SQL.

Look At Selecting the records and going through each filed with the SELECT
option on selecting the record.

Good Luck

"ielmrani via AccessMonster.com" wrote:

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

Thanks len for your quick reply.
I am not sure how this is going to be done.

Do I make a lookup table? And create a form and put the following code in a
text box?
Result = DLookup("Asset", "tbl1", "[PlanId]=" & Forms![FormName]!
[PlanYouAreLookingFor])

I know how to do forms, tables, macros but coding (VBA) I am good at.
I'll appreciate if you walk me through all the steps.

Thanks again.
Ismail
Len wrote:
There are several ways.

Result = DLookup("Asset", "tbl1", "[PlanId]=" &
Forms![FormName]![PlanYouAreLookingFor])

If you know VBA and some SQL you can do it that way also. If you have to
look through the entire record the above method can be time consumming from a
programmer's point of view and sloppy, but it works.

Dim db As dao.Database

Set db = CurrentDb

Set rstT = db.OpenRecordset("SELECT * FROM YourTable")

The * represents selecting all the fields in the table.

From here look up how to go through each field. There are two methods, the
syntax will take a little getting use to if you are not familiar with SQL.

Look At Selecting the records and going through each filed with the SELECT
option on selecting the record.

Good Luck

Hi everyone,
I was told to do lookup tables for the following but I do not know how to do
[quoted text clipped - 47 lines]

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

--
Message posted via http://www.accessmonster.com

I posted a function in Tek-Tips where you asked the same question
http://www.tek-tips.com/viewthread.c...1334371&page=1.

--
Duane Hookom
Microsoft Access MVP


"ielmrani via AccessMonster.com" wrote:

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1

This worked thanks to Duane Hookom.
Here is the answer.

I created a table:

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
...
You could then write a function that accepts arguments of the PlanID and
Assets value and returns the BP.
Here is the funtion:

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


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.

Ismail

Duane Hookom wrote:
I posted a function in Tek-Tips where you asked the same question
http://www.tek-tips.com/viewthread.c...1334371&page=1.

Hi everyone,
I was told to do lookup tables for the following but I do not know how to do
[quoted text clipped - 47 lines]

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200702/1