How to join on two disparate keys (one is string, other is int)?

Posted on 2009-07-16
Last Modified: 2012-08-14
I have two tables in MS Access that are suppose to share a common key...however the number in one is actually stored as a string while the other is stored as an int.

I think I want to use the convert() function...

Select blah1.key, blah2.key
from blah1
ON blah1.key = blah2.key

How do I use the convert() or something like it for the join?
Question by:M3hcSS
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
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 24873399
Either use CInt(strColumnName) or CStr(intColumnName).
Select blah1.key, blah2.key
from blah1
ON blah1.key = CStr(blah2.key)
Select blah1.key, blah2.key
from blah1
ON CInt(blah1.key) = blah2.key

Open in new window

LVL 30

Expert Comment

ID: 24874244
You may need to create a query of any table changing the format of the relevant field.
Then join the query with the other table.

Accepted Solution

M3hcSS earned 0 total points
ID: 24938603
Solution was

JOIN Table2 ON Table2.CityList LIKE '%' + Table1.City + '%'

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 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