MS access replace null or zero values with nearest non-zero row-CodePudding

I have a sample dataset like this

 Group  Age Value
    A   1   
    A   2   0
    A   3   20
    B   1   0
    B   2   0
    B   3   25
    B   4   0
    B   5   0
    B   6   50

For each distinct value in Group, replace null or zero Value. Ideal output will be

 Group  Age Value
    A   1   20
    A   2   20
    A   3   20
    B   1   25
    B   2   25
    B   3   25
    B   4   25
    B   5   50
    B   6   50

UPDATE

This seems to do the trick

UPDATE test
SET [Value] = nz(DMin(
    "[Value]", 
    "test", 
    "[Group] = '" & [Group] & "' AND 
     [Age] < " & [Age] & " AND 
     [Value] <> 0 AND [Value] IS NOT NULL"
),
 DMax(
    "[Value]", 
    "test", 
    "[Group] = '" & [Group] & "' AND 
     [Age] > " & [Age] & " AND 
     [Value] <> 0 AND [Value] IS NOT NULL"
))
WHERE [Value] = 0 OR [Value] IS NULL;

CodePudding user response：

You can use this (in MsAccess SQL a column can be derived from a subquery returning or one value - or no rows):


select 
     tactl.[Group], 
     tactl.Age, 
     (  select trepl.[Value]
        from test trepl
        where tactl.[Group]=trepl.[Group]
              and nz(trepl.[Value],0)<>0
              and abs(tactl.Age-trepl.Age)=
                      ( select MIN(abs(ta.Age-tb.Age)) as MinDistanceFound 
                        from    test ta
                                inner join
                                test tb
                                on (ta.[Group]=tb.[Group]  and nz(tb.[Value],0) <> 0)
                       where (ta.[Group]=tactl.[Group]  and ta.[Age]=tactl.[Age])
                    )
      )  as [Value]
from test tactl

GROUP and VALUE can be reserved words in most SQL implementations (they are in Access); try to use other words for them (I put them in brackets to overcome issues)

CodePudding user response：

Consider domain aggregation such as DMin in order to have an updateable query:

UPDATE test
SET [Value] = DMin(
    "[Value]", 
    "test", 
    "[Group] = '" & [Group] & "' AND 
     [Age] > " & [Age] & " AND 
     [Value] <> 0 AND [Value] IS NOT NULL"
)
WHERE [Value] = 0 OR [Value] IS NULL;