Is there a more elegant way of doing this ??

T

tmuhammad

Hi Folks
I am using the following code with a few loops to shap up the data the way I
want which is pretty explainable looking
at the code, however it does not look very elegant as for each case I am
repeating the same queries so I was
wondering if someone can show of a more elegant way

Thanks much



Dim vPeriodSubTot As Integer
Dim vChargeSubTot As Double
Dim vNCrPeriod1 As Integer
Dim vNCrCharge1 As Double
Dim vDelPeriod As Integer
Dim vDelCharge As Double
Dim vNNCrPeriod As Integer
Dim vNNCrCharge As Double
Dim vNCrCredPeriod As Integer
Dim vNCrCredCharge As Double
Dim RsID As DAO.Recordset
Dim RsPeriod As DAO.Recordset
Dim RsCntPrNCr As DAO.Recordset
Dim RsCntPrDel As DAO.Recordset
Dim RsCntPrNNCr As DAO.Recordset
Dim RsCntPrNCrCred As DAO.Recordset
Dim RsCntPrRR As DAO.Recordset

Set RsID = db.OpenRecordset("SELECT DISTINCT tblTransactions.Regid
AS Regid FROM tblTransactions", dbOpenDynaset, dbReadOnly)
Do Until RsID.EOF
Set RsPeriod = db.OpenRecordset("SELECT DISTINCT
ABS(tblTransactions.Period) AS AbsPeriod FROM tblTransactions WHERE
tblTransactions.Period<>0 AND tblTransactions.Regid ='" & RsID!Regid & "'",
dbOpenDynaset, dbReadOnly)
Do Until RsPeriod.EOF
Select Case RsPeriod!AbsPeriod
Case 1
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [1-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [1-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 2
Set RsCntPrNCr =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrPeriod,
SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [2-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [2-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 3
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [3-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [3-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 4
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [4-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [4-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 5
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [5-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [5-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 6
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [6-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [6-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 7
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [7-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [7-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 8
Set RsCntPrNCr =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrPeriod,
SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [8-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [8-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 9
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [9-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [9-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 10
Set RsCntPrNCr =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrPeriod,
SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [10-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [10-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

End Select
RsPeriod.MoveNext
Loop
RsID.MoveNext
Loop

Set RsID = Nothing
Set RsPeriod = Nothing
Set RsCntPrNCr = Nothing
Set RsCntPrDel = Nothing
Set RsCntPrNNCr = Nothing
Set RsCntPrNCrCred = Nothing
Set RsCntPrRR = Nothing
 
G

Graham R Seach

You can't be serious! Do you really expect us to read through and analyse
all that?

In a nutshell - what are you trying to accomplish?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html


tmuhammad said:
Hi Folks
I am using the following code with a few loops to shap up the data the way I
want which is pretty explainable looking
at the code, however it does not look very elegant as for each case I am
repeating the same queries so I was
wondering if someone can show of a more elegant way

Thanks much



Dim vPeriodSubTot As Integer
Dim vChargeSubTot As Double
Dim vNCrPeriod1 As Integer
Dim vNCrCharge1 As Double
Dim vDelPeriod As Integer
Dim vDelCharge As Double
Dim vNNCrPeriod As Integer
Dim vNNCrCharge As Double
Dim vNCrCredPeriod As Integer
Dim vNCrCredCharge As Double
Dim RsID As DAO.Recordset
Dim RsPeriod As DAO.Recordset
Dim RsCntPrNCr As DAO.Recordset
Dim RsCntPrDel As DAO.Recordset
Dim RsCntPrNNCr As DAO.Recordset
Dim RsCntPrNCrCred As DAO.Recordset
Dim RsCntPrRR As DAO.Recordset

Set RsID = db.OpenRecordset("SELECT DISTINCT tblTransactions.Regid
AS Regid FROM tblTransactions", dbOpenDynaset, dbReadOnly)
Do Until RsID.EOF
Set RsPeriod = db.OpenRecordset("SELECT DISTINCT
ABS(tblTransactions.Period) AS AbsPeriod FROM tblTransactions WHERE
tblTransactions.Period<>0 AND tblTransactions.Regid ='" & RsID!Regid & "'",
dbOpenDynaset, dbReadOnly)
Do Until RsPeriod.EOF
Select Case RsPeriod!AbsPeriod
Case 1
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [1-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [1-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 2
Set RsCntPrNCr =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrPeriod,
SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [2-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [2-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 3
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [3-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [3-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 4
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [4-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [4-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 5
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [5-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [5-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 6
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [6-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [6-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 7
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [7-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [7-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 8
Set RsCntPrNCr =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrPeriod,
SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [8-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [8-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 9
Set RsCntPrNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NCrPeriod, SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [9-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [9-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

Case 10
Set RsCntPrNCr =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrPeriod,
SUM(Nz([Charge],0)) AS NcrCharge FROM tblTransactions WHERE
tblTransactions.Code=1 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCr.EOF And RsCntPrNCr.BOF)
Then
vNCrPeriod1 = 0
vNCrCharge1 = 0
Else
vNCrPeriod1 = RsCntPrNCr!NCrPeriod
vNCrCharge1 = RsCntPrNCr!NCrcharge
End If
Set RsCntPrDel = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
DelPeriod, SUM(Nz([Charge],0)) AS DelCharge FROM tblTransactions WHERE
tblTransactions.Code=7 AND tblTransactions.Regid='" & RsID!Regid & "' AND
tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrDel.EOF And RsCntPrDel.BOF)
Then
vDelPeriod = 0
vDelCharge = 0
Else
vDelPeriod = RsCntPrDel!DelPeriod
vDelCharge = RsCntPrDel!Delcharge
End If
Set RsCntPrNNCr = db.OpenRecordset("SELECT
tblTransactions.id, tblTransactions.Regid,COUNT(Nz([period],0)) AS
NNCrPeriod, SUM(Nz([Charge],0)) AS NNCrCharge FROM tblTransactions WHERE
tblTransactions.Code IN (2,3,4,19) AND tblTransactions.Regid='" & RsID!Regid
& "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod & " GROUP BY
tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNNCr.EOF And RsCntPrNNCr.BOF)
Then
vNNCrPeriod = 0
vNNCrCharge = 0
Else
vNNCrPeriod = RsCntPrNNCr!NNCrPeriod
vNNCrCharge = RsCntPrNNCr!NNCrcharge
End If
Set RsCntPrNCrCred =
db.OpenRecordset("SELECT tblTransactions.id,
tblTransactions.Regid,COUNT(Nz([period],0)) AS NCrCredPeriod,
SUM(Nz([Charge],0)) AS NCrCredCharge FROM tblTransactions WHERE
tblTransactions.Code IN (8,9,10,11) AND tblTransactions.Regid='" &
RsID!Regid & "' AND tblTransactions.Period= " & RsPeriod!AbsPeriod * -1 & "
GROUP BY tblTransactions.id, tblTransactions.Regid")
If (RsCntPrNCrCred.EOF And
RsCntPrNCrCred.BOF) Then
vNCrCredPeriod = 0
vNCrCredCharge = 0
Else
vNCrCredPeriod =
RsCntPrNCrCred!NCrCredPeriod
vNCrCredCharge =
RsCntPrNCrCred!NCrCredCharge
End If

vPeriodSubTot = vNCrPeriod1 - vDelPeriod +
vNNCrPeriod + vNCrCredPeriod
vChargeSubTot = vNCrCharge1 - vDelCharge +
vNNCrCharge + vNCrCredCharge

db.Execute "INSERT INTO
tblSummaryYearCount([Regid],[Period],[No created],[No deleted w/i grace
period],[No renewed/transferred/recoverd],[No deleted in renew/transfer
grace period],[Net],[Charges]) VALUES('" & RsID!Regid & "'," &
RsPeriod!AbsPeriod & "," & vNCrPeriod1 & "," & vDelPeriod & "," &
vNNCrPeriod & "," & vNCrCredPeriod & "," & vPeriodSubTot & "," &
vChargeSubTot & ")"
db.Execute "UPDATE tblSummary SET [10-yr
Count] =" & vPeriodSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"
db.Execute "UPDATE tblSummary SET [10-yr
Charges]= " & vChargeSubTot & " WHERE [Regid]= '" & RsID!Regid & "'"

End Select
RsPeriod.MoveNext
Loop
RsID.MoveNext
Loop

Set RsID = Nothing
Set RsPeriod = Nothing
Set RsCntPrNCr = Nothing
Set RsCntPrDel = Nothing
Set RsCntPrNNCr = Nothing
Set RsCntPrNCrCred = Nothing
Set RsCntPrRR = Nothing
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Heavy data munching - is there a more efficient way of doing this? 8

Top