Solved Find duplicates while ignoring nulls (1 Viewer)

[tmyers]

I used the wizard to create a find duplicate query:

SELECT dbo_tblTicketDetails.[SOLineID], dbo_tblTicketDetails.[ID], dbo_tblTicketDetails.[Account #], dbo_tblTicketDetails.[Customer #], dbo_tblTicketDetails.[Customer Name], dbo_tblTicketDetails.[Account Name], dbo_tblTicketDetails.[Sales Register #], dbo_tblTicketDetails.[Invoice Date], dbo_tblTicketDetails.[BillingMonthDate], dbo_tblTicketDetails.[Written By], dbo_tblTicketDetails.[Mfr], dbo_tblTicketDetails.[CatalogNo], dbo_tblTicketDetails.[LineType], dbo_tblTicketDetails.[UPC], dbo_tblTicketDetails.[QTY Ship], dbo_tblTicketDetails.[Price], dbo_tblTicketDetails.[Price UOM], dbo_tblTicketDetails.[Extened Price], dbo_tblTicketDetails.[Cost], dbo_tblTicketDetails.[Cost UOM], dbo_tblTicketDetails.[Extended Cost], dbo_tblTicketDetails.[SPA Cost], dbo_tblTicketDetails.[SPA Cost UOM], dbo_tblTicketDetails.[Extended SPA Cost], dbo_tblTicketDetails.[PC #], dbo_tblTicketDetails.[Line #], dbo_tblTicketDetails.[Inside Sales #], dbo_tblTicketDetails.[SOID], dbo_tblTicketDetails.[Outside Sales #], dbo_tblTicketDetails.[Billing Day], dbo_tblTicketDetails.[Order Type], dbo_tblTicketDetails.[Customer PO #]
FROM dbo_tblTicketDetails
WHERE (((dbo_tblTicketDetails.[SOLineID]) In (SELECT [SOLineID] FROM [dbo_tblTicketDetails] As Tmp GROUP BY [SOLineID] HAVING Count(*)>1 )))
ORDER BY dbo_tblTicketDetails.[SOLineID];

I am not super versed in SQL outside of simple statements, but how could I add to the WHERE clause for it to exclude records where [SOLineID] is null?

 

[theDBguy]

Hi. I'm not sure what you are asking but how about?

WHERE (((dbo_tblTicketDetails.[SOLineID]) In (SELECT [SOLineID] FROM [dbo_tblTicketDetails] As Tmp WHERE [SOLineID] Is Not Null GROUP BY [SOLineID] HAVING Count(*)>1 )))

 

[arnelgp]

you can also save your original query and create new query out of it:

select orig_query.* from orig_query where Not (orig_query.SOLineID Is Null);

 

[tmyers]

That is what I ended up doing Arnel. The additional SQL statement in the WHERE clause is what threw my off for a minute. Also doesn't help I am bouncing between many languages this morning (DAX, M, VBA and SQL).