Combining fields 2

[BlueMonkey]

hi,

I've "inherited" a database that's being used as part of a project, and have been asked to introduce a combo box listing all training delegates names, to prevent the mistyping of names we're currently experiencing. Easy, right? the catch is, the delegates names are in a non-relational format - ie Delegate1, Delegate2 and Delegate3.

The project is winding down, and my suggestions of making this area relational are falling on deaf ears at present.

Is there a trick I could use with a query to list delegates from these three fields into a single column, which would allow me to create the combo box?

Thanks in advance

BM

B.M.

 

[Remou]

How about a Union Query?
[tt]SELECT Delegate1 From tblTable
UNION
SELECT Delegate2 From tblTable[/tt]

 

[BlueMonkey]

Brilliant. Thanks very much

B.M.

 

[BlueMonkey]

Remou,

Is it possible to also include an ID field in this query, allowing me to synchronise the delegates to the date of their training?

BM

B.M.

 

[PHV]

Something like this ,
SELECT Delegate1, 1 AS Which, ID From tblTable
UNION SELECT Delegate2, 2, ID From tblTable
UNION SELECT Delegate3, 3, ID From tblTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BlueMonkey]

Absolutely brilliant, thanks very much for all of your help.

If I get a payrise out of this, I'll thank you properly.

B.M.