I'd like to get some opinions about how others would handle this situation: I have a table that includes information about Contractors. The table includes a 7-character auto-increment Contr_No field and a 20-character Contractor name field. In another table, I store information about projects done by the Contactors. Right now, Contractors are linked to Projects one-to-many using the Contr_No field.
Here's the issue: The Projects are commonly referred to by the Contractor Name/Project Name (i.e. the Smith North Hills project where Smith is the Contractor and North Hills is the project name). Since the Contractor Name is not a field in the Projects table, a colleague set this up to make Contr_No a "lookup field." In field rules, lookup rules are defined for the Contr_No field, with Contr_No to be filled but not displayed; and Contractor to be displayed but not filled. To be honest, this is a feature I didn't even realize existed (I thought you'd see the name when you looked it up, but when you selected one, the Contr_No would be displayed on your form or browse), but the result is that when you view the field on a form or browse, the Contractor name is displayed rather than the Contr_No; when you do an Ascending or Descending sort, the fields are sorted by name rather than number; and when you use the Contr_No field on a report, the name prints out instead of the number. In other words, to a large extent, the user sees the Contractor name even though the field they are looking at is Contr_No.
However, in experimenting with this, I've found that if you do a query or find, the number is used rather than the name, so the user would be seeing a Contractor name, but would need to know and use the Contr_No when doing a query or find. I think this would be more than a little confusing to the user.
My feeling is that we should just include the Contractor name field in the Projects table even if this doesn't follow normalization rules to the letter. It would be filled automatically when the Contr_No is looked up and would be read-only, so it couldn't be changed. Then we'd write a little procedure for updating the project records when the name is changed in the Contractor table (it won't be changed very often).
Any opinions on how you would handle this? Thanks.
Here's the issue: The Projects are commonly referred to by the Contractor Name/Project Name (i.e. the Smith North Hills project where Smith is the Contractor and North Hills is the project name). Since the Contractor Name is not a field in the Projects table, a colleague set this up to make Contr_No a "lookup field." In field rules, lookup rules are defined for the Contr_No field, with Contr_No to be filled but not displayed; and Contractor to be displayed but not filled. To be honest, this is a feature I didn't even realize existed (I thought you'd see the name when you looked it up, but when you selected one, the Contr_No would be displayed on your form or browse), but the result is that when you view the field on a form or browse, the Contractor name is displayed rather than the Contr_No; when you do an Ascending or Descending sort, the fields are sorted by name rather than number; and when you use the Contr_No field on a report, the name prints out instead of the number. In other words, to a large extent, the user sees the Contractor name even though the field they are looking at is Contr_No.
However, in experimenting with this, I've found that if you do a query or find, the number is used rather than the name, so the user would be seeing a Contractor name, but would need to know and use the Contr_No when doing a query or find. I think this would be more than a little confusing to the user.
My feeling is that we should just include the Contractor name field in the Projects table even if this doesn't follow normalization rules to the letter. It would be filled automatically when the Contr_No is looked up and would be read-only, so it couldn't be changed. Then we'd write a little procedure for updating the project records when the name is changed in the Contractor table (it won't be changed very often).
Any opinions on how you would handle this? Thanks.
Comment