updating a field after counting occurances

[Guest]

Hi All. Hope someone can help me. I have a table that contains the
following fields: claim#;line#.
I need to count the occurances of line# within each claim and update the
line# with the sequential numbering of each line: ie:
claim # line#
100 1
110 1
110 2
110 3
120 1

currently the table is sorted by claim# and the line# field 0 for each
record.
Can anyone help me figure out a way to read a record, increment a counter,
write the record to a table, read the next record, determine if the claim# is
the same, then either reset the counter or increment the counter and repeat
the process?

All help is welcome and appreciated. Thanks

 

[Guest]

If it can be done with SQL, it is beyond my knowledge; however, it can be
done using recordset processing. Here is an example:
BTW, don't use # in field names.

Dim rst As Recordset
Dim lngClaim As Long
Dim lngLine As Long

Set rst = Currentdb.OpenRecordset("SELECT [ClaimNbr], [LineNbr] FROM
ClaimTable ORDER BY ClaimNbr, LineNbr;")

If rst.RecordCount = 0 Then
MsgBox "No Records To Process"
Else
With rst
.MoveLast
.MoveFirst
lngClaim = !ClaimNbr
lngLine = 1
Do While Not .EOF
.Edit
!LineNbr = lngLine
.Update
.MoveNext
If !ClaimNbr = lngClaim Then
lngLine = lngLine + 1
Else
lngClaim = !ClaimNbr
lngLine = 1
End If
Loop
.Close
End With
End If

 

[Guest]

Thanks Dave. I didn't use the # in any field names...just a lazy typist.
LOL. I'll give this a try.

Dee

If it can be done with SQL, it is beyond my knowledge; however, it can be
done using recordset processing. Here is an example:
BTW, don't use # in field names.

Dim rst As Recordset
Dim lngClaim As Long
Dim lngLine As Long

Set rst = Currentdb.OpenRecordset("SELECT [ClaimNbr], [LineNbr] FROM
ClaimTable ORDER BY ClaimNbr, LineNbr;")

If rst.RecordCount = 0 Then
MsgBox "No Records To Process"
Else
With rst
.MoveLast
.MoveFirst
lngClaim = !ClaimNbr
lngLine = 1
Do While Not .EOF
.Edit
!LineNbr = lngLine
.Update
.MoveNext
If !ClaimNbr = lngClaim Then
lngLine = lngLine + 1
Else
lngClaim = !ClaimNbr
lngLine = 1
End If
Loop
.Close
End With
End If
--
Dave Hargis, Microsoft Access MVP

Hi All. Hope someone can help me. I have a table that contains the
following fields: claim#;line#.
I need to count the occurances of line# within each claim and update the
line# with the sequential numbering of each line: ie:
claim # line#
100 1
110 1
110 2
110 3
120 1

currently the table is sorted by claim# and the line# field 0 for each
record.
Can anyone help me figure out a way to read a record, increment a counter,
write the record to a table, read the next record, determine if the claim# is
the same, then either reset the counter or increment the counter and repeat
the process?

All help is welcome and appreciated. Thanks

 

[Guest]

So are you saying that in your example your results would be:

100 1 - 1 occurrence
110 1 - 1 occurrence
110 2 - 1 occurrence
110 3 - 1 occurrence
120 1 - 1 occurrence

If this is what you're looking for, unless there's something I'm missing,
you can do it with this query (changing the names of the table and fields as
needed):

SELECT tblClaims.claim, tblClaims.line, Count(tblClaims.line) AS CountOfline
FROM tblClaims
GROUP BY tblClaims.claim, tblClaims.line;

This is just a summary query, grouping by the claim# and line#, and creating
a new variable that gives the count of each line# within each claim#.

This would give you a count of the number of times each disctinct line#
appeared for each claim.

 

[Guest]

Okay, give it a try, but be sure you have your eye protection, steel toed
boots, and welder's gloves
--
Dave Hargis, Microsoft Access MVP

Thanks Dave. I didn't use the # in any field names...just a lazy typist.
LOL. I'll give this a try.

Dee

If it can be done with SQL, it is beyond my knowledge; however, it can be
done using recordset processing. Here is an example:
BTW, don't use # in field names.

Dim rst As Recordset
Dim lngClaim As Long
Dim lngLine As Long

Set rst = Currentdb.OpenRecordset("SELECT [ClaimNbr], [LineNbr] FROM
ClaimTable ORDER BY ClaimNbr, LineNbr;")

If rst.RecordCount = 0 Then
MsgBox "No Records To Process"
Else
With rst
.MoveLast
.MoveFirst
lngClaim = !ClaimNbr
lngLine = 1
Do While Not .EOF
.Edit
!LineNbr = lngLine
.Update
.MoveNext
If !ClaimNbr = lngClaim Then
lngLine = lngLine + 1
Else
lngClaim = !ClaimNbr
lngLine = 1
End If
Loop
.Close
End With
End If
--
Dave Hargis, Microsoft Access MVP

Hi All. Hope someone can help me. I have a table that contains the
following fields: claim#;line#.
I need to count the occurances of line# within each claim and update the
line# with the sequential numbering of each line: ie:
claim # line#
100 1
110 1
110 2
110 3
120 1

currently the table is sorted by claim# and the line# field 0 for each
record.
Can anyone help me figure out a way to read a record, increment a counter,
write the record to a table, read the next record, determine if the claim# is
the same, then either reset the counter or increment the counter and repeat
the process?

All help is welcome and appreciated. Thanks

 

[Guest]

Yes Jim. That's what I need my end result table to look like.
Each claim has several lines that compose it. In the claim lines table the
enrties right now are
before after
100 0 100 1
110 0 110 1
110 0 110 2
110 0 110 3
120 0 120 1

So i need to update the line number field in my table to reflect that claim
100 has 1 line in it, claim 110 has 3 lines (numbered 1,2,3 - 3 separate
records) and claim 120 has 1 line. My problem was that I couldn't figure out
how to get a counter to reset to 1 after each distinct claim number.