asked on

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.

stalhw

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

Jeffrey Coachman

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

stalhw

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.

Jeffrey Coachman

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

javierpdx

ASKER

It does not need to delete. Just not show in the query. I'm testing it now.
Thanks.

Jeffrey Coachman

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

javierpdx

ASKER

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

ASKER CERTIFIED SOLUTION

stalhw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

javierpdx

ASKER

Thank you. I appreciate the help.