Solved: Drop down SQL statement multi fields

[Movian]

Hi,
i have a drop down with the following SQL query as its row source

"SELECT DISTINCT DX FROM Ledger;"

Now we have changed this a little and now have 4 distinct Dx Fields

Dx, DX2, DX3 & DX4

i need to restructure the SQL query so that it takes the distinct values from all 4 fields to produce the list for the drop down.

Let me give an example to better explain what i need

Record - DX - DX2 - DX3 - DX4
1 - 456 - 455 - Null - Null
2 - 455 - 123 - 456 - Null

i would need the drop down list to provide the following options.

456
455
123

i think i need to use a merge command. but im not exactly sure on the syntax. Any help would be appreciated.

~edit

If i can't do this through a SQL statement then i will need to produce a search routine using indexes to check if the DX allready exists and use a lookup table to store the results and then use somthing akin to the origional SQL statement to put the list into the drop down row source. Obviously this is not the best solution and i would prefere to do everything within the SQL statement as i should be able to.

[OBP]

Movian, I was thinking in terms of suggesting a lookup table, but what this questions says to me is that the Table design is not correct and you are trying to work around it.
Surely the DX fields should be individual records in a Subtable?

My SQL knowledge and single SQL book are too limited to provide an answer to the SQL to do this.

[Movian]

Found it

my work process involves me trying to find solutions liek this when i can't figure it out on my own. After 45 minutes of Google fu failure i post here
Then i keep looking. This time however i found the answer with a little more google-fu

SELECT DISTINCT (ans) AS Expr1
FROM (SELECT DX AS ans FROM Ledger UNION SELECT DX2 AS ans FROM Ledger UNION SELECT DX3 AS ans FROM Ledger UNION SELECT DX4 AS ans FROM Ledger ) AS Temp;

Now i just need to figure out how to exclude null or empty values

[CreganTur]

Now i just need to figure out how to exclude null or empty values

For SQL you can do this by using Field IS NULL (where Field is the field's real name) for Field = "" (for field equals empty string).

Does that help at all?

[Movian]

The problem is if i add a WHERE clause

EG WHERE NOT Ledger.DX = '' AND NOT Ledger.DX2 = '' etc etc

Then when you click the drop down it prompts for what the value of Ledger.DX is

also tried Temp Ans etc to no avail. However its not a big problem as there will only ever be one empty space.