updating a field after counting occurances

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
Thanks Dave. I didn't use the # in any field names...just a lazy typist.
LOL. I'll give this a try.

Dee

Klatuu said:
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


Dee said:
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
Click to expand...
Click to expand...
 
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.
 
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


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

Dee

Klatuu said:
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


Dee said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.
 
Back
Top