Sub NormalizeDB()
'сливание таблиц-дней в таблицу-период
Dim tmp, tDate, tRate, strSQL
Dim rstReps As DAO.Recordset
Dim rstExRates As DAO.Recordset
Dim rstDATA As DAO.Recordset
Set rstReps = CurrentDb.OpenRecordset("SELECT * FROM Reports_archive WHERE [Дата отчетов] > #20/02/2004#", dbOpenDynaset)
Set rstExRates = CurrentDb.OpenRecordset("tblExchRates", dbOpenDynaset)
'rstExRates.MoveLast
rstReps.MoveFirst
'перебор дат сохраненных отчетов
Do While Not rstReps.EOF
tmp = Format(rstReps.Fields(0), "dd/mm/yy")
Debug.Print tmp
'получим дату\курс
Set rstDATA = CurrentDb.OpenRecordset(tmp, dbOpenDynaset)
tDate = CDate(rstDATA!Expr1)
tRate = rstDATA!Expr2
rstDATA.Close
'сохр курс
With rstExRates
.AddNew
!fDate = tDate
!fUSDRate = tRate
.Update
End With
'выгрузить данные в общую табл
strSQL = "INSERT INTO tblAccData ( Дата, [Счет], Сальдо ) " & _
"SELECT CDate([Expr1]) AS Expr3, Счет, Сальдо " & _
"FROM [" & tmp & "]"
CurrentDb.Execute strSQL
rstReps.MoveNext
Loop
rstReps.Close
Set rstReps = Nothing
rstExRates.Close
Set rstExRates = Nothing
Set rstDATA = Nothing
End Sub |