Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Preventing duplicate records from showing in query

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

    Default Preventing duplicate records from showing in query

    I have query where one member can hold different positions within our organization but I only want their name to appear once in the query-- it'ss being used to generate a report. I did change unique values to yes on the query property sheet but I don't think this situation would apply since they are different records with different values

    The names are being sent to published in a directory and we want there name to only appear once (the positions they hold is immaterial).

    I unpivoted the old table in Powerquery and then exported it back into Access because the old table used to hold multiple positions in one field record as opposed to multiple records for one person with different positions.

    For example,

    John Smith is a president and State council member. In my query, I have a record as a president and a State Council Member (because they are different records). The position is not going to be shown in the directory, only the John Smith.

    Is there a way in access to show only the first occurrence for the member (like vlookup will only find the first member and not the second one)?

    Thank you for your help,

    Michael

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,599
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing duplicate records from showing in query

    I don't think this situation would apply since they are different records with different values
    That's exactly what unique values setting is for, but it will perform this against every field you include. So one way is to create a query that returns only the unique values on one field, then use that query in the other query but only use the field from the unique query, not the second one. If that field can contain nulls, then the query should prevent returning those records. Not sure if that will work for you without seeing the rest of the related fields.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

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

    Default Re: Preventing duplicate records from showing in query

    I think I get what you're driving it.

    Thank you for the suggestion. In the query I was showing all the positions which created dups. I have them in the query but have the show box unchecked. When i run the query all I get is the duplicates. However, I have another problem because I have members who are not officers and show not be on the report. I was thinking of creating a nested in to say that if a position for a member is null in the local, State or Service Council fields then exclude them. But, I don't know if Access will accept this or if it does.

    Any suggestions how I exclude members who do not have data in all 3 fields (local, state, Service Center)?

    Thank you for your help,

    Michael

  4. #4
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,599
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing duplicate records from showing in query

    Any suggestions how I exclude members who do not have data in all 3 fields (local, state, Service Center)?
    Put Is Not Null in each applicable field. Just guessing here without seeing data and query. I should have been more clear on the first reply. TableA, Field1 is used to create the unique values query, qryUnique. You add qryUnique the same way you add any table to a second query, plus add TableA. You equal join Field1 of qryUnique to Field1 of TableA, then bring in whatever other fields you need from TableA. If you need Field1 in the second query, get it from qryUnique.

    If that doesn't get you to a solution, it would be better if you posted some sample data. Suggest creating small table in a spreadsheet and paste into your post. You should get a nice html type table. I find it's best to center all the data before copying.

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

    Default Re: Preventing duplicate records from showing in query

    I was thinking along these lines. If I put your critieria in the 3 fields it creates an And statement. My goal is to create an And criteria that covers 3 fields. I'll try that and will let you know what happens. Otherwise, I'll have to create some sort of unique query as you suggest.

    Michael

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,599
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing duplicate records from showing in query

    If I put your critieria in the 3 fields it creates an And statement. My goal is to create an And criteria that covers 3 fields.
    I'm not following. I thought what I posted was the answer to your question, but maybe the question isn't really how I exclude members who do not have data in all 3 fields (local, state, Service Center) but is really how do I exclude members who do not have data in at least 1 of 3 fields? In the second case, then yes, a multiple AND condition probably wouldn't work. You may have to create outer joins between tables. Again, just guessing since I have little knowledge of what you have query-wise.

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

    Default Re: Preventing duplicate records from showing in query

    What I am trying to achieve is get members that have nothing in all 3 fields. These are members who don't hold any positions in the organization.
    Wouldn't that And across those 3 field exclude members who don't hold a position?

    Should I post a snapshot of what I'm looking at?

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

    Default Re: Preventing duplicate records from showing in query

    Your SQL can be:

    Code:
    Select * 
    From Table
    Where Not ([Field1] Is Null Or [Field2] Is Null Or [Field3] Is Null)
    So much easier to write SQL!

    In the query builder, Access converts this by adding a new column to the query, with the Show box unchecked, the criteria set to False, and the Field value at the top is "[Field1] Is Null Or [Field2] Is Null Or [Field3] Is Null"

    ----------------------

    Note:
    That's for the case of at least one null.

    If you want only exclusion where all the fields are not null then just reverse the logic more or less:
    Code:
    Select * 
    From Table
    Where ([Field1] Is Not Null AND [Field2] Is Not Null AND [Field3] Is Not Null)
    In the query builder, this is the same as just putting Is Not Null in the criteria line for each of these three fields (using the same row)
    Last edited by xenou; Dec 6th, 2017 at 12:34 PM.

    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

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

    Default Re: Preventing duplicate records from showing in query

    Thank you.

    This is a snapshot of what I'm trying to:

    Name Local Service State
    Norma
    Jennifer
    Elana
    Lily
    David
    Vernon
    Eric
    Sophie
    Dr.
    Elaine
    Michael
    Mike
    Princess
    Joe
    Ryan
    Ara
    Becky
    John
    Julius
    Rebekah


    All the other members have at least one position in Local, Service or State positions. Since these members don't have any text in any of these fields, they should be excluded from the list.

    This is my query Where clause which isn't working for the Local Leadership, Service Center Positions and State Council positions.

    The other criteria except for these three work correctly. I don't know where to put these three exclusion clauses to exclude the members in the abve table. I could just exclude them individually but I'm thinking there has to be a more efficient way to do this.



    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));

  10. #10
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,599
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Preventing duplicate records from showing in query

    I can't work with that, plus this seems to have gone from a unique values problem to one of Nulls. I suggested sample data in post 4 but you've elected not to provide it (along with a sample of the desired outcome, which I forgot to mention). So you have a few suggestions you can try instead. Another one I'll make is to group when you mix AND with OR. Using a snippet of your sql as an example, is it

    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))


    or is it

    (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)

    there is a difference.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

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
  •