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

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

-- 

        Ken Snell
<MS ACCESS MVP>


"Jason Lepack" <jlepack@gmail.com> wrote in message 
news:1178719630.695350.22120@y5g2000hsa.googlegroups.com...
>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
> 

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
..

"Ken Snell (MVP)" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
news:ed7XySkkHHA.3928@TK2MSFTNGP02.phx.gbl...
> 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
>
> -- 
>
>        Ken Snell
> <MS ACCESS MVP>
>
>
> "Jason Lepack" <jlepack@gmail.com> wrote in message 
> news:1178719630.695350.22120@y5g2000hsa.googlegroups.com...
>>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
>>
>
> 

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

On May 9, 10:17 am, "Ken Snell \(MVP\)"
<kthsneisll...@ncoomcastt.renaetl> wrote:
> 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
>
> --
>
>         Ken Snell
> <MS ACCESS MVP>
>
> "Jason Lepack" <jlep...@gmail.com> wrote in message
>
> news:1178719630.695350.22120@y5g2000hsa.googlegroups.com...
>
>
>
> >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- Hide quoted text -
>
> - Show quoted text -

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

Cheers,
Jason Lepack

On May 9, 11:47 am, "John Spencer" <spen...@chpdm.edu> wrote:
> 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
> .
>
> "Ken Snell (MVP)" <kthsneisll...@ncoomcastt.renaetl> wrote in messagenews:ed7XySkkHHA.3928@TK2MSFTNGP02.phx.gbl...
>
>
>
> > 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
>
> > --
>
> >        Ken Snell
> > <MS ACCESS MVP>
>
> > "Jason Lepack" <jlep...@gmail.com> wrote in message
> >news:1178719630.695350.22120@y5g2000hsa.googlegroups.com...
> >>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- Hide quoted text -
>
> - Show quoted text -