How to add custom rows to an SQL query in MS Access?

Question

I tried using UNION but I used to get this message:

Query input must contain at least one table or query

The query I tried was:

SELECT NULL AS ID, NULL AS Item
  UNION 
    SELECT Items.ID AS ID, Items.Item as Item
    FROM Items 
    INNER JOIN People 
    ON Items.PID=People.PID
    WHERE People.Name='John';

Both select statements run OK independently.

score 1 · Answer 1 · answered Jun 23 '15 at 14:12

1

Use a Dual table

SELECT NULL AS ID, NULL AS Item
FROM dual
UNION ALL
SELECT i.ID AS ID, i.Item AS Item
FROM Items i
INNER JOIN People p ON i.PID = p.PID
WHERE p.Name = 'John';

answered Jun 23 '15 at 14:12

Matt

14,906
27
99
149

2

I don't believe Access has the Dual table, though if you join your answer with HansUp's from here: http://stackoverflow.com/questions/7933518/table-less-union-query-in-ms-access-jet-ace then it should work. – Newd Jun 23 '15 at 14:15
It does use DUAL in Access... @Newd – Hituptony Jun 23 '15 at 14:16
@Hituptony Are you sure? Do you happen to have a link that references that? – Newd Jun 23 '15 at 14:17
@Newd you have to create it, but it can be done. http://gpgonaccess.blogspot.com/2010/03/making-dual-table.html – Hituptony Jun 23 '15 at 14:17
@Hituptony I would go ahead and click the link that I posted and review the answer I mentioned :-) – Newd Jun 23 '15 at 14:19
@Newd You speaka my language. – Hituptony Jun 23 '15 at 14:20

score 1 · Answer 2 · answered Jun 23 '15 at 14:24

In MS Access, you will need to use a kludge. This might work:

SELECT TOP 1 NULL AS ID, NULL AS Item
FROM (SELECT TOP 1 *
      FROM ITEMS
      ORDER BY ID
     ) as i
UNION ALL
SELECT Items.ID AS ID, Items.Item as Item
FROM Items INNER JOIN
     People
     ON Items.PID = People.PID
WHERE People.Name = 'John';

The only purpose of the subquery is to get a table with one row. Remember that TOP 1 in MS Access can return more than one row if there are ties.

plus 1 for teaching me a new word "kludge"... and a good answer ofc — Matt, Jun 23 '15 at 14:33

score 0 · Answer 3 · answered Jun 23 '15 at 14:12

You need to specify what table you want your data from. Try this:

SELECT NULL AS ID, NULL AS Item
FROM Items INNER JOIN People ON Items.PID=People.PID
UNION 
SELECT Items.ID AS ID, Items.Item as Item
FROM Items INNER JOIN People ON Items.PID=People.PID
WHERE People.Name='John';

score 0 · Answer 4 · answered Jun 24 '15 at 09:34

Perhaps if the union query order is changed to start with the formed query and the null query to follow:

SELECT Items.ID AS ID, Items.Item as Item
FROM Items 
INNER JOIN People 
ON Items.PID=People.PID
WHERE People.Name='John'

union 

SELECT NULL AS ID, NULL AS Item

How to add custom rows to an SQL query in MS Access?

4 Answers4