sequence using DCount problem

A

Atchleykl

I'm trying to sequence claims in a table. Claims can have any number of
service lines, so the claim number is not unique. I want the claims labled
Claim 1, Claim 2 and so on for each customer number. I have ordered the
table by customer number and then claim number and tried the following to do
the numbering in a field called "Seq".

UPDATE TEST SET TEST.SEQ = "Claim " & 1+DCount(
"MEMBER_ID","TEST","MEMBER_ID=""" & MEMBER_ID & """ AND CLAIM_NBR < """ &
CLAIM_NBR & """");

Unfortunately, it doesn't work. I get:

Claim 1 'This would be the first service line of claim 1
Claim 1 'This would be the second service line of claim 1
Claim 1 'third
Claim 4 'This is the first service line of claim 2, not 4
Claim 4 'This is the second line of claim 2
Claim 6 ' This is the first service line of claim 3 not 6... and so on.

Is there a way to do this?...

Claim 1
Claim 1
Claim 1
Claim 2
Claim 2
Claim 3

Thanks.
 
D

Duane Hookom

How about replacing "Test" in the DCount() with a query name of a query that
groups by MEMBER_ID and CLAIM_NBR?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top