Results 1 to 5 of 5
  1. #1
    jawman23 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    2

    Creating relationship based on part of a text field

    Hi everyone,

    I have a problem that's had me stuck for a few hours now. I'll start with an overview of the system. Each each record in table1 has a unique four character (alpha-numeric) code to identify it. The first two numbers of this code represent the group it is in. (Ie. 15AB and 1502 are both grouped together) The second table stores values that apply to the entire group. I need to create a relationship between these two tables based on the first two characters in the ID field.

    Things I've tried:
    * Making a calculated field with left$() formula - Access doesn't allow relationships on calculated fields


    * Create a new field for just the first two characters and create a data macro for after update and after insert to update that field with the expression - cannot edit the field the user is on

    Does anyone know how I can accomplish something like this?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,509
    Do yourself a big favor. One concept One field. In all of your tables ensure there is a unique identifier so the system can keep track of your records.

    In Access an autonumber field can do this. Set it to Primary Key(PK). A PK should be considered for system use -- you and users do not have to deal with it or see it.
    I would even suggest a table for Groups
    eg
    tblGroups
    GroupID PK
    GroupName
    other group specific info.

    see this tutorial for more info -- work through the sample.

    Good luck with your project

  3. #3
    NTC is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2009
    Posts
    2,392
    You must make a new field in those tables; and write those 2 characters into that field. Then you will be able to join.

  4. #4
    jawman23 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2014
    Posts
    2
    Thanks for the help, I think adding an autonumber field to each will be the best solution. Thanks for the help!

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,509
    You should still look to storing different things in different fields.

    Good luck with your project

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

Similar Threads

  1. Creating a Date from a Text Field
    By GSPOwner1 in forum Queries
    Replies: 6
    Last Post: 12-17-2013, 08:41 AM
  2. Conditional Formatting based on another field - Part II
    By Harley Guy in forum Programming
    Replies: 8
    Last Post: 10-17-2013, 12:46 PM
  3. Replies: 2
    Last Post: 05-05-2010, 02:52 PM
  4. Copying only part of data from a text field
    By davedejonge in forum Queries
    Replies: 1
    Last Post: 02-03-2010, 05:18 PM
  5. Creating field based an another field's total
    By yashysmashy in forum Queries
    Replies: 0
    Last Post: 09-02-2009, 03:48 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 - Senior Forums