By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,727 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

Duplicate values

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.