Duplicate values

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Pablo
    #1

    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
    Tags: None
    • Phil Stanton
      #2
      Re: Duplicate values

      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" <pablo17@hispav ista.com> wrote in message
      news:e97b5703.0 312190834.761e6 d9c@posting.goo gle.com...[color=blue]
      > 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[/color]


        Comment

        • Pablo
          #3
          Re: Duplicate values

          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" <discussion@sta ntonfamily.co.u k> wrote in message news:<3fe32f08$ 0$38858$65c6931 4@mercury.nildr am.net>...[color=blue]
          > 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" <pablo17@hispav ista.com> wrote in message
          > news:e97b5703.0 312190834.761e6 d9c@posting.goo gle.com...[color=green]
          > > 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[/color][/color]

            Comment

            Previous template Next
            Working...