Delete a record based on a field being null

I have some records in a field (Column A) that are duplicate.  I want to not show the duplicate records if Column B is null.  
For example, records 1-4 are the same person and column A has an email address.  I want to not show the duplicate items in when column B is null.  How can I do this in my access query?
Also, some Column B emails can already be unique, so if it unique, then show.

Thanks.
javierpdxAsked:
Who is Participating?
 
stalhwConnect With a Mentor Commented:
Personnaly I prefer LEFT JOINs to RIGHT ones, so I wrote it differently, but if we apply, the basic SQL I gave earlier to your case, I think it would be somethign like this:
SELECT L.UNIQUE_ID, L2.advEmail
FROM Listofadvs L
LEFT JOIN Listofadvs L2 ON L.adv_ID=L2.advID
LEFT JOIN aliMATNSP A ON L.EMAIL_ADDR=A.[PREFERRED EMAIL]
WHERE L2.advEmail is not null OR L.UNIQUE_ID NOT IN (
SELECT DISTINCT LL.UNIQUE_ID FROM Listofadvs LL INNER JOIN Listofadvs LL2 ON LL.adv_ID=LL2.advID WHERE LL2.advEmail is not null
)

Open in new window

There's probably a better way to do this, but it should work
0
 
stalhwCommented:
Something like the following should work:
SELECT T1.ColA, T1.ColB
FROM table1 T1
WHERE T1.ColB is not null OR T1.ColA NOT IN (
  SELECT DISTINCT ColA FROM table1 WHERE ColB is not null
)

Open in new window


So show only ColA if ColB is not null, or if there's no entry for that ColA with ColB not null

The only issue I see if if you have duplicate of ColA and everyone as ColB Null...

But since you didn't really mention that case it's hard to know what you need exactly
0
 
Jeffrey CoachmanMIS LiasonCommented:
Does this *Have* to be in a query?
This is ridiculously simple to do in a "Report" with the "Hide Duplicates" property of a control.

It is also not clear why you can't just show a unique list of "People"
...From the "People" table.

Finally it is always clearer if you post a sample of the raw data, and then post a clear graphic of the *exact* output you are expecting...

;-)

JeffCoachman
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
stalhwCommented:
And I may have to correct my query, because it's not clear from your question if column A is email or name or what?
Would be much easier if you could show the real structure of your table.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Oh, Sorry
...I seem to have misinterpreted the request of:
"Delete a record based on a field being null"
...so you can ignore my previous post...

Jeff
0
 
javierpdxAuthor Commented:
It does not need to delete.  Just not show in the query.  I'm testing it now.
Thanks.
0
 
Jeffrey CoachmanMIS LiasonCommented:
The to be sure (as  stalhw requested)
1. Post a sample of you raw data
2. Post a clear graphic of the *exact* output you are seeking, for all contingencies
0
 
javierpdxAuthor Commented:
Sorry, I should have submitted more details the first time.
The issue is I'm joining two sources of data.  The "advEmail" has some Null values. The Nulls are OK when Unique_ID is actually unique.  Otherwise, I would not like to show it.
So on the attached image, I would only want to show records 6-8.

SELECT MCDD.adv_ID, Listofadvs.advID, Listofadvs.advEmail, MCDD.FIRST_NAME, aliMATNSP.[First Name], MCDD.LAST_NAME, aliMATNSP.Last, MCDD.UNIQUE_ID, MCDD.EMAIL_ADDR, aliMATNSP.[PREFERRED EMAIL], aliMATNSP.UID, aliMATNSP.Middle
FROM aliMATNSP RIGHT JOIN (Listofadvs RIGHT JOIN MCDD ON Listofadvs.advID = MCDD.adv_ID) ON aliMATNSP.[PREFERRED EMAIL] = MCDD.EMAIL_ADDR;

Open in new window

duplicateData.JPG
0
 
javierpdxAuthor Commented:
Thank you.  I appreciate the help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.