Preventing duplicate records from showing in query

bearcub

Well-known Member
Joined
May 18, 2005
Messages
701
Office Version
  1. 365
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
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
 
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
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Is the Service column in the last post [Service Center Council] or is it [Service Center Positions]?
 
Upvote 0
Service Center Positions - Service Center Council is the office location.

Sorry, tried to make the headers brief.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top