Create a join on part of a field

Posted on 2011-05-05
Last Modified: 2012-05-11
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.
Question by:dbfromnewjersey
    LVL 47

    Accepted Solution

    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)

    LVL 74

    Assisted Solution

    by:Jeffrey Coachman
    <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 the MS Access help files for more info.


    Author Comment

    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.  
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    Then the join clause I used in my previous post should work.


    Author Comment

    Thank you very much. Very useful technique.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    794 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now