Calculated Field in a Table - Microsoft Access Forum


	Back to Access Forum Comments List

Calculated Field in a Table

Seyhan Gul

15 months ago

Hello. I am a beginner in MS Access and I am trying to build up my own database for work. I have a "products" table and I have to use Product Codes for each record in this table. The product code consists of values from 3 different fields: Category + Sub-Category + Sequence No = Product Code. I use numbers in each of these fields but formatted as text. For example, 1 (for Category) + 100 (for Sub-category) + 001 (for Sequence No) = 1100001 (Product Code). I created a "Calculated" field with an expression [Category] & [SubCategory] & [SequenceNo], and in this way I can have proper Product Codes. Do you think this is a correct way of creating the product codes in my table? Can I have problems in the future when I create relationships with other tables because I am using a calculated field in a table?

Thank you.

Richard Rost @Reply

15 months ago

I strongly recommend against putting calculated fields in tables. They have lots of problems that you run into later on. I suggest using a calculated query field for this. You won't be able to form relationships with it, but that's why I recommend using auto numbers for all of your relationships. You don't have to worry about what auto numbers are; that's for the database to form those internal relationships. You can create your product number if you want, but do it with a query.

Calculated Fields
AutoNumbers Good or Bad?
AutoNumbers Are NOT For You
Customer Codes

	Richard Rost @Reply 15 months ago
Or, if you really want to do that, then in the before insert event of your form (after all of those fields have been entered), you could create that product code and save that in the table. Make sure it's an indexed, no-duplicates field. But I wouldn't use that as your primary key.

	Seyhan Gul OP @Reply 15 months ago
Thanks a lot for your reply. I think I'll give up this idea as it is not possible to index a calculated field, and I might accidentally create two identical product codes using this method.

	Richard Rost @Reply 15 months ago
Well, you can still create that value. As long as you set the field equal to index, no duplicates, then you won't accidentally create two identical product codes. But you'll have to check for it and see if it's already there. If it is, maybe just add one to it or more. There are lots of ways around it. Watch those videos I gave you first, especially the customer codes one.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen