Problem with SELECT DISTINCT - still returns duplicates

[Ben2k]

I have the following SQL statement

SELECT DISTINCT CARS.[Stock Number], CARS.Make, CARS.Model, CARS.Registration, CARS.ReCon1, CARS.[RC Cost1], CARS.ReCon2, CARS.[RC Cost2], CARS.ReCon3, CARS.[RC Cost3], CARS.ReCon4, CARS.[RC Cost4], CARS.ReCon5, CARS.[RC Cost5], CARS.ReCon6, CARS.[RC Cost6], CINVOICE.Date, [rc cost1]+[rc cost2]+[rc cost3]+[rc cost4]+[rc cost5]+[rc cost6] AS Expr1
FROM CARS INNER JOIN CINVOICE ON CARS.[Stock Number]=CINVOICE.[Stock Number]
WHERE (((CINVOICE.Date) Between Forms!Form2!start And forms!form2!end) And ((CARS.Status)="sold"));

I added the DISTINCT keyword myself however the query still returns duplicates, do I need to add it anywhere else? I want only one of any Stock Number in my query.

Thanks

 

[Joe4]

The problem is that DISTINCT selects distinct combination of every field in your select clause, not just the Stock Number.

Something like:
SELECT DISTINCT CARS.[Stock Number]
FROM...
WHERE...

would return distinct Stock Numbers only (if Stock Number was the ONLY field in you select statement).

The expression:

SELECT DISTINCT CARS.[Stock Number], CARS.Make, CARS.Model, CARS.Registration, CARS.ReCon1, CARS.[RC Cost1], CARS.ReCon2, CARS.[RC Cost2], CARS.ReCon3, CARS.[RC Cost3], CARS.ReCon4, CARS.[RC Cost4], CARS.ReCon5, CARS.[RC Cost5], CARS.ReCon6, CARS.[RC Cost6], CINVOICE.Date, [rc cost1]+[rc cost2]+[rc cost3]+[rc cost4]+[rc cost5]+[rc cost6] AS Expr1

selects distinct combinations of all the fields you have listed above in the Select statement.

 

[Ben2k]

Thanks,

Is there anyway around it then?

Ben

 

[Joe4]

Without really knowing what you are trying to accomplish, I would say you might have to break it up into a few queries. First, create a query that does your SELECT DISTINCT on the Stock Number field. Then use that query in another query to add back in the other fields. You may need to use Grouping (click on the Totals icon in your query) to select the First, Last, Sum, or whatever to ensure that you still only end up with one record per Stock Number.