Adding my own rows to a Union Query without adding another table?

[PaulSchrum]

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

 

[Bob Barrows]

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

 

[John W. Vinson]

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/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[PaulSchrum]

Thank you Marshall. I will implement this.

- Paul

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 -