yoonie_85

10-13-2004, 03:43 PM

hello!

alright, i'm having a lot of trouble doing this, i've made several attempts and this is the closest i've come...but i'm still runnin into some problems, and was wondering if someone could help me.

i need to choose every 550th person from a table. and check the 3rd column...if it's bigger than 18000 copy, paste it to table1 then find the next customer with a value smaller then 18000 and copy and paste it to table2.

go back to the 550 record and find the next record (imcremeting by 550)

and repeat the entire process over again.

here's what I have so far...

Sub Make()

Dim OriginalRS As Recordset

Dim CustWithOutWHRS As Recordset

Dim CustWithWHRS As Recordset

Dim CustCode As Long

Dim PremCode As Long

Dim TotalConsumption As Long

Dim Larger As Boolean

Dim index As Long

Dim i As Long

Dim Start As Integer

Dim NumREcords As Long

Set OriginalRS = CurrentDb.OpenRecordset("Original", dbOpenDynaset)

Set CustWithOutWHRS = CurrentDb.OpenRecordset("CustWithOutWH", dbOpenDynaset)

Set CustWithWHRS = CurrentDb.OpenRecordset("CustWithWH", dbOpenDynaset)

CustCode = OriginalRS!CustCode

PremCode = OriginalRS!PremCode

Larger = False

Start = 0

OriginalRS.MoveLast

NumREcords = OriginalRS.RecordCount

OriginalRS.MoveFirst

Do Until OriginalRS.EOF

For i = Start To NumREcords Step 550

TotalConsumption = OriginalRS!ConsumptionTOtal

If TotalConsumption > 18000 Then

Larger = True

CustWithOutWHRS.AddNew

CustWithOutWHRS!CustCode = OriginalRS!CustCode.Value

CustWithOutWHRS!PremCode = OriginalRS!PremCode.Value

CustWithOutWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value

CustWithOutWHRS.Update

Else

Larger = False

CustWithWHRS.AddNew

CustWithWHRS!CustCode = OriginalRS!CustCode.Value

CustWithWHRS!PremCode = OriginalRS!PremCode.Value

CustWithWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value

CustWithWHRS.Update

End If

If Larger = True Then

Do Until Larger = False Or OriginalRS.EOF

OriginalRS.MoveNext

TotalConsumption = OriginalRS!ConsumptionTOtal

If TotalConsumption > 18000 Then

Larger = True

Else

Larger = False

End If

Loop

CustWithWHRS.AddNew

CustWithWHRS!CustCode = OriginalRS!CustCode.Value

CustWithWHRS!PremCode = OriginalRS!PremCode.Value

CustWithWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value

CustWithWHRS.Update

Else

If Larger = False Then

Do Until Larger = True Or OriginalRS.EOF

OriginalRS.MoveNext

TotalConsumption = OriginalRS!ConsumptionTOtal

If TotalConsumption > 18000 Then

Larger = True

Else

Larger = False

End If

Loop

CustWithOutWHRS.AddNew

CustWithOutWHRS!CustCode = OriginalRS!CustCode.Value

CustWithOutWHRS!PremCode = OriginalRS!PremCode.Value

CustWithOutWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value

CustWithOutWHRS.Update

End If

End If

Start = Start + 550

OriginalRS.Move (Start)

TotalConsumption = 0

Next i

Loop

OriginalRS.Close

Set OriginalRS = Nothing

CustWithOutWHRS.Close

Set CustWithOutWHRS = Nothing

CustWithWHRS.Close

Set CustWithWHRS = Nothing

End Sub

My problem is that it won't increment by 550...instead it's going to random numbers. If someone culd help me out i'd really appreciate it. thanks

alright, i'm having a lot of trouble doing this, i've made several attempts and this is the closest i've come...but i'm still runnin into some problems, and was wondering if someone could help me.

i need to choose every 550th person from a table. and check the 3rd column...if it's bigger than 18000 copy, paste it to table1 then find the next customer with a value smaller then 18000 and copy and paste it to table2.

go back to the 550 record and find the next record (imcremeting by 550)

and repeat the entire process over again.

here's what I have so far...

Sub Make()

Dim OriginalRS As Recordset

Dim CustWithOutWHRS As Recordset

Dim CustWithWHRS As Recordset

Dim CustCode As Long

Dim PremCode As Long

Dim TotalConsumption As Long

Dim Larger As Boolean

Dim index As Long

Dim i As Long

Dim Start As Integer

Dim NumREcords As Long

Set OriginalRS = CurrentDb.OpenRecordset("Original", dbOpenDynaset)

Set CustWithOutWHRS = CurrentDb.OpenRecordset("CustWithOutWH", dbOpenDynaset)

Set CustWithWHRS = CurrentDb.OpenRecordset("CustWithWH", dbOpenDynaset)

CustCode = OriginalRS!CustCode

PremCode = OriginalRS!PremCode

Larger = False

Start = 0

OriginalRS.MoveLast

NumREcords = OriginalRS.RecordCount

OriginalRS.MoveFirst

Do Until OriginalRS.EOF

For i = Start To NumREcords Step 550

TotalConsumption = OriginalRS!ConsumptionTOtal

If TotalConsumption > 18000 Then

Larger = True

CustWithOutWHRS.AddNew

CustWithOutWHRS!CustCode = OriginalRS!CustCode.Value

CustWithOutWHRS!PremCode = OriginalRS!PremCode.Value

CustWithOutWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value

CustWithOutWHRS.Update

Else

Larger = False

CustWithWHRS.AddNew

CustWithWHRS!CustCode = OriginalRS!CustCode.Value

CustWithWHRS!PremCode = OriginalRS!PremCode.Value

CustWithWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value

CustWithWHRS.Update

End If

If Larger = True Then

Do Until Larger = False Or OriginalRS.EOF

OriginalRS.MoveNext

TotalConsumption = OriginalRS!ConsumptionTOtal

If TotalConsumption > 18000 Then

Larger = True

Else

Larger = False

End If

Loop

CustWithWHRS.AddNew

CustWithWHRS!CustCode = OriginalRS!CustCode.Value

CustWithWHRS!PremCode = OriginalRS!PremCode.Value

CustWithWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value

CustWithWHRS.Update

Else

If Larger = False Then

Do Until Larger = True Or OriginalRS.EOF

OriginalRS.MoveNext

TotalConsumption = OriginalRS!ConsumptionTOtal

If TotalConsumption > 18000 Then

Larger = True

Else

Larger = False

End If

Loop

CustWithOutWHRS.AddNew

CustWithOutWHRS!CustCode = OriginalRS!CustCode.Value

CustWithOutWHRS!PremCode = OriginalRS!PremCode.Value

CustWithOutWHRS!TotalConsumption = OriginalRS!ConsumptionTOtal.Value

CustWithOutWHRS.Update

End If

End If

Start = Start + 550

OriginalRS.Move (Start)

TotalConsumption = 0

Next i

Loop

OriginalRS.Close

Set OriginalRS = Nothing

CustWithOutWHRS.Close

Set CustWithOutWHRS = Nothing

CustWithWHRS.Close

Set CustWithWHRS = Nothing

End Sub

My problem is that it won't increment by 550...instead it's going to random numbers. If someone culd help me out i'd really appreciate it. thanks