Create new field - If/Then, Lookup?

[hlock]

Access 2007 - I have to label each claim with a claimtype based on a
combination of fields and I'm not sure how to do this. In AS400 query, I
used conditional calculations, but would really prefer to find a solution
that is easier to update and manage. Hopefully, the table below gives an
idea of the combination of fields that I am working with. Each claim has all
of these fields. However, all claims with Aslob 052 should be labeled
"Casualty". All claims with Aslob 230 and FieldB=EPG are labeled "Surety,
but those with Aslob 230 and FieldA=EIC01 should be labeled "Property". Any
suggestions would be appreciated. Thanks.

Aslob Subline TOL FieldA FieldB FieldD ClaimType
052 Casualty

040 0050 Property

230 EPG Surety

050 917 Casualty

080 043 Property

FIDCR Casualty

030 0026 Casualty

230 EIC01 Property

 

[Daryl S]

Hlock -

I would recommend a combination of the table and a query to drive this.
Create a ClaimTypes table that looks exactly like the chart you have in your
posting, but put an asterisk in all fields that are blank. Then you can run
a query like this:

UPDATE ClaimTypes, Claims SET Claims.[Claim Type] = [ClaimTypes]![Claim Type]
WHERE (((Claims.ASLOB) Like [ClaimTypes]![ASLOB]) AND ((Claims.Subline) Like
[ClaimTypes]![Subline]) AND ((Claims.TOL) Like [ClaimTypes]![TOL]) AND
((Claims.FieldA) Like [ClaimTypes]![FieldA]) AND ((Claims.FieldB) Like
[ClaimTypes]![FieldB]) AND ((Claims.FieldD) Like [ClaimTypes]![FieldD]) AND
(Claims.[Claim Type] is Null));

You can update the table any time without having to update the query (unless
for some reason you need to add more fields for more criteria). This assumes
all text fields in the ClaimTypes table. You can also run this query for a
specific claim to be run on the AfterUpdate event of a record. This query
will only add the Claim Type to a record if it isn't there. That is, it
won't change any existing claim types. You can change that by removing the
last criteria in the WHERE clause.

Remember to back up your data before testing anything like this!

 

[hlock]

I think I've got it. What I'm struggling with is some claimtypes only
require going to the aslob level to determine the claim type. However, other
claimtypes require going to the aslob, fielda, and fieldb level. So as shown
below, how do I handle the empty fields?

 

[De Jager]

This is really a question of modelling. What ClaimType represents is a
relationship type between the entity types which the Aslob, FieldA and
FieldB
columns are referencing, and possibly others if other columns' values can
play a role in determining the ClaimType.

If we assume its just the three columns you've mentioned the relationship
type would be modelled by a table with columns Aslob, FieldA, FieldB and
ClaimType. The first three are referenced by the corresponding tree
columns
in the claims table. These tree column in fact constitute the composite
primary key of this new table, and should be designated as such. The
ClaimType column is a foreign key referencing the primary key of a
ClaimTypes
table.

This additional table should have rows for all possible combinations of
Aslob, FieldA and FieldB (and any others which come into play) which can
be
found in the claims table, with the appropriate value for each combination
in
the ClaimType column.

Its then simply a question of joining the claims table to the new table on
the three (or more) columns in a query and returning the ClaimType column
from the new table. Note that if the claim type will always be determined
by
these combinations of values you should not have a ClaimType column in the
claims table as it can be obtained via the query at any time. To
duplicate
its value in the claims table would introduce redundancy and the
consequent
risk of inconsistent data. The only circumstance in which you would
duplicate the column in the claims table would be if the value is not
'time-
independently' determined by the combination of values, i.e. if the values
which determine a category might change over time, but you would want
prior
claims records to retain the claim type value which applied at the time
the
claim record was created. This is analogous to product prices, which you
would not want to change in existing invoices should the price later be
changed in the products table. See the sample Northwind database for an
example of how this is handled.

Ken Sheridan
Stafford, England