469,935 Members | 1,603 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,935 developers. It's quick & easy.

Duplicate values

Hello, there,
I have a table tblData which has pharmacy data. The table has
following fields:
ClaimNum, LineNum...

The ClaimNum has claim number which is 12 characters. LineNum is NULL.
The table looks like this
ClaimNum LineNum
abcde1234561
abcde1234561
abcde1234562
abcde1234563
abced1234564
abcde1234564
abcde1234564
abcde1234564
abcde1234564
abcde1234565

The thing is that I have some duplicate ClaimNum, which is
understandable because some claims may have mutiple lines. I would
like to update the [LineNum] to 1 if the ClaimNum is not duplicate, to
2 if it is the second duplicate, to 3 if it is the third duplicate...
So, the combination of ClaimNum and LineNum could serve as Primary
key.

so the table would look like
ClaimNum LineNum
abcde1234561 1
abcde1234561 2
abcde1234562 1
abcde1234563 1
abcde1234564 1
abcde1234564 2
abcde1234564 3
abcde1234564 4
abcde1234564 5
abcde1234565 1
Thanks a lot in advance.
Pablo
Nov 12 '05 #1
2 1752
I may be wrong, but shouldn't you be using 2 tables, 1 for the ClaimNum
which would only need to be entered once, and thus avoid typing mistakes and
1 table for the line numbers. I am guessing that they hold more information
than just a number, possibly drug names.
entry would be via a form and subform
the linenumber would be incremented BeforeInsert of something in the subform

HTH

Phil
"Pablo" <pa*****@hispavista.com> wrote in message
news:e9**************************@posting.google.c om...
Hello, there,
I have a table tblData which has pharmacy data. The table has
following fields:
ClaimNum, LineNum...

The ClaimNum has claim number which is 12 characters. LineNum is NULL.
The table looks like this
ClaimNum LineNum
abcde1234561
abcde1234561
abcde1234562
abcde1234563
abced1234564
abcde1234564
abcde1234564
abcde1234564
abcde1234564
abcde1234565

The thing is that I have some duplicate ClaimNum, which is
understandable because some claims may have mutiple lines. I would
like to update the [LineNum] to 1 if the ClaimNum is not duplicate, to
2 if it is the second duplicate, to 3 if it is the third duplicate...
So, the combination of ClaimNum and LineNum could serve as Primary
key.

so the table would look like
ClaimNum LineNum
abcde1234561 1
abcde1234561 2
abcde1234562 1
abcde1234563 1
abcde1234564 1
abcde1234564 2
abcde1234564 3
abcde1234564 4
abcde1234564 5
abcde1234565 1
Thanks a lot in advance.
Pablo

Nov 12 '05 #2
Hello, Phil,
Thanks for reply.
The problem is that I was given the data and asked to update them. I
am not designing any input form. The data came from a database that
was not well designed. When they entered the pharmacy data, they just
enter duplicate claim number for a claim with mutile lines. So now I
am supposed to add service lines, so the combination of service line
and claim number could serve as primary key. Even though I have
figured out a way to get the goal by using SQL's cross join funciton
(not available in Access), I still want to be able to get a way to do
it in Access environment.
But anyway, thanks for help.
Pablo

"Phil Stanton" <di********@stantonfamily.co.uk> wrote in message news:<3f***********************@mercury.nildram.ne t>...
I may be wrong, but shouldn't you be using 2 tables, 1 for the ClaimNum
which would only need to be entered once, and thus avoid typing mistakes and
1 table for the line numbers. I am guessing that they hold more information
than just a number, possibly drug names.
entry would be via a form and subform
the linenumber would be incremented BeforeInsert of something in the subform

HTH

Phil
"Pablo" <pa*****@hispavista.com> wrote in message
news:e9**************************@posting.google.c om...
Hello, there,
I have a table tblData which has pharmacy data. The table has
following fields:
ClaimNum, LineNum...

The ClaimNum has claim number which is 12 characters. LineNum is NULL.
The table looks like this
ClaimNum LineNum
abcde1234561
abcde1234561
abcde1234562
abcde1234563
abced1234564
abcde1234564
abcde1234564
abcde1234564
abcde1234564
abcde1234565

The thing is that I have some duplicate ClaimNum, which is
understandable because some claims may have mutiple lines. I would
like to update the [LineNum] to 1 if the ClaimNum is not duplicate, to
2 if it is the second duplicate, to 3 if it is the third duplicate...
So, the combination of ClaimNum and LineNum could serve as Primary
key.

so the table would look like
ClaimNum LineNum
abcde1234561 1
abcde1234561 2
abcde1234562 1
abcde1234563 1
abcde1234564 1
abcde1234564 2
abcde1234564 3
abcde1234564 4
abcde1234564 5
abcde1234565 1
Thanks a lot in advance.
Pablo

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by sri2097 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.