Vba SQL：根据记录计数更新记录 [英] Vba SQL: update record based on the count of record

问题描述

我有两个不同的表，我想根据几个条件更新其中一个。我的桌子是：



表1

I have two different tables and I want to update one of them base on several conditions. My tables are:

Table1

---------------------------
ID   |     N1   |    N2
---------------------------
1          22        12
1           5        0
1          87        12
2          67        0
2           6        0
2           3        0
2          60        12
3          55        0
3          64        12
4           8        0
4          75        12
4           4        0
5          58        12
5          69        12
5          36        12
5           3        0

表2

Table2

--------------------------
ID   |     MX   |   RN
--------------------------
1          33        2
2          45        3
3          99        4
4          67        2
5          87        4

我想只计算一个N2 = 0的人在table1中使用公式





N2 = MX-N1-RN



所以例如当ID = 1时只有一个0所以我们将所有不是0的N1加起来







N2 = 33-（87 + 22）-2 = -78



和ID = 3和= 5




N2 = 99-（64）-4 = 31



N2 = 87-（58 + 69 + 36）-4 = -80



然后将使用新记录更新该ID的N2。 ID = 2将被忽略，因为有三个记录= 0，因此对于ID = 4，有两个记录= 0.



更新的表将是Table1

I want to calculate only those with one N2 = 0 in the table1 using the formula


N2= MX-N1-RN

So for example when ID=1 there is only one 0 so we will sum all the N1 that are not 0



N2=33-(87+22)-2 = -78

and the same in ID=3 and =5


N2=99-(64)-4 = 31

N2=87-(58+69+36)-4 = -80

Then N2 of that ID will be updated with the new record. ID=2 will be ignored because there are three records = 0 and so for ID=4 there are two records =0.

The updated table will be Table1

---------------------------
ID   |     N1  |     N2
---------------------------
1          22        12
1           5       -78
1          87        12
2          67         0
2           6         0
2           3         0
2          60        12
3          55        31
3          64        12
4           8         0
4          75        12
4           4         0
5          58        12
5          69        12
5          36        12
5           3      -80

我尝试了什么：



所以我想要使用sql Query来做它但我不知道如何正确完成它。



我所做的代码如下：



Sql查询：

What I have tried:

So I wanted to do it using sql Query but I didn't know how to complete it correctly.

The code I've done is the following:

Sql Query:

UPDATE TABLE1 AS I INNER JOIN TABLE2 AS P 
ON I.ID = P.ID
 SET I.N2  = IIF(I.N2 = -0, 
(P.MX - SUM(DMax("N2 ", "TABLE1 ", 
"ID=" & I.ID)) - P.RN), I.N2 )
WHERE I.[ID] IN 
             (SELECT B.[ID] 
              FROM TABLE1 B
              GROUP BY B.[ID] 
              HAVING (Count(B.[ID]) > 1)
                AND (Min(B.N2 ) <> Max(B.N2 ))
                AND (Min(B.N2 ) = 0 OR Max(B.N2 ) = 0))
AND I.N2 IN (0);

VBA代码：

VBA code:

Private Sub GET_CAL()
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Query1"
    DoCmd.SetWarnings True
End Sub

推荐答案

with temp as (
select a.ID, (select MX - sum(N1) - RN from #table2 where id=a.ID) as N2NewValue
from #table1 as a
where a.N2<>0  and a.ID in(select id from #table1 where N2=0 group by N2,ID having count(ID)=1)
group by a.ID)
update #table1 set N2=b.N2NewValue
from #table1 as a, temp as b
where a.ID=b.ID and a.N2=0

检查此查询

check this query

这篇关于Vba SQL：根据记录计数更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！