What would be the easiest way to (without creating a new database table) create a single table with a unique list of account numbers from multiple tables that I can use to then left outer join my multiple tables on to.

In other words I have 3 tables for example that all have the following Account1 as a field and a value field.

Now in Table 1, Account1 has a b and c and in Table 2 the field has a b and d and in Table 3 a and c only.

Now I want to join them so that I have Account1 showing a b c and d (without duplicates) and then use this to left outer join tables1 2 and 3's values to this unique list.

(Bearing in mind that my unique list is not just account1 it is actuall account 1,2,3,4,and 5! So may have to create template table for this!)

Any help is appreciated.

I have tried doing a select distinct

This message has been edited. Last edited by: Michael_86,

It does depend if your source is a SQL or focus format file and if the second step where you want to join this first result with is also focus or SQL.
You can create a union select in SQL or you can do more or less the same in focus.
You say not to be willing to create a new table, but in fact you do that even if it is a temporary one.
If you select the data from the first table you can do this ...

TABLE FILE ONE
BY ACCOUNT1
BY FAAAA 
ON TABLE HOLD AS HOLDA
END
TABKE FILE TWO
BY ACCOUNT1
BY FAAAA
ON TABLE HOLD AS HOLDB
END
...and so on...
Then...
TABLE FILE HOLDA
BY ACCOUNT1
BY FAAAA
ON TABLE HOLD AS FINALHOLD FORMAT FOCUS INDEX ACCOUNT1
MORE
FILE HOLDB
...
END

This should give you the unique selection of account1 with the fields

If you want to join this intermediate result with an other table you need to add an index to the hold file
That is what I did in the last hold statement

I hope this is what you want

Hi Frank,

Thanks for the reply, that worked perfectly for what I needed, but I didnt need to link back unique list as in the end I created a new table that I uploaded as a master file to link to directly. But I have used this already in a different report. So thank you for your help.