A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

SELECT DISTINCT query is not deduplicating



 
 
Thread Tools Display Modes
  #1  
Old June 26th, 2008, 11:06 AM posted to microsoft.public.access.queries
shannonsider
external usenet poster
 
Posts: 4
Default SELECT DISTINCT query is not deduplicating

Hi,

I am running a 'SELECT DISTINCT' query that is not deduplicating. I am using
Microsoft Access 2000.

I have two tables; one is called 'places' and the other is called
'description'.

Places has three fields called description, place_no, and place. This is how
it looks.

place_no place description
2696 Dún Mór 4313
22697 Dunmore 4313
22699 Dunmore 4313
22700 Downamore 4313
22701 Dunmore 4313
22702 Dunmore 4313

The second table is called description; it has two fields called
description_no, and situation.

It looks like this.

description_no situation
4313

The query

SELECT DISTINCT places.place, description_no, situation
FROM places LEFT JOIN description ON
places.description=description.description_no
WHERE place='dunmore'
ORDER BY places.place;

is returning

place description_no situation
Dunmore 4313
Dunmore 4313
Dunmore 4313
Dunmore 4313

It is not deduplicating despite the 'SELECT DISTINCT'.

I can do three things to make it work.

1. Remove the 'ORDER BY places.place' clause
2. 'description_no' in the description table is a primary key. If I change
this it works
3. If I put any value into the situation field rather than leave it null.

None of these are great options.

I am wondering what is going on?

Any help would be much appreciated?

Regards
John
  #2  
Old June 26th, 2008, 12:56 PM posted to microsoft.public.access.queries
scubadiver
external usenet poster
 
Posts: 1,673
Default SELECT DISTINCT query is not deduplicating


Q1) On what fields do you want the rows to be based?

Q2) I presume you have a 1-to-many relationship between 'places' and
'description'?

"shannonsider" wrote:

Hi,

I am running a 'SELECT DISTINCT' query that is not deduplicating. I am using
Microsoft Access 2000.

I have two tables; one is called 'places' and the other is called
'description'.

Places has three fields called description, place_no, and place. This is how
it looks.

place_no place description
2696 Dún Mór 4313
22697 Dunmore 4313
22699 Dunmore 4313
22700 Downamore 4313
22701 Dunmore 4313
22702 Dunmore 4313

The second table is called description; it has two fields called
description_no, and situation.

It looks like this.

description_no situation
4313

The query

SELECT DISTINCT places.place, description_no, situation
FROM places LEFT JOIN description ON
places.description=description.description_no
WHERE place='dunmore'
ORDER BY places.place;

is returning

place description_no situation
Dunmore 4313
Dunmore 4313
Dunmore 4313
Dunmore 4313

It is not deduplicating despite the 'SELECT DISTINCT'.

I can do three things to make it work.

1. Remove the 'ORDER BY places.place' clause
2. 'description_no' in the description table is a primary key. If I change
this it works
3. If I put any value into the situation field rather than leave it null.

None of these are great options.

I am wondering what is going on?

Any help would be much appreciated?

Regards
John

  #3  
Old June 26th, 2008, 02:54 PM posted to microsoft.public.access.queries
Michel Walsh
external usenet poster
 
Posts: 2,404
Default SELECT DISTINCT query is not deduplicating

SELECT DISTINCT works on the SELECTED fields, note the plural form, not on
ONE field you have "in mind".


If you use

SELECT DISTINCT primaryKeyField, someOtherField FROM ...

definitively, since each primaryKeyField value different, the DISTINCT won't
do much. Each record will be returned, even if you have in mind, distinct
someOtherField only.


If you use

SELECT DISTINCT f1, f2 FROM somewhere

then each possible COUPLE {f1, f2} would be kept only once. It does not
return only DISTINCT {f1} and DISTINCT {f2}. It returns DISTINCT {f1,
f2}, as couple. As example, {4, 5} and {4, 6} are distinct, even if 4 is
repeated twice... that does not matter, it is the couple, like {FristName,
LastName} that defines distinct people, not just their first name, not just
their last name.



In the case of NULL, a NULL is NOT equal to another NULL. A null is not
different than another NULL. So, question, are two NULL ... distinct ?

In theory, by the SQL standard, two nulls are NOT distinct, but, to make a
story short, there are cases where Jet is buggy about it. Use a GROUP BY to
avoid the problem:

SELECT f1, f2 FROM somewhere GROUP BY f1, f2

should NOT duplicate null, while it is possible to have duplicated nulls
with

SELECT DISTINCT f1, f2 FROM somewhere

but not always. Generally, it got it right, but if one of the field has an
index not allowing duplicated value, then the SELECT DISTINCT will wrongly
assume that no duplicate == distinct. It is not. A null does not duplicate
another null, but a null is not distinct to another null. Two different
concepts: duplication (based on equal) and distinct (based on grouping),
that Jet specifications mixed up in that case. Note that MS SQL Server 2005
and previous, also messed up the definition, since it does not, wrongly,
allow two null under an no dup index.



Vanderghast, Access MVP



"shannonsider" wrote in message
...
Hi,

I am running a 'SELECT DISTINCT' query that is not deduplicating. I am
using
Microsoft Access 2000.

I have two tables; one is called 'places' and the other is called
'description'.

Places has three fields called description, place_no, and place. This is
how
it looks.

place_no place description
2696 Dún Mór 4313
22697 Dunmore 4313
22699 Dunmore 4313
22700 Downamore 4313
22701 Dunmore 4313
22702 Dunmore 4313

The second table is called description; it has two fields called
description_no, and situation.

It looks like this.

description_no situation
4313

The query

SELECT DISTINCT places.place, description_no, situation
FROM places LEFT JOIN description ON
places.description=description.description_no
WHERE place='dunmore'
ORDER BY places.place;

is returning

place description_no situation
Dunmore 4313
Dunmore 4313
Dunmore 4313
Dunmore 4313

It is not deduplicating despite the 'SELECT DISTINCT'.

I can do three things to make it work.

1. Remove the 'ORDER BY places.place' clause
2. 'description_no' in the description table is a primary key. If I change
this it works
3. If I put any value into the situation field rather than leave it null.

None of these are great options.

I am wondering what is going on?

Any help would be much appreciated?

Regards
John



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:01 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.