I have several tables in my database that I use as lists to select items from. For example, the Status table has two fields: An autonumber as the primary key and a Status field which can have only two values either "Open" or "Closed"

So it looks like:

1 Open
2 Closed

Everything in my database works. But, if I want to be able to see "Open" or "Closed" rather than 1 or 2 I have to write an SQL statement. Is the autonumber field necessary? Can I just make the Open and Closed fields the primary keys to prevent duplication or would this have bad ramifications in the future?

It would also be good if I have to export to Excel to have the words rather than the numbers as identification.

I have read that lookup fields in tables are ultra evil and must avoided. But the Norwind sample database always has an autonumber and another field.

Thanks in advance, Dwight

Dwight:

Are you using this for a combobox in a form? If so, you can change the Bound Column from 0 to 1 and it will store Open or Closed instead of 1 or 2.

Can you give a little more infomration on what the table is used for?

Yes, I use this information in a combo box on a form. In another example I enter infromation about client meetings so I have a Meeting Type table that has 1 = Phone 2= In Person 3 = Internet etc. I also have one of these tables set up to list types of client transactions 1 = buy 2 = sell etc. They are always used as combo boxes on forms.

I do not see the point of the number but I do not want to discard them if it is correct to have them in a database.

Thanks for the bound column tip.

The point of the number is that over time, code values may change and you don't want to have to update hunderds or thousands of rows to change a country name from Ceylon to Sri Lanka.