DISTINCT function 1

[Lost500]

Hi all,
I am trying to just get unique values in my query. however when i use the DISTINCT function in the query i get an error saying one or more of the fields are not comparable. i do have a unique ID field can i place the function somewhere else in the query or use something else to just look at the ID field and return just the unique records? any help would be great.

 

[PHV]

What is the SQL code raising the error ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Does your query contain any memo, OLE Object, or hyperlink fields? These won't work with DISTINCT.

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

The code is:
SELECT DISTINCT fieldname, fieldname, ect.
FROM Tablename INNER JOIN Tablename ect.


I am sure that I have a memo field in here but is there any way to use the Unique id field to get rid of duplicates and still include the fields i want in my query? Thaks for the help

 

[lespaul]

the pseudo SQL isn't what we'd like to see, we'd really like to see the ACTUAL SQL....additionally it would be helpful if you gave us some sample data and your expected results.

Thanks,

Leslie

Have you met Hardy Heron?

 

[dhookom]

You can't get rid of duplicates with a memo field. You can reformat it as a text field using

Left([Your Memo Field],255)

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

thanks dhookom! I think i'll try to convert all my memo fields any advice as to what to convert them to?

 

[dhookom]

You don't need to change your table structure unless you really only need to store less than 256 characters. If this is the case, change the field to TEXT 255. If you need to store more than 255 characters, as I stated, you can use the Left() function to change the appearance of the value so that you can use DISTINCT or GROUP BY.

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

Thanks dhookom but i get the error, [Microsoft][ODBC SQL Serber Driver][SQL Server]Argument data type ntext is invalid for argument 1 of left function. (#8116), any advice?

 

[dhookom]

I missed any mention earlier of this being an SQL Server table ;-). Is this an ADP or MDB? Is this a pass-through or regular query?

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

It is a MDB and a regular query.

 

[dhookom]

Since you probably want to open this read-only, I would create a pass-through query with SQL like:

SELECT DISTINCT [info_id]
       ,Convert(VarChar(255),[minutes]) as Minutes
      ,[create_dte]
      ,[create_by]
      ,[apprv_dte]
      ,[apprv_by]
      ,[post_dte]
      ,[post_by]
      ,[Team]
      ,[MeetDate]
  FROM [meeting_minutes]

You could enter a number up to nearly 8000 depending on the number of characters in other fields.

Duane
Hook'D on Access
MS Access MVP

 

[Lost500]

thanks dhookom. worked great!