[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Create a join on part of a field

Table1 has a field 2 bytes in length.  Table2 has a field 10 bytes in length.  I would like to join the two tables on the Table1 field and the first two bytes of the Table2 field.  How to do it? Thank you.
0
dbfromnewjersey
Asked:
dbfromnewjersey
  • 2
  • 2
2 Solutions
 
Dale FyeCommented:
Is this a text field?  You cannot do non-equi joins in the query grid, so what I generally do is create the join as an equi-join in the grid, then go to SQL view to do something like:

INNER JOIN TableA.FieldName = Left(TableB.FieldName, 2)

0
 
Jeffrey CoachmanCommented:
<Table1 has a field 2 bytes in length.>
Is this a text field?

Then you mean 2 *characters* (as fyed states)
;-)

MS Access does not directly reference text in Bytes (only numbers)
In other words, 1 character will not necessarily be 1 byte

See: DataType Property
...in the MS Access help files for more info.

JeffCoachman
0
 
dbfromnewjerseyAuthor Commented:
The first two characters of the 10 character field will always be upper case alpha. The 2 character field in the other table will also always be upper case alpha.  
0
 
Dale FyeCommented:
Then the join clause I used in my previous post should work.

0
 
dbfromnewjerseyAuthor Commented:
Thank you very much. Very useful technique.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now