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
Field B from Table B (3 records)
Record primary key FieldB
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.