Results 1 to 4 of 4
  1. #1
    Comet2 is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2010
    Posts
    1

    combining tables that have no direct connection field


    I am a newbie and have hit the wall.

    Book1 table - fields id, Author, isdn, copyright, title....

    Availability table - fields sp_id, isdn10, list, new, used, ....

    isdn in Book1 table is a 13 character field, that needs to be MID to 9

    idsn10 in availability table is a 10 character field, that needs to be Right, 9

    The calculated 9 character field should match up but I have no idea how to make it happen. Help!

  2. #2
    ConneXionLost's Avatar
    ConneXionLost is offline Simulacrum
    Windows XP Access 2003
    Join Date
    Jan 2010
    Location
    Victoria, Canada
    Posts
    291
    Build one query from each table (include the respective calculated fields in the queries), then join the first two queries, on the calculated fields, in a third query.

    Cheers,

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,921
    Can you add fields to the tables? If so then add one field to each table with the MID and RIGHT specs you stated. Then create the relationship with these new fields. If you can't modify the tables then create queries or each table that adds the field with the specs you need and you can join them in a third query.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Actually, Access can join on simple calculated field:

    tableA inner join tableB on mid(tableA.ISDN,1,)=right(tableB.ISDN10,9)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Joining or Combining Two tables
    By escuro19 in forum Queries
    Replies: 2
    Last Post: 02-16-2010, 03:55 PM
  2. combining 2 tables
    By psrs0810 in forum Access
    Replies: 11
    Last Post: 01-07-2010, 08:55 AM
  3. Combining Tables
    By king_bowzow in forum Queries
    Replies: 0
    Last Post: 08-05-2009, 10:15 AM
  4. Linked tables and ODBC connection strings
    By cwcadm in forum Import/Export Data
    Replies: 7
    Last Post: 05-26-2009, 07:30 PM
  5. Help combining 3 tables
    By luscioussarita in forum Queries
    Replies: 1
    Last Post: 12-14-2005, 03:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums