Solved

Access SQL - add constant row to result

Posted on 2013-06-19
2
940 Views
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).

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
Comment
Question by:MonkeyPie
2 Comments
 
LVL 5

Accepted Solution

by:
DOSLover earned 500 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 
UNION ALL 
select Top 1 '(NONE)' as DivisionName 
    from DIVISION

Open in new window

0
 

Author Closing Comment

by:MonkeyPie
ID: 39261394
perfect.  Thank you.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

777 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