Results 1 to 7 of 7
  1. #1
    Jaron is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    63

    Prompting user to accept or decline potential duplicates while importing excel table


    Hi,
    I have a database table to which I will be adding multiple records from various other contributors. I would like to build a simple, import-friendly excel table that I can mail out to others, so that they can fill it out and send it back to me. Then I will save import steps as a macro or something similar, so that I can easily import their data.
    The hard part is that I would like a way for Access to compare the new records with the existing records, identify potential duplicate records by predetermined criteria (in other words, 'wild-carded' to some level of specificity, since duplicates could be off by just a few characters), and then prompt me to examine each record to be imported alongside however many existing records that have been identified as potential duplicates. Then I as the user could:
    1. accept the import record as a new record
    2. decline importing that record since it is a duplicate of an existing record, or
    3. edit by hand an existing record that is a duplicate of the proposed import, but may require additional information, and then decline to import the new one.
    After each prompt, I would like the import process to continue to the next potential duplicate, and when there are no duplicates, to complete the import and display a message box that says "Import complete".
    Does anyone have any ideas, or am I dreaming too big for Access capabilities?
    Thanks

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    The main problem you're going to have is the 'duplicates could be off by just a few characters'.

    What you're basically saying is that you would want to flag the following as a 'possible duplicate':

    Permanent record = John Smith

    incoming record 1: jon smith
    incoming record 2: johnny smith
    incoming record 3: john smtih

    where not only the length of the string is different but the spelling is different (or wrong). The rest of the stuff is pretty easy to do but the length to which you want to go to identify a potential duplicate is determined by how much coding you want to do.

    in theory I suppose you could compare the number and frequency of letters in a text field and compare that against existing records to see if there is more than an 80% match or something but realistically you're looking for word recognition (similar to when you look up a word in an online dictionary if you've misspelled it the dictionary will offer you what you MIGHT have meant). Access, as far as I know, does not have that type of lexicon or dictionary type function you'd have to build it yourself.

  3. #3
    Jaron is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    63
    What I had in mind was for Access to look for a match of at least 3 characters in any or all of the fields that I designate, or something similar (I would tweak it for sensitivity).
    Thus, Access would say:
    "is 'Johnny A. Smith, 1001 East Rd.' the same as any of the following?
    1. 'John Smith, 1001 E. Rd'
    2. 'Jon Smith, 585 Beachside St.'
    3. 'Mary Esmilne, 857 4th Ave.'"
    I could then choose to drop the import of that record, since obviously it's a duplicate of #1. Or I might like the fact that the import contains the middle initial, so I edit the existing record to match before dropping the import.

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    ok so you're going to visually go through your list of possible append records and search your existing data (by hand) to see if you have something matching.

    the type of search you're talking about isn't going to be automatic, how would your process know to pick up the SMI rather than, say JOH.

    If you're talking about building a manual process, where you import your excel information to your table as a temporary record (you can have a yes/no field that indicates whether it's permanent or not) then cycle through the 'new' records one by one then manually type in a search string for your 'permanent' record THEN make your decision about keeping or discarding it, yes, that can be done.

    Code:
    tblTest
    PK  LastName  FirstName  Perm  Address  
    1   Mouse     Mickey     Y     101 Mouse Lane
    2   Jagger    Mick             202 England St
    3   Mouse     Mickey           101 Mouse LN
    4   Mouse     Minnie           101 Mouse LN
    I think I'd build an form where you could pick a 'new' record (one with an empty or N in the PERM flag field) from a list box (because I don't like continuous forms)

    then have another list box that had a search ability to search only existing records (ones with a PERM flag of Y) for any string you're looking for so your criteria for that would be

    like * & forms!formname!searchfieldname & "*"

    and you'd use that as a criteria for last name OR first name OR address

  5. #5
    Jaron is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    63
    Hm. Not much better than doing each record by hand. I already have a search form where I can type a few characters and look for matches, and an 'Add New' command button if I want to type a new record in. But if I want to add, say, 20 records, that gets labor intensive.
    As to picking up 'JOH' or 'SMI', is there a way to pick up both, if there is a match of 3 characters? Also, if there is no match whatsoever, I would like the record to be imported as a permanent record without user input. I have seen this in other DBs, but they may have been more advanced than Access.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    There are ways to try and find multiple strings in a single string but you'd likely need two search boxes to do it so your criteria would be something like:

    like * & forms!formname!searchfield1name & "*" or like * & forms!formname!searchfield2name & "*"

    if you're pulling a value from a form, but you'd still have to type the values in.

    Let me just say that if your text field has an expected format (for instance lastname space firstname you might be able to parse those and use them as search criteria but it would not be foolproof. Without knowing what your dataset looks like it's a shot in the dark but since all of your examples are by name I thought if they *always* had the same format you might be able to do a little something more with it.

  7. #7
    Jaron is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    63
    Well I've tried to eliminate formatting inconsistencies by putting everything in separate fields, so my table has a separate field for first name, last name, street address, city, zip, and phone number. I guess the problem is, I don't want to pull my criteria from a form; I want the DB to be able to recognize matching patterns in two tables. I suppose that is a monumental task though, because it would have to check every possible combination of three characters in the import table against every possible combination of three characters in the DB table, and look for matches. Likely not possible, but thanks for your suggestions so far. I'll keep thinking about it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Importing And Comparing A Table From Excel
    By David92595 in forum Import/Export Data
    Replies: 5
    Last Post: 10-04-2012, 08:23 PM
  2. Importing excel data into table automatically
    By jwalker55 in forum Import/Export Data
    Replies: 2
    Last Post: 01-10-2012, 07:56 AM
  3. Accept user input to populate new record
    By bbrazeau in forum Forms
    Replies: 1
    Last Post: 01-03-2012, 05:45 PM
  4. Beginner-Importing excel table question
    By simmonsmtb in forum Import/Export Data
    Replies: 3
    Last Post: 03-07-2011, 11:55 AM
  5. Replies: 2
    Last Post: 04-22-2010, 10:40 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums