cannot join on Memo, error

[dharkangel]

Hi Everyone,

I have this query:

SELECT QPTActions.[Issue ID]
FROM QPTActions INNER JOIN AssigneeInfo ON QPTActions.[Assigned To]=AssigneeInfo.Assignee;

that should work but when I run it I get a 'Cannot join on MEMO, OLE or Hyperlink Object QPTActions.[Assigned To]=AssigneeInfo.Assignee'

The problem is I cannot modify the QPTActions table because it is a linked table. I tried making the AssignedInfo.Assignee field a memo but it still gives me the same error.

Is there any way I can get around this?

Thank you,

 

[lespaul]

You're saying that the field [Assigned To] is a memo field in the actual database? What's stored in that field?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[dharkangel]

Yes, [Assigned To] is a Memo field from a linked table. It stores 'LastName, FirstName'. This is coming from an external datasource, or else I would do the obvious of converting it to a text field, but I can't.

 

[Golom]

Memo, Object and Hyperlink fields are not stored "in" the tables. They are kept elsewhere and the table has only a pointer to them. SQL knows nothing about pointers so you can't attempt to link tables using those field types.

You can do this though

SELECT QPTActions.[Issue ID]

FROM QPTActions, AssigneeInfo 

WHERE QPTActions.[Assigned To]=AssigneeInfo.Assignee

but note that only the first 255 characters will be compared.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[PHV]

And this ?
SELECT QPTActions.[Issue ID]
FROM QPTActions, AssigneeInfo
WHERE QPTActions.[Assigned To]=AssigneeInfo.Assignee;


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