Complex Imbedded "If...Then" Statement.

[MySki]

I have developed a database that projects the probabiity of retirement for employees. I have several queires that bring the the percent probablity of requiremet for several critiera. For example:

If the emp. is under FERS and is between age 50-55 with 20-25 years of service, probability for retirment is 2.50%.
FERS between age 55-60 with 20-25 years, probability is 4.63%. I have 20 of these different criteria for FERS retirement employees and the same number for empoyes under the CSRS retirement system, for a total of 40 different criteira buckets.

Under my probability field in the query, I have developed an imbedded "If..Then" statement that contains each of the criteria. However, the Access query will only allow me to enter about 16 of the If statements and then it gives me an error telling me it must truncate the formula and that the query formula is too complex.

Does anyone have any suggestions on how I can make this formula work? Or do it a better way???

Thanks!

 

[PHV]

Write your own function you can call in the query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MySki]

Can you give me an example? Not sure How to do that.

Thanks

 

[Golom]

Have you considered putting your criteria into another table? I don't know exactly what form your criteria take but an IIF statement of the form

IIF ( A = "ABC", 1,
IIF ( A = "XYZ", 2, 
IIF ( A = "FGH", 3,
etc.

could appear as
[tt]
AValue Result
ABC 1
XYZ 2
FGH 3
[/tt]
and you could then just do a JOIN to that table and match to the criteria that applied in each case.

 

[MySki]

That's a good idea. I already have the criteria in a separate table that I could import into Access. I've never done a JOIN before. Can you point me in the right direction?

Thanks!

 

[Golom]

Only in the most general terms since I don't know what your fields or conditions look like

Select T.EmployeeID, C.Probability

From myTable As T INNER JOIN Conditons As C
     ON T.UnderFRS = C.UnderFRS

Where T.Age BETWEEN C.MinAge And C.MaxAge
  AND T.Experience BETWEEN C.MinExp And C.MaxExp

Just using the criteria that you mentioned in your first post. Your "Conditions" table would look something like
[tt]
Conditions
MinAge MaxAge MinExp MaxExp UnderFRS Probability
55 60 20 25 Yes 2.50
etc.
[/tt]

 

[MySki]

Below is the code I currently have. I have been trying to add the join to bring in my probability with no success. the probability table is called ProbPercent and the fields are MinAge, MaxAge, MinSvc, MaxSvc and Probability. If the AgeYr and SvcYr are Between the Min/Max Age and Min/Max Svc, then I want to bring in the respective probability. How would I do that..Thanks again,


SELECT [Data-2005-Name Split].DPMTL_MLG, ([SvcYr]+3) AS Yr3S, ([AgeYr]+1) AS Yr1A, ([AgeYr]+2) AS Yr2A, ([AgeYr]+3) AS Yr3A, ([SvcYr]+1) AS Yr1S, ([SvcYr]+2) AS Yr2S, [Data-2005-Name Split].HQFLD, [Data-2005-Name Split].ADMINOFC, [Data-2005-Name Split].SSN, [Data-2005-Name Split].Name, [Data-2005-Name Split].[Pay Plan], [Data-2005-Name Split].Occup_Series, [Data-2005-Name Split].Grade, [Data-2005-Name Split].PositionTitle, [Data-2005-Name Split].[Supervisory Code], [Data-2005-Name Split].Stat, [Data-2005-Name Split].Ret_Cov_Cd, [Data-2005-Name Split].Dty_City_Nam, [Data-2005-Name Split].[Dty-State_Nam], [Data-2005-Name Split].Basis, [Data-2005-Name Split].AgeYr, [Data-2005-Name Split].Birth_Dt_Yr, [Data-2005-Name Split].SvcYr, [Data-2005-Name Split].[Critical Position], [Data-2005-Name Split].[Retirement Plan], ]
FROM [Data-2005-Name Split]
WHERE ((([Data-2005-Name Split].DPMTL_MLG)=[Forms]![frmSelectProgramOffice]![DPMTL_MLG]) AND (([Data-2005-Name Split].Basis) Is Not Null) AND (([Data-2005-Name Split].[Critical Position])="no"));

 

[Golom]

SELECT S.DPMTL_MLG, ([SvcYr]+3) AS Yr3S, ([AgeYr]+1) AS Yr1A, ([AgeYr]+2) AS Yr2A, ([AgeYr]+3) AS Yr3A, ([SvcYr]+1) AS Yr1S, ([SvcYr]+2) AS Yr2S, S.HQFLD, S.ADMINOFC, S.SSN, S.Name, S.[Pay Plan], S.Occup_Series, S.Grade, S.PositionTitle, S.[Supervisory Code], S.Stat, S.Ret_Cov_Cd, S.Dty_City_Nam, S.[Dty-State_Nam], S.Basis, S.AgeYr, S.Birth_Dt_Yr, S.SvcYr, S.[Critical Position], S.[Retirement Plan], 
[COLOR=red]P.Probability[/color]

FROM [Data-2005-Name Split] As S[COLOR=red], ProbPercent As P[/color]

WHERE [COLOR=red]S.AgeYr BETWEEN P.MinAge AND P.MaxAge
  AND S.SvcYr BETWEEN P.MinSvc AND P.MaxSvc[/color]
  AND S.DPMTL_MLG=[Forms]![frmSelectProgramOffice]![DPMTL_MLG] 
  AND S.Basis Is Not Null 
  AND S.[Critical Position] = 'no'

 

[MySki]

This is what I have now and when I run, I get a blank screen. Only thing I changed was adding S.RetirementPlan=P.RetPlan (because their is a distinction between the FERS and CSRS Retirement systems. Any thoughts why I'm getting a blank screen?

SELECT S.DPMTL_MLG, ([SvcYr]+3) AS Yr3S, ([AgeYr]+1) AS Yr1A, ([AgeYr]+2) AS Yr2A, ([AgeYr]+3) AS Yr3A, ([SvcYr]+1) AS Yr1S, ([SvcYr]+2) AS Yr2S, S.HQFLD, S.ADMINOFC, S.SSN, S.Name, S.[Pay Plan], S.Occup_Series, S.Grade, S.PositionTitle, S.[Supervisory Code], S.Stat, S.Ret_Cov_Cd, S.Dty_City_Nam, S.[Dty-State_Nam], S.Basis, S.AgeYr, S.Birth_Dt_Yr, S.SvcYr, S.[Critical Position], P.Probability, S.[Retirement Plan],

FROM [Data-2005-Name Split] AS S, tblProb AS P
WHERE ((([S.DPMTL_MLG)=[Forms]![frmSelectProgramOffice]![DPMTL_MLG])
AND ((S.Basis) Is Not Null)
AND ((S.AgeYr) Between P,[MinAge] And P.[MaxAge])
AND ((S.SvcYr) Between P.[MinSvc] And P.[MaxSvc])
and ((s.[Retirement Plan]=P.RetPlan
AND ((S.[Critical Position])="no"));

 

[PHV]

As you only have AND operators in your WHERE clause, you may safely get rid of all those parenthesis:
WHERE S.DPMTL_MLG=[Forms]![frmSelectProgramOffice]![DPMTL_MLG]
AND S.Basis Is Not Null
AND S.AgeYr Between P.MinAge And P.MaxAge
AND S.SvcYr Between P.MinSvc And P.MaxSvc
AND S.[Retirement Plan]=P.RetPlan
AND S.[Critical Position]="no";


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MySki]

Got it!! You are awesome. Thanks so much for your help..

Lori

 

[MySki]

I'm back,

Been trying to figure this out for the last week, but no luck. When I run my query above, I only return around 2500 records. there are 4617 employees eligible for retirement, I should be returning 4617 results. I thought maybe I had duplicate criteria, howeer, when I run an unmatched query I can find no reason why the records would not come in. Any ideas?

I can send a truncated piece of my database if that would help...I'm stumped.
Thanks

 

[CaptainD]

Since your query has all "and's" then each criteria has to be met. If you have NULL values in the field where you have

"AND S.Basis Is Not Null

then those records will not show up. Same for the rest of the criteria. If you remove the where cluase, then you will get all records. The where cluase filters data.

 

[MySki]

Agreed, however, my problem is that I have 4617 recors that meet the AND S. Basis Is Not Null criteria and they are still not showing up.. I double checked and they do fall into the max/min critieria so they should show up but just are not for some reason I cant figure out.

 

[lespaul]

I would start by removing all the criteria from the query and adding them back one at a time to see where the issue lies.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[MySki]

I figured out what my error was in my above question. If my max or min variable was null, I just left it blank in my table, which access did not recognize. Once I put in a value (0 or 100), I was able to bring back all records. Here is my next quandry.

I am trying to predict probability three years out. Thus, I have created Yr1A, Yr2A, Yr3A and Yr1S, Yr2S, Yr3S to calculate age and years of service in the out years. I also need to bring in my probabilities for these years as well as the current year.

Following the above advice, I created three new probability tables for my new years tblProb1, tblProb2 adn tblProb3. I then put in my where criteria for the age and years of service. I tried using both Or and And critieria with no success. I just can't seem to get the probabilities to come in for my out years without getting numberours duplicate entries.

Here is the code I have so far:

SELECT [Data-2005-Name Split].DPMTL_MLG, ([SvcYr]+3) AS Yr3S, ([AgeYr]+1) AS Yr1A, ([AgeYr]+2) AS Yr2A, ([AgeYr]+3) AS Yr3A, ([SvcYr]+1) AS Yr1S, ([SvcYr]+2) AS Yr2S, [Data-2005-Name Split].Name, [Data-2005-Name Split].AgeYr, [Data-2005-Name Split].SvcYr, [Data-2005-Name Split].[Retirement Plan], tblProb.Probability,

FROM [Data-2005-Name Split], tblProb,
WHERE ((([Data-2005-Name Split].DPMTL_MLG)<>"OIG") AND (([Data-2005-Name Split].AgeYr)>=[tblProb].[MinAge] And ([Data-2005-Name Split].AgeYr)<[tblProb].[MaxAge]) AND (([Data-2005-Name Split].SvcYr)>=[tblProb].[MinSvc] And ([Data-2005-Name Split].SvcYr)<[tblProb].[MaxSvc]) AND (([Data-2005-Name Split].[Retirement Plan])=[tblProb].[RetPlan]));

Any advice on how to bring in the probabilities from my other tables without getting duplicates?

Thanks