Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot join memo field

Posted on 2013-12-16
8
Medium Priority
?
486 Views
Last Modified: 2013-12-19
Hi,

I have SQL code and im trying to join memo fields


Is someone able to add the line of SQL

Where Left([INPUT D2 DATA].[Customer Type], 255) = Left([Calculation D2].[Client Type], 255

Im new to SQL so keep getting the syntax wrong.

Many thanks

SELECT INPUT_D2_all.ID, INPUT_D2_all.ClientID, INPUT_D2_all.[Customer Type Family], INPUT_D2_all.[Customer Type], INPUT_D2_all.LoB, INPUT_D2_all.Segment, INPUT_D2_all.[Business Type], INPUT_D2_all.[Employment Status], INPUT_D2_all.[Key controller flag], INPUT_D2_all.[Country of Incorp/ Registration / Trust Est], INPUT_D2_all.[Country of Primary Operation], INPUT_D2_all.[Country of Business Focus], INPUT_D2_all.[Country of SoW], INPUT_D2_all.[Country of Nationality / Citizenship], INPUT_D2_all.[Country of Perm Residence], INPUT_D2_all.[Country of Government], INPUT_D2_all.[Legal Entity Structure], INPUT_D2_all.[Product Risk], INPUT_D2_all.[Trade Flag], INPUT_D2_all.[PCM Flag], INPUT_D2_all.[Safe Deposit Product Flag], INPUT_D2_all.[$ Cash transactions - m1], INPUT_D2_all.[$ Cash transactions - m2], INPUT_D2_all.[$ Cash transactions - m3], INPUT_D2_all.[$ Cash transactions - m4], INPUT_D2_all.[$ Cash transactions - m5], INPUT_D2_all.[$ Cash transactions - m6], INPUT_D2_all.[$ Cash transactions - m7], INPUT_D2_all.[$ Cash transactions - m8], INPUT_D2_all.[$ Cash transactions - m9], INPUT_D2_all.[$ Cash transactions - m10], INPUT_D2_all.[$ Cash transactions - m11], INPUT_D2_all.[$ Cash transactions - m12], INPUT_D2_all.[# Cash transactions - m1], INPUT_D2_all.[# Cash transactions - m2], INPUT_D2_all.[# Cash transactions - m3], INPUT_D2_all.[# Cash transactions - m4], INPUT_D2_all.[# Cash transactions - m5], INPUT_D2_all.[# Cash transactions - m6], INPUT_D2_all.[# Cash transactions - m7], INPUT_D2_all.[# Cash transactions - m8], INPUT_D2_all.[# Cash transactions - m9], INPUT_D2_all.[# Cash transactions - m10], INPUT_D2_all.[# Cash transactions - m11], INPUT_D2_all.[# Cash transactions - m12], INPUT_D2_all.[$ Cross-border wires m1], INPUT_D2_all.[$ Cross-border wires m2], INPUT_D2_all.[$ Cross-border wires m3], INPUT_D2_all.[$ Cross-border wires m4], INPUT_D2_all.[$ Cross-border wires m5], INPUT_D2_all.[$ Cross-border wires m6], INPUT_D2_all.[$ Cross-border wires m7], INPUT_D2_all.[$ Cross-border wires m8], INPUT_D2_all.[$ Cross-border wires m9], INPUT_D2_all.[$ Cross-border wires m10], INPUT_D2_all.[$ Cross-border wires m11], INPUT_D2_all.[$ Cross-border wires m12], INPUT_D2_all.[# Cross-border wires - m1], INPUT_D2_all.[# Cross-border wires - m2], INPUT_D2_all.[# Cross-border wires - m3], INPUT_D2_all.[# Cross-border wires - m4], INPUT_D2_all.[# Cross-border wires - m5], INPUT_D2_all.[# Cross-border wires - m6], INPUT_D2_all.[# Cross-border wires - m7], INPUT_D2_all.[# Cross-border wires - m8], INPUT_D2_all.[# Cross-border wires - m9], INPUT_D2_all.[# Cross-border wires - m10], INPUT_D2_all.[# Cross-border wires - m11], INPUT_D2_all.[# Cross-border wires - m12], INPUT_D2_all.[$ PCM wires - m1], INPUT_D2_all.[$ PCM wires - m2], INPUT_D2_all.[$ PCM wires - m3], INPUT_D2_all.[$ PCM wires - m4], INPUT_D2_all.[$ PCM wires - m5], INPUT_D2_all.[$ PCM wires - m6], INPUT_D2_all.[$ PCM wires - m7], INPUT_D2_all.[$ PCM wires - m8], INPUT_D2_all.[$ PCM wires - m9], INPUT_D2_all.[$ PCM wires - m10], INPUT_D2_all.[$ PCM wires - m11], INPUT_D2_all.[$ PCM wires - m12], INPUT_D2_all.[# PCM wires - m1], INPUT_D2_all.[# PCM wires - m2], INPUT_D2_all.[# PCM wires - m3], INPUT_D2_all.[# PCM wires - m4], INPUT_D2_all.[# PCM wires - m5], INPUT_D2_all.[# PCM wires - m6], INPUT_D2_all.[# PCM wires - m7], INPUT_D2_all.[# PCM wires - m8], INPUT_D2_all.[# PCM wires - m9], INPUT_D2_all.[# PCM wires - m10], INPUT_D2_all.[# PCM wires - m11], INPUT_D2_all.[# PCM wires - m12], INPUT_D2_all.[$ wires to high risk countries m1], INPUT_D2_all.[$ wires to high risk countries m2], INPUT_D2_all.[$ wires to high risk countries m3], INPUT_D2_all.[$ wires to high risk countries m4], INPUT_D2_all.[$ wires to high risk countries m5], INPUT_D2_all.[$ wires to high risk countries m6], INPUT_D2_all.[$ wires to high risk countries m7], INPUT_D2_all.[$ wires to high risk countries m8], INPUT_D2_all.[$ wires to high risk countries m9], INPUT_D2_all.[$ wires to high risk countries m10], INPUT_D2_all.[$ wires to high risk countries m11], INPUT_D2_all.[$ wires to high risk countries m12], INPUT_D2_all.[# wires to high risk countries m1], INPUT_D2_all.[# wires to high risk countries m2], INPUT_D2_all.[# wires to high risk countries m3], INPUT_D2_all.[# wires to high risk countries m4], INPUT_D2_all.[# wires to high risk countries m5], INPUT_D2_all.[# wires to high risk countries m6], INPUT_D2_all.[# wires to high risk countries m7], INPUT_D2_all.[# wires to high risk countries m8], INPUT_D2_all.[# wires to high risk countries m9], INPUT_D2_all.[# wires to high risk countries m10], INPUT_D2_all.[# wires to high risk countries m11], INPUT_D2_all.[# wires to high risk countries m12], INPUT_D2_all.[Turnover (GPB HNAH only)], INPUT_D2_all.[SCC flag], INPUT_D2_all.[SCC reason], INPUT_D2_all.[Previous Risk Rating], INPUT_D2_all.[Previous Country Risk Factor Rating], INPUT_D2_all.[Previous Business Type Risk Factor Rating], INPUT_D2_all.[Previous Legal Entity Risk Factor rating], INPUT_D2_all.[Previous Product Risk Factor Rating], INPUT_D2_all.[Assumption flag], INPUT_D2_all.LOB1, INPUT_D2_all.Country, INPUT_D2_all.[Cash Only], INPUT_D2_all.[Cash/Xborder], INPUT_D2_all.PRR, Calculation_D2_all.[Overal RAG]
FROM Calculation_D2_all INNER JOIN INPUT_D2_all ON Calculation_D2_all.[Client Type] = INPUT_D2_all.ClientID
WHERE (((INPUT_D2_all.LOB1)="GPB"));
0
Comment
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 51

Accepted Solution

by:
Gustav Brock earned 668 total points
ID: 39721087
The simplest, I guess, would be to combine the join into the where clause:

FROM
    INPUT_D2_all,
    Calculation_D2_all
Where
    [INPUT_D2_all].[ClientID] = [Calculation_D2_all].[Client Type]
    And
    Left([INPUT D2 DATA].[Customer Type], 255) = Left([Calculation D2].[Client Type], 255)

/gustav
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 668 total points
ID: 39721089
Why is Customer_Type a Memo field?  Just seems unusual (I asked this in one of your other questions as well).  From the field name, it seems like a text field would be a better choice... versus something like 'Notes' which may receive a lot of user input.

If you could change the field to TEXT (even with a long length such as 500), you would eliminate this "can't join on memo" issue.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39721097
Plus, that Left(FieldName,255) in the JOIN seems like a hack.  

If the field can contain more than 255 characters, you would run into problems with the JOIN if any differences occurred after the first 255 characters,  

If the field contains 255 characters or LESS, why not cleanly eliminate the problem by defining it as Text instead of Memo?
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:Seamus2626
ID: 39721100
When i try and change data type, i get the error "Microsoft Access cant change the data type"

There isnt enough disk space or memory
0
 
LVL 61

Expert Comment

by:mbizup
ID: 39721115
Have you tried compact/repairing the database?
0
 
LVL 51

Expert Comment

by:Gustav Brock
ID: 39721118
It is a hack but it works as you can't join memo fields. No repair is needed.

It doesn't matter if a memo holds more than 255 chars but, of course, the first the first 255 chars should be unique if any memo field is considered unique.

/gustav
0
 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 664 total points
ID: 39721138
Just to kick in a comment or two, I can't think of any logical reason why you'd want to join on a memo field, which means more then likely, your database design needs some re-work.

A word to the wise: I'd stop and do that now before you continue on as this will just be the start of many issues.

Access (and other relational products) were desinged to work with properly structured databases.   If your database is not, then it's not going to work well, and maybe not even at all.

For example, while you might be able to hack a join on the first 255 characters, the CPU/memory requirements will be intensive.   Performance will be dismal and you may find it no longer works even with ten or twenty thousand records.

Jim.
0
 

Author Closing Comment

by:Seamus2626
ID: 39728687
Rebuild is needed!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question