Preventing duplicate records from showing in query
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Preventing duplicate records from showing in query

  1. #11
    Board Regular
    Join Date
    May 2005
    Posts
    497
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing duplicate records from showing in query

    Sorry for the confusion. Like I mentioned, everything works in the query except for excluding the members who don't have anything in Local, Service or State fields.

    For example, I have e people with at least one position in the following categories and the last one doesn't have one. I want to exclude Lola who has nothing - the dashes or blanks represent nothing in that field:

    Name --- Local ---- State ----Service
    Joe President
    Abe --------- Council
    Bob --------- ----------- Chair
    Lola --------- ----------- -------

    Sorry for the confusion.

    I wish I could send a sample file of what I'm looking at but I don't know how to post it on this site.

    Sorry of the confusion and thank you for your help

    Michael

  2. #12
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,862
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Preventing duplicate records from showing in query

    Is the Service column in the last post [Service Center Council] or is it [Service Center Positions]?

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #13
    Board Regular
    Join Date
    May 2005
    Posts
    497
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing duplicate records from showing in query

    Service Center Positions - Service Center Council is the office location.

    Sorry, tried to make the headers brief.

  4. #14
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,862
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Preventing duplicate records from showing in query

    Note that you are mixing and matching a lot of criteria here:
    Code:
    WHERE
    (
    	(
    		([All SCC Data Redone].[Display Name])<>""
    		And 
    		([All SCC Data Redone].[Display Name]) Not Like "Vacant*"
    	)
    	AND 
    	(
    		([All SCC Data Redone].[Local Leadership Positions])<>"*Team Member"
    		Or 
    		([All SCC Data Redone].[Local Leadership Positions]) Is Not Null
    	)
    	AND 
    	(
    		([All SCC Data Redone].[Service Center Positions]) Is Not Null
    	)
    	AND 
    	(
    		([All SCC Data Redone].[State Council Positions]) Is Not Null
    	)
    	AND 
    	(
    		([All SCC Data Redone].[Service Center Council]) Like "Gold*"
    	)
    	AND 
    	(
    		([All SCC Data Redone].Active)=True
    	)
    )
    This makes everything hard and you can't be sure which criteria is failing.

    Scale this back. Start with a query that filters out the records where the positions are all blank. Then start putting in the remaining criteria *one by one* and checking how each one works at each step.

    Code:
    WHERE 
    (
    	(
    		([All SCC Data Redone].[Local Leadership Positions]) Is Not Null
    		AND 
    		([All SCC Data Redone].[Service Center Positions]) Is Not Null
    		AND 
    		([All SCC Data Redone].[State Council Positions]) Is Not Null
    	)
    )
    Also note that "nothing", "blank", "empty", and Null are generally confusing things (to non-experts). You fields might be blank but not be null. They can be blank if they have empty strings in them, but that is not the same as Null.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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