How to add calculated column in Access via SQL

Question

How to add calculated column to Access table in SQL?

I know that I can add a column with SQL as follows:

ALTER TABLE Clients ADD COLUMN AccountDate TEXT(60)

Thanks, Vitor

Answer 1

You cannot add a calculated column with SQL because the calculated field requires an expression and cannot be provided through SQL. Technically calculated field is the base type - int, double, text, etc. Above the base type, this is an expression that helps Access do math / logic.

You can use VBA to create a calculated column.

-- create a module and let assume that
-- your table has Field1 integer and Field2 integer
-- we will add field3

Public Sub CreateField()

    Dim DB As DAO.Database
    Dim TableDef As DAO.TableDef
    Dim Fld As DAO.Field2

    Set DB = CurrentDb()
    Set TableDef = DB.TableDefs("Table1")

    Set Fld = TableDef.CreateField("field3", dbDouble)
    Fld.Expression = "[field1] * [field2]"
    TableDef.Fields.Append Fld

    MsgBox "Added"

End Sub

As Gordon and Bee Jones mentioned, you can create a view or saved query with the appropriate calculations.

Answer 2

, MS Access . :

create view v_clients as
    select c.*, (col1 + col2) as col3
    from clients;

Answer 3

What do you want to calculate? Have you tried something like:

ALTER TABLE Clients 
ADD COLUMN AccountDate AS (Column1 * Column2);