How to create a new column in a select query

Question

In MS Access, I want to insert a new column into the returned result of a select query. The new column has the same value for every row. For example, my select returns columns A, B and I want C to be the new column created by the select query:

A   B   C
----------
a1  b1  c
a2  b2  c
a3  b3  c

score 84 · Accepted Answer · answered Sep 22 '10 at 13:29

84

select A, B, 'c' as C
from MyTable

answered Sep 22 '10 at 13:29

D'Arcy Rittich

167,292
40
290
283

Is there any reference for this? – Dante Oct 16 '22 at 12:57
can use case expression to set targeted value for field C. – Nieb_Hasan_077 Dec 10 '22 at 06:55

score 12 · Answer 2 · edited Oct 21 '20 at 00:04

12

SELECT field1, 
       field2,
       'example' AS newfield
FROM TABLE1

This will add a column called "newfield" to the output, and its value will always be "example".

edited Oct 21 '20 at 00:04

Luís Ramalho

10,018
4
52
67

answered Mar 03 '15 at 08:36

Chenthil

296
4
9

score 1 · Answer 3 · answered Sep 22 '10 at 14:53

It depends what you wanted to do with that column e.g. here's an example of appending a new column to a recordset which can be updated on the client side:

Sub MSDataShape_AddNewCol()

  Dim rs As ADODB.Recordset
  Set rs = CreateObject("ADODB.Recordset")
  With rs
    .ActiveConnection = _
    "Provider=MSDataShape;" & _
    "Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Tempo\New_Jet_DB.mdb"
    .Source = _
    "SHAPE {" & _
    " SELECT ExistingField" & _
    " FROM ExistingTable" & _
    " ORDER BY ExistingField" & _
    "} APPEND NEW adNumeric(5, 4) AS NewField"

    .LockType = adLockBatchOptimistic

    .Open

    Dim i As Long
    For i = 0 To .RecordCount - 1
      .Fields("NewField").Value = Round(.Fields("ExistingField").Value, 4)
      .MoveNext
    Next

    rs.Save "C:\rs.xml", adPersistXML

  End With
End Sub

How to create a new column in a select query

3 Answers3

Linked