Select Distinct with Memo item.

[Dummy99]

I have
TableA: Key = Auto Number
Ans = Memo
TableB: KeyWord = Text
KeyId = Number (equal to TableA.Key)
Misc = Text
QueryA: Combines the above two with a join on Key and KeyId

I want to display Ans based on the users choice of one or more KeyWord but when more than one KeyWord, if the Ans is the same, I want to display the Ans only once.

HOW TO DO IT? That isssss the question.

I do a Select Query on QueryA as follows:

strSQL = "SELECT * FROM QueryA & choice

choice = a pre-developed WHERE clause, e.g.,
WHERE (((QueryA.KeyWord) = 'sample')) or (((WHERE (Query.keyWord = 'demo')) and QueryA.Ans and Query.AKeyId

This is followed by:

Set qdf = dbs.CreateQueryDef("QueryB", strSQL)

This works fine. It selects all the records needed into QueryB. The next step is where I have problems. Because there are records which could have the same Ans and KeyId I want to display only one of them but if I try to do a DISTINCT selection, I get the error message: "3114 Can't include Memo or OLE Object when you select unique values (Not QueryB.KeyId=0 And Not QueryB.Ans=0)"

My DISTINCT selection is:

strSQL = "SELECT DISTINCT QueryB.KeyId and QueryB.Ans FROM QueryB"

and is followed by:

Set qdf = dbs.CreateQueryDef("QueryC", strSQL)
Forms!Question.RecordSource = "QueryC" which is to display the selection results in continous form.

Your assistance will be grately appreciated by
Dummy99

 

[tlbroadbent]

This looks like an MS Access question. You should use an Access forum rather than the MS SQL Server forum. I recommend forum701, "Microsoft: Access Queries and JET SQL." Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.