Solved

Access SQL - add constant row to result

Posted on 2013-06-19
2
1,005 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

691 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