Database is Access 2003 format
Developing in Access 2007

I want to build a combo box with row values from a query (simple
enough) /plus/ a few values I want to type in myself. Say the query
returns two rows:

Alligator cracking
Excessive potholes

Now I want to have a value that is always there plus what the query
returns, as in

Excessive Cross Slope
Inadequate Cross Slope
Alligator cracking
Excessive potholes

in which the first two are always there and the last two come from the
query.

Now it seems to me that this could be done with a union of the query
and a SELECT * FROM tbl_alwaysThere, but I would like to avoid adding
another table to my database just to accomplish this.

I guessed at a few things: SELECT "Excessibe Cross Slope"; and SELECT
* FROM "Excessive Cross Slope" but neither of those worked.

Does anyone know how I can accomplish this?

- Paul Schrum

PaulSchrum wrote:
> Database is Access 2003 format
> Developing in Access 2007
>
> Now it seems to me that this could be done with a union of the query
> and a SELECT * FROM tbl_alwaysThere, but I would like to avoid adding
> another table to my database just to accomplish this.
>
> I guessed at a few things: SELECT "Excessibe Cross Slope"; and SELECT
> * FROM "Excessive Cross Slope" but neither of those worked.
>
> Does anyone know how I can accomplish this?
>
> - Paul Schrum

Unfortunately, Jet requires a FROM clause. Why not do a

UNION ALL
SELECT TOP 1 "Excessibe Cross Slope"
FROM TheTableWhereYouAreGettingTheOtherValues
?

Alternatively, you could use one of the system tables ...

On Mon, 14 Feb 2011 19:20:45 -0600, Marshall Barton <(E-Mail Removed)>
wrote:

>I use a utility table with a single record (sometimes used
>for things like system id, client name, or whatever), then I
>can do what you want this way:

This is a builtin feature of default Oracle database installations (the table
is named "Dual", and it's uneditable); it can come in very handy.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

Thank you Marshall. I will implement this.

- Paul

On Feb 14, 8:20*pm, Marshall Barton <marshbar...@wowway.com> wrote:
> PaulSchrum wrote:
> >Database is Access 2003 format
> >Developing in Access 2007
>
> >I want to build a combo box with row values from a query (simple
> >enough) /plus/ a few values I want to type in myself. *Say the query
> >returns two rows:
>
> >Alligator cracking
> >Excessive potholes
>
> >Now I want to have a value that is always there plus what the query
> >returns, as in
>
> >Excessive Cross Slope
> >Inadequate Cross Slope
> >Alligator cracking
> >Excessive potholes
>
> >in which the first two are always there and the last two come from the
> >query.
>
> >Now it seems to me that this could be done with a union of the query
> >and a SELECT * FROM tbl_alwaysThere, but I would like to avoid adding
> >another table to my database just to accomplish this.
>
> >I guessed at a few things: SELECT "Excessibe Cross Slope"; *and SELECT
> >* FROM "Excessive Cross Slope" but neither of those worked.
>
> I use a utility table with a single record (sometimes used
> for things like system id, client name, or whatever), then I
> can do what you want this way:
>
> SELECT somefield FROM lookuptable
> UNION ALL
> SELECT "Excessive Cross Slope" FROM onerowtable
> UNION ALL
> SELECT "Inadequate Cross Slope" FROM onerowtable
>
> If you don't already have a one row table and you can not
> bring yourself to create one, then use:
>
> SELECT somefield FROM lookuptable
> UNION
> SELECT "Excessive Cross Slope" FROM lookuptable
> UNION
> SELECT "Inadequate Cross Slope" FROM lookuptable
>
> Your lookuptable might be small enough that you won't be
> bothered by this being slower than the one row table.
>
> --
> Marsh
> MVP [MS Access]- Hide quoted text -
>
> - Show quoted text -