how do i join tables with mismatched data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

in 1 table there is a person code which is a number but in another table i
wish to join to has got a learner ref (person code) but that is text. I dont
see how to join them together.
 
If the "learner ref (person code)" in the second table contains text strings
which are the same as the numbers in the first table, you can open the table
in design mode and change the data type of that field to correspond to the
data type in the first table. Note that this may cause problems elsewhere
in your database (eg. other links which use the learner ref text, formatting
in text boxes, ...).

If the text and numbers are same (except for data type) but you can't change
the data type in the second table for some reason, you could include a
conversion function in the Join clause, eg:

.... INNER JOIN tbl2 ON tbl1.PersonCode = clng(tb2.LearnerRef) ...
Use clng() if PersonCode is a long integer, or cint() if PersonCode is an
integer, or ...

If the text and the numbers aren't the same, you'll have to match them
manually.

Rob
 
Back
Top