Hello is there a SQL calc that can count a list of distinct values where each field can have one or more values separated by a ¶?

Right now I'm using: ExecuteSQL ("SELECT COUNT (DISTINCT Field) FROM Table" ; "" ; "")

but this only counts the first value of a field that contains two values giving me an incorrect result (example below would incorrectly result: 4)

Example:

a¶b

a¶b

c

d

d

e¶f

e

I would like the result to display a count of =6

What is that field you're interested in, and what do its multiple values represent?

EDIT: OK, I can deduce that from your calculation; if you're interested in Members, you could do this:

Let ( [

memberIDs =

ExecuteSQL ( "

SELECT \"MCustomerIds\"

FROM \"Invoice Data\"

WHERE MExpiryFYear >= SelectedYear

" ; "" ; ""

) ;

Case (

not IsEmpty ( memberIDs ) ;

ExecuteSQL ( "

SELECT COUNT (*)

FROM Customers

WHERE id /* customer table primary key */ IN (" & Substitute ( memberIDs ; ¶ ; "," ) & ")

" ; "" ; ""

) ;

0

)

)

But again, I think you're trying to make up for deficiencies in your data structure by employing elaborate calculations (which, IIRC, has also been suggested previously …)