Access SQL - add constant row to result

Posted on 2013-06-19
Medium Priority
Last Modified: 2013-06-19
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).

red group
blue group
green group

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?
Question by:MonkeyPie

Accepted Solution

DOSLover earned 2000 total points
ID: 39261154
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 
select Top 1 '(NONE)' as DivisionName 
    from DIVISION

Open in new window


Author Closing Comment

ID: 39261394
perfect.  Thank you.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
If you need to implement application level security in an Access database application or other VBA code, I strongly encourage you to take advantage of Active Directory groups.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

587 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question