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
