Conditional Select 1

[vladk]

Hello,

I am asking for the help with a select query. I have a table of "factors" by "state code" and by some "program code".

There are 12 factors (12 fields of factors: "Factor One",..., "Factor Twelve") plus 2 fields for state and program.

Some factor fields can be blank. The decimal values of factors are gradualy increasing with 0.05 increment starting with 0.6 for "Factor One" and ending with 1.15 for "Factor Twelve".

This is example of a record:

State Code AZ
Program Code O
Factor One 0.6
Factor Two
Factor Three
Factor Four 0.75
Factor Five
Factor Six 0.85
Factor Seven
Factor Eight
Factor Nine 1.0
Factor Ten
Factor Eleven
Factor Twelve 1.15

There are two (2) non-blank input parameters for the query (in addition to the "State Code" and the "Program Code").

The first input value is the "proposed tier move" for the query, which is the integer beween -11 and 11.
The second input value is the "initial factor", which is the decimal between 0.6 and 1.15.

The query should produce the "actual factor" and the "actual tier move" for the given "State Code" and the "Program Code"

For the above example record:
If the "proposed tier move" is -3 and the "initial factor" is 1.0 then it must produce 0.85 and -3.
If the "proposed tier move" is 3 and the "initial factor" is 1.0 then it must produce 1.15 and 3.

If the "proposed tier move" is too big (jumps beyond the limits of available factors, or fields)then the furthest non-blank factor with the correcponding actual tier move must be given as an output.

However, the pattern of the blank fields is unique for each record.

If input values hit the blank field then the query should produce the furthest before this blank field in the appropriate direction non-blank factor value and the corresponding "actual tier move".

For example, the "proposed tier move" of -7 and the "initial factor" of 1.0 will give 0.75 and -5,
and the "proposed tier move" of 2 and the "initial factor" of 1.0 will give 1.0 and 0.

I am struggling with formalization of the logic for the proposed query.

I will appretiate any help.

Thank you

vladk

 

[PaulBricker]

One quick question. If the "proposed tier move" is -4 and the "initial factor" is 1.0 which value from your example do you want returned? .85 or .75

Paul

 

[PaulBricker]

Another question. What if the initial factor is blank? Does it still act as the starting point?
Sending the calculations out to a function with the proposed tier move and the initial factor should produce the results you need if you can clarify the questions.




Paul

 

[vladk]

Hi Paul,

Thank you for your reply. The first answer is 0.85.

The second answer is in my initial post:

...There are two (2) non-blank input parameters for the query (in addition to the "State Code" and the "Program Code")...


Thank you again!

vladk

 

[Remou]

Here is my version.

SELECT [T1_Normal].[StateCode], [T1_Normal].[ProgramCode], [T1_Normal].[Factor], [T1_Normal].[FVal], DlookUp("Factor","T1_Normal","StateCode='AZ' AND ProgramCode='O' AND FVal='" & IV & "'")-Factor AS Fx, [PTM] AS PTM
FROM T1_Normal
WHERE T1_Normal.FVal In (SELECT Min(FVal) FROM T1_Normal WHERE StateCode="AZ" AND ProgramCode="O" AND Factor Between DlookUp("Factor","T1_Normal","StateCode='AZ' AND ProgramCode='O' AND FVal='" & IV & "'")+(PTM)  And DlookUp("Factor","T1_Normal","StateCode='AZ' AND ProgramCode='O' AND FVal='" & IV & "'"));

SELECT T1.StateCode, T1.ProgramCode, 1 As Factor,T1.FactorOne As FVal FROM T1
UNION SELECT T1.StateCode, T1.ProgramCode, 2 As Factor, T1.FactorTwo  As FVal FROM T1
UNION SELECT  T1.StateCode, T1.ProgramCode, 3 As Factor, T1.FactorThree As FVal FROM T1
UNION SELECT T1.StateCode, T1.ProgramCode, 4 As Factor, T1.FactorFour As FVal FROM T1
UNION SELECT T1.StateCode, T1.ProgramCode, 5 As Factor, T1.FactorFive As FVal FROM T1
UNION SELECT  T1.StateCode, T1.ProgramCode, 6 As Factor, T1.FactorSix As FVal FROM T1
UNION SELECT  T1.StateCode, T1.ProgramCode, 7 As Factor, T1.FactorSeven As FVal FROM T1
UNION SELECT T1.StateCode, T1.ProgramCode, 8 As Factor, T1.FactorEight As FVal FROM T1
UNION SELECT T1.StateCode, T1.ProgramCode, 9 As Factor, T1.FactorNine As FVal FROM T1
UNION SELECT T1.StateCode, T1.ProgramCode, 10 As Factor, T1.FactorTen As FVal FROM T1
UNION SELECT T1.StateCode, T1.ProgramCode, 11 As Factor, T1.FactorEleven As FVal FROM T1
UNION SELECT T1.StateCode, T1.ProgramCode, 12 As Factor, T1.FactorTwelve As FVal
FROM T1;

 

[Remou]

That should be:

[CODE --> Query #2]PARAMETERS IV Currency;
SELECT [T1_Normal].[StateCode], [T1_Normal].[ProgramCode], [T1_Normal].[Factor], [T1_Normal].[FVal], DlookUp("Factor","T1_Normal","StateCode='AZ' AND ProgramCode='O' AND FVal='" & IV & "'")-Factor AS Fx, [PTM] AS PTM
FROM T1_Normal
WHERE T1_Normal.FVal In (SELECT Min(FVal) FROM T1_Normal WHERE StateCode="AZ" AND ProgramCode="O" AND Factor Between DlookUp("Factor","T1_Normal","StateCode='AZ' AND ProgramCode='O' AND FVal='" & IV & "'")+(PTM) And DlookUp("Factor","T1_Normal","StateCode='AZ' AND ProgramCode='O' AND FVal='" & IV & "'"));[/code]

 

[vladk]

Remou!

Thank you for your reply! I will be able to test it by the end of this week, and I will let you know how it works.

Thank you!

vladk