Sorting a "null" field. (1 Viewer)

John Sh

Member
Local time
Today, 19:55
Joined
Feb 8, 2021
Messages
408
I have a table with about 12k records and 90 odd fields. This is the way it has to be so please don't talk about normalisation. It has nothing to do with this problem.
I have extracted data from this table to a smaller table for the purpose of creating records.
One of the fields has some entries but is, for the most part, empty, I.e. null.
I was having a problem with a couple of double ups in this small table and found if I sort on the empty field, [Infra], the four double up records appear at the top of the table. The same behaviour is evident in the main table with these, and a few other, records. If I copy one of these records and paste it into the small table the copied record exhibits the same behavior.
I have attached a sample of the table.
If you sort on any field other than "Infra" all is normal. if you sort in Infra, "A to Z", and look at the "Family" field you will see the four records in question.
This is most obvious if you sort on "Family" then "Infra".
If I delete the "Infra" field then reinsert it in design the problem disappears.
In the main table there are 8 records, entered in sequence, displaying this same behaviour. Records before and after this group appear to be normal.
 

Attachments

  • Database1.accdb
    416 KB · Views: 89

GPGeorge

Grover Park George
Local time
Today, 01:55
Joined
Nov 25, 2004
Messages
1,775
How do you define "double up" in this context? What constitutes a "double up" record?
Have you ensured that the field in question contains Nulls and text values only? That it does not contain any Zero Length Strings (ZLS)?

To the human eye, a ZLS "looks like" a Null, but to the database engine, they are not the same.
 

plog

Banishment Pending
Local time
Today, 03:55
Joined
May 11, 2011
Messages
11,611
I have a table with about 12k records and 90 odd fields. This is the way it has to be...

Blink twice and scratch your left ear if the man with the gun is still near you and can read everything we type.

When I sort by Infra Ascending I see no "double ups". When I sort by Infra Descending I see no "double ups". Agree with Geoge--please define a "double up". Better yet, add an autonumber primary key (assuming your captors will allow that) so that you can explicitly talk about which rows are "double ups". That way you can repost your database and say ID=473 and ID=17 are "double ups" and we know what you are talking about.

Additionaly, you never talk about how data gets into your table. If there's a problem with unwanted data then that's the place to turn. Access only works on the data you give it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:55
Joined
Feb 28, 2001
Messages
26,996
If you actually somehow got NULL in those fields, they will behave differently than empty (but otherwise normal) files. That is because when comparing NULL to anything else, it is ALWAYS "not equal". Access compares nulls different than it compares anything else. The best way to correct the problem is to write an UPDATE query for those fields to change the NULL to a zero-lengthy string ("").

UPDATE table SET field = "" WHERE field IS NULL ;

Or something similar. After you do this, you get fields that sort normally.
 

John Sh

Member
Local time
Today, 19:55
Joined
Feb 8, 2021
Messages
408
Sorry folks. I know what I mean, just didn't explain it to you guys.
The best way to explain it is with an example.
Run the record, Bay5.
At the top you will see Chenopodiaceae twice, once for shelf 4 and again for shelf 3. This is as it should be.
Look further down to Dilleniaceae and you will see one entry shelf 2 boxes 1 - 4, this is correct.
Below that you will see the "double up", Dilleniaceae shelf 2 box 1. This is from the bad record.
To the best of my knowledge, all records in the Infra field are null, not "ZLS", unless populated with genuine data.
John
 

Attachments

  • Database1.accdb
    512 KB · Views: 80

plog

Banishment Pending
Local time
Today, 03:55
Joined
May 11, 2011
Messages
11,611
You absolutely have empty strings and NULLs in Infra. Give this query a shot:

SELECT Bays.Family, Bays.Infra, Bays.Bay, Bays.Shelf, Bays.Family, IIf(IsNull([infra]),"NULL","Empty") AS Empty
FROM Bays
WHERE (((Bays.Infra)="")) OR (((Bays.Infra) Is Null))
ORDER BY IIf(IsNull([infra]),"NULL","Empty") DESC;

Your specific example, Family='Dilleniaceae' has one of each. However, even when you fix that, those 2 records will not be exact duplicates--one will have FirstBox=1 and the other FirstBox=4. How do you know which one is "wrong"? And what would you like to do to fix it? Do you want to identify the "wrong" record so you can delete it? Or would you like a query to route around it?
 

GPGeorge

Grover Park George
Local time
Today, 01:55
Joined
Nov 25, 2004
Messages
1,775
Sorry folks. I know what I mean, just didn't explain it to you guys.
The best way to explain it is with an example.
Run the record, Bay5.
At the top you will see Chenopodiaceae twice, once for shelf 4 and again for shelf 3. This is as it should be.
Look further down to Dilleniaceae and you will see one entry shelf 2 boxes 1 - 4, this is correct.
Below that you will see the "double up", Dilleniaceae shelf 2 box 1. This is from the bad record.
To the best of my knowledge, all records in the Infra field are null, not "ZLS", unless populated with genuine data.
John
This still doesn't make sense to me, I'm afraid. What does it mean to say this represents a "double up"?
Explain, please, how you determine that.

And, which one is the "bad" record?

I think the issue of Null vs ZLS depends on clarifying what this part of the problem means as well.

In furtherance of the example someone offered, I also created a query to expose the existence of both Null and ZLS's in your data.

1657843282486.png
 

Attachments

  • AWF_2022_0714.zip
    28.6 KB · Views: 80

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 03:55
Joined
Feb 28, 2001
Messages
26,996
Let's try to agree on nomenclature. When you say "Double Up" - are you referring to a case where two things appear in the same place even though the physical reality is that only one "thing" can occupy a bay/shelf/box# combination? I.e. capacity should be either 0 or 1 "thing" at all times, never 2 or more? Because if that is the case, GPGeorge has identified a case where that ain't so.

If that is NOT what you meant, if there is actually a capacity greater than one for a bay/shelf/box combo, then "double up" needs to be explained better in words. Once we agree on what you mean, perhaps we can resolve the issue better.
 

John Sh

Member
Local time
Today, 19:55
Joined
Feb 8, 2021
Messages
408
You absolutely have empty strings and NULLs in Infra. Give this query a shot:



Your specific example, Family='Dilleniaceae' has one of each. However, even when you fix that, those 2 records will not be exact duplicates--one will have FirstBox=1 and the other FirstBox=4. How do you know which one is "wrong"? And what would you like to do to fix it? Do you want to identify the "wrong" record so you can delete it? Or would you like a query to route around it?
Thank you Plog.
when the records are fixed, the second Dilleniaceae record will not show as it will be encompassed in the first showing on the report.
I have identified the bad records in the main table., I will correct the problem records there, there are only 8 so not such a big job, I will convert all the empty records to ZLS.
John
 

John Sh

Member
Local time
Today, 19:55
Joined
Feb 8, 2021
Messages
408
Let's try to agree on nomenclature. When you say "Double Up" - are you referring to a case where two things appear in the same place even though the physical reality is that only one "thing" can occupy a bay/shelf/box# combination?
Eactly. The "double up", "duplication", call it what you will, only shows up in the report. It does not create a problem in the main table.
I have run the query from Plog and found there are some 20 null records in the Infra field. I will convert them to ZLS and my little problem should disappear.
Thanks to all who have responded.
John
 

Users who are viewing this thread

Top Bottom