Access Ver 2.0 - Adding information from 3 colums in a table into a drop down box in a form?

[michael.nilsen]

Hi All,

Just a quick one. I have a table with contact names (contact 1, contact
2, contact 3...) i want to be able to complie these contacts together
and list them in a combo box or list box. The contacts are in a
customer table and the customer name is primary key.

Thanks
Michael.

 

[John Vinson]

Hi All,

Just a quick one. I have a table with contact names (contact 1, contact
2, contact 3...) i want to be able to complie these contacts together
and list them in a combo box or list box. The contacts are in a
customer table and the customer name is primary key.

Thanks
Michael.

Create a UNION query. Create a new Query; open the SQL window (I
believe in 2.0 - TEN YEARS AGO! - it was on the View menu option).

Edit it to

SELECT Contact1 FROM Customers WHERE Contact1 IS NOT NULL
UNION
SELECT Contact2 FROM Customers WHERE Contact2 IS NOT NULL
UNION
SELECT Contact3 FROM Customers WHERE Contact3 IS NOT NULL

Save this query and use it as the RowSource for the combo or listbox.

Note that - even for 2.0 - your table design could use a lot of work!
Customer names are a VERY BAD primary key, since names are not unique;
you have repeating fields which should be in another table; I'd guess
there are more such problems.


John W. Vinson[MVP]

 

[Michael]

Hi John,

Thanks for your help and advice. Yes i agree, this database is not very
well designed or bulit. It was made a very long time ago and i'm not
the creator, more like the poor person trying to make it do things that
i probably can't or i can't for that matter.

in regards to the union query, is there a way i can limit the list to
the contact name for a perticlur client, for example..

there is a main form called project details. On this from you can
select customer name from a drop down list (customer table as its
sorce, customer name as its sorce controll) each customer has 3
contacts so i really only want to display those contact in a drop down
list.

Thanks for help once again.