Lookup field / autonumber woes

[Bogzla]

So, basically my database has quite a number of lookup fields in tables as there is much repeated data, each one references to a seperate table with an autonumber field (ID) and a text field (Value)

Currently I have column one (ID) as the bound column in such cases, as this is what the guy who started me on access said to do, and it's fine until I try and export any query with such a field to Excel.

I have found, through experimentation, that setting the 2nd column (Value) as the bound column allows me to export the values rather than numbers, but I don't know if this is a sensible thing to do? (ie, I don't know what other unexpected effects this might have)

Any guidance here would be very welcome, thanks,
Bogzla

 

[RuralGuy]

The Evils of Lookup Fields in Tables

 

[Bogzla]

Yes, I know, but I know of no alternative that will work in the same way

 

[Bogzla]

I mean, I understand there are issues, but it is the easiest way for me to store a series of values that could be referenced from more than one table / form. If I use a value list I will have to alter every instance of it if I want to add a new value...

There is no way typing values in every time would be justified for this database, it's critical that each instance of a particular value is spelt exactly the same

 

[RuralGuy]

Lookup Controls, such as ComboBoxes, on forms is the preferred approach to your issue and is just fine. The users should never be looking at the table directly anyway.

 

[Bogzla]

Yep, think I had one of those 'moments of clarity' on my walk home from work. Seems realy stupid but I was under the impression lookup controls on forms had to have similar controls on the actual table (think it was an assumption I made early on and never got round to thinking about it properly..).
Anyway, thanks for your time RG

 

[Bogzla]

Yep, think I had one of those 'moments of clarity' on my walk home from work. Seems realy stupid but I was under the impression lookup controls on forms had to have similar controls on the actual table (think it was an assumption I made early on and never got round to thinking about it properly..).
Anyway, thanks for your time RG

 

[RuralGuy]

You're certainly welcome and lots of success with the rest of the project.

 

[neileg]

You should be exporting a query, not the table. The query should include the text value rather than the ID field. This way you have an efficient design but your export gives you what you need.