Full Version: Intermittent Query Problem
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
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?
When I wrote the above, I wasn't able to discuss the exact error message I was getting, because it only happens intermittently. Well, it happened again, so I wrote down what it said:

S.C.A.T. can't represent the join expression [tblDescription].[DescriptionID]=[tblEquipment].[DescriptionID] in Design View.

*One or more fields may have been deleted or renamed
*The name of one or more fields or tables specified in the join expression may be misspelled
*The join may use an operator that isn't supported in Design view, such as > or <.

Thing is, I never renamed any fields in any tables, and the query was working right before it gave me this messages without me changing anything prior to getting the message. Oh, and S.C.A.T. is the name of my database.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.