Cannot join on Memo, OLE, or Hyperlink Object - Access 2007 - outer joins
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.
bucketblast
I have interest in business intelligence, crystal reports, ms sql server, as well as access and excel.
Updated on June 04, 2022Comments
-
bucketblast almost 2 years
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.