Four Tier Indicators base on 2 Fields (1 Viewer)

Hello everyone,

I need help. I am trying to determine the four Tier Indicators base on 2 Fields, the Primary ID and The Relationship Code. Here is the logic.

Tier Indicators

1. Tier SGL: A Subscriber that has a “Relationship Indicator” of ‘01’, and does NOT share a “Primary ID” (only this subscriber is present – no ‘02’ or ‘03’)

2. Tier SAC: Subscriber who share a “Primary ID” and have the “Relationship Indicators” ‘01’ and ‘03’ (but NOT ‘02’)

3. Tier SAS: Subscriber who share a “Primary ID” and have the “Relationship Indicators” ‘01’ and ‘02’ (but NOT ‘03’)

4. Tier FAM: Subscriber who share a “Primary ID” and have the “Relationship Indicators” ‘01’, ‘02’, ‘03’ (ALL 3 indicators must be present)

I would like to create a query that will be able to determine the Tier codes but don’t know how? How do you determine if the Primary ID is shared or not shared? Do I use a iif function or something else? Any help will be appreciated.

Thank you,

Several things:
1. Your table has no primary key field - add one e.g. SubscriberID or make a joint PK field of both the existing fieds (but see point 3)
2. You have used a Number field for Rel_ID so it can't be 01/02/03
If you need those values, it MUST be a text field
3. Why do you have duplicate values for several records ?
e.g. 66666666 & 3 appears five times as does 7777777 & 3.
If you used a joint PK this wouldn't be possible.

This is probably easier to do with a procedure but before I or anyone else spends time on this, please:
a) sort out the points above & repost your sample db
b) also post what the results SHOULD look like for these records

You've presented 4 Cases, however, they are not all inclusive. What happens when data falls outside your 4 cases? For example, only has 3. Only has 2. Or has 2 and 3? What tier do those fall into?

Thank you for the quick reply! Below are the answers to your questions.

1. Your table has no primary key field - add one e.g. SubscriberID or make a joint PK field of both the existing fieds (but see point 3)
1A. I forgot to add a PK to my sample. I updated the sample to include it.

2. You have used a Number field for Rel_ID so it can't be 01/02/03
If you need those values, it MUST be a text field
2A. I used a Number Field cause it will not need to be 01/02/03 format.

3. Why do you have duplicate values for several records ?
e.g. 66666666 & 3 appears five times as does 7777777 & 3.
3A. Background info:
I added a PK called ID, ID field reflect the Subscribers ID and the Rel_ID field reflects the individuals relationships.

Example:
ID# 1111111111 is the Subscriber
ID# 122222222 is the Subscribers Partner
ID# 133333333 is the Subscriber and Partners Child.

So with this info you can see that REL_ID 1 = Subscriber, 2=Partner and 3=Child.

To associate the Child and Partner to the Subscriber there is a PRIM_ID field.

My actual DB has 2 tables one stores the Child and Partner data with a reference to the Subscriber ID and the other table has only the Subscriber data only. For the updated Sample I provided, I placed all in one table.

I updated the sample as requested and here is what the results should look like.

Expected Results
PRIM_ID TIER
111111111 FAM
222222222 SAS
333333333 SAC
444444444 SAC
555555555 SGL
666666666 SAC
777777777 FAM
888888888 SAS

To answer Plogs question about his concerns pertaining to what happens when data falls outside your 4 cases. My DB will not allow you to add a 2 or a 3 unless there is a 1 to associate it too.

Thank you for all your help.

My DB will not allow you to add a 2 or a 3 unless there is a 1 to associate it too.

Click to expand...

1. Never trust your assumptions. Not saying that statement is wrong, but I am skeptical. Run this query on your actual dataset:


If any results are returned by that query, you are incorrect in your assumption and your data does not conform to your assumptions.

2. If the above statement is correct, you can achieve what you want with just queries. Of course you will need a new table to hold all your Tiers. This is the exact table you will need, including data:

Tiers
TierName, Indicator_Count, Indicator_Max
SGL, 1, 1
SAC, 2, 3
SAS, 2, 2
FAM, 3, 3

Then, to put your data into tiers you would need 2 subqueries. This is the first one:



Paste that into a query and name it 'sub1'. It just makes all your PRIM_ID/REL_ID permutations unique. Next, paste the below subquery into a new query:


Name it 'sub2'. Then to get the results you want, create a new query, bring in sub2 and Tiers into it. JOIN them via their _Count and _Max fields. Bring down PRIM_ID and TierName and run it.

Plog,

I used the code you provided to check if my assumptions were correct and it was, no results were returned by the query. Afterwards I did everything else you said, and it worked perfectly with the sample and actual dataset.

Thank you,

Rinova