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

 

[bearcub]

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

 

[xenou]

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

 

[bearcub]

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

Sorry, tried to make the headers brief.

 

[xenou]

Note that you are mixing and matching a lot of criteria here:

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.

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.