Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369

    Unanswered: Locating duplicate records in memo fields

    In my data base a series of records are duplicated in memo fields and this is ongoing situation. I wish to knock out these duplicates. The duplicates are in a field of a table that is on the many side of a one to many and the other fields only have time, date, ID Number so I would knock out the whole record.

    Of course if it was not a memo field then this would be very simple.

    So far the best I can come up with is to have a new field in the query based on the Len function. The counting works OK. I put about 12 pages of writing in a field and it counted and if I added or removed a charater or added a space etc the change was reflected.

    Does anybody have a better way of doing it or does anyone see any special problems with using Len to try and pick up the duplicates

    Mike

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Wait a sec... do you have multiple duplicate RECORDS or are you searching for duplicate STRINGS within a single field?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by Teddy
    Wait a sec... do you have multiple duplicate RECORDS or are you searching for duplicate STRINGS within a single field?
    Record 1.......MemoField..... = abcd
    Record 2.......MemoField..... = The quick brown fox
    Record 3.......MemoField..... = xyz
    Record 4.......MemoField..... = The very quick brown fox
    Record 5.......MemoField..... = The quick brown fox

    I want to pick up Record 2 and Record 5

    The actual entries range from a few lines of writing through to a couple of pages.

    Mike

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ah, ok... try using a SELECT INTO statement using distinct.

    Eg:

    SELECT DISTINCT * INTO yourTempTable
    FROM yourCurrentTable

    Oh, this is also assuming you don't have any relationships set up yet. If you do, then it may be quite bit more complicated.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by Teddy
    Ah, ok... try using a SELECT INTO statement using distinct.

    Eg:

    SELECT DISTINCT * INTO yourTempTable
    FROM yourCurrentTable

    Oh, this is also assuming you don't have any relationships set up yet. If you do, then it may be quite bit more complicated.
    I am lost there.

    Table is called SalesNotes and field in question is OneOffMerge

    I make a query and what goes where?

    Mike

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    The basic concept behind what I suggested is taking ONE of each record and dumping it into a temporary table, the either renaming the new table or shuttling it back.

    "SELECT DISTNCT" selects only (you guessed it) distinct rows from a dataset.

    So if you had data like:

    1
    1
    2
    3
    4
    4
    5
    6

    you would get:

    1
    2
    3
    4
    5
    6
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    The query is not acceptin what I am entering on criteria row because obviously what I am doing is wrong.

    I have made a copy of the table SalesNotes and called it SalesNotesDups Or is your query making another table.

    So what do I enter under the memo field OneOffMerge

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I don't think you understand what I"m suggesting...

    Google around for "SELECT INTO queries" & "SELECT DISTINCT" and read up a bit. It's not too bad once you have your head around the general concept.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by Teddy
    I don't think you understand what I"m suggesting...

    Google around for "SELECT INTO queries" & "SELECT DISTINCT" and read up a bit. It's not too bad once you have your head around the general concept.
    I have 3 very big Access 95 books that have a lot of that stuff but I have never gone into before because I have never had to.

    I notice the Access wizard Find Duplicates does not want any part of a memo field. I also tried a macro opening a form for matching records on the memo field and that worker up to the 250 character limit.

    Mike

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    use select distinct. use google.

    try this:

    SELECT DISTINCT yourmemofield INTO TemporaryTable
    FROM yourtable

    Don't do anything else, just that, changing yourmemofield and yourtable to their respective names. See what it does. Get back to me.

    Or hell, just do a normal query:

    SELECT DISTINCT yourmemofield
    FROM yourtable
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Made a query on the table and entered on the criteria row

    SELECT DISTINCT yourmemofield INTO TemporaryTable
    FROM yourtable

    With my field name and the name of the table the query was based on.

    But as soon as I click on another line it comes up with

    The Expression Contains a bad sub query

    Solution

    Enclose the sub query in parenthesis

    I fiddled about a bit but then gen wrong syntax messages. I has to turn the volume down of the computer will all the bells that were ringing

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    This

    SELECT DISTINCT ReasonsSaleStore.OneOffMerge
    FROM ReasonsSaleStore;

    brought up the message

    Can't include Memo or OLE object wehn you select unique values (ReasonsSaleStore.OneOffMerge)

    I change the the OneOffMerge to a date field that has heaps of duplicates and it worked, that is only returned one of each different date.

    My Access 95 Help seems to indicate that DinstinctRow will pull the duplicates but only if two tables are involved. However that did not work.

    One I found where I was going I then changed it to your suggestion.

    So it looks like I am back to using the Len function. I am very sure it would do the job as the chance of two sets of notes being different but having the same character count would be very remote. However, it is still not the perfect solution.

    Mike

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    looks like an A-version thing.
    my A2k will happily perform
    SELECT DISTINCT mytable.memofield FROM mytable

    since you are A95, here is a DAO proposal. it's great to do a bit of code before breakfast on a sunday morning - works up the appetite.
    it is going to be very slow, but you are only going to do it once, so who cares.

    you need
    a form with a button named "GO"
    your original table "SalesNotes" containing a memo field "OneOffMerge"
    a copy of the "SalesNotes" called "nodupeNotes"

    Code:
    private sub go_click()
      dim dabs as database
      dim recI as recordset
      dim recO as recordset
      dim strI as string
      dim strO as string
      dim strSQL as string
      dim isDupe as boolean
      strSQL = "DELETE * FROM nodupeNotes;"
      docmd.setwarnings false
        docmd.runsql strSQL
      docmd.setwarnings true
      set dabs = currentdb
      set recI = dabs.openrecordset("SalesNotes")
      set recO = dabs.openrecordset("nodupeNotes")
      with recI
        recO.addnew
          recO!OneOffMerge = !OneOffMerge
          'use a similar syntax for any other fields of interest
    
    
        recO.update
        .movenext
        do while not .eof 
          strI = !OneOffMerge
          recO.movefirst
          isDupe = False
          do while not recO.EOF
            strO = recO!OneOffMerge
            if strO = strI then
              isDupe = True
              exit loop
            endif
            recO.movenext
          loop
          if not isDupe then
            recO.addnew
            recO!OneOffMerge = !OneOffMerge
            'use a similar syntax for any other fields of interest
    
            recO.update
          endif
          .movenext
        loop
      end with
      msgbox "nodupeNotes contains no duplicates
    end sub
    didn't test it!!! hope there are no typos.

    izy
    Last edited by izyrider; 07-11-04 at 07:04.
    currently using SS 2008R2

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    can't get all the code on the site.
    see .txt attached.
    currently using SS 2008R2

  15. #15
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by izyrider
    can't get all the code on the site.
    see .txt attached.
    Thanks for that. I will have a go tomorrow. I am heading to bed soon for an early Sunday night.

    One thing I did find interesting with A95 and the memo field was that although a query won't have a thing do with a memo field (including the Find Duplicate wizard) I could open a matching record with a macro BUT only up to 250 characters approx. I bet if I checked further it would be 255. One I had more characters then the message came up about too much text or whatever it said.

    Using the Len function against records with several pages of writing is a bit different to a normal calculated field in the sense that when alter the memo field by a character or two and then click elsewhere there is no change on the Len field except when you sort A to Z and of course if close and reopen the query.

    Regards,

    Mike

Posting Permissions

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