Hi all and thanks for taking the time to read my post.
I am having this ongoing issue with queries in my database involving related fields. As an example, the tables are supposed to be related as follows:
The way it's supposed to work, is that each piece of equipment has an item name. But, each item name can have multiple descriptions. For example, an item name might be 'computer'. A computer can have a description of 'desktop', 'laptop', or 'server'.
Originally, my employer indicated that the description would be something the user would type in on the fly. So, I didn't even make any type of lookup table for description, and instead that control on the form was a text box. When it dawned on my employer that she'd like to have more control over what is entered there, we decided it was more appopriate to put descriptions into a look up table, and I made the table above called 'tblDescription'. I changed the field 'Description' in the table called 'tblEquipment' to an Integer instead of a text field. And, I made a relationship between tblDescription.DescriptionID and tblEquipment.DescriptionID in the relationship window.
When I make a query using tblDescription and tblEquipment in the same query, I often times get an error based on this relationship (the error says something about the fields not being related). I have to physically make the relationship in the query window (drag DescriptionID from tblDescription to DescriptionID in tblEquipment to make a line between them) before it will work. Then, my query will work for awhile. But, at certain unexpected times, it inevitably ends up giving me the error again, and I have to go into design mode and make the link again.
Has anyone else experienced this type of problem before? Perhaps you have a suggestion about how I could fix the problem once and for all?