Results 1 to 5 of 5
  1. #1
    natmat16 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2018
    Posts
    2

    help with matching a complete field in one table against a field in a separate table

    Sorry, if I am doing this incorrectly, however if anyone can help me that would be appreciated. I have two tables with sets of data that I need to match up with a query. Table one contains all of the data, and the field i need to match to has a string of alphanumeric characters where the number i am seeking is embedded at the beginning and it is not a set length. Table two contains the data I am seeking, however I cannot write a query to match them.


    example:
    table 1 column2 table 2 column 3
    122frs34812489481348 10001
    22efwee1974516484 1246884
    10001549841818188 122
    12468842574818418 22

    i need to get a result where i can find record 10001 (first record in table 2) in table one in the long string (in this example it will be record 3). Don't laugh but i tried using something such as like "[table2].[field]*" but i guess its been too long since I've used my access knowledge.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Make Q1 to extract the 1st field,
    select left(field1,instr(field1," ")-1) as myKey from table1

    then make Q2 using Q1 to join to table2
    select table2.* from q1,table2 where q1.myKey= table2.keyField

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Your example data is somewhat difficult to read - try editing it with the Table tools in the advanced editor?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  4. #4
    natmat16 is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2018
    Posts
    2
    sorry - i had put spaces between the two columns but when it posted it removed them. it should be

    table 1 column2

    122frs34812489481348
    22efwee1974516484
    10001549841818188
    12468842574818418

    table 2 column 3
    10001
    1246884
    122
    22

    when i do a search on table two data in table one i need to match only the beginning of the string in table 1.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    natmat16,

    Use code tags when posting formatted code --highlight the code or text involved, then click on the octothorpe/hash symbol at the top of the window.

    Could you please tell us in simple, plain English what this data represents? The data and the string doesn't give much context.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-13-2017, 03:00 PM
  2. Replies: 1
    Last Post: 08-08-2015, 10:34 AM
  3. Break field out into separate table (aka normalize).
    By mountainclimber in forum Database Design
    Replies: 4
    Last Post: 06-19-2015, 01:13 PM
  4. Replies: 3
    Last Post: 03-07-2014, 10:39 AM
  5. Replies: 5
    Last Post: 10-24-2013, 04:17 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