Access 2007 question

Ok, so I know that not very many people use Access, so I don't know how much help I'm going to get. Here's my problem. I've got a table that's pulling data (this is using the Asset List database template). In one column, I'm asking my employees to input their material type, say for instance steel or wood or whatever. Then in another column, I'm assigning that material type an internal code, say A100 for steel or A200 for wood. But the employees don't know these codes (or rather, shouldn't need to know them). I would like to populate the code column automatically based on the material they input in the material column (i.e., if they fill out the asset form with material type wood, the corresponding material code cell automatically becomes A200). How would I do this? I tried setting up a query, but it didn't seem to work, so maybe I did it wrong.

 

Standard database design rules dictate that you should NEVER have the same info represented twice in a row in a table. In this case you are violating that rule, by having a "friendly" name and a code for the material type. Instead you should have a table of material types, in it you would have the material name and a material code. Then establish a relationship between the table you have and this material type table. The value stored in your table should be the internal code. Then anywhere that these users see that code, the relation would be used to display the material name instead.

You didn't say how the data is actually being populated. Are folks typing directly into the table? (BTW that's never a really good idea.) Or are you using a form? If you are using a form, you can create a drop down list for that field that displays the material type in a user friendly form (i.e. steel, wood, etc.) but enter the data into the table using the internal codes. This dropdown list would be populated from the material type table.

Actually, if you create the material type table Access will let you reference the table while in datasheet view (i.e. where users directly input into the tables). In the design view when you select a column there is a lookup tab. This tab will let you establish the relationship and show the friendly name field and bind the column to the internal code field. This way the code is what is always stored, but the friendly name is what is always displayed to the user.

Gary

 

I created a table for our cooks at college, so when they made the menu for the week they don't have to re-enter common foods (pizza, hamburger, etc) every week. I created two different tables, one for breakfast and the other for lunch and dinner. Using a different table, I think access but I can't remember, I set up a query so they designate the breakfast table or lunch/dinner table and enter the new data (food). I then set up a three column page in publisher, linking the main breakfast and lunch/dinner table to it. They would choose from the drop-box what food they will be serving on what day.

 

Thanks for the help, guys. I'll try this out.