DISTINCT Truncates Memo Field?

[WebGodiva]

I have a query where i'm concatenating several fields into one and then combining them with another query for the end result...

Every thing works fine except when I place the DISTINCT in the SELECT Statement, I get however many times the second query records (i.e., qry1 has 1 concatenated record [created from 3 original fields in a related table] - when i bring it into qry 2 [from a related table] which has 17 records, I get 51 records for the same one employee)

If I use the DISTINCT statement in my SQL, it truncates the one memo field in the record. Is there a way to work-around this?

If I use DISTINCT, I get the appropriate 17 records - without it 51. Any help would be appreciated. I've researched the issue and the only answers I've seen are from 2001 - i was hoping there was something else out there to help me out...





"Unless you try to do something beyond what you have already mastered, you will never grow." Ralph Waldo Emerson

 

[PHV]

Any chance you could post a little more info like tables structure, SQL code and why you get 3 times the resulset without the DISTINCT predicate ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

DISTINCT involves a comparison of field values and only the first 255 bytes of a memo field can be compared ... hence you get only 255 characters returned.

As PHV suggests, let us see the SQL and we may be able to figure out an alternative.

 

[WebGodiva]

thank you for the response. I believe I have worked my way around it by creating a table with the first query which requires the SELECT DISTINCT

Query where all fields from two queries are combined from two separate (related) tables - All employee information is related by Emp_No in my DB.

SELECT DISTINCT qryJoinRegsFinalConcat.JoinedReg1, qryCombineProjWMain.[SELECT], qryJoinRegsFinalConcat.Emp_No, qryCombineProjWMain.Lname, qryCombineProjWMain.OtherCerts, qryCombineProjWMain.Fname, qryCombineProjWMain.Title, qryCombineProjWMain.Job_Responsibilities, qryCombineProjWMain.School_1, qryCombineProjWMain.Year_1, qryCombineProjWMain.Degree_1, qryCombineProjWMain.Specialization_1, qryCombineProjWMain.School_2, qryCombineProjWMain.Year_2, qryCombineProjWMain.Degree_2, qryCombineProjWMain.Specialization_2, qryCombineProjWMain.School_3, qryCombineProjWMain.Year_3, qryCombineProjWMain.Degree_3, qryCombineProjWMain.Specialization_3, qryCombineProjWMain.Years_with_SITE, qryCombineProjWMain.Years_with_Other, qryCombineProjWMain.Years_Total, qryCombineProjWMain.Experience, qryCombineProjWMain.tblEngProjects_Emp_No, qryCombineProjWMain.Project_Name, qryCombineProjWMain.Project_Location, qryCombineProjWMain.Client, qryCombineProjWMain.Year_Completed, qryCombineProjWMain.Year_Built, qryCombineProjWMain.Project_Role, qryCombineProjWMain.Project_Description INTO tblMergetoWord
FROM qryJoinRegsFinalConcat RIGHT JOIN qryCombineProjWMain ON qryJoinRegsFinalConcat.Emp_No=qryCombineProjWMain.tblEngineersMain_Emp_No
WHERE (((qryCombineProjWMain.[SELECT])=On));

By adding the tbl.EngineersMain_Emp_No and then chaning the query to a make table query, I was able to get it to simply complete for the one employee. Prior to that, If one employee had 4 registrations and 17 projects, when combined somehow it would mutliply 17 x 4 and the records it would return was 68 - Basically all 17 projects once for each of four registrations (even though they were concatenated). When I had the SELECT DISTINCT - i got 17 records but Word would not except the records as a merge file. Now, by adding the tbl.EngineersMain_Emp (which is the base table in the DB), the entire query runs, makes a table and then i can pass to word for the merge.

I appreciate your help and quick response....I keep plugging and maybe someday this project will be done...if I can keep my head above water.






"Unless you try to do something beyond what you have already mastered, you will never grow." Ralph Waldo Emerson