Thread: combining columns into 1 access

Hi mugs62,

If you're just learning how to build an Update query, or doing homework, or whatever, then... no problem, here's the SQL of what you described:

Code:

UPDATE tblAddress SET tblAddress.Address3 = [City] & ", " & [State] & ", " & [Zip];

However, you should note that you would not usually do something like this in a table. You'd be far better off building your combined address in a select query, and using it's result where-ever you need it in your database (i.e. on forms or in reports, etc.). Normally, tables shouldn't store "calculated" fields based on other fields.

Otherwise, Enjoy!

Cheers,

This is the way to create table with two fields

Code:

Sub NewDatabase()
    Dim wspDefault As Workspace, dbs As Database
    Dim tdf As TableDef, fld1 As Field, fld2 As Field
    Dim idx As Index, fldIndex As Field

    Set wspDefault = DBEngine.Workspaces(0)
    ' Create new, encrypted database.
    Set dbs = wspDefault.CreateDatabase("Newdb.mdb", _
        dbLangGeneral, dbEncrypt)
    ' Create new table with two fields.
    Set tdf = dbs.CreateTableDef("Contacts")
    Set fld1 = tdf.CreateField("ContactID", dbLong)
    fld1.Attributes = fld1.Attributes + dbAutoIncrField
    Set fld2 = tdf.CreateField("ContactName", dbText, 50)
    ' Append fields.
    tdf.Fields.Append fld1
    tdf.Fields.Append fld2
    ' Create primary key index.
    Set idx = tdf.CreateIndex("PrimaryKey")
    Set fldIndex = idx.CreateField("ContactID", dbLong)
    ' Append index fields.
    idx.Fields.Append fldIndex
    ' Set Primary property.
    idx.Primary = True
    ' Append index.
    tdf.Indexes.Append idx
    ' Append TableDef object.
    dbs.TableDefs.Append tdf
    dbs.TableDefs.Refresh
    Set dbs = Nothing
End Sub