如何通过SQL向Access添加计算列 [英] How to add a calculated column to Access via SQL

问题描述

如何在SQL中将计算出的列添加到Access表中?

How do i add a calculated column to an Access table in SQL?

我知道我可以使用SQL添加这样的列:

I know i can add a column with SQL like this:

ALTER TABLE Clients ADD COLUMN AccountDate TEXT(60)

谢谢， 维托尔(Vítor)

Thanks, Vítor

推荐答案

您无法使用SQL添加计算列，因为计算字段需要一个表达式并且不能通过SQL提供.从技术上讲，计算字段是基本类型-int，double，text等.在基本类型上方是一个表达式，可以帮助Access进行数学/逻辑运算.

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

您可以使用VBA创建计算列

-- create a module and let's 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

如Gordon和BJones所述，您可以使用相关计算来创建视图或保存的查询.

As Gordon and BJones mentioned, you could create a view or saved query with relevant calculation.

这篇关于如何通过SQL向Access添加计算列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！