Select Distinct with Memo item 1

[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

 

[LittleSmudge]

How about:-

choice = "WHERE ((tblB.KeyWord) = 'sample') OR (tblB.KeyWord = 'demo')) "

strSQL = "SELECT DISTINCT KeyId FROM tblB " & choice

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

Forms!Question.RecordSource = "SELECT Ans FROM tblA INNER JOIN QueryONE ON tblA.Key = QueryONE.KeyId"


QED?

G LS

 

[Dummy99]

G LS

Works fine. In addition to the good code you provided, I see the key to my problem was to do my DISTINCT selection at an earlier stage - very educational for me; hope it is a good lesson for others. You have my star.

By the way, what's "QED"?

Many thanks.
Dummy99

 

[LittleSmudge]

QED

Latin - Stand for Quad Errat Demonstratum ( Pardon my latin spelling )

Which means "That which was to be shown."


In modern terms, with the "?" after it .. ..
"Does that do it for you ? "

G LS

PS. Thanks for the Star - It's nice to know when some idea or advice "hits the spot".