CODEWITHSUNDEEP
ms-accessnullduplicates
GIBIT
GIBIT

Reputation: 97

Microsoft Access Cant Remove duplicates that contains Null value

I am having trouble removing/finding duplicates when a value in a certain column contains a null value

This was my original Criteria

    In (SELECT [PQT] FROM [Departure] As Tmp GROUP BY [PQT],[IDNUSER],[Name]
    HAVING Count(*)>1  And [IDNUSER] = [Departure].[IDNUSER] And [Name] = [Departure].[Name])

For example, lets say Name would be the column that could contain null values,

I tried replacing it with 

And IIf(IsNull([Name]), " ", [Name]) = IIf(IsNull([Departure].[Name]), " ",
[Departure].[Name])

It didn't seem to work. I also tried using NZ, with no success

Here are example datas that my table would contain

**PQT**     **IDNUSER**      **Name**  
BNC          001              Bob  
BNC          001              Bob
BNC          002              
BNC          002

Results of my current query The 2 first data with the IDNUSER 001 is considered as a duplicate since all fields have values. but IDNUSER 002 would not be considered as a duplicate since the value in Name is null. I would like my query to find all duplicates.

Upvotes: 1

Views: 1446

Answers (1)

HansUp
HansUp

Reputation: 97131

Using your sample data for Departure table, this is the output from the following SELECT query.

PQT IDNUSER user_name num_matches
BNC 001     Bob                 2
BNC 002                         2

SELECT
    d.PQT,
    d.IDNUSER,
    Nz([Name]," ") AS user_name,
    Count(*) AS num_matches
FROM Departure AS d
GROUP BY
    d.PQT,
    d.IDNUSER,
    Nz([Name]," ");

However your question mentioned removing duplicates, so I don't know what more to do at this point. I don't see how you can distinguish between the 2 Bob rows ... which one to discard and which one to keep. That would be possible if we had a unique column (such as a primary key) to distinguish between them. Alternatively you could store the output from this GROUP BY query into another table with what the Access UI calls an "append query". The pattern for such a query would be similar to this:

INSERT INTO OtherTable (
    PQT,
    IDNUSER,
    user_name,
    num_matches
    )
SELECT
    d.PQT,
    d.IDNUSER,
    Nz([Name]," ") AS user_name,
    Count(*) AS num_matches
FROM Departure AS d
GROUP BY
    d.PQT,
    d.IDNUSER,
    Nz([Name]," ");

Notice also I consolidated the two rows with IDNUSER = 002 into one row. That was my best guess as to what you wanted. However my inclination would be to require values in the Name column --- in other words prohibit Nulls. But if you need to allow the Nulls and want them handled differently, tell us what you want.

Edit: Looking at this again, I don't see a real need for the NZ() function. This version counts the matches correctly even when the groups include Null for [Name].

SELECT
    d.PQT,
    d.IDNUSER,
    [Name] AS user_name,
    Count(*) AS num_matches
FROM Departure AS d
GROUP BY
    d.PQT,
    d.IDNUSER,
    [Name];

Upvotes: 3

Related Questions