Search code examples
sqlms-accessalter-table

Insert columns with repeating default data into query

Using MS Access for a "simple" task.

SELECT Users.UserName, Users.ID, Record.Course
FROM Record INNER JOIN Users ON Record.Name = Users.Username;

What I need are two additional columns to the output. One that just says "Add" and one that says "Role2". These will be the same for every row.

So if originally the query pulled a line that looked like this:

smith,ABC123,DEF-100
jones,ABC456,DEF-101

I would want it to instead output:

Add,smith,ABC123,Role2,DEF-100
Add,jones,ABC456,Role2,DEF-101

My attempts with "ALTER TABLE" have failed.

Solution

  • ALTER TABLE does not apply here. This just calls for calculated fields with literal text.

    SELECT "ADD" AS F1, Users.UserName, Users.ID, "Role2" AS F2, Record.Course
FROM Record INNER JOIN Users ON Record.Name = Users.Username;

    Use whatever field names you prefer in place of F1 and F2.