UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Preventing Duplicate Records From Being Displayed, Access 2016    
 
   
bearcublandon
post Dec 5 2017, 07:15 PM
Post#1



Posts: 4
Joined: 5-December 17



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[attachment=84664:Preventi...plicates.jpg]
Attached File(s)
Attached File  Preventing_duplicates.jpg ( 1.18MB )Number of downloads: 12
 
Go to the top of the page
 
Doug Steele
post Dec 5 2017, 07:24 PM
Post#2


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


welcome2UA.gif
What are the details of your tables?

Hopefully your database is properly designed with a Members table and a Positions table, with an intersection table to represent the many-to-many relationship between those two. If that's the case, simply report on your Members table, without worrying about the Positions table.

If that's not the case, while you're waiting to redesign it correctly, you should be able to write a query that doesn't return position information, so that Unique Value would only return one row per member.

--------------------
Go to the top of the page
 
bearcublandon
post Dec 6 2017, 01:13 PM
Post#3



Posts: 4
Joined: 5-December 17



Thank you Doug, I was able to resolve this issue by just excluding the position names. The data comes from another data base used the organization. The goal is to build around this table since everything we need is stored in that table (membership information on one record. A member will have multiple records if they hold different positions within our organization).

The next problem I have is to exlude these members since they don't have any data in these fields (local, service or state). I'll trying to figure out a way to exclude them if the member doesn't hold a local, service or state position:

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

As of now, when I the local, service and state fields are not being displayed in the query, their names appear. We need them not to appear because the directory we are publishing contains members who hold at least one of these 3 positions. Is this possible to do? This is my where clause which isn't presently working:

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

The Not Null statements are not working as expected. Am I writing something wrong? I did try the "Or" statement with an "And" but it doesn't work either. I could just manually exclude each member by name but I don't think that would be the most efficient way of doing this.

Thank you for your help.

Michael
Go to the top of the page
 
Doug Steele
post Dec 6 2017, 01:40 PM
Post#4


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Check your parentheses.

See whether this works any better:

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



--------------------
Go to the top of the page
 
bearcublandon
post Dec 6 2017, 05:19 PM
Post#5



Posts: 4
Joined: 5-December 17



I don't think it is working. The fields aren't null, they are just. The though occurred to me to make 3 different queries for unique members that then combine them into one.

I've made the unique tables and there are members that appear on 1, 2 or 3 of the queries. Which query could I use to combine this query into the query that I could see for the report? I wouldn't have a bunch of criteria in in query to include him or her exclude this position. I think this work around is the only thing that I think will work. I tried creating a 4th query but the joins were not working. I tried using the customer ID but that didn't work. So frustrating!

I'm attaching a jpeg that I copied from Excel. This is the query. The rows highlighted in yellow are the ones I need to exclude from the report. You can see the 3 fields have to have nothing in them for it to work. I'm close but still no cigar.

Thank you

Michael

Attached File(s)
Attached File  Yellow_to_exclude.jpg ( 199.54K )Number of downloads: 4
 
Go to the top of the page
 
orange999
post Dec 6 2017, 07:36 PM
Post#6



Posts: 1,713
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Try one of these for your where

A:
CODE
WHERE (([Display Name]<>"")
  AND ([Local Leadership Positions] & [Service Center Positions] & [Service Center Council] ) > "")


B:
CODE
WHERE (([Display Name]<>"")
  AND ( [Local Leadership Positions]  IS NOT NULL
  AND  [Service Center Positions] IS NOT NULL
  AND [Service Center Council] IS NOT NULL) )

This post has been edited by orange999: Dec 6 2017, 07:44 PM

--------------------
Good luck with your project!
Go to the top of the page
 
bearcublandon
post Dec 6 2017, 11:23 PM
Post#7



Posts: 4
Joined: 5-December 17



Thank you, I'LL so that. The question I have is where do I put it in the query or in the SQL statement. I've already tried putting in the appropriate query fields but seems to make the query smaller

I have thought about making unique queries and then joining them into a single query but I it seems the joings I'm using (the Member ID field) doesn't work. Thank you for your help.

Michael
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 08:01 PM