how to compare 2 nested parent-child hierarchies..

[nycdon]

I'm working on a system with extensive data validation and status checking.
It essentially matches up sellers with potential buyers, by checking through
all seller data to see if item meets their minimum purchase criteria.

Both seller and client purchase criteria are in parent-child hierarchy,
often with multiple levels of nesting, as indicated below.

My question is how to run a screening process, and compare these 2
heirarchical structures, to get an eventual list of potential cLients meeting
their purchase criteria.

The data to be validated and screened for status is in the parent table
Policy,
with numerous child tables linked by Policy ID.

Policy Table
PolicyID - PK
PolicyType (int)

PolicyLives Table
PolicyID - FK
LifeID - FK

Insured Table
LifeID - FK
State

Underwriter Table
LifeID - FK
LE (int)

Various items on Insured table and on Underwriter table, need to be screened
against CLient purchase criteria, using the following data structure:
(each client can have various purchase criteria rows, with multiple
underlying child rows to indicate what states applicable, which LE's
accepted, and which policy types acceptable, etc).


CLientInfo Table
CLientID - PK

ClientPurchaseCriteria Table
PurchCriteriaID - PK
ClientID - FK
MinFace(int)
MaxFace(int)
StateSpecific(y/n)

ClientStates Table
PurchCriteriaID - FK
State (text)

CLientLE Table
PurchCriteriaID - FK
AcceptableProvider (text)
Required (y/n)

ClientPolicyTypes
PurchCriteriaID - FK
Type (int)

Any assistance as to how to approach this type of screening would be greatly
appreciated!

 

[Stefan Hoffmann]

hi,

My question is how to run a screening process, and compare these 2
heirarchical structures, to get an eventual list of potential cLients meeting
their purchase criteria.
[...]

This is still very vague...

mfG
--> stefan <--

 

[Clifford Bass]

Hi,

Could you just create a query that uses all of those tables,
appropriately joined and with the appropriate conditions? If you get any
rows, then it is okay to proceed. If none, then stop.

Clifford Bass

 

[nycdon]

Essentially I need to check that elements in Policy data (such as face value
on Policy Table, Insured state on Insured table, LE on Underwriter table),
match up with a particular Client's purchase criteria

(such as.. the Policy table row's facevalue, is within the minFace and
maxFace values on a ClientPurchaseCriteria row, then for that same
PurchCriteriaID - the PolicyType from PolicyTable exists in ClientPolicyTypes
table and the LEvalues on each Underwriter table row for this LifeID (within
the PolicyID) fall within Min and MaxLE values on the CLientPurchaseCriteria
row;
... then the Policy Insured states are included in same PurchCriteriaID's
value on ClientStates table; and that the LE values for each insured
(LifeID) in Underwriter Table exist in the CLientLE table with same
PurchCriteriaID as above).

i know..it's complicated, or seems that way to me -

Data looks like following:

Policy Table
PolicyID FaceValue PolicyType
0107-544 30000 4

PolicyLives Table
PolicyID LifeID
0107-544 01
0107-544 02

Insured table
LifeID State DOB
01 NY 5/1/35
02 NJ 12/1/45
03 PA 6/2/37

Underwriter Table
LifeID LE Date LEValue
01 1 4/1/2007 55
01 2 4/15/2007 65
02 1 5/1/2007 22
02 3 5/12/2007 30

Screening data...

ClientInfo Table
ClientID
01
02
03

ClientPurchaseCriteria
ClientID PurchCriteriaID MinFace MaxFace MinLE MaxLE MinAge
01 01 10000 20000 45
125 62
01 02 20001 30000 35
130 65
01 03 30001 55000 45
120 65
02 04 5000 25000 50
125 65
02 05 25001 60000 50
125 62

ClientStates
PurchCriteriaID State
01 FL
01 AK
02 FL
02 NY
02 NJ
03 NY
03 NJ
04 AZ
05 NJ

CLientLE
PurchCriteriaID LE Required
01 01 N
01 02 N
02 01 N
02 03 Y
02 02 N
03 01 N
03 04 N

CLientPolicyTypes
PurchCriteriaID Type
01 01
01 02
01 04
02 03
02 04
03 02
04 01
04 02

hi,

My question is how to run a screening process, and compare these 2
heirarchical structures, to get an eventual list of potential cLients meeting
their purchase criteria.
[...]

This is still very vague...

mfG
--> stefan <--

 

[nycdon]

I've tried to no avail - I'm thinking I may need to do something like a
recursive process.

 

[Clifford Bass]

Hi,

So, using the data you posted for Stefan, what would the situation be?
That is, if client comes in and ask to _________________ the data will allow
him to go ahead. But if he asks to ______________ the data will not allow it.

Clifford Bass

 

[nycdon]

Hi,

It would be to show all clients that would be interested in this Policy,
using the following criteria:

- the Policy's facevalue is within the minFace and maxFace values on a
ClientPurchaseCriteria row
- then for that same PurchCriteriaID; the PolicyType exists in
ClientPolicyTypes table
- AND the LEvalues on each Underwriter table row for this LifeID (within the
PolicyID) fall within Min and MaxLE values on the CLientPurchaseCriteria row
- AND the Policy Insured states are included in same PurchCriteriaID's
value on ClientStates table
- and that the LE values for each insured
(LifeID) in Underwriter Table exist in the CLientLE table with same
PurchCriteriaID as above).

I think my problem or sticking point is I'm thinking SQL, but in comparing
the Policy Info against the Client Screening data, there is no key to link
both, so SQL kind of does not make sense in this, as far as I can see. I'm
thinking I may need to read in 2 recordsets; 1 being then loop/recurse
through the Client data to see where I get matches.

It just seems so complex I can't get my head around, and looking for some
direction/ideas.

thanks!

 

[Clifford Bass]

Hi,

Try this:

SELECT Policy.PolicyID, Policy.FaceValue, ClientPurchaseCriteria.MinFace,
ClientPurchaseCriteria.MaxFace, Policy.PolicyType,
ClientPurchaseCriteria.PurchaseCriteriaID, ClientPurchaseCriteria.ClientID,
Insured.DOB, DateAdd("yyyy",-[MinAge],Date()) AS DOBMax,
ClientPurchaseCriteria.MinAge, PolicyLives.LifeID, Insured.State,
Underwriter.LE, Underwriter.LEValue, ClientPurchaseCriteria.MinLE,
ClientPurchaseCriteria.MaxLE
FROM ((Policy INNER JOIN PolicyLives ON Policy.PolicyID =
PolicyLives.PolicyID) INNER JOIN Insured ON PolicyLives.LifeID =
Insured.LifeID) INNER JOIN Underwriter ON PolicyLives.LifeID =
Underwriter.LifeID, (((ClientInfo INNER JOIN ClientPurchaseCriteria ON
ClientInfo.ClientID = ClientPurchaseCriteria.ClientID) INNER JOIN
ClientStates ON ClientPurchaseCriteria.PurchaseCriteriaID =
ClientStates.PurchaseCriteriaID) INNER JOIN ClientLE ON
ClientPurchaseCriteria.PurchaseCriteriaID = ClientLE.PurchaseCriteriaID)
INNER JOIN ClientPolicyTypes ON ClientPurchaseCriteria.PurchaseCriteriaID =
ClientPolicyTypes.PurchaseCriteriaID
WHERE (((Policy.FaceValue) Between [MinFace] And [MaxFace]) AND
((Policy.PolicyType)=[Type]) AND
((Insured.DOB)<=DateAdd("yyyy",-[MinAge],Date())) AND
((Insured.State)=[ClientStates].[State]) AND
((Underwriter.LE)=[ClientLE].[LE]) AND ((Underwriter.LEValue) Between [MinLE]
And [MaxLE]));

Note that I changed the Underwriter Date field's name to LEDate. While
Access allows the use of reserved words such as Date and Name as object
names, it can lead to confusion, and as such it is better to avoid them. See
http://support.microsoft.com/default.aspx/kb/286335/ for a list.

The above SQL retrieves two rows for client 1, purchase criteria 2,
life ID 1, state NY and LE 1 and 2. Is that correct?

Hope that helps,

Clifford Bass