Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Unanswered: Copy records without duplicates

    Hello

    I need a query to copy records from a table to another table without duplicates.

    Many thanks in advance and your help is most appreciated.
    Carlos

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have you had a look at APPEND queries and the Unique Values / Unique Records properties of a query?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what exclude ALL duplicates.. ie don't copy records which are duplicated,
    or exclude the copy of other records.

    the former is fairly easy to do with an Append query
    the problem with trying to remove 'just' the copied record is trying to decide which is the copy and which is the original you want to keep.

    do you know how or why you got duplicates in the first place.. if so was a systems problem (ie down to the design or a human factors problem.. ie the users banged in records more than once)
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    I had this issue for Document control db which I had revisions of documents registered and I wanted to report only the latest revisions
    I tried the Append query with unique value and it did't work so I used code to compare records with same document number and put the highest revision in the destination table.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Aran1
    I had this issue for Document control db which I had revisions of documents registered and I wanted to report only the latest revisions
    I tried the Append query with unique value and it did't work so I used code to compare records with same document number and put the highest revision in the destination table.
    if you are doing document/version control.. then surely all revisions are important!

    you can always restrict what you display by judicious use of a where clause
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    if you are doing document/version control.. then surely all revisions are important!
    I needed a report to show only the latest revisions
    you can always restrict what you display by judicious use of a where clause
    I had to show all related fields so working with where clause was not possible
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a where clause is always possible.. iot acts as a filter to limit the rows you want to see returned. mixed with a group by and a top n predciate it sould be possibel to get the huighest reviision number....... or all revsion numbers.

    if you store all the revisions then it can often be just as important to see the revision history
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    you made me to check my db again and see if I was doing something wrong butI was not Healdem,
    In my table there is
    Doc Num, Rev., Receive date, Over due date, answer date and few other fields that are diffrent in each revision of documents so where clause doesn't work as you should have more than one where which is not aceptable by ACCESS or maybe I don't know enough about queries.
    I can send the tables file in order you check it out and if it is possible I would appreciate you to let me know how?
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your document revision number is the master element, if you pick the highest revision number then you have the most recent revision.

    you may need to do a bit of fancy trickery to handle 3.0.1.3.5 or what ever coding you use for a document version.. but that can be handled similar to an IP address.

    the reality is that your document control system may not need to know previous versions.. if I were to do a docuemnt control / version control system I'd want to knwo what happened, when. Id want to knwo who did what and why, and I'd want to see what happened throughout the life of the document. bear in mind that a document may well be released to match a specific version of a product/software whatever..once its released in my books it needs to be sealed. some QC procedures (eg EU CE certification requires that a products docuemtnation is sealed at specific states). some QC procedures eg BS 5750/IS900x require simialr steps. DNV require such steps in some cases.

    but as ever its your system.. as long as the customer is happy.. thats what counts.. its their cash that pays our salaries (except mine natch where its just thrown away on a wastrel)
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Thanx Healdem for your advices, tbh this sytem is working for Oil & Gas Projects which follow its own standards and there is individual reports which one of them is the latest revisions of project documents and is being used for construction as well as checking against MDR to be sure all documents are generated. (they want to do it manualy!)
    also they consider the revisions on digit number starting from 0 so it was very easy for me.
    <<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
    Graham Bell

Posting Permissions

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