• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1169
  • Last Modified:

Access SQL - add constant row to result

I want to create a report producing job totals for each division, and then list new job details if any.  I want a total for every division, plus a total for (NONE) as some jobs don't have a division.

SO, my report source query needs to have every division from DIVISION table, plus one more (NONE).

eg.
red group
blue group
green group
(NONE)


I have done this before but just can't find it.

It is something like:
select DivisionName from DIVISION UNION ALL select "(NONE)"

but access requires an input table for this last bit.

I do not want to create a dummy table, nor add (NONE) to my DIVISION table.  I'm sure I have been successful with this before.  I am dreaming?
0
MonkeyPie
Asked:
MonkeyPie
1 Solution
 
DOSLoverCommented:
I think you were almost there! Yes, Access requires a table name. A 'Top 1' from the same table can help as follows:

select DivisionName from DIVISION 
UNION ALL 
select Top 1 '(NONE)' as DivisionName 
    from DIVISION

Open in new window

0
 
MonkeyPieAuthor Commented:
perfect.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now