Solved

Access SQL - add constant row to result

Posted on 2013-06-19
2
920 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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 views 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 Microsoft Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now