Page 1 of 6 123456 LastLast
Results 1 to 15 of 76
  1. #1
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80

    Exclamation Filtering Multiple data set from a single column Field

    Hello Everyone I am trying to Filter data that has miss matched First and Last name but may potentially be the same person by compareing certain information such as their number and date of birth, here is the table.

    Number MI First Name Last Name DOB
    15241543 123456789 James Matheson 2/25/1980
    15241543 123456789 Jams Matheson 2/25/1980
    12345679 124512451 Monroe Matheson 3/25/1980
    12345679 124512451 Monro Matheson 3/25/1980
    54789654 152415241 Dilan Pumley 4/23/1970
    54789658 154215246 Michael Lan 1/30/1989

    As you can see the person named "James Matheson" has a similar account to "jams Matheson" their DOB and Number are the same so it can potentially be the same person. So in the end when this table is filtered it should only show the information for "James Matheson","Jams Matheson", "Monroe Matheson", and "Monro Matheson". So I am trying to filter out anyone that meet those criteria, here is the code I am thinking of.



    for(i=1 to Len(DOB))
    for(j=1 to Len(MI))
    for(k=1 to Len(Number))
    for(L=1 to Len([First Name]))
    for(M=1 to Len([Last Name]))

    if ( (DOB(i)=DOB(i++)) AND ((Number(k)=Number(k++)) OR (MI(j)=MI(j++))) ([First Name](L)<>[First Name](L++) OR [Last Name](M)<>[Last Name](M++))) Then

    Me.Filter=DOB(i) & "AND DOB(i++)"
    end if


    next
    next
    next
    next
    next


    So my main problem is how can I go through each value in a column the current way I have it now is it takes the length of the string instead of the column so it does not work well and can I filter multiple values in a single field at the same time?

    THANKS ANY HELP IS GREATLY APPRECIATED!!!!

    Last edited by mr879; 05-22-2014 at 12:31 PM.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Applying multiple parameters to the same field requires criteria like:

    WHERE [fieldname]=x Or [fieldname]=y Or [fieldname]=z

    or

    WHERE [fieldname] IN (x, y, z)

    Why are you filtering on names and not the MI?

    What is this data about?

    Do you have a table of the unique MI values? Are you trying to create that dataset?

    I think what you are trying is very difficult, if not impossible. Bad data makes life very difficult.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I could not assign field name to a certain value because I am not looking for a single value I am trying to filter data where the First names or Last names do not match up for people who are potentially the same such as "james" and "jams" and I base this if the MI, or Number is the same and also the DOB. The reason I do not use MI to filter the data is because sometimes the data set will not have the MI or the Number available but always has the DOB available so I decided to Filter the DOB instead if the condition statement is met. And note I am cycleing through thousands of data this is only a portion of it.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Data management relies on consistency. There are so many possible permutations of name misspellings, nearly impossible to do comprehensive comparison

    Should these records be considered related:

    Jaimes Mathison 2/25/1980
    James Matheson 2/25/1980
    Jim Matheson 2/25/1980
    Jamie Mathison 2/25/1980
    Jams Matheson 2/25/1980
    Jim Mateson 2/25/1980
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    yes but I dont just consider the DOB because if i just considered the DOB the result will not be valid since it can be a different person with the same DOB but Similar Name or the same Name so I also consider the "Number" or "MI" depend on which is available at the time because then I can definetly tell if it is the same person with just misspelled names.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not sure how the Number is helpful. It is different for each record. So if the MI is not a reliable common identifier because it is not always provided, I have no idea how you will be able to apply sort/filter to this dataset.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I am sorry if the data sheet is not clear I have only provided a very fraction of the data to set an example. So basically each user can either have the "number" or "MI" available but nontheless they will have one of those. When a user has multiple accounts their number or MI will still be the same so this data will be consistent but their first and last names may be different. The "number" and "MI" is like their identification number the reason two different ones are available is because depending on how they signed up they are issued one of the two numbers. Say the person signed up in the two different websites which is really just one company with two different branches one handles identification by "MI" and another by "Number" so depending if the user signed up in both websites or just one they will be issued both identifications or just one them.

    I have changed the data in the number field to make more sense.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I see now that the Number values are repetitive.

    Sorry, still don't see how you can use the names to filter/sort. Mostly because there is no way to know which is the 'correct' spelling to use as the master to compare to.

    This sort of issue has been discussed in forum threads. The conclusion invariably is root of the issue is bad data structure. The Number and MI are not consistent across both websites or possibly even within the website. If Number and MI are each assigned by only one website, how did same value get associated with different name spellings?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    Thats the thing I wont be filtering the names to filter the data but information related to the person such as their DOB if you look at the IF statement I have included conditions where if they are met then that means they are the same person with missmatched first and last names and then after that I filter the DOB not the names themselves.

    The same values I am not sure about but as far as I know they apply the same number to the new account if the user signs up again with a different name but using a different email or something but has the same DOB.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    That is still trying to use names to identify the filter criteria.

    It is possible to use LIKE operator.

    Suppose user enters a name or partial name string in a textbox. The query search can be:

    SELECT * FROM tablename WHERE [LastName] LIKE "*" & Forms!formname!textboxname & "*";

    User enters Math and all the Matheson records are listed.

    Then user can select from that list the appropriate Matheson to further narrow the search by Number and/or MI.

    Cascading combo and list boxes are often used for hierarchical filtering. Perhaps that is what you can use.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    Can you clarify how i can loop through the values in a single column such as "MI"

    Is it something like this?

    for (i=1 to MI.Count)

    next

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    AFAIK, can't do what you want in a query.

    Can use VBA to open a recordset and loop through records. Use code to compare values according to whatever rules you define and build whatever filter criteria is needed. However, there is a limit to how long a filter string can be.

    I am just really fuzzy on what your 'rules' are.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    what do you mean by rules?

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Rules - like the If conditional expression pseudocode in your first post.

    My example SQL statement in post 10 defines a rule in the WHERE clause that specifies all records with last name like *Math* should be retrieved.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    all the rules are set out in the if condition statement other then that there is no more to add.

Page 1 of 6 123456 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filtering Data from a specified column
    By mr879 in forum Programming
    Replies: 16
    Last Post: 05-15-2014, 10:59 AM
  2. Multiple choice in a single field
    By fabads in forum Access
    Replies: 6
    Last Post: 05-21-2013, 11:45 AM
  3. Replies: 2
    Last Post: 08-10-2012, 03:42 PM
  4. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  5. Filtering dupe Data in a single field
    By label027 in forum Queries
    Replies: 4
    Last Post: 10-25-2011, 01:18 PM

Tags for this Thread

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