Create a field in a table that is the combined results of other fields in the same tbale

[dave_282]

Hi All,



I want to create a field in a table that is the combined results of other fields in the same table. The table is already set up with all the fields called code1, code2, code3..all the way to 7 and another filed is called Combined Code. I want combined code to show what ever is in each off these 7 fields, which are optional. I want them split with a - .

Does anyone know where any how I do that?

[cyanidem]

Create your CombinedCode field in table design mode first. Then run update query.

UPDATE YourTableName
SET CombinedCode=code1 & "-" & code2 & "-" & code3 & "-" & code4 & "-" & code5 & "-" & code6 & "-" & code7;

(Assuming code1, code2, etc are of text type.)

[dave_282]

Thank you. Where do I do that? I thought I'd need to update the field properties in some way? I cant see where to do an update query.

[cyanidem]

As I said:
1. Create CombinedCode field in table design mode. Meaning right click on table name in navigation bar on the left and choose design mode. Then add new field at the end of fields list with data type Text.
2. You may use qery wizard to create simple select query of this table of yours. Then in query design mode (again, right click on query name on the left, design mode) change query type to update, then under your CombinedCode field, in "update to" row input: code1 & "-" & code2 & "-" & code3 & "-" & code4 & "-" & code5 & "-" & code6 & "-" & code7.
3. Before you run your query view it first to make sure you'll be updating CombinedCode to proper values.

[orange]

The fields code1, code2, code3.....code7 indicate a not normalized structure. This is contrary to basic relational database design. Could you please describe what you are trying to do in plain English -- no Access jargon?

Before getting too deeply into the nitty gritty of Access, you should do 1 or 2 tutorials on database and table design. It will prevent hours of frustration.

Here are 2 tutorials from Rogers Access Library that should help. It would take about 30-45 minutes.
1) Entity Relationship
2) Normalization

Good luck.

[dave_282]

Im afraid this didn't work "code1 & "-" & code2 & "-" & code3 & "-" & code4 & "-" & code5 & "-" & code6 & "-" & code7;" . There are columns in a table and I have another column called combinedcode. I want the value of the code1, code 2, code3 etc fields to populate the combinedcode field. I would have though there's some syntax I can just put in default value of the field property or something?

[cyanidem]

I'm afraid default value won't solve your problem. Have you tried update query? Or are you still waiting for some magic syntax? Also you really should read links provided by orange.

[John_G]

If you are trying to create field that will be automatically populated when the other fields are populated, you can't. Excel is good for that sort of thing, but not Access. The only way would be to use a form with a bit of VBA to populate that field.

That said, it is bad database practice to include calculated fields in a database table. Create a normal select query containing the calculation instead.

[dave_282]

Having an update query isn't ideal as I need it to do it automatically, not have something else that needs to be run. Yeah I knew everything in those links.

[cyanidem]

Then do it in BeforeUpdate event or something, as John_G suggests.

[June7]

1. As Orange suggested, your data structure is not normalized. If you understand this and still want this structure, then all good.

2. Saving calculated data is usually a bad idea. It is certainly not necessary in this case. Calculate the 'combined code' when needed.

3. If a field is empty, the "-" will still concatenate by using the & operator. If the field has Null and you want to suppress the "-", try:

code1 & "-" + code2 & "-" + code3 & "-" + code4 & "-" + code5 & "-" + code6 & "-" + code7