How to create a new column in a select query

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

3 Answers

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

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".

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