select distinct doesn;t work

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello al

I "select distinct field1, field2, field3" in access, where field3 is a short time datatype. It doesn't work. It returns all rows in "order by field3" sort

If I remove the "time" field, the select distinct query works just fine. It selects unique "field1" records as expected. What's wrong with me

Thank You
 
Hello all

I "select distinct field1, field2, field3" in access, where field3 is a short time datatype. It doesn't work. It returns all rows in "order by field3" sort.

If I remove the "time" field, the select distinct query works just fine. It selects unique "field1" records as expected. What's wrong with me?

Thank You
Click to expand...

Just because you've got it set as short time for display doesn't mean
that Access isn't storing the full date/time as part of the field.

What you can do is force the result by using a format to ensure that
the rest of the data is non existant.

SELECT DISTINCT [Field1], [Field2], Format([Field3],"Short Time") AS
NewTime
FROM [YourTable];
-D
 
DISTINCT applies to the combination of ALL the fields in the SELECT clause.

What are you trying to do? If you want any one of the times in field3 then
perhaps a group/totals query would work.

SELECT Field1, Field2, Max(Field3) as LastTime
FROM TheTable
GROUP BY Field1, Field2
 
Back
Top