I am working on a Access 97 database as a front end with Oracle 8 as a back end. In one of the reports I have code that uses a record set to pull the information together. When the report is ran there are four fields and if there is information in one of the fields twice the field with the total is added together for each one of those records. IE: If the vendor field is Cash and there is cash for 350.00 and one for 500.00 they will both show separately up with 850.00 as the amount.

Here is the Code

Thanks for the help.DoCmd.RunSQL "DELETE * FROM VCNHESTIMATER_TBL where VCNHEST is not null"

varNoTrpSeats = 0
varNofSS = 0
TotalCost = 0
TtlCstWBsVr = 0
TtlCstWAgVr = 0
Set dbs = CurrentDb

sqlstr = "select * from trip_tbl where [tripid] =" & vartrp
Set rstForTrpGrpTbl = dbs.OpenRecordset(sqlstr)
If rstForTrpGrpTbl.RecordCount = 0 Then
TotalVHIDPlan = 0
ExpPlan = TotalVHIDPlan + ExpPlan
Else
rstForTrpGrpTbl.MoveLast
varCntForTrGr = rstForTrpGrpTbl.RecordCount
rstForTrpGrpTbl.MoveFirst
For varTrGr = 1 To varCntForTrGr
sqlstr = "select * from trip_group_tbl where [tripid] =" & vartrp
Set rstForTrpGrpsts = dbs.OpenRecordset(sqlstr)
If rstForTrpGrpTbl.RecordCount = 0 Then
varNofSS = 0
Else
rstForTrpGrpsts.MoveLast
varCntForTrGrsts = rstForTrpGrpsts.RecordCount
rstForTrpGrpsts.MoveFirst
For varTrGrsts = 1 To varCntForTrGrsts
sqlstr = " select * from trip_group_tbl where [tripid] =" & vartrp & _
" and [groupid] = " & rstForTrpGrpsts!GroupID
Set rstForgrpsts = dbs.OpenRecordset(sqlstr)
varNofSS = varNofSS + rstForgrpsts!NoofSeatsSold
rstForTrpGrpsts.MoveNext
Next varTrGrsts
End If
'********
varNofSS = varseatssold
'********
'varTrpSeats = rstForTrpGrpTbl!NoofSeats
'varTrpSeatsAvble = rstForTrpGrpTbl!SeatsAvailable
'varNofSS = rstForTrpGrpTbl!NoofSeats - rstForTrpGrpTbl!SeatsAvailable
'varNoTrpSeats = varNoTrpSeats + varNofSS
sqlstr = " select * from tripmap where [tripid] =" & vartrp & _
" and not (isnull(vhid)) "
Set rstForGrpMapTbl = dbs.OpenRecordset(sqlstr)
If rstForGrpMapTbl.RecordCount <> 0 Then
rstForGrpMapTbl.MoveLast
varCntForGrp = rstForGrpMapTbl.RecordCount
rstForGrpMapTbl.MoveFirst
For varGrpMap = 1 To varCntForGrp
TtlCstWBsVr = 0
TtlCstWAgVr = 0
varVVHID = rstForGrpMapTbl!VHID
sqlstr = " select * from variablecostnonhotel_tbl where [vhid] = " & varVVHID & " and [paxoption] = No"
Set rstForVCNHTbl = dbs.OpenRecordset(sqlstr)
If rstForVCNHTbl.RecordCount <> 0 Then
varVID = rstForVCNHTbl!VendorID
VHIDdesc = rstForVCNHTbl!FeatureDescription
sqlstr = "select * from Agevariation_tbl where [TripID] = " & vartrp & _
" and [VHID] = " & rstForGrpMapTbl!VHID
Set rstForAgeVarTbl = dbs.OpenRecordset(sqlstr)
If rstForAgeVarTbl.RecordCount <> 0 Then
rstForAgeVarTbl.MoveLast
varCntForAgVr = rstForAgeVarTbl.RecordCount
rstForAgeVarTbl.MoveFirst
For varAgVr = 1 To varCntForAgVr
TtlCstWAgVr = TtlCstWAgVr + rstForAgeVarTbl!Cost * rstForAgeVarTbl!NumberofPax
varNofSS = varNofSS - rstForAgeVarTbl!NumberofPax
rstForAgeVarTbl.MoveNext
Next varAgVr
Else
sqlstr = "select * from busvariation_tbl where [TripID] = " & vartrp & _
" and [vhid] = " & rstForGrpMapTbl!VHID
Set rstForBusVarTbl = dbs.OpenRecordset(sqlstr)
If rstForBusVarTbl.RecordCount <> 0 Then
rstForBusVarTbl.MoveLast
varCntForBsVr = rstForBusVarTbl.RecordCount
rstForBusVarTbl.MoveFirst
Else
For varBsVr = 1 To varCntForBsVr
TtlCstWBsVr = TtlCstWBsVr + rstForBusVarTbl!Cost * rstForBusVarTbl!NumberofPax
varNofSS = varNofSS - rstForBusVarTbl!NumberofPax
rstForBusVarTbl.MoveNext
Next varBsVr
End If
End If
'varfeaturecode = rstForVCNHTbl!FeatureCode
TotalCost = TotalCost + TtlCstWBsVr + TtlCstWAgVr + (rstForVCNHTbl!Cost * varNofSS)
TotalVHIDPlan = TotalCost
ExpPlan = TotalVHIDPlan + ExpPlan
sqlstr = "Select * from [checkwriting_tbl] where [TripID] = " & vartrp & _
" and [VendorID] =" & varVID
'" and [VendorID] =" & varVID & " and [FeatureCode] = " & varfeaturecode
Set rstForPrevAmt = dbs.OpenRecordset(sqlstr)
If rstForPrevAmt.RecordCount <> 0 Then
rstForPrevAmt.MoveLast
varCntPrevAmt = rstForPrevAmt.RecordCount
rstForPrevAmt.MoveFirst
For VarAmt = 1 To varCntPrevAmt
TotalVHIDActual = TotalVHIDActual + rstForPrevAmt!TotalPaymentAmt
ExpActual = TotalVHIDActual + ExpActual
VHIDDiff = TotalVHIDActual - TotalVHIDPlan
rstForPrevAmt.MoveNext
Next VarAmt
Else
TotalVHIDActual = 0
ExpActual = TotalVHIDActual + ExpActual
VHIDDiff = TotalVHIDActual - TotalVHIDPlan
End If
sqlstr = "Select * from [suppliers_tbl] where [vendorID] = " & varVID
Set rstvname = dbs.OpenRecordset(sqlstr)
varvendorname = rstvname!VendorName
DoCmd.RunSQL " INSERT INTO VCNHESTIMATER_TBL (TRIPID, ProgramID, " & _
" totalplan, totalactual, VID, VHID, vendorname, description) " & _
" VALUES (" & vartrp & ", " & varprogramid & ", " & TotalVHIDPlan & ", " & TotalVHIDActual & _
" , " & varVID & ", " & varVVHID & ", '" & varvendorname & "', '" & VHIDdesc & "')"
TotalCost = 0
TotalVHIDPlan = 0
TotalVHIDActual = 0
End If
'optionVHID
sqlstr = " select * from variablecostnonhotel_tbl where [vhid] = " & varVVHID & " and [paxoption] = Yes"
Set rstForVCNHTbl = dbs.OpenRecordset(sqlstr)
If rstForVCNHTbl.RecordCount <> 0 Then
varVID = rstForVCNHTbl!VendorID
VHIDdesc = rstForVCNHTbl!FeatureDescription
varppoptions = 0
sqlstr = "SELECT * FROM paxoptions_tbl " & _
" WHERE accountid IN (SELECT accountID FROM payaccount_tbl where [tripid] =" & vartrp & ") " & _
" and vhid = " & varVVHID
Set rstppoptions = dbs.OpenRecordset(sqlstr)
If rstppoptions.RecordCount = 0 Then
varppoptions = 0
Else
rstppoptions.MoveLast
varpaxoptions = rstppoptions.RecordCount
rstppoptions.MoveFirst
For options = 1 To varpaxoptions
varppoptions = varppoptions + rstppoptions!Rate
rstppoptions.MoveNext
Next
End If
TotalCost = varppoptions
TotalVHIDPlan = TotalCost
ExpPlan = TotalVHIDPlan + ExpPlan
sqlstr = "Select * from [checkwriting_tbl] where [TripID] = " & vartrp & _
" and [VendorID] =" & varVID
Set rstForPrevAmt = dbs.OpenRecordset(sqlstr)
If rstForPrevAmt.RecordCount <> 0 Then
rstForPrevAmt.MoveLast
varCntPrevAmt = rstForPrevAmt.RecordCount
rstForPrevAmt.MoveFirst
For VarAmt = 1 To varCntPrevAmt
TotalVHIDActual = TotalVHIDActual + rstForPrevAmt!TotalPaymentAmt
ExpActual = TotalVHIDActual + ExpActual
VHIDDiff = TotalVHIDActual - TotalVHIDPlan
rstForPrevAmt.MoveNext
Next VarAmt
Else
TotalVHIDActual = 0
ExpActual = TotalVHIDActual + ExpActual
VHIDDiff = TotalVHIDActual - TotalVHIDPlan
End If
sqlstr = "Select * from [suppliers_tbl] where [vendorID] = " & varVID
Set rstvname = dbs.OpenRecordset(sqlstr)
varvendorname = rstvname!VendorName
DoCmd.RunSQL " INSERT INTO VCNHESTIMATER_TBL (TRIPID, ProgramID, " & _
" totalplan, totalactual, VID, VHID, vendorname, description) " & _
" VALUES (" & vartrp & ", " & varprogramid & ", " & TotalVHIDPlan & ", " & TotalVHIDActual & _
" , " & varVID & ", " & varVVHID & ", '" & varvendorname & "', '" & VHIDdesc & "')"
TotalCost = 0
TotalVHIDPlan = 0
TotalVHIDActual = 0
End If
'end of optionVHID
rstForGrpMapTbl.MoveNext
Next varGrpMap
End If
rstForTrpGrpTbl.MoveNext
Next varTrGr
End If

TotalCost = 0
varNofSS = 0