sum values ​​with condition

[g.adamo@email.it]

hallo ,
I have a table with three qualitative variables in the first three columns and the numerical values ​​on the last column. id est for example:

checkpoint	system	tilt	values
1	a	1	0,3
1	a	2	0,2
1	b	0	0,2
1	b	1	0,1
1	b	2	0,4
1	b	3	0,5
2	a	1	0,1
2	a	2	1,2
2	b	0	2
2	b	1	4
2	b	2	0,2
2	b	3	0,7

i have to sum values of "systems" both for each "checkpoint" both for each "tilt" .but when the values for the system is null (like "chechpoint =1"," system = a", "tilt= 0" or like "chechpoint =1"," system= a"," tilt= 3") i have to sum first or last not null depend if the tilt i'm considering is minus of the first valid or greater of the last valid .
please help

[andy49]

Ok so just to get my head around this

If the system is 1,b,3 you're expecting the sum of all the values for x,b,3

Whereas if the system is 1,a,0 you're expecting (this is bit I don't follow I think)???


Sent from my iPhone using Tapatalk

[g.adamo@email.it]

i have to do a sum that i can explain so:
checkpoint tilt sum system values
1 0 (value "system a tilt 1" becouse i don't have "system a tilt 0" + value "system b tilt 0")
1 1 (value "system a tilt 1" + "value system b tilt 1")
..............................
2 3 ( value "system a tilt 2" becouse i don't have "system a tilt 3"+ value "system b tilt 3")

[andy49]

sumvalueswithcondition - Copy.zip

I hope this is the sort of thing you meant

Open the form

the small button shows the results

[g.adamo@email.it]

thank you very much
but now i have a question:
is your solution able to resolve the question if both the range of system and the range of tilt are different (more than 2 system a,b; tilt 0,1,...,10)?
if yes is it sufficient rewrite vba in some parts?

[andy49]

That would depend:

The example you gave suggested that the only null values would be at either tilt 0 or tilt 3

If this is the case (the null value cannot be at say tilt 2?), then the program can be extended to larger tilt values easily by just changing

If rs.Fields("tilt") = 3 Then

to say

If rs.Fields("tilt") = 10 Then

HOWEVER, if the tilt values were from 0 to 10 and the value 1,2,3,4,5,6,7,8,9 could be a null value then I'm gonna need more info. (which value would the sumsystem take?)

[g.adamo@email.it]

the value on "tilt" could be null from 0 to 10 (for example system = a colud have value null for tilt 0,1,2,9,10)and the "systems" could be more than 2.
for each chepoint when the value is null on "tilt" for the "system" number N i have to consider the first value not null for the same system from the beginning or the last not null (on the example i posted for "tilt"= 0 and "system"="a" I have to use first not null that is on tilt 1 after i have to consider tilt 2 when i'll arrive to tilt 3 becouse the last not null is on tilt 2).
for example on the table that follws there are 3 systems but only system a has values null on tilt 0,9,10

chepoint	System	Tilt	value
1	a	1	1,012
1	a	2	1,146
1	a	3	1,199
1	a	4	1,07
1	a	5	0,785
1	a	6	0,429
1	a	7	0,352
1	a	8	0,944
1	b	0	0,004
1	b	1	0,018
1	b	2	0,015
1	b	3	0,01
1	b	4	0,005
1	b	5	0,007
1	b	6	0,018
1	b	7	0,034
1	b	8	0,051
1	b	9	0,066
1	b	10	0,08
1	c	0	0,066
1	c	1	0,052
1	c	2	0,042
1	c	3	0,028
1	c	4	0,012
1	c	5	0,013
1	c	6	0,033
1	c	7	0,056
1	c	8	0,079
1	c	9	0,101
1	c	10	0,122
2	a	1	0,424
2	a	2	0,566
2	a	3	0,79
2	a	4	0,968
2	a	5	1,107
2	a	6	1,109
2	a	7	0,945
2	a	8	0,643
2	b	0	0,001
2	b	1	0,016
2	b	2	0,019
2	b	3	0,02
2	b	4	0,02
2	b	5	0,017
2	b	6	0,012
2	b	7	0,009
2	b	8	0,014
2	b	9	0,023
2	b	10	0,032
2	c	0	0,052
2	c	1	0,113
2	c	2	0,131
2	c	3	0,135
2	c	4	0,128
2	c	5	0,105
2	c	6	0,074
2	c	7	0,053
2	c	8	0,079
2	c	9	0,139
2	c	10	0,209
3	a	1	0,524
3	a	2	0,511
3	a	3	0,461
3	a	4	0,387
3	a	5	0,29
3	a	6	0,207
3	a	7	0,104
3	a	8	0,053
3	b	0	0,013
3	b	1	0,031
3	b	2	0,029
3	b	3	0,026
3	b	4	0,022
3	b	5	0,018
3	b	6	0,014
3	b	7	0,01
3	b	8	0,006
3	b	9	0,005
3	b	10	0,008
3	c	0	0,015
3	c	1	0,006
3	c	2	0,005
3	c	3	0,004
3	c	4	0,003
3	c	5	0,002
3	c	6	0,002
3	c	7	0,001
3	c	8	0,001
3	c	9	0
3	c	10	0,001
4	a	1	0,018
4	a	2	0,017
4	a	3	0,012
4	a	4	0,013
4	a	5	0,026
4	a	6	0,039
4	a	7	0,052
4	a	8	0,058
4	b	0	0,041
4	b	1	0,015
4	b	2	0,01
4	b	3	0,014
4	b	4	0,019
4	b	5	0,023
4	b	6	0,025
4	b	7	0,023
4	b	8	0,02
4	b	9	0,016
4	b	10	0,012
4	c	0	0,469
4	c	1	0,202
4	c	2	0,141
4	c	3	0,194
4	c	4	0,278
4	c	5	0,351
4	c	6	0,381
4	c	7	0,376
4	c	8	0,331
4	c	9	0,27
4	c	10	0,209
5	a	1	0,025
5	a	2	0,032
5	a	3	0,039
5	a	4	0,042
5	a	5	0,043
5	a	6	0,036
5	a	7	0,022
5	a	8	0,008
5	b	0	0,191
5	b	1	0,29
5	b	2	0,296
5	b	3	0,272
5	b	4	0,225
5	b	5	0,152
5	b	6	0,09
5	b	7	0,146
5	b	8	0,279
5	b	9	0,433
5	b	10	0,597
5	c	0	0,231
5	c	1	0,346
5	c	2	0,352
5	c	3	0,329
5	c	4	0,27
5	c	5	0,184
5	c	6	0,11
5	c	7	0,177
5	c	8	0,341
5	c	9	0,53
5	c	10	0,735
6	a	1	0,226
6	a	2	0,214
6	a	3	0,189
6	a	4	0,153
6	a	5	0,112
6	a	6	0,077
6	a	7	0,038
6	a	8	0,019
6	b	0	0,013
6	b	1	0,002
6	b	2	0,002
6	b	3	0,001
6	b	4	0,001
6	b	5	0
6	b	6	0
6	b	7	0
6	b	8	0
6	b	9	0
6	b	10	0
6	c	0	0,4
6	c	1	0,399
6	c	2	0,383
6	c	3	0,36
6	c	4	0,322
6	c	5	0,269
6	c	6	0,216
6	c	7	0,151
6	c	8	0,095
6	c	9	0,067
6	c	10	0,1
7	a	1	0,013
7	a	2	0,015
7	a	3	0,016
7	a	4	0,016
7	a	5	0,014
7	a	6	0,012
7	a	7	0,009
7	a	8	0,008
7	b	0	0,168
7	b	1	0,145
7	b	2	0,15
7	b	3	0,143
7	b	4	0,148
7	b	5	0,139
7	b	6	0,128
7	b	7	0,112
7	b	8	0,089
7	b	9	0,077
7	b	10	0,069
7	c	0	0,001
7	c	1	0,004
7	c	2	0,004
7	c	3	0,004
7	c	4	0,005
7	c	5	0,005
7	c	6	0,005
7	c	7	0,004
7	c	8	0,004
7	c	9	0,003
7	c	10	0,003
8	a	1	0,008
8	a	2	0,012
8	a	3	0,021
8	a	4	0,032
8	a	5	0,051
8	a	6	0,065
8	a	7	0,079
8	a	8	0,078
8	b	0	0,536
8	b	1	0,9
8	b	2	1,172
8	b	3	1,416
8	b	4	1,63
8	b	5	1,823
8	b	6	1,978
8	b	7	2,079
8	b	8	2,116
8	b	9	2,088
8	b	10	2,001
8	c	0	0,016
8	c	1	0,056
8	c	2	0,071
8	c	3	0,083
8	c	4	0,094
8	c	5	0,102
8	c	6	0,109
8	c	7	0,113
8	c	8	0,116
8	c	9	0,118
8	c	10	0,117
9	a	1	0,019
9	a	2	0,014
9	a	3	0,006
9	a	4	0,009
9	a	5	0,022
9	a	6	0,03
9	a	7	0,04
9	a	8	0,052
9	b	0	0,25
9	b	1	0,073
9	b	2	0,165
9	b	3	0,343
9	b	4	0,526
9	b	5	0,721
9	b	6	0,905
9	b	7	1,084
9	b	8	1,231
9	b	9	1,344
9	b	10	1,431
9	c	0	0,003
9	c	1	0,003
9	c	2	0,007
9	c	3	0,014
9	c	4	0,023
9	c	5	0,034
9	c	6	0,042
9	c	7	0,053
9	c	8	0,061
9	c	9	0,065
9	c	10	0,066
10	a	1	0,009
10	a	2	0,013
10	a	3	0,02
10	a	4	0,028
10	a	5	0,04
10	a	6	0,047
10	a	7	0,05
10	a	8	0,045
10	b	0	0,544
10	b	1	0,788
10	b	2	0,951
10	b	3	1,089
10	b	4	1,201
10	b	5	1,299
10	b	6	1,359
10	b	7	1,383
10	b	8	1,364
10	b	9	1,307
10	b	10	1,211
10	c	0	0,007
10	c	1	0,031
10	c	2	0,036
10	c	3	0,039
10	c	4	0,042
10	c	5	0,045
10	c	6	0,047
10	c	7	0,048
10	c	8	0,048
10	c	9	0,049
10	c	10	0,047

sorry for my request but it's very important for me

[andy49]

Does either take priority? In your example 6b5 is null so moving up would be 6b6 (also null) then 6b7(also null).

Would i have to take the value for 6b4 in this case.

(Or do I use 6b1 as the first not null value in the system)




Sent from my iPhone using Tapatalk

[g.adamo@email.it]

in this case 6b4

[andy49]

Pm


Sent from my iPhone using Tapatalk

[andy49]

sumvalueswithcondition - Copy1.zip

I think this does the job on a larger scale.

let me know

[g.adamo@email.it]

I think it's ok but if i try changing input (another tbltilt dates) on the second step or button a message of error appears:

errore di run time '3144':
errore di sintassi nell'espressione UPDATE


so if I press the debug button it appears:

sqlstring = "UPDATE tbltilt_full SET tbltilt_full.[values] =" & updateval & " WHERE (([tbltilt_full].[code]=" & Chr(34) & rs.Fields("code") & Chr(34) & "));"

CurrentDb.Execute (sqlstring) is highlited in yellow

rs.MoveNext
i = i + 1
Loop

could you adjust?
till now your work seems the best way
thank you in advance

[andy49]

Can you give me more details of the changes you tried to make?

[andy49]

sumvalueswithcondition - Copy11.zip


Might be a little easier to manage

The tbltilt table can be deleted and a new one uploaded with new values

[ssanfu]

Be aware that "System", "Value" and "Values" are reserved words and shouldn't be used as object names!