?
Solved

Delete a record based on a field being null

Posted on 2012-08-24
9
Medium Priority
?
593 Views
Last Modified: 2012-08-28
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.
0
Comment
Question by:javierpdx
  • 3
  • 3
  • 3
9 Comments
 
LVL 8

Expert Comment

by:stalhw
ID: 38330930
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38330936
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
 
LVL 8

Expert Comment

by:stalhw
ID: 38330953
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38331118
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
 

Author Comment

by:javierpdx
ID: 38331169
It does not need to delete.  Just not show in the query.  I'm testing it now.
Thanks.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38331182
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
 

Author Comment

by:javierpdx
ID: 38331394
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
 
LVL 8

Accepted Solution

by:
stalhw earned 2000 total points
ID: 38336705
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
 

Author Closing Comment

by:javierpdx
ID: 38341737
Thank you.  I appreciate the help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question