Add a row to a query result.

[Jason Lepack]

I have a query that I am using to return a list of jobs to be used as
the data source of a combo box in a local vb.net application.

job:
job_id, job_num, other insignificant columns
1, '12345-11', ...
2, '12346-22', ...
etc.

My query is this:
SELECT
job_id,
job_num
FROM
job
ORDER BY job_id

And returns:
1, '12345-11'
2, '12346-22'

Now, I would like to return this:
-1, 'New Job...'
1, '12345-11'
2, '12346-22'

Can I not use a Union Query for that?

I can use:
SELECT -1, 'New Job...'

but when I union it with the existing query I get:
"Query input must contain at least one table or query."

Any help including a better way of doing this would be much
appreciated.

Cheers,
Jason Lepack

 

[Ken Snell \(MVP\)]

Yes, but you need a table that contains those values in a single record
(easiest approach):

SELECT
job_id,
job_num
FROM
job

UNION ALL

SELECT
job_id,
job_num
FROM
jobNewJob
ORDER BY job_id

 

[John Spencer]

Try the following

SELECT
job_id,
job_num
FROM
job

UNION

SELECT
-1, "New Job"
job_num
FROM
job
ORDER BY job_id

If there are a lot of records in the JOB table that could be slow. You can
speed it up by using any table that contains just a few records to do the
second query.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jason Lepack]

Thanks Ken, I used your solution, I was already thinking about doing
that, and just added a column for each of the different combo boxes so
that I could have the different descriptions.

id, job, item, life
-1, 'New Job...', 'New Item...', 'New Life...'

Cheers,
Jason Lepack

 

[Jason Lepack]

Thanks John. I already tried that and didn't like the result. You're
as always helpful though.

Cheers,
Jason Lepack