traversing a large recordset

[aeg]

I have a table with over 350,000 records, in which i have a field
("ClaimNoCount") to count the unique claim numbers. I have the following
code to do the job but it takes too loooong to run. (Infact I have to
terminate it.) Is it possible to write the following code differently
(perhaps more efficiently)? thanks

Sub SetClaimNoCount()
Dim db As Database
Dim rs As Recordset
Dim tempString As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_AllUnion_PRM") ' or SQL statement
While Not rs.EOF
'rs.MoveFirst
'Debug.Print rs.Fields("ClaimNo")
tempString = rs.Fields("ClaimNo")
rs.MoveNext
If tempString = rs.Fields("ClaimNo") Then
rs.Edit
rs!ClaimNoCount = 0
rs.Update
Else
rs.Edit
rs!ClaimNoCount = 1
rs.Update
End If
Wend
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[John W. Vinson]

I have a table with over 350,000 records, in which i have a field
("ClaimNoCount") to count the unique claim numbers. I have the following
code to do the job but it takes too loooong to run. (Infact I have to
terminate it.) Is it possible to write the following code differently
(perhaps more efficiently)? thanks

Sub SetClaimNoCount()
Dim db As Database
Dim rs As Recordset
Dim tempString As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_AllUnion_PRM") ' or SQL statement
While Not rs.EOF
'rs.MoveFirst
'Debug.Print rs.Fields("ClaimNo")
tempString = rs.Fields("ClaimNo")
rs.MoveNext
If tempString = rs.Fields("ClaimNo") Then
rs.Edit
rs!ClaimNoCount = 0
rs.Update
Else
rs.Edit
rs!ClaimNoCount = 1
rs.Update
End If
Wend
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub

How about a solution with no code at all?

SELECT Count(*) FROM
(SELECT DISTINCT ClaimNo FROM tbl_AllUnion_PRM);

If there is not an index on ClaimNo this will benefit greatly from creating
such an index.

 

[aeg]

Thanks John
Not sure if thats going to work.
here is bit more detail on what i need:

ClaimNo VendorName
claim1 vendor1
claim1 vendor2
claim1 vendor3
claim2 vendor1

if i do a dictinct count without the vendor field i would get 2
but i need the vendor detail in the table. so when i count with the vendor
i would get 4 but i want to get 2.

so i added a field ("claimnocount") and the code is trying to check if the
claimno of the current record is same as previous, if yes then count is 0
otherwise 1. (pretty simple if it was in Excel.)
when i create a pivot to analyse, i simply sum the ClaimNoCount field to
have a distinct count of claims and drill to all other details on the table
(like vendor name) if necessary.

thanks

 

[John W. Vinson]

Thanks John
Not sure if thats going to work.
here is bit more detail on what i need:

ClaimNo VendorName
claim1 vendor1
claim1 vendor2
claim1 vendor3
claim2 vendor1

if i do a dictinct count without the vendor field i would get 2
but i need the vendor detail in the table. so when i count with the vendor
i would get 4 but i want to get 2.

so i added a field ("claimnocount") and the code is trying to check if the
claimno of the current record is same as previous, if yes then count is 0
otherwise 1. (pretty simple if it was in Excel.)
when i create a pivot to analyse, i simply sum the ClaimNoCount field to
have a distinct count of claims and drill to all other details on the table
(like vendor name) if necessary.

I guess I don't understand.

These are two separate operations. Sure, you can have the detail level query
with four records. You can also have a count distinct query (using the
Subquery that I suggested) to get the count.

The count should NOT be stored, anywhere, in any table, unless you have a
DEMONSTRATED need to do so, since any addition, deletion, or editing of a
vendor anywhere in your table will invalidate your count.

 

[aeg]

normally, infact, never i had to store the count. but for the pivottable
(excel) to dynamically calculate an average cost per claim vs ave cost per
transaction, i had to store the count on a separate field. (unless a pivot
table can calculate averages dynamically based on distict count of claim
numbers on selected dimensions, which i don't think it does.)
yes, the count does chane if the table changes, hence i tried the VBA for
the recordset to number the count of claims. so the recordset operation
(VBA) suppose to look at the record before and if the claim no is same as
current one, then update the count field value to '1' otherwise '0'. what's
the best way to write this?

thanks