Distinctrow

[Guest]

Hi,
I am having a problem using Distinctrow. My understanding is that
distinctrow looks at all query fields and will eliminate a row if the data in
all fields is identical with any other row. I have a table that has 5384
rows and three fields. I am trying to list the items only once if they are
identical. If I use distinctrow I still get all 5384 rows. When I look at
the result I can see that there are items that are listed more than once that
have the exact same information. Here is what I see:
Jane Smith 216-687-7000 01-5a-5520
Jane Smith 216-687-7000 01-5a-5520
One of these should not have shown in my query result. I appreciate any
help I can get. Thanks.

 

[Rick Brandt]

Hi,
I am having a problem using Distinctrow. My understanding is that
distinctrow looks at all query fields and will eliminate a row if the
data in all fields is identical with any other row. I have a table
that has 5384 rows and three fields. I am trying to list the items
only once if they are identical. If I use distinctrow I still get
all 5384 rows. When I look at the result I can see that there are
items that are listed more than once that have the exact same
information. Here is what I see:
Jane Smith 216-687-7000 01-5a-5520
Jane Smith 216-687-7000 01-5a-5520
One of these should not have shown in my query result. I appreciate
any help I can get. Thanks.

DISTINCT will eliminate duplicates across all fields actually included in
the query's output. DISTINCTROW just indicates that the same actual source
rows should not be output more than once, but different rows that happen to
contain the same data will still be included in the output. Sounds like you
need DISTINCT instead.

 

[Guest]

Hi Rick,

Thanks for the help. I just tried Distinct instead and dropped 7 rows to
5377 but I am still seeing information that is identical. Within the first
32 rows I have found 9 rows that completely match 9 other rows. Here is the
SQL:

SELECT DISTINCT Combined.strName, Combined.strPhone, Combined.strMailZone
FROM Combined;

Besides going through 5377 records to delete them myself is there anything
else I can do?

 

[Rick Brandt]

Hi Rick,

Thanks for the help. I just tried Distinct instead and dropped 7
rows to 5377 but I am still seeing information that is identical.
Within the first 32 rows I have found 9 rows that completely match 9
other rows. Here is the SQL:

SELECT DISTINCT Combined.strName, Combined.strPhone,
Combined.strMailZone FROM Combined;

Besides going through 5377 records to delete them myself is there
anything else I can do?

The only thing I can think of is that the rows are "similar" but not exactly
identical. Padding on the end perhaps that you can't see?

 

[Michel Walsh]

Hi,


The DISTINCT works by considering all the SELECTed fields. So, you may get
the same strName repeated many times, but each time, it would be with a
different strPhone or a different strMailZone.



SELECT strName, LAST(strPhone), LAST(strMailZone)
FROM combined
GROUP BY strName


would list each name only once, with one of its phone and mail zone value.


DISTINCTROW, for a select, has an effect only in a join where not all the
implied tables supply a field in the SELECT clause. Otherwise, it behaves
just like a DISTINCT (for a SELECT query type).

Hoping it may help,
Vanderghast, Access MVP