Duplicates showing up as unique

[Todd]

I've recently migrated dBaseIV tables to Access 2000 for
the Paleontology Dept. of a local museum. Many fields are
not required and are therefore blank. I just ran a query
to sum up specimen numbers for to see the totals for
genus/species entries.

SELECT CMMI.NAME, Count(CMMI.NAME) AS Records,
Sum(CMMI.SPECNO) AS Specimens
FROM CMMI
GROUP BY CMMI.NAME;

For the new records entered in Access where the NAME field
was left blank, Count(CMMI.NAME) returned 0 while
Sum(CMMI.SPECNO) returned the accurate number of specimens
- a strange result. Among the 3000+ entries that were
migrated, an accurate result for Count(CMMI.NAME) is
returned. This is a problem because these two groups with
empty NAME fields should be grouped together. Why is
Access doing this?

 

[david epsom dot com dot au]

"The Count function doesn't count records that have Null fields unless expr
is the asterisk (*) wildcard character"

Access will leave a blank field as NULL unless you specify no nulls.
Access 2000 will import NULL (missing) fields as blank strings.
So your imported data has blank strings "", and your new data has nulls.

You can use an update query to change them one way or the other,
and you may wish to change the field definition so that it does
not allow both empty string and null.

(david)