1 2 Previous Next 15 Replies Latest reply on Sep 14, 2015 11:38 AM by madmike6537

    Have field equal to all possible distinct options of a related field

    madmike6537

      Hello,

       

      I have a tough one, thinking of scripting it but figured I would ask the experts first if there is a way to do this with a calculation.

       

      I have a field, call it fieldA. I would like fieldA from table A to be equal to the Distinct options of the data entries in fieldB in table B. I need the format to be in a text list inside the field like so:

       

      field A Table A (all inside one field and one record)

      Primary Key       Field A

      1                       option1

                                option2

                                option3

       

      Field B from Table B (3 records)

      Record primary key   FieldB

      1                               option1

      2                               option2

      3                               option3

       

      I have tried using ExecuteSQL with something like this:

       

      ExecuteSQL("SELECT DISTINCT FieldA from TABLEA"; "" &)

       

      It returns the results in the right format, but there are duplicates, I need the distinct values. The other problem with this is that I need RELATED values, and of course ExecuteSQL doesnt automate that. I am not sure I can make my WHERE statement work to show the right related values. Was hoping for a calc to do this instead of using SQL maybe.

       

      Any suggestions?

        1 2 Previous Next