Adding a calculated field to a table in Access10

[skyrat]

I'm trying to help a friend with a verybadly build Access database. Access 10, upgraded from 3 and 7, now running in Windows 10. Being a newbie at Access myself (not in other,bigger, databases) I've met a problem adding a calculated field to atable. As it's a Dutch version of Access, I'll try to translate hereinto English wherever necessary.
One example is a field calculating afull name from Firstname, Familyname etc.

1. I chose “Velden bewerken” (edit fields ?)
2. “Meer velden” (more fields ?)
3. From the dropdown list I chose “berekend veld” (calculated field ?)
4. From the next dropdown list I chose “Tekst” (text ?)
5. According to everything I found on the internet so far, an expression builder should now appear,but absolutely nothing happens !!!

please help me !!!

[ranman256]

you dont add calculated fields to a table, you add them to a query.

[CJ_London]

I think you are perhaps starting from the wrong point
1. open table in design view
2. select a field or create a new one on a new row
3. for data type select calculated

or you can add the expression at the bottom.

Calculated fields are not advised, they have limited functionality and cannot be indexed. In the case of concatenating names together it is rarely as simple as firstname + middlename + family name. You may need title (Mr/Mrs/Ms/Dr etc) there may not be a middle name, you may need to remove double spaces, use proper format (James rather than james or JAMES) etc. So the calculation becomes complex and perhaps not doable at the table level. As Ranman suggest, better to do in a query

[skyrat]

I think you are perhaps starting from the wrong point
1. open table in design view
2. select a field or create a new one on a new row
3. for data type select calculated

or you can add the expression at the bottom.



Calculated fields are not advised, they have limited functionality and cannot be indexed. In the case of concatenating names together it is rarely as simple as firstname + middlename + family name. You may need title (Mr/Mrs/Ms/Dr etc) there may not be a middle name, you may need to remove double spaces, use proper format (James rather than james or JAMES) etc. So the calculation becomes complex and perhaps not doable at the table level. As Ranman suggest, better to do in a query

If i try that there is NO type "calculated", that's exactly why I tried it the way I did !! And THERE it does show "calculated". But when I chose "text" after that, it stops completely.

[CJ_London]

sounds like although you may have upgraded to using 2007 you have not upgraded the actual database from .mdb to .accdb

[Micron]

I'm on the "forget it" side of things. See http://allenbrowne.com/casu-14.html

You're only going to make a "very badly" built db worse, and there's absolutely no reason to combine data such as first/last names as you mentioned. In fact, such combinations can very easily become problematic. You do such things on forms/reports or queries, not in tables.

[skyrat]

Access was upgraded to 10 and that's the version (according to several internet cases) where Access introduced calculated fields in tables. So I'm not the only one who wants that possibility. In all other databases I used it's possible and often even advised !!

[skyrat]

Access was upgraded to 10 and that's the version (according to several internet cases) where Access introduced calculated fields in tables. So I'm not the only one who wants that possibility. In all other databases I used it's possible and often even advised !!
But you're right : it's an .mdb, not .accdb so that may be the reason.
The owner of the database (not me) probably didn't upgrade to .accdb. Can that still be done ?

[Micron]

Yes, you can save it as an accde. Likely there won't be any incompatible features going from older to newer version. If you convert, you may find that no one will be able to open the file from an older version, so you definitely want to keep an original version.
You can argue for calculated table fields all you want since you will find an audience of believers. I'm not one of them, but all I've got to go on is experience, which maybe doesn't count for some people.

[skyrat]

Do you mean .accde now, or accdb as mentioned before ?

[Micron]

sorry, I meant accdb. I presume you know the difference. Regardless, the notion to save a backup beforehand is applicable either way.

[skyrat]

I saved the database as .accdb, looked up the table I want to add the calculated field in, went into design mode, but there still isn't a possibility for type Calculated for a new field !
Of course I always make a backup of a working database before I start changing it

[Micron]

Your secondary problem might be that you didn't save that table yet, which is why no fields appear in the list.
No wait, you closed the db and re-opened and went to the table, and surely you can't save a table with no fields.
I'm with Ajax; there is no Text option if you follow the right process.

[skyrat]

Just to see if I'm using the right Access10 version I started a whole new empty database, added a table with fielda (text) and fieldb (text).
Then I added a fieldc and ....... the data type now shows "calculated" and after putting [fielda]+[fieldb] as the expression for fieldc it works . So the Access version is OK, but the real database causing the problem still doesn't
I keep trying !!

[CJ_London]

you could try importing your tables from the 'old' db to the new one. Would recommend caution and copy structure only, and if that gives what you want, then import the data. If you had genuinely updated the backend to .accdb (as well as the front end) and you could not add calculated fields, it implies to me that the old db may be corrupted in some way.

Will be interested to hear how you get on with it practically - In my view since you can't edit it or index it at best it is a field you might display once in a while without having to calculate it.