Select Distinct returning duplicates (1 Viewer)

[fat controller]

I have a combo box which has the following query as its source:

SELECT DISTINCT SafeDriving.ID, SafeDriving.SDYear
FROM SafeDriving;

The field SDYear is a four digit number to represent a year, and is stored as a number. I am trying to get the combo to show only one instance of each year/number even although there may be more than one in the table - however, I am still seeing duplicates; have I missed something?

 

[Ranman256]

if ID is an autonum, it will never be distinct.
Remove that field from the query and you should get what you want.

 

[fat controller]

Thank you - I feel such a muppet for not spotting that one - Doh!

Cheers

 

[fat controller]

If I remove the ID, I get absolutely nothing in the combo - am I going about this the hard way?

 

[Minty]

Possibly - Are you storing the ID or the year?

If it's the ID and there are multiples you need to determine which ID you want - earliest or latest ? and modify the query accordingly probably using a Max() or Min()

 

[fat controller]

I am not storing it at all - only using it to set a variable to then run a report.

Here is the SQL as it stands:

SELECT DISTINCT SafeDriving.ID, Min(SafeDriving.SDYear) AS MinOfSDYear
FROM SafeDriving
GROUP BY SafeDriving.ID;

If I remove SafeDriving.ID, the combo is blank

 

[Minty]

I'll re-word - which column is the bound column - the Year or the ID? If all you want is the unique years in your data then simply use

SELECT SDYear
FROM SafeDriving
GROUP BY SDYear;

 

[fat controller]

Thank you - I had the bound column as 1 initially, then when I last tried to remove the ID field, I was getting a blank box; of course, I still had to change the properties to 1 column instead of 2, and then delete the column size for column 0 which was set to 0cm to hide the ID......

All working perfectly now, thank you