Page 2 of 6 FirstFirst 123456 LastLast
Results 16 to 30 of 76
  1. #16
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    You could simply join the table with itself, using your condition statement as the condition for the join.

  2. #17
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, the code does not make sense to me. Did you try to program it? Exactly which records would you expect to be retrieved by that filter? There really aren't any parameters in that expression.

    A filter expression would be like:

    Me.Filter = "DOB=#2/25/1980# OR DOB=#3/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.

  3. #18
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    Does the value have to be a specific value when filtering? The way I was thinking of doing it was filter any data where the value within the cell does not meet the condition.

  4. #19
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And am I not clear on what that condition is. Your pseudocode does not make sense to me. Criteria that selects a record based on values in other records of same table is not easy. Usually involves nested subquery or domain aggregate function.

    Joining a table to itself, as hapm suggests, is useful in many situations, I am just not sure if it can help you.

    If you can define the algorithm logic (the 'rules') in plain English statements, then it can be programmed.
    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. #20
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    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++))) AND ([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



    what this nested for loop does is go through all the values in the DOB column, MI, Number, First Name, and Last Name. The "IF" statement is to compare the current cell value with the next cell value hence the i and i++. So the condition reads

    if (DOB(current cell value)=DOB(next cell value)) AND ((Number(current cell value)=Number(next cell value)) OR (MI(Curretn Cell value)=MI(next cell value)))_
    AND ([First Name](current cell value)<>[First Name](next cell value) OR [Last Name](Current Cell value)<>[Last Name](next cell value))) Then

    Filter the current cell value and the next cell value as the loop runs through data.

    end if
    next
    next
    next
    next
    next

  6. #21
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The logic is still not explicitly defined. I still don't understand the point of your code. So what if it determines that two or more records have the same Number and MI? A unique set of Number and MI values can be generated with a GROUP BY or DISTINCT query. None of this will help associate the Number and MI with the 'correct' name because there is no way to know which is correct. A GROUP BY query could return the name from the first or last record of each Number/MI/DOB grouping. Although this is not technically filtering the records, maybe it is close to what you are looking for. If you want a dataset of just the unique Number/MI/DOB combination, then exclude the First() expressions.

    SELECT Number, MI, DOB, First([First Name]) AS FirstName, First([Last Name]) AS LastName FROM tablename GROUP BY Number, MI, DOB;

    Also, as previously stated, 'looping' cannot be done within the query. A record cannot see values of other records in same table except through a subquery or domain aggregate function. A query can call a custom function that tests each record data against some condition and return a value.

    Even if the loop within query were possible, it seems that it would compare each record with all other records and at some point in the loop the condition will fail and nothing will be retrieved.

    Wish I could do better job of explaining limitations of this data and alternatives for manipulating it.

    How many possible spellings for each name - only 2? One from each website?
    If you want to compare the names in two records that have the same Number/MI/DOB, it is possible to generate a dataset that will display the two names side-by-side (subquery, self-join).
    What you don't show in the posted data is a unique record id field. An autonumber field can provide that. A unique record ID is often needed for nested subqueries to work.
    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. #22
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    I am having trouble understanding this in any other langauge such as as matlab, or c++ you would loop through the values and store values that you see fit. Why is it not the same for vba? I mean I would assume it would be the same since vba deals with data most of the time depending on what you are trying to do.

  8. #23
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You may be mixing VBA and SQL.

    VBA can open a recordset object and loop through the records. Code can read the data in each record and do something with it. One of the things code can do is build a text string that can be used as filter criteria for a form or report.

    Query objects use SQL to manipulate records. Query objects cannot 'loop' through their records. SQL statements cannot include 'loop' structure.
    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. #24
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    So what is the best course of action I should take to get the results I want for this?

  10. #25
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I still don't quite understand what you want to achieve.

    Your first post presented a table which you say is representative of your raw data.

    What would the 'filtered' results look like?
    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. #26
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    This is the expected table after filter is applied.


    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

  12. #27
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Looks like that's just returning records where there are more than one Number/MI/DOB combination (that's a rule I can work with). One way (assumes Number and MI are text fields):

    SELECT Number, MI, [First Name], [Last Name], DOB FROM tablename WHERE DCount("*", "tablename", "Number='" & [Number] & "' AND MI='" & [MI] & "' AND DOB=#" & [DOB] & "#")>1;

    Or try:

    SELECT Number, MI, [First Name], [Last Name], DOB FROM tablename WHERE Number & MI & DOB IN (SELECT Number & MI & DOB AS ID FROM tablename WHERE Count(*) > 1 GROUP BY Number, MI, DOB);

    Variations could be to consider only:

    Last Name and DOB

    or only MI and DOB

    or only Number and DOB

    or Number and MI and DOB and part of Last Name such as maybe the first 5 characters.
    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. #28
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    What is this code suppose to do?

  14. #29
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Those are SQL statements of SELECT queries. They should return records as shown in the desired output you posted. Use the query builder to construct query object. Can copy/paste into the SQL View of query builder (be sure to use the actual table name). Then switch to Design View.
    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. #30
    mr879 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    May 2014
    Posts
    80
    what did you mean when you said number and MI should be considered as text fields the way it is right now is just two column field names.

Page 2 of 6 FirstFirst 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