ms-access - 带有 7 个集合或 1 个数组的 MS Access 更新表？
问题描述
所以尽量保持这部分非常简短：我有两个表。Tbl1 在单独的行中有每个公司按年份划分的财务帐户。表 2 每家公司只有一次，所有财务数据现在都在一行中。我怎么做？目前尝试使用 Collections 进行第二次尝试使用 Arrays。
大家好，我有两张桌子 Figs1 和 Sabi。Figs1 的设置如下：NIF、PeriodEnding、材料、折旧、非交易收入、总利息、税前利润、TotalEmpRemu。
因此，您将获得重复的公司 ID，其中每个财务数据作为行。
在 Sabi 中，它更改为每家公司有 1 行，所有数据都在列中，例如 PeriodEnding_Latest、PeriodEnding -1、PeriodEnding -2 等，直到 -6。我为 Figs1 中的每一列做了一个集合，我想以正确的顺序更新表 Sabi。
所以 PeriodEnding 集合将有 {(31/12/2018), (31/12/2017), (31/12/2016), (31/12/2015), (31/12/2014), (31/12 /2013)}
这些值需要转到 PeriodEnding_Latest、PeriodEnding -1、PeriodEnding -2 等。
我有更新 SQL 语句并用变量填充它：
SQL = "UPDATE SabiFigures1 SET SabiFigures1.[Closing Date Last avail yr] = '& DateFiled1 &', SabiFigures1.[Closing Date Year - 1] = '& DateFiled2 &', " & _
"SabiFigures1.[Closing Date Year - 2] = '& DateFiled3 &', SabiFigures1.[Closing Date Year - 3] = '& DateFiled4 &', SabiFigures1.[Closing Date Year - 4] = '& DateFiled5 &, " & _
"SabiFigures1.[Closing Date Year - 5] = '& DateFiled6 &', SabiFigures1.[Material costs th EUR Last avail yr] = '2933', SabiFigures1.[Material costs th EUR Year - 1] " & _
"= '2791', SabiFigures1.[Material costs th EUR Year - 2] = '3721', SabiFigures1.[Material costs th EUR Year - 3] = '3021', SabiFigures1.[Material costs th EUR Year - 4] " & _
"= '3005', SabiFigures1.[Material costs th EUR Year - 5] = '1890', SabiFigures1.[Depreciation th EUR Last avail yr] = '49', SabiFigures1.[Depreciation th EUR Year - 1] = " & _
"'52', SabiFigures1.[Depreciation th EUR Year - 2] = '47', SabiFigures1.[Depreciation th EUR Year - 3] = '42', SabiFigures1.[Depreciation th EUR Year - 4] = '54', " & _
"SabiFigures1.[Depreciation th EUR Year - 5] = '63', SabiFigures1.[Financial revenue th EUR Last avail yr] = Null, SabiFigures1.[Financial revenue th EUR Year - 1] " & _
"= Null, SabiFigures1.[Financial revenue th EUR Year - 2] = Null, SabiFigures1.[Financial revenue th EUR Year - 3] = Null, SabiFigures1.[Financial revenue th EUR Year " & _
"- 4] = Null, SabiFigures1.[Financial revenue th EUR Year - 5] = Null, SabiFigures1.[Financial expenses th EUR Last avail yr] = Null, SabiFigures1.[Financial expenses " & _
"th EUR Year - 1] = Null, SabiFigures1.[Financial expenses th EUR Year - 2] = Null, SabiFigures1.[Financial expenses th EUR Year - 3] = Null, " & _
"SabiFigures1.[Financial expenses th EUR Year - 4] = Null, SabiFigures1.[Financial expenses th EUR Year - 5] = Null, SabiFigures1." & _
"[P/L before tax th EUR Last avail yr] = '407', SabiFigures1.[P/L before tax th EUR Year - 1] = '252', SabiFigures1.[P/L before tax th EUR Year - 2] " & _
"= '1076', SabiFigures1.[P/L before tax th EUR Year - 3] = '597', SabiFigures1.[P/L before tax th EUR Year - 4] = '329', SabiFigures1.[P/L before tax th EUR Year - 5] = " & _
"'102', SabiFigures1.[Cost of employees th EUR Last avail yr] = '1226', SabiFigures1.[Cost of employees th EUR Year - 1] = '1205', SabiFigures1.[Cost of employees th EUR Year - 2] " & _
"= '1310', SabiFigures1.[Cost of employees th EUR Year - 3] = '1157', SabiFigures1.[Cost of employees th EUR Year - 4] = '1319', SabiFigures1.[Cost of employees th EUR Year - 5] = '1342' " & _
"WHERE (((SabiFigures1.[NIF Code])='A01011550'));"
db.Execute SQL
代码几乎进入第一个表：Figs1 并获得第一个 regnumber，然后它将进入我已预先填充 unqiue NIF 的 Sabi。如果它在 Figs1 和 Sabi 中找到对应的 NIF，那么它应该填写集合中的变量，但是，我不知道如何执行变量“变量”。例如，变量的基本结构应该是 DateFiled，但是当它遍历 PeriodEndings 的集合时，它应该从 DateFiled1 变为 Datefiled6。
Set rsFigs1 = CurrentDb.OpenRecordset("Select * FROM Figs1Ready ORDER BY NIF, PeriodEnding DESC;")
If Not (rsFigs1.EOF And rsFigs1.BOF) Then
rsFigs1.MoveFirst
Do Until rsFigs1.EOF = True
NIF = rsFigs1!NIF
Set rsFormat = CurrentDb.OpenRecordset("Select * FROM SabiFigures1;")
If Not (rsFormat.EOF And rsFormat.BOF) Then
rsFormat.MoveFirst
Do Until rsFormat.EOF = True
nIFF = rsFormat![NIF Code]
If NIF = nIFF Then
Set qdfDef = CurrentDb.QueryDefs("PopulateSabiFigures1")
qdfDef.Parameters("NIF: ").Value = nIFF
Set rstDef = qdfDef.OpenRecordset()
Set PeriodEnding = RSToColl(rstDef, "PeriodEnding")
Set Materials1 = RSToColl(rstDef, "Materials")
Set Depreciation1 = RSToColl(rstDef, "Depreciation")
Set NonTrading1 = RSToColl(rstDef, "Non-Trading Income")
Set TotalInterest = RSToColl(rstDef, "Total_Interest_Charges")
Set Pretax = RSToColl(rstDef, "Pretax_Profit")
Set TotalRemu = RSToColl(rstDef, "Total_Empl_Remu_000")
For i = 1 To 6
VariableName = "DateFiled" & i
Next i
For Each Period In PeriodEnding
Debug.Print TypeName(Period)
Next
End If
SQL = 'The massive SQL statement above
db.Execute SQL
rsFormat.MoveNext
Loop
End If
rsFigs1.MoveNext
Loop
End If
rstDef.Close
Set rstDef = Nothing
rsFormat.Close
Set rsFormat = Nothing
rsFigs1.Close
Set rsFigs1 = Nothing
也许我正在以错误的方式看待问题，任何帮助和指示将不胜感激。提前致谢。加了几张图希望对你有帮助
对于最先提出 Array 的 Gustav，我可以这样做：这将逐年（行）单独返回。我想一次更新一行以节省时间，而不是更新每年的值。几乎每行更新 6 次，我想这需要更长的时间。
Set rsFormat = CurrentDb.OpenRecordset("Select * FROM SabiFigures1;")
If Not (rsFormat.EOF And rsFormat.BOF) Then
rsFormat.MoveFirst
Do Until rsFormat.EOF = True
nIFF = rsFormat![NIF Code]
Set qdfDef = CurrentDb.QueryDefs("PopulateSabiFigures1")
qdfDef.Parameters("NIF: ").Value = nIFF
Set rstDef = qdfDef.OpenRecordset()
rstDef.MoveLast
rstDef.MoveFirst
varRecord = rstDef.GetRows(rstDef.RecordCount)
For intI = 0 To 5 'UBound(varRecord, 2)
For intJ = 0 To UBound(varRecord, 1)
Debug.Print varRecord(intJ, intI)
Next intJ
Next intI
'whole row updated here after each value of the array is passed to variables for each year and financial value.
rstDef.Close
Set rstDef = Nothing
rsFormat.MoveNext
Loop
End If
解决方案
你好，所以我想通了。该代码非常庞大，因此任何可以使其更加精简和灵活的人（例如，我需要更改的列）将不胜感激。
Dim db As DAO.Database
Set db = CurrentDb
Dim rsFormat As DAO.Recordset
Dim rsFigs1 As Object
Dim qdfDef As DAO.QueryDef
Dim rstDef As Object
Dim varRecord As Variant
Dim NIF As String
Dim nIFF As String
Dim intI As Integer
Dim intJ As Integer
Dim RegNum, LatestDate, Date1, Date2, Date3, Date4, Date5, LatestMaterial, Material1, Material2, Material3, Material4, Material5, LatestDepreciation, Depreciation1, Depreciation2 As String
Dim Depreciation3, Depreciation4, Depreciation5, LatestTrading, Trading1, Trading2, Trading3, Trading4, Trading5, LatestTotalInterest, TotalInterest1, TotalInterest2, TotalInterest3 As String
Dim TotalInterest4, TotalInterest5, LatestPreTaxProfit, PreTaxProfit1, PreTaxProfit2, PreTaxProfit3, PreTaxProfit4, PreTaxProfit5, LatestTotEmpRem, TotEmpRem1, TotEmpRem2, TotEmpRem3 As String
Dim TotEmpRem4, TotEmpRem5, SQL As String
Set rsFormat = CurrentDb.OpenRecordset("Select [NIF Code] FROM SabiFigures1;")
If Not (rsFormat.EOF And rsFormat.BOF) Then
rsFormat.MoveFirst
Do Until rsFormat.EOF = True
nIFF = rsFormat![NIF Code]
Set qdfDef = CurrentDb.QueryDefs("PopulateSabiFigures1")
qdfDef.Parameters("NIF: ").Value = nIFF
Set rstDef = qdfDef.OpenRecordset()
rstDef.MoveLast
rstDef.MoveFirst
varRecord = rstDef.GetRows(rstDef.RecordCount)
For intI = 0 To 5
For intJ = 0 To UBound(varRecord, 1)
Debug.Print varRecord(intJ, intI)
On Error Resume Next
If intI = 0 Then
If intJ = 0 Then
RegNum = varRecord(intJ, intI)
ElseIf intJ = 1 Then
LatestDate = varRecord(intJ, intI)
ElseIf intJ = 2 Then
LatestMaterial = varRecord(intJ, intI)
ElseIf intJ = 3 Then
LatestDepreciation = varRecord(intJ, intI)
ElseIf intJ = 4 Then
LatestTrading = varRecord(intJ, intI)
ElseIf intJ = 5 Then
LatestTotalInterest = varRecord(intJ, intI)
ElseIf intJ = 6 Then
LatestPreTaxProfit = varRecord(intJ, intI)
ElseIf intJ = 7 Then
LatestTotEmpRem = varRecord(intJ, intI)
Else
MsgBox "Error in Loop"
Exit Sub
End If
ElseIf intI = 1 Then
If intJ = 0 Then
RegNum = varRecord(intJ, intI)
ElseIf intJ = 1 Then
Date1 = varRecord(intJ, intI)
ElseIf intJ = 2 Then
Material1 = varRecord(intJ, intI)
ElseIf intJ = 3 Then
Depreciation1 = varRecord(intJ, intI)
ElseIf intJ = 4 Then
Trading1 = varRecord(intJ, intI)
ElseIf intJ = 5 Then
TotalInterest1 = varRecord(intJ, intI)
ElseIf intJ = 6 Then
PreTaxProfit1 = varRecord(intJ, intI)
ElseIf intJ = 7 Then
TotEmpRem1 = varRecord(intJ, intI)
Else
MsgBox "Error in Loop"
Exit Sub
End If
ElseIf intI = 2 Then
If intJ = 0 Then
RegNum = varRecord(intJ, intI)
ElseIf intJ = 1 Then
Date2 = varRecord(intJ, intI)
ElseIf intJ = 2 Then
Material2 = varRecord(intJ, intI)
ElseIf intJ = 3 Then
Depreciation2 = varRecord(intJ, intI)
ElseIf intJ = 4 Then
Trading2 = varRecord(intJ, intI)
ElseIf intJ = 5 Then
TotalInterest2 = varRecord(intJ, intI)
ElseIf intJ = 6 Then
PreTaxProfit2 = varRecord(intJ, intI)
ElseIf intJ = 7 Then
TotEmpRem2 = varRecord(intJ, intI)
Else
MsgBox "Error in Loop"
Exit Sub
End If
ElseIf intI = 3 Then
If intJ = 0 Then
RegNum = varRecord(intJ, intI)
ElseIf intJ = 1 Then
Date3 = varRecord(intJ, intI)
ElseIf intJ = 2 Then
Material3 = varRecord(intJ, intI)
ElseIf intJ = 3 Then
Depreciation3 = varRecord(intJ, intI)
ElseIf intJ = 4 Then
Trading3 = varRecord(intJ, intI)
ElseIf intJ = 5 Then
TotalInterest3 = varRecord(intJ, intI)
ElseIf intJ = 6 Then
PreTaxProfit3 = varRecord(intJ, intI)
ElseIf intJ = 7 Then
TotEmpRem3 = varRecord(intJ, intI)
Else
MsgBox "Error in Loop"
Exit Sub
End If
ElseIf intI = 4 Then
If intJ = 0 Then
RegNum = varRecord(intJ, intI)
ElseIf intJ = 1 Then
Date4 = varRecord(intJ, intI)
ElseIf intJ = 2 Then
Material4 = varRecord(intJ, intI)
ElseIf intJ = 3 Then
Depreciation4 = varRecord(intJ, intI)
ElseIf intJ = 4 Then
Trading4 = varRecord(intJ, intI)
ElseIf intJ = 5 Then
TotalInterest4 = varRecord(intJ, intI)
ElseIf intJ = 6 Then
PreTaxProfit4 = varRecord(intJ, intI)
ElseIf intJ = 7 Then
TotEmpRem4 = varRecord(intJ, intI)
Else
MsgBox "Error in Loop"
Exit Sub
End If
ElseIf intI = 5 Then
If intJ = 0 Then
RegNum = varRecord(intJ, intI)
ElseIf intJ = 1 Then
Date5 = varRecord(intJ, intI)
ElseIf intJ = 2 Then
Material5 = varRecord(intJ, intI)
ElseIf intJ = 3 Then
Depreciation5 = varRecord(intJ, intI)
ElseIf intJ = 4 Then
Trading5 = varRecord(intJ, intI)
ElseIf intJ = 5 Then
TotalInterest5 = varRecord(intJ, intI)
ElseIf intJ = 6 Then
PreTaxProfit5 = varRecord(intJ, intI)
ElseIf intJ = 7 Then
TotEmpRem5 = varRecord(intJ, intI)
Else
MsgBox "Error in Loop"
Exit Sub
End If
Else
MsgBox "Error in Loop"
Exit Sub
End If
Next intJ
Next intI
SQL = "UPDATE SabiFigures1 SET SabiFigures1.[Closing Date Last avail yr] = '" & LatestDate & "', SabiFigures1.[Closing Date Year - 1] = '" & Date1 & "', " & _
"SabiFigures1.[Closing Date Year - 2] = '" & Date2 & "', SabiFigures1.[Closing Date Year - 3] = '" & Date3 & "', SabiFigures1.[Closing Date Year - 4] = '" & Date4 & "', " & _
"SabiFigures1.[Closing Date Year - 5] = '" & Date5 & "', SabiFigures1.[Material costs th EUR Last avail yr] = '" & LatestMaterial & "', SabiFigures1.[Material costs th EUR Year - 1] " & _
"= '" & Material1 & "', SabiFigures1.[Material costs th EUR Year - 2] = '" & Material2 & "', SabiFigures1.[Material costs th EUR Year - 3] = '" & Material3 & "', SabiFigures1.[Material costs th EUR Year - 4] " & _
"= '" & Material4 & "', SabiFigures1.[Material costs th EUR Year - 5] = '" & Material5 & "', SabiFigures1.[Depreciation th EUR Last avail yr] = '" & LatestDepreciation & "', SabiFigures1.[Depreciation th EUR Year - 1] = " & _
"'" & Depreciation1 & "', SabiFigures1.[Depreciation th EUR Year - 2] = '" & Depreciation2 & "', SabiFigures1.[Depreciation th EUR Year - 3] = '" & Depreciation3 & "', SabiFigures1.[Depreciation th EUR Year - 4] = '" & Depreciation4 & "', " & _
"SabiFigures1.[Depreciation th EUR Year - 5] = '" & Depreciation5 & "', SabiFigures1.[Financial revenue th EUR Last avail yr] = '" & LatestTrading & "', SabiFigures1.[Financial revenue th EUR Year - 1] " & _
"= '" & Trading1 & "', SabiFigures1.[Financial revenue th EUR Year - 2] = '" & Trading2 & "', SabiFigures1.[Financial revenue th EUR Year - 3] = '" & Trading3 & "', SabiFigures1.[Financial revenue th EUR Year " & _
"- 4] = '" & Trading4 & "', SabiFigures1.[Financial revenue th EUR Year - 5] = '" & Trading5 & "', SabiFigures1.[Financial expenses th EUR Last avail yr] = '" & LatestTotalInterest & "', SabiFigures1.[Financial expenses " & _
"th EUR Year - 1] = '" & TotalInterest1 & "', SabiFigures1.[Financial expenses th EUR Year - 2] = '" & TotalInterest2 & "', SabiFigures1.[Financial expenses th EUR Year - 3] = '" & TotalInterest3 & "', " & _
"SabiFigures1.[Financial expenses th EUR Year - 4] = '" & TotalInterest4 & "', SabiFigures1.[Financial expenses th EUR Year - 5] = '" & TotalInterest5 & "', SabiFigures1." & _
"[P/L before tax th EUR Last avail yr] = '" & LatestPreTaxProfit & "', SabiFigures1.[P/L before tax th EUR Year - 1] = '" & PreTaxProfit1 & "', SabiFigures1.[P/L before tax th EUR Year - 2] " & _
"= '" & PreTaxProfit2 & "', SabiFigures1.[P/L before tax th EUR Year - 3] = '" & PreTaxProfit3 & "', SabiFigures1.[P/L before tax th EUR Year - 4] = '" & PreTaxProfit4 & "', SabiFigures1.[P/L before tax th EUR Year - 5] = " & _
"'" & PreTaxProfit5 & "', SabiFigures1.[Cost of employees th EUR Last avail yr] = '" & LatestTotEmpRem & "', SabiFigures1.[Cost of employees th EUR Year - 1] = '" & TotEmpRem1 & "', SabiFigures1.[Cost of employees th EUR Year - 2] " & _
"= '" & TotEmpRem2 & "', SabiFigures1.[Cost of employees th EUR Year - 3] = '" & TotEmpRem3 & "', SabiFigures1.[Cost of employees th EUR Year - 4] = '" & TotEmpRem4 & "', SabiFigures1.[Cost of employees th EUR Year - 5] = '" & TotEmpRem5 & "' " & _
"WHERE (((SabiFigures1.[NIF Code])='" & RegNum & "'));"
db.Execute SQL
SQL = ""
RegNum = ""
LatestDate = ""
Date1 = ""
Date2 = ""
Date3 = ""
Date4 = ""
Date5 = ""
LatestMaterial = ""
Material1 = ""
Material2 = ""
Material3 = ""
Material4 = ""
Material5 = ""
LatestDepreciation = ""
Depreciation1 = ""
Depreciation2 = ""
Depreciation3 = ""
Depreciation4 = ""
Depreciation5 = ""
LatestTrading = ""
Trading1 = ""
Trading2 = ""
Trading3 = ""
Trading4 = ""
Trading5 = ""
LatestTotalInterest = ""
TotalInterest1 = ""
TotalInterest2 = ""
TotalInterest3 = ""
TotalInterest4 = ""
TotalInterest5 = ""
LatestPreTaxProfit = ""
PreTaxProfit1 = ""
PreTaxProfit2 = ""
PreTaxProfit3 = ""
PreTaxProfit4 = ""
PreTaxProfit5 = ""
LatestTotEmpRem = ""
TotEmpRem1 = ""
TotEmpRem2 = ""
TotEmpRem3 = ""
TotEmpRem4 = ""
TotEmpRem5 = ""
'For intI = 6 To UBound(varRecord, 2)
' For intJ = 0 To UBound(varRecord, 1)
' Debug.Print varRecord(intJ, intI)
' Next intJ
'Next intI
rstDef.Close
Set rstDef = Nothing
rsFormat.MoveNext
Loop
End If
推荐阅读
- python-3.x - 在 Mac OS X High Sierra 上使用 Python 3.7 构建 OpenCV 4.0.0
- javascript - D3忽略绘图缺失值
- javascript - 如何根据上一个下拉列表中的选择创建新的下拉列表
- react-native - 在 Haste 模块映射中不存在 React Native MQTT 模块`url`
- python - 使用 Python 将文件永久更改为只读
- bootstrap-4 - 如何修复body里面有侧边栏。身体偏移
- html - 在导航栏中将 div 搜索栏居中
- android - Flutter我正在使用小部件错误：初始化程序中只能访问静态成员
- css - 我如何覆盖 ngx carousel css 以禁用导航
- amazon-web-services - aws cli：我的本地窗口上的安全令牌无效