Question

[Solved] Cannot join on Memo, OLE, or Hyperlink Object – Access 2007 – outer joins

I have 2 tables like this in Access 2007…

 Table 1 - tbEmployees Details
           FieldName      Data Type
      PK   *ID            Autonumber 
           EmployeeID     Number
           First          Text
           ....           ...

 Table 2 - tbEmployeesQualification
           FieldName    Data Type
     PK   *ID           Autonumber  
           CV (attachment)  Attachment  
           Qualification / Certificate (memo box)
           Skills (memo box)

I am at design stage in relationship. I am trying to use outer join’s from the above tables because I want to attached CV’s for each employee. I have tried the following.
Drag the ID from tbEmployees Details onto the tbEmployeesQualification. Then the Join Properties a screen appears…

   Left Table  -            Right Table -
   tbEmployees Details      tbEmployeesQualification 
   Left Column -            Right Column 
   ID                       Qualification / Certificate

I have selected Option 2 – Include ALL records from …. where the join fields are equal.

To test this I have used Query. When I use Query Wizard it seems query shows results that includes all the data from tbEmployees Details and all the data tbEmployeesQualification BUT

when I use Query Design I get the following error…

Cannot join on Memo, OLE, or Hyperlink Object ([tbEmployees Details].ID=[     
tbEmployeesQualification].[Qualification/Certificate])

The error is correct. I know ID has Autonumber as data type and Qualification / Certificate from tbEmployeesQualification as memo data type so therefore inner join cannot be used. So my question is how do I solve this problem? or can it be solved by using outer joins or do I need to do something else that I have not seen?

Thanks in advance for the time to answer my question(s).

EDIT –
I think foreign key maybe the answer. I have just added ID with data type as Number in the tbEmployeesQualification. Create one to many relationship one is tbEmployees Details and many is tbEmployeesQualification. I will update soon.

Solution #1:

The problem was solved by using one-to-many relationship. In tbEmployees Table I put First Name in Field Name (PK) with data type Text. In tbEmployeesQualification table again I created Field Name FirstName with data type Text.

I linked tbEmployees Table (one) to tbEmployeesQualification table (many). This solved the problem.

Respondent: bucketblast
The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Most Popular

To Top
India and Pakistan’s steroid-soaked rhetoric over Kashmir will come back to haunt them both clenbuterol australia bossier man pleads guilty for leadership role in anabolic steriod distribution conspiracy