Nested If too long 1

[DanAuber]

I have a table with 13 fields:

Table 1
PD1_val
PD2_val
PD3_val....etc

I have another table holding a value e.g. 4 (Table 2)

I want to run a query that says, if the value in Table 2 is 4, then return the data in PD4_val from Table 1.

I can do it with a nested if - but it needs 13 if's nested which makes the expression builder too long.

Is there an easier way to do this at all ?

Thanks for any help

 

[Remou]

Perhaps:
Expr1: Choose([field1],[pd1],[pd2],[pd3])

 

[DanAuber]

Thanks Remou - that did the trick fine. I do have another problem now though (!)

I also want to do this query: -

Table 1 is still the same having

Pd1_val,Pd2_val,Pd3_val,Pd4_val upto pd13_val

Table 2 still holds one value in 1 field - e.g. 5

I want to also run a query saying that if the value in Table 2 is 5 then output Pd1_val+Pd2_val+Pd_3_val+Pd4_val+Pd5_val

or if the value was 3 then only sum the first 3 fields.

Once again - if I write this in expression builder it is too long...

Any help gratefully received

Dan

 

[Golom]

And what is it's 4 for example?

This makes it zero if the value is neither 3 nor 5.

Expr1: IIf(fld IN (3,5),Pd1_val + Pd2_val + Pd_3_val + 
       IIf(fld=5,Pd4_val + Pd5_val,0),0)

 

[DanAuber]

Hi Golom - what you have is fine, but there are 13 possibilities (13 financial periods)

Thus the statement gets too big for expression builder - 13 nested ifs - hence my problem...

 

[Remou]

These numbers are sequential, right?

PDPick: Choose([field1],[pd1],[pd2],[pd3])
Sum: ([PDPick]*([PDPick]+1))/2

 

[DanAuber]

Hi Remou - sorry - I don't quite understand your last post.

Here's an example with numbers

Table 1

Field Value
Pd1_Val 100
Pd2_Val 134
Pd3_Val 5
Pd4_Val 452
Pd5_Val 453
Pd6_Val 325
Pd7_Val 422
Pd8_Val 33
Pd9_Val 45
Pd10_Val 454
Pd11_Val 321
Pd12_Val 33
Pd13_Val 0

Now if the value in Table 2 is 4

I would want to return the first 4 of the above fields summed - which in this example is 100+134+5+452=691


Whatever the value in Table 2 is, then that's how many of the values in table 1 I need summed.

(This is a function for calculating period to date balance so for example the period to date balance for period 4 is 691, the period to date balance for period 2 is 100+134=234)

I hope this makes more sense

Dan

 

[jebry]

Hi!

Since the value in your pd fields appear to be, to a certain extent, random, you will probably need to create a public function which adds up the correct fields and returns the value and you can call that from the query passing it the field in the other table.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[Golom]

You are paying the price for a badly un-normalized table. This may still be too long

IIf(fld>=1 , Pd1_Val , 0) +
IIf(fld>=2 , Pd2_Val , 0) +
IIf(fld>=3 , Pd3_Val , 0) +
IIf(fld>=4 , Pd4_Val , 0) +
IIf(fld>=5 , Pd5_Val , 0) +
IIf(fld>=6 , Pd6_Val , 0) +
IIf(fld>=7 , Pd7_Val , 0) +
IIf(fld>=8 , Pd8_Val , 0) +
IIf(fld>=9 , Pd9_Val , 0) +
IIf(fld>=10, Pd10_Val, 0) +
IIf(fld>=11, Pd11_Val, 0) +
IIf(fld>=12, Pd12_Val, 0) +
IIf(fld>=13, Pd13_Val, 0)

 

[Remou]

In a continuation of Jebry's post:

SQL
[tt]SELECT Table1.Field1, Choose([field1],[pd1],[pd2],[pd3]) AS PDPick, SumPD(Choose([field1],1,2,3)) AS SumPD
FROM Table1, PD;[/tt]

Function

Function SumPD(NoOfFields)
'References: Microsoft DAO 3.6 Object Library
Dim intTmp
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("PD")
If Nz(NoOfFields, 0) = 0 Then
    SumPD = 0
Else
    For i = 1 To NoOfFields
        intTmp = intTmp + rs.Fields(i - 1)
    Next
    SumPD = intTmp
End If
End Function

 

[Golom]

Or the function route

Public Function SumPD(NumVals As Long, ParamArray Vals() As Variant) As Long
    Dim X                           As Variant
    Dim n                           As Integer
    For Each X In Vals
        If Not IsNull(X) Then SumPD = SumPD + X
        n = n + 1
        If n >= NumVals Then Exit For
    Next
End Function

And call it from SQL with

Select SumPD(fld, Pd1_Val, Pd2_Val, ..., Pd12_Val, Pd13_Val) As Expr1, ...

 

[DanAuber]

Thanks everyone for their help. Golon - the function works a dream - thanks