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