2 fields into 1 select dropdown

[sdagger2]

Hi

I am stuck on a problem I'm not sure is possible but hope it is. I currently have a dynamic dropdown select box which simply takes the value from one field of an access database table. Using ASP and Access, The code I use simply selects all unique town/cities from a field called 'towncity'. An output would something like:

SELECT DISTINCT towncity from table order by towncity;

<option>Birmingham</option>
<option>Crewe</option>
<option>Preston</option>
<option>Wigan</option>

and so on. What I want to do is get unique data from 2 fields in the table (district and towncity)and join them into one dropdown. So that the out put would be similar to:

SELECT DISTINCT district, towncity from table order by <alphabetically?>;

<option>Batherton</option>
<option>Birmingham</option>
<option>Crewe</option>
<option>Haslington</option>
<option>Preston</option>
<option>Wigan</option>
<option>Willaston</option>
<option>Wybunbury</option>

Is there a way I can do this? I may want to extend this by combine 3 fields into one unique dropdown. ie, District, TownCity and County. Many thanks for anyone who can help me.

 

[PaulBricker]

In Access, you might try a Union query to get all the values into one field (assuming that in your second example 4 of those values are districts and 4 are town/cities. I don't know if this would create a distinct value. You might have to create distinct queries of all the tables you want and then combine those queries into a Union query.
Just a thought.

Paul

 

[sdagger2]

Thanks but i'm not sure if I can do this? How can you use a union query for 2 or more fields in the same table? Not sure but I thought union is for joining between two tables?

I am rubbish at this so need abc. Sorry.

 

[PaulBricker]

Select Distinct towncity from table
Union select distinct district as towncity from table;

That's all it should take. Then use that query for your asp code.

Paul

 

[PaulBricker]

Or if you want to change the name of the field in both tables, you would just add an alias for the first line also.

Select Distinct towncity as NewField from table
Union Select Distinct District as NewField from table;

Paul

 

[sdagger2]

Thanks guys that is fantastic help you gave. Could I ask a further question regarding this also?

Can I easily do it with 3 fields? ie, another union? if so, how would the syntax be? and finally, can all the results be easily ordered or grouped in alphabetical order?

Many thanks again. You stars!!

 

[PaulBricker]

Select Distinct towncity as NewField from table
Union Select Distinct District as NewField from table
Order By NewField;

If you want to add a third field, you just repeat the Union Select line with the appropriate information. In your case I think it was
Union Select Distinct County as New Field from table

Paul