有人可以帮我吗?我想更新tblTotals
使用Sum
的Cost
从tblAppointments
。我有:
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 +
+---------+---------+-------------+
谢谢。
有时,尽管设计不佳,但您别无选择,只能尝试提出解决方案。在这种情况下。我曾经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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句