Lookup Table getting Corrupted

[Brian1960]

Hi All,

I have lookup table "TblLu_Consultants" which has two fields
ConsultantID AutoNumber
Consultant Text

The table is used in two ways, one to store the ID number in another table as a lookup on a Form and the second to store the Consultant Name in the table. They are both used in several tables.

I have occasionally had the Consultant Name being corrupted in the table by the ID. Thus loosing the Consultant name.

We use the Lookup on several Forms.

Anyone seen this corruption before.

Access 97

Thanks
Brian

 

[quest4]

No, well at least when the table are properly constructed. You might want to read up on table normalization here:
http://www.microsoft-accesssolutions.co.uk/table-of-contents.htm
When your tables are right everything runs better. hth.

 

[Brian1960]

Lookup problem

Not quite sure what Normalisation issues are represented here. The lookup table is a lookup table and nothing else. A previous deisgner decided to save the actual name to a record in a table to speed up loading certain very complex Forms & Reports and I concurred with this.
The ID is saved in records in most tables,
E.g Consultant in charge of a Client, and Consultant who has to perform an action (E.G. Conact the client by a date)

Still doesn't get round the issue of the Lookup table getting corrupted.
Brian

 

[quest4]

I have was just checking, you would be surprised at some of the table structures I have come across, and yes they have cause problems. When trying to fix something I alway start in the beginning, whcih in a dbase is the tables and then go from there. Maybe try this, make a copy of the dbase, then change it's name. Create a new dbase and import everything into it. Then runC&R on the dbase. See if the problem is still there. hth.