To Subquery or Not

[plog]

No, let's continue it here. Can you provide sample data? Give us data that are in your tables, what results you expect back and then what results you are actually getting.

 

[rinova]

No, let's continue it here. Can you provide sample data? Give us data that are in your tables, what results you expect back and then what results you are actually getting.

I updated my previous Post. Check it out I posted it Today 03:00 PM. I think I got everything you requested. If I missed something let me know.

 

[plog]

I seriously think you are making this harder than it is just to see how far you can string us along. First, the sample data you provided is so genericized its confusing--is Part_SSN1 the same as DEP_SSN01? Also, I have no idea what table that data is from--I assume its Dependents, but I am not certain. Third, The field names atop your sample data don't match any of the field names in the SQL you posted. Fourth, looking back to your initial post, now there are even more relationship and termination codes.

Fifth and worst of all, I think if we somehow get this 1 query to do what you want, that only gets you 1/4 of the way to where you want to be.

Let's start over. Post some sample data from AcctMbrs, some corresponding data from Dependents, the tier definitions (in words, not codes) and then tell us what Tier each sample row in AcctMbrs should fall into based on the sample data you provide.

 

[rinova]

Thanks for being so patient with me plog, I'll have that info for you shortly. I just need to replace the real SSN's with fake ones. I will also try provide the database table definitions for ActMbr and dependents and in words not code explain each sample row in ActMbrs should fall into based on the sample data I'm providing.

 

[rinova]

Tier definitions:
Participant - Member only
Participant Spouse - Member with Spouse
Participant Children - Member with Child(ren)
Participant Spouse and Child(ren) - Member with Spouse and Child(ren)

I attached a sample of the database to this post.

ActMbr.prt_ss_nbr with SSN# 111111111 should be in tier Participant Spouse and Child(ren)
ActMbr.prt_ss_nbr with SSN# 222222222 should be in tier Participant Spouse
ActMbr.prt_ss_nbr with SSN# 333333333 should be in tier Participant Children
ActMbr.prt_ss_nbr with SSN# 444444444 should be in tier Participant Children
ActMbr.prt_ss_nbr with SSN# 555555555 should be in tier Participant Spouse
ActMbr.prt_ss_nbr with SSN# 666666666 should be in tier Participant
ActMbr.prt_ss_nbr with SSN# 777777777 should be in tier N/A (Participant terminated)
ActMbr.prt_ss_nbr with SSN# 888888888 should be in tier Participant Spouse
ActMbr.prt_ss_nbr with SSN# 999999999 should be in tier Participant Children

I don't mind trying to solve each tier with it's own separate query, just in case your trying to write something that will do it all in one shot.

Thank you,

Rinova

 

[plog]

Do you have tables that relate to you relationship and termination codes? Something that explains what all those letters map to? If so, can you upload those as well?

 

[vbaInet]

Plog already asked for this but if you can upload an Excel file of sample data and what you expect to get from that sample data it will be easier so we have everything in one place.

 

[rinova]

I don't have tables that relate to the relationship and termination codes. I will check again to make sure. I did explain what the relationship codes meant in a previous post. I will do the same for termination codes. I will upload a sample of the data in an excel sheet tonight.

 

[plog]

That's fine, don't waste your time. I have already started on one at work, and just wanted to incorporate it into any existing table you had. I won't have access to that file until Wednesday but I should be able to post a solution then.

This doesn't relate to your project, but how come you don't have these tables--how do you keep track of what all those codes mean?

 

[rinova]

the database was not created by me, I usually ask the staff what the codes mean and then document it.

Thank you all for helping me out.

 

[plog]

Based on the data you provided, I think you have SSN # 777777777 wrong. I show that participant has an active child.

I've attached a modified database that does what you want. The query that provides you with the tier inormation you want is called ParticipantTiers.

Let me know if you have any questions about any of the tables or queries I created.

 

[rinova]

Your right the dependent was active and the spouse was terminated. Thanks for correction.

 

[rinova]

Based on the data you provided, I think you have SSN # 777777777 wrong. I show that participant has an active child.

I've attached a modified database that does what you want. The query that provides you with the tier inormation you want is called ParticipantTiers.

Let me know if you have any questions about any of the tables or queries I created.

This works perfect! Awesome! Thank you! Thank you! Thank you!