Trying to clean up a database

[sarahbanker]

ok, so I've inherited a nightmare of an Access 03 database, and trying to clean up the admin and maintainence side of it. It's being used as a tool to provide revised credit card limits based on gross income and UMI (uncommitted monthly income). I've tried various ways, but keep coming unstuck, so any help would be appreciated.

Scenerio:

Table Client
Fields Client No
UMI
Gross Income
ProposedCreditL imitUMI

Basically, this last field is calculated value, based on a matrix taking the two values of gross income and UMI, and assigned a value.

The current query uses a formula that is unbearably long, and not friendly to changes, such as ranges and credit limits to be assigned. These do change, both in values, and number of ranges etc.

A cut down version of it looks like this - we may in the future add another UMI band, or condense them etc. This doesn't translate well in this post, so I hope it's clear enough.
Gross Income
UMI 25000-40000 40001-55000 55001+
0-50 5000 5000 6000
51-100 5000 6500 7500
101-150 6000 7000 8000
151-200 6500 8000 9000
200+ 7000 9000 10000

So, I'm after a query that takes the client UMI and Gross Income, finds where in this matrix it falls, and updates the Client_Table, Field ProposedCreditL imitUMI with the applicable value. Anything has to be better than the current built formula!

[FishVal]

Hi, there.

It smells like you could benefit here greatly using database like approach to the problem.

The matrix could be represented by 3 tables with the following relationship.
For example

tblMatrixUMI
keyMatrixUMIID Autonumber(Long ), PK
lngUMILowerLimi t Number(Long)
lngUMIUpperLimi t Number(Long)

tblMatrixGrossI ncome
keyMatrixGIID Autonumber(Long ), PK
lngGILowerLimit Number(Long)
lngGIUpperLimit Number(Long)

tblMatrixValues
keyMatrixValueI D Autonumber(Long ), PK
keyMatrixGIID Number(Long), FK(tblMatrixGro ssIncome
keyMatrixUMIID Number(Long), FK(tblMatrixUMI )
lngValue Number(Long)

Limit and Value types may be double, but as soon as in example they are integers I've proposed fields of Long type.

Next - you make table join like the following

qryMatrix

SELECT tblMatrixUMI.lngUMILowerLimit, tblMatrixUMI.lngUMIUpperLimit, tblMatrixGossIncome.lngGILowerLimit, tblMatrixGossIncome.lngGIUpperLimit, tblMatrixValues.lngValue FROM (tblMatrixValues INNER JOIN tblMatrixUMI ON tblMatrixValues.keyMatrixGIID = tblMatrixUMI.keyMatrixGIID) INNER JOIN tblMatrixGrossIncome ON tblMatrixValues.keyMatrixGIID = tblMatrixGrossIncome.keyMatrixGIID;
 

Thus you receive dataset which records contains matrix values with correspondent limit values.

Next - you join [Client] table to [qryMatrix].

SELECT * FROM [Client] INNER JOIN qryMatrix ON [Client].[Gross Income]>=qryMatrix.lngGILowerLimit AND [Client].[Gross Income]<=Nz(qryMatrix.lngGIUpperLimit,2000000000) AND [Client].[UMI]>=qryMatrix.lngUMILowerLimit AND [Client].[UMI]<=Nz(qryMatrix.lngUMIUpperLimit,2000000000);
 

Note that if upper limit is not specified (assumed to be infinitely high and correpondent field is empty), then Nz() function is used to replace it with practically unreacheble 2000000000 - almost the limit of Long type values.

Voila - no formulas, matrix values are in tables so you may build a form to view/change them interactively, SQL is used instead of VBA which makes the solution fly like a rocket.

Hope this makes sense.

Regards,
Fish