Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Identify Duplicate records and update the second duplicate

    Dear Seniors,

    I am working on Document Register, where I have 1 document which are issued to Client many times say (3 Times).

    My Document register will be like this

    DocumentNumber
    Revision
    Status
    IssueDate
    ReissueCheck

    Now I want to update the Field ReissueCheck as "Y" when the same document is issued in Same Status but different revision (Put Y in only second entry onwards).

    I had done the same in excel using the following countif formula, Please help me to do the same in access query.

    Thanks and Regards
    R.Vadivelan

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so is this an oracle problem (where you've also posted this question) or an Access problem

    I suppose you could run an update query using a sub select that counts the number of versions (revisions) and sets a marker if it exceeds whatever threshold you want.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    Hi Healdam,

    Its a Access problem. By mistake I posted in oracle problem since the subject is similar to my problem.

    Could you please help me in detail for using an update query for the above said problem in access.

    I used the access query, but I am getting the update in all the duplicate fields, where as I want the update in second duplicate only.

    Thanks and Regards
    R. Vadivelan

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you want the to find
    drawings with more than one issue... well that should be easy enough COUNT the number of drawings GROUPed BY Drawing No
    use that iin your sub select to identoify drwigns that need updating

    then you need the most recent issue (presumably the highest)
    that should be easy enough
    use MAX function

    then develop your update
    WHERE the drawing number is the same as the drwign number returned from the sub query
    AND the issue number is NOT the same as the highest issue number for that drawing

    how do you propose to limit rows so that you only set the column once... I can't see the drawing office being to keen to keep having to review the same drawings
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2013
    Posts
    163
    Hi Healdem,

    Quote Originally Posted by healdem View Post
    so you want the to find
    drawings with more than one issue... well that should be easy enough COUNT the number of drawings GROUPed BY Drawing No
    use that iin your sub select to identoify drwigns that need updating

    then you need the most recent issue (presumably the highest)
    that should be easy enough
    use MAX function

    then develop your update
    WHERE the drawing number is the same as the drwign number returned from the sub query
    AND the issue number is NOT the same as the highest issue number for that drawing

    how do you propose to limit rows so that you only set the column once... I can't see the drawing office being to keen to keep having to review the same drawings
    Yes I want to find the Drawings that are more than 1 issue and I had found the same as you said. I want the Reissue field to be updated for all other that First issue.

    For ex

    DrawingNo Date Reissue
    A 03-Apr-14
    A 04-Apr-14 Y
    A 05-Apr-14 Y

    How can I use the update query if the condition Not met. I had tried a Lot but my result is coming as follows.

    DrawingNo Date Reissue
    A 03-Apr-14 Y
    A 04-Apr-14 Y
    A 05-Apr-14 Y

    I don't know where I missed out, Please help me to sort out this issue

    Thanks and Regards
    R. Vadivelan

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And the query you tried is
    the problem with that query is
    this is reasonably complex so in the absence of your testdata im not going to consume my time trying to build an environment where a query can be developed.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Oct 2013
    Posts
    163
    Hi Healdem,

    Sorry for not providing the required details earlier. Herewith I had enclosed my sample register for your review. Presently I am using the excel to check the reissue status and Import to Access.

    But Now we are not able to do the same, so I need the same thing to be done in Access.

    My objective is to update the "IssueStatus" in Table as "REISSUED" when the same document is issued in same status. As you can see even though the RevisionCode are different, For us RevisionStatus is a critical factor. In Some cases the document has been issued 3-4 times in same status. So want the "REISSUED" to be updated for 2,3 & 4th issue.

    I hope I had clearly explained the problem & kindly help me to sort out this issue.

    Thanks and regards
    R. Vadivelan
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so your actual column names bear no resemblance to the column names stated in post #1
    sorry I don't have the time or inclination to convert what you are typing into what your db actually is
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Oct 2013
    Posts
    163
    Hi Healdem,

    Sorry for the Inconvenience caused. However I tried to manage this issue with help of your previous responses. The following steps I had followed to solve my issue

    1) First I created a Query1 with Minimum dates from Table 1, Put that as "0". Then I Created a Table 2 using the Make table query.

    2) Created a second query by (Linking both the Table 1 & 2), and put the Text as Duplicate for the Values not equal to "0" and it worked.

    Hope I had done the right thing. But I am not able to run the update query directly from (Query1) since it is always saying error message it should be updateable query. Then I goggled and understood that Update query will not work if the Min date function.

    Is there any other way to do the same?

    Thanks and Regards
    R. Vadivelan

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    almost certainly but to be honest I can't be bothered trying to pick up the pieces, translating from what you say into what you mean work out what how your db is set up as opposed to how you describe it.

    Someone else may be prepared to help you find a solution, but its not going to be me.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Oct 2013
    Posts
    163
    Hi Healdem,

    Sorry for the inconvenience caused to you.

    Thanks and Regards
    R. Vadivelan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •