Duplicates that have no value (1 Viewer)

kitty77

Registered User.
Local time
Today, 15:45
Joined
May 27, 2019
Messages
693
I created a query "Find Duplicates" with the wizard.
It finds duplicates, exactly what I'm looking for but it also finds duplicates for records that have no value in that field. Not sure why it returns those records. I have hundreds of records with blank values in that field but it seems to pick out a few. I check those records and don't know why it would be returning that record.

Has anyone encountered this?
 

Micron

AWF VIP
Local time
Today, 15:45
Joined
Oct 20, 2018
Messages
3,476
My first though is that the field for those records aren't "blank" at all. Perhaps they contain empty strings. If the table field in question does not allow empty strings (allow zero length property is 'no'), then forget that idea. If they are allowed and you're not sure about those fields, you can create a select query on that table and specify "" as criteria for that field. Include the PK in the query so you can see which records contain zls. If no results, that isn't the issue.

Second is that your query looks for dupes in other fields, or also pulls in null values. You might have to post the query sql as a start. Sample data might help too.
 

kitty77

Registered User.
Local time
Today, 15:45
Joined
May 27, 2019
Messages
693
Ok, I created a select query with "" for that field and sure enough, it returned the same records that the duplicate query found.

But when I look at those records, no value is in it?

So, what does this mean?

Thanks...
 

jdraw

Super Moderator
Staff member
Local time
Today, 15:45
Joined
Jan 23, 2006
Messages
15,364
kitty77,

We know nothing of your application nor query(s) based on this thread. Since it is your database, you know more about the subject matter and the environment you work in.
What is the database about in general?
What is the meaning/definition of the field you are checking for duplicates?
What are the typical values you'd expect in that field?
What does a non-entry/blank/"" signify to you?

Readers do not have nearly as much info as you do regarding your database, so need to get some info from you in order to offer focused responses.
Good luck.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:45
Joined
Oct 29, 2018
Messages
21,358
Ok, I created a select query with "" for that field and sure enough, it returned the same records that the duplicate query found.

But when I look at those records, no value is in it?

So, what does this mean?

Thanks...
Hi Kitty. What you see and what the computer sees can be two different things. A "" is an empty string and it has a value to the computer although it's invisible to the human eyes. So, all those records are duplicates because they all have "" in them instead of nulls. Nulls and "" are two different things to the computer, but they both look like "nothing" to us. If you replace the "" with Null, then the computer won't consider them as duplicates because Null cannot be equal to anything.
 

Micron

AWF VIP
Local time
Today, 15:45
Joined
Oct 20, 2018
Messages
3,476
Ok, I created a select query with "" for that field and sure enough, it returned the same records that the duplicate query found.

But when I look at those records, no value is in it?

So, what does this mean?

Thanks...
If you knew what a zls was you wouldn't ask that. That ought to inspire you to research it before posting back. For me, questions are
- how do they get there (imported data?)
- do you simply convert them to Null from time to time
- do you set table field to not allow (errors likely will occur)
An update query will fix your immediate issue. Set the field to Null Where Field = ""
 

kitty77

Registered User.
Local time
Today, 15:45
Joined
May 27, 2019
Messages
693
So, how to create a query that checks for real duplicates and the "null"

This is what I have using the duplicate wizard...

In (SELECT [Msamplenumber1] FROM [Main] As Tmp GROUP BY [Msamplenumber1] HAVING Count(*)>1 )
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:45
Joined
Oct 29, 2018
Messages
21,358
So, how to create a query that checks for real duplicates and the "null"

This is what I have using the duplicate wizard...

In (SELECT [Msamplenumber1] FROM [Main] As Tmp GROUP BY [Msamplenumber1] HAVING Count(*)>1 )
I take it this means you can't "clean up" the data. Is that correct? If so, you could try the following:


Code:
In (SELECT [Msamplenumber1] FROM [Main] As Tmp [B]WHERE [Msamplenumber1]<>""[/B] GROUP BY [Msamplenumber1] HAVING Count(*)>1 )
Hope it helps...
 

kitty77

Registered User.
Local time
Today, 15:45
Joined
May 27, 2019
Messages
693
I would like to clean up the data but not sure how? Is there a way to show "" vs null or blank?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:45
Joined
Oct 29, 2018
Messages
21,358
I would like to clean up the data but not sure how? Is there a way to show "" vs null or blank?
Hi. Not sure what you mean by "show." An empty string and a null value "looks" the same to the human eyes - nothing. So, what did you want to see? Otherwise, if you want to see the records with empty strings, then you could use a criteria like:


[FieldName]=""


Or, if you want to see the records with null values, then the criteria could be:


[FieldName] Is Null
 

kitty77

Registered User.
Local time
Today, 15:45
Joined
May 27, 2019
Messages
693
Ok, so once I find the records with "" how do I fix or change them to null?
 

Micron

AWF VIP
Local time
Today, 15:45
Joined
Oct 20, 2018
Messages
3,476
I would like to clean up the data but not sure how? Is there a way to show "" vs null or blank?
I answered that in post 6. Update "" to Null. If you haven't turned off warnings, you'll get a prompt to confirm updating for x records. Since you already seem to know how many are zls, the number in the confirmation message ought to be the same.
Afterwards, your duplicates query won't find them because as was already pointed out, Null is not equal to anything - it is, in fact, unknown. Thus it won't show up as a duplicate.
EDIT - I see posts are crossing, so I should ask if you know how to create an update query...
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:45
Joined
Oct 29, 2018
Messages
21,358
Ok, so once I find the records with "" how do I fix or change them to null?
Hi Kitty. I'll let Micron answer that question, but did you try the modified SQL statement I gave you in post #8? Just curious...
 

Micron

AWF VIP
Local time
Today, 15:45
Joined
Oct 20, 2018
Messages
3,476
I'm shutting down for the night so based on post 7 I'd say
Code:
UPDATE [Main] SET [Main].[Msamplenumber1] = Null WHERE [Main].[Msamplenumber1] = "";
Translation - in a field named Msamplenumber1 in a table named Main, change any zero length strings to Null.

If that is incorrect, don't run it. Regardless, always test suggested changes on copies of your db objects as a precaution.

I edited (added [] around the 1st Main) although I don't think they are required anywhere but on the field name, which contains numbers, which IMHO it should not.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 19:45
Joined
Sep 12, 2006
Messages
15,614
Just as a nasty little catch related to this. If you have a mutli-field unique index, you can "duplicate" records if part of the index is null.

eg, an order item file with key something like.

order line, required date

if you have a business rule where
if there is a date, then a delivery has to be for that date only
if the date is blank/null, then a delivery request can be for any date.

You will find you can duplicate rows with the null date, which won't be what you intended or expected, I imagine.
 

Micron

AWF VIP
Local time
Today, 15:45
Joined
Oct 20, 2018
Messages
3,476
GTH - that's a case where you probably should store zls instead of allowing null.
You cannot have 2 records with AAA in 1st field and zls in the other field of a composite index that doesn't allow dupes.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:45
Joined
Feb 19, 2002
Messages
42,973
Fields that are part of a compound unique index should NEVER be allowed to be null. Personally i prefer to not deal with ZLS either so I also don't allow ZLS in any text fields. If I want customerName to be required, then "" should NOT be valid.
 

Mark_

Longboard on the internet
Local time
Today, 12:45
Joined
Sep 12, 2017
Messages
2,111
@ OP,
One way you CAN be getting ""s in your table is by having an end user enter a value THEN decide to blank it out. If this is something an end users can do you you DON'T want ""s but want NULL instead, you'd need to put some code in the before update for that control to change "" to NULL.
 

Micron

AWF VIP
Local time
Today, 15:45
Joined
Oct 20, 2018
Messages
3,476
@ OP,
One way you CAN be getting ""s in your table is by having an end user enter a value THEN decide to blank it out.
I have tried that many times. The end result has always been Null, not zls. Do you have an example?
 

Mark_

Longboard on the internet
Local time
Today, 12:45
Joined
Sep 12, 2017
Messages
2,111
I have tried that many times. The end result has always been Null, not zls. Do you have an example?

Have to go back and look. I remember hitting this about 7 years ago when people were blanking out values/typing over data. I remember it mostly because there were values that were supposed to be NULL that weren't and it was giving me a headache for reporting.
 

Users who are viewing this thread

Top Bottom