Results 1 to 4 of 4
  1. #1
    fzxxx is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Apr 2020
    Posts
    7

    changing one field in a relationship between tables

    Hi. I have 2 tables. I'd like to to set a relationship between them.

    The first table has an ID like XXXXX, while the second table has the same ID but written in different way: X-XX-XX.

    At the moment I set the relationship outside Access , for example using SUBSTITUTE([columnname], "-", "")

    Apparently there is a similar function (REPLACE) in Access , but I am not sure how this function can be used in a relationship.
    Any suggestion? Thanks

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    You should have them the same and just use the Format to see them as you wish.?
    Use the Replace() function to remove the hyphens, then set the relationship.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the only way would be to write all your queries in sql using a non standard join and not use the query builder

    SELECT *
    FROM tbl1 INNER JOIN tbl2 ON tbl1.ID=replace(tbl2.id,"-","")

    if XXXXX is numeric then you may also need to convert your text value to a number

    This will negate the use of indexing so query will be slow for large datasets.

    As Welshgasman says, better to store the value without the - and use formatting when required

  4. #4
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Can you explain why you have these 2 tables with nonmatching fields which you want to set a relationship on?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

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

Similar Threads

  1. Replies: 2
    Last Post: 08-26-2017, 07:51 AM
  2. Replies: 3
    Last Post: 04-18-2017, 02:28 AM
  3. Replies: 7
    Last Post: 09-09-2015, 10:34 AM
  4. Replies: 2
    Last Post: 05-11-2014, 11:48 AM
  5. Changing tables based on field date
    By dssrun in forum Programming
    Replies: 6
    Last Post: 07-10-2011, 10:17 AM

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