Preventing duplicate records from showing in query

[bearcub]

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

 

[Micron]

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.

 

[bearcub]

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

 

[Micron]

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.

 

[bearcub]

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

 

[Micron]

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.

 

[bearcub]

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?

 

[xenou]

Your SQL can be:

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:

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)

 

[bearcub]

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

 

[Micron]

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.