2 Replies Latest reply on Apr 15, 2009 9:51 AM by philmodjunk

    relating tables?



      relating tables?


      I have 2 tables that I am trying to relate with each other through my employee position numbers


      I have created a position# field in both tables and linked them. My issue is that the position numbers are 6 digits long and i am using the link to fill in some form letters to get magers info.


      when I enter an employee position number (example 901225)

      i need the related table to show any position number between 901001 and 901999 = John Smith as the manager. That way on my form letter John Smith's info that I have on his record will fill in.


      Please help if you can understand

        • 1. Re: relating tables?

          Hi underdog.com


          The best way to achieve this would be to use calculation fields for your relationship on each side, and both calculations only displaying the first three characters from the employee position number.


          So in both tables create a calculation called _PositionKey [number] and with the following calc, changing according to the name of the appropriate field in both tables.


          left ( position# ; 3 )


          And then make a join between both _PositionKey fields in both tables, and this should do the trick.


          I hope this helps 

          • 2. Re: relating tables?

            If there is any possibility that future use of the system might result in number ranges where the first three digits are NOT the same, you might try the following:


            Define number fields: MinPosition and MaxPosition.


            Define your relationship with two pairs of matching fields:


            Position# > MinPosition AND

            Position# < MaxPosition


            now you can handle a greater range of possible values.