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
    522
    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,984
    Post Thanks / Like
    Mentioned
    4 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
    522
    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,984
    Post Thanks / Like
    Mentioned
    4 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

Some videos you may like

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
  •