Blanks Screwing Up Remove Duplicates Query

  • Thread starter Thread starter Marc
  • Start date Start date
M

Marc

Hi. I am trying to remove duplicates from a table. I have
set up my 5 column table with all 5 columns together as
the primary key and then am doing an append query to
append the unique records to a new table.

The problem is that wherever a blank cell exists in my
original data exists, that entire row is NOT returned as a
unique record. Thus, I am losing a ton of records that I
need.

How do I deal with these blanks?

Thanks...Marc
 
I'm assuming by blanks you are seeing null fields. The way Access deals
with nulls is a pain in the A** as far as I am concerned. I wish there
was a set up in Access to make null blank and empty fields all compare
equal, much like Paradox does. Programming would be SO much easier.

OK, I'm off the soap box now... Are you getting an error? like "key
cannot contain nulls" or something like that?
 
Hi. I am trying to remove duplicates from a table. I have
set up my 5 column table with all 5 columns together as
the primary key and then am doing an append query to
append the unique records to a new table.

The problem is that wherever a blank cell exists in my
original data exists, that entire row is NOT returned as a
unique record. Thus, I am losing a ton of records that I
need.
Click to expand...

A Primary Key cannot contain any NULL fields - I suspect these records
are being rejected for that reason, not because they're duplicates!

I'd suggest instead using the "Unique Values" property on your append
query to select only one record from each set of duplicates (after
removing the primary key from the target table).
 
I'm assuming by blanks you are seeing null fields. The way Access deals
with nulls is a pain in the A** as far as I am concerned. I wish there
was a set up in Access to make null blank and empty fields all compare
equal, much like Paradox does. Programming would be SO much easier.
Click to expand...

There is:

Set the Required property of each such Text field to True, its Allow
Zero Length String to True, and its Default property to "".
 
Thanks - I wasn't aware of that and will use it in the future.

My comment still holds true. Its a pain to have to do that for each
field when other languages have it as a default.
 
Back
Top