Combo box

[reterrig]

I have a table that has 3 fields: Field1, Field2, Field3 with data i.e.
Field1 Field2 Field3
a b c
d e f
etc

I need a the combo box to show them in a list i.e.
a
b
c
d
e
f
etc

is any way to do this?

Thanks
Peter

 

[Wayne-I-M]

Hi

I don't think you can have a field like you have shown.
Field 1 A and D
Field 2 B and E
Field 3 C and F
Well of course you "can" but this would be a big mistake. Each field should
contain only one item of data.

Can you give more details

Generally you can create a query and base the combo on that or a table or
just type in the items you want if they will not change etc etc

 

[Brendan Reynolds]

I have a table that has 3 fields: Field1, Field2, Field3 with data i.e.
Field1 Field2 Field3
a b c
d e f
etc

I need a the combo box to show them in a list i.e.
a
b
c
d
e
f
etc

is any way to do this?

Thanks
Peter

You can do it using a UNION query as the row source of the combo box ...

SELECT Field1 FROM YourTable
UNION ALL SELECT Field2 FROM YourTable
UNION ALL SELECT Field3 FROM YourTable

When you find yourself having to do this kind of thing, you can be pretty
sure that there is a fundamental flaw in your database design. Those three
fields should probably be rows in a related table.

 

[reterrig]

Wayne,
Sorry I am not an expert in Access. I clarify my question:
RecordID Field1 Field2 Field3
1 a b c
2 d e f
3 g h i
etc
fields contain only one item data but each line is a different record

 

[reterrig]

Brendan,
Thank you very much. I have created union query and works. No I have another
problem. toe Combo box lists also empty lines and doubliates. Which way I can
avoid the combo box to list empty lines and to avoid listing the doublicates.

Thanks for your assistance

Peter

 

[Douglas J. Steele]

Use the Distinct keyword to avoid duplicates, and put an appropriate Where
clause to ignore rows with empty values. Remember that empty values can be
spaces, zero-length strings ("") or Null.

SELECT DISTINCT Field1
FROM MyTable
WHERE Len(Trim([Field1] & "")) > 0
ORDER BY Field1

 

[John Spencer]

Use UNION instead of UNION ALL to get rid of the duplicates.

Use a where clause in each subquery to get rid of nulls

SELECT Field1 FROM YourTable WHERE Field1 is Not Null
UNION SELECT Field2 FROM YourTable WHERE Field2 is Not Null
UNION SELECT Field3 FROM YourTable WHERE Field3 is Not Null

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County