select data of some columns of the same row

[DBAPP]

Hi,
I'm developing a small Access 2007 database
I am looking for a dropdown list for one of the columns that would automatically reflect the contents of the previous 4 columns of the same row.

sample table mentioned below. Column 6 (Winner) should be a dropdown reflecting the nominations1,2,3

Zone	Nomination 1	Nomination 2	Nomination 3	Nomination 4	Winner
South	CV Raman	Narayanmurthy	Mohanlal	I M Vijayan
East	Azim Premji	Bhaichung Bhutia	Jagdish Chandra Bose	Bhupen Hazarika
North	Gulzaar	Sam Pitroda	Kapil Dev	KP Singh
West	Sachin Tendulkar	Asha Bhosale	Anil Ambani	Anil Kakodkar

[John_G]

Hi -

A Combo box (dropdown) cannot easily do that. Normally a dropdown (combo) selects and displays data from different rows of a table or query. However, since a combo box can also use values from a Value List (Row Source Type = Value List), you could use VBA to generate the value list for the dropdown, in the form's On current event.

But you would be better off to rethink your data structure - it is not properly normalized. You should really have a separate table for the nominations:

tblNominations

Zone
Nomination_Number
Nomination_Name

The combo would then select values from that table, though you might still have to use VBA to change its source for each record, using the Zone as criteria.

HTH

John