sql - 使用来自另一个表的值的总和更新一个表

Question

有人可以帮我吗？我想tblTotals使用from更新Sum。我有：CosttblAppointments

Private Sub btnUpdate_Click()
Dim rs1 As DAO. Recordset
Dim rs2 As DAO. Recordset

Set rs1 =  CurrentDB.OpenRecordset("SELECT Pets, Sum(cost) As TotalCost FROM tblAppointments WHERE (((DateDiff('m',[AppointmentDate],DateSerial(Year(Date()),1,1))) Between -6 And 5)) GROUP BY Pets")

Set rs2 = CurrentDB.OpenRecordset("SELECT Pets, TotalCost FROM tblTotals")

With rs1
If Not rs1.BOF then rs1.Movefirst 
Do Until r1.EOF
rs2. Edit
rs2.Fields("TotalCost").Value = rs1.Fields("TotalCost").Value
rs2.Update
Set rs1 = Nothing
Set rs2 = Nothing
End With 


End Sub

tblTotals

+=========+=========+=============+
+  Pets   +    Dr   +  TotalCost  +
+=========+=========+=============+
+  Cats   +   John  +  £20.00     +
+---------+---------+-------------+ 
+  Dogs   +   Sam   +  £80.00     +
+---------+---------+-------------+ 
+  Rabits +   Ellis +  £85.35     +
+---------+---------+-------------+
+ Parrots +   Ellis +  £63.00     +
+---------+---------+-------------+`

tblAppointments:

+=========+=========+=============+=================+
+  Pets   +    Dr   +   Cost      + AppointmentDate +
+=========+=========+=============+==================
+  Cats   +   John  +  20.50      + 12/02/2020      +
+---------+---------+-------------+-----------------+
+  Dogs   +   Sam   +  80.00      + 10/05/2020      +
+---------+---------+-------------+-----------------+ 
+  Dogs   +   Sam   +  80.00      + 12/02/2020      +
+---------+---------+-------------+-----------------+ 
+  Rabits +  Ellis  +  £85.35     + 12/12/2019      +
+---------+---------+-------------+-----------------+ 
+  Cats   +  John   +  20.50      + 12/10/2019      +
+---------+---------+-------------+-----------------+ 
+  Cats   +  John   +  20.50      + 12/09/2019      +
+---------+---------+-------------+-----------------+ 
+ Parrots +  Ellis  +  £63.25     + 12/08/2019      +
+---------+---------+-------------+-----------------+` 

预期成绩：

tblTotals

+=========+=========+=============+
+  Pets   +    Dr   +  TotalCost  +
+=========+=========+=============+
+  Cats   +   John  +  £61.50     +
+---------+---------+-------------+ 
+  Dogs   +   Sam   +  £160.00    +
+---------+---------+-------------+ 
+  Rabits +   Ellis +  £85.35     +
+---------+---------+-------------+
+ Parrots +   Ellis +  £63.00     +
+---------+---------+-------------+

谢谢。

标签： sqlvbams-access

Answer 1

有时您别无选择，只能尝试提出解决方案，尽管设计不佳。在这种情况下。我曾经DAO使用Update tblTotals聚合查询作为recordset. 只要所讨论的表的大小相当小，您就不会注意到任何重大的速度问题。

Private Sub btnUpdate_Click()

Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset

Set rs1 =  CurrentDB.OpenRecordset("SELECT Pets, Sum(Cost) As TotalCost FROM tblAppointments WHERE (((DateDiff('m',[AppointmentDate],DateSerial(Year(Date()),1,1))) Between -6 And 5)) GROUP BY Pets")

Set rs2 = CurrentDB.OpenRecordset("SELECT Pets, TotalCost FROM tblTotals")


rs1.MoveFirst
Do Until rs1.EOF
rs2.MoveFirst
Do Until rs2.EOF
If rs1![Pets] = rs2![Pets] Then
rs2.Edit
rs2.Fields("TotalCost").Value = rs1.Fields("TotalCost").Value
rs2.Update
End If
rs2.MoveNext
Loop
rs1.MoveNext
Loop

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing

End Sub