formula identify differences

[dastr]

Hi all,
I have the attached data:

Basically I would like to populate column three "OUTCOME" according to the following logic:

if train A10 has multiple destinations (we see this in the second column) then in outcome I woudl like to have "multiple destinations" if the destination is only one (in case of trains A15 and A16) , then I would like to have that city in column 3 "OUTCOME".

Any tips will be appreciated,
Thanks.

 

[namliam]

I would make a second query first, to count the number of entries for each train:
Select Train, count(*) NumberOfDest from yourtable group by train

Then in your "result" query join this back into the table use an iif to see if the count is 1 then display destination otherwize "multiple"

 

[dastr]

Hi Namlim,
Thanks for the quick reply.
What if we have a duplicate line, say:

A15LondonLondon

We have duplicated twice on two different lines? Then it will count it twice, right?

Thanks.

 

[namliam]

yes twice with the result that it will display multiple.