creating a query out of thin air

[smith_lannynospam]

I was wondering if it is possible to create simple queries with the
data included in the select statement, similar to how you use the word
VALUES (x1,x2,...) in in INSERT statement.

The reason I want to do this is for simple graphs. For example, a pie
chart where there are only 2 values needed and they are calculated at
runtime. I can create a one-row query by doing something like:

Select "value1" as Field1, "Value2" as Field2, etc.

but I want to add an extra row or two, because the rowsource for the
chart object requires at least two rows, the first being for labels I
believe. Right now I am having to use temporary tables, which I do not
like, especially for something so small.

Also, I would like to express my thanks to this group. It has made me a
better developer. If you were all in one place, I would send you a box
of donuts, and Krispy Kremes too, not the cheap grocery store kind!

[Beacher]

Although I can't help you in your situation, thanks for the donuts :)

[Chuck Grimsby]

Sure. No problem. You'll need to use VBA however, to "hold" your SQL
string while you "build" it. After that, you can either use it to do
whatever you need. On occasion, you'll need to store the string in a
QueryDef for later processing. I tend to do a _lot_ of this, by the
way. Very often, I'll find myself in need of a bunch of CrossTab
queries that need to be linked. So I build the SQL String in code,
write it to a DAO QueryDef, and go on to the next Crosstab. When I'm
done with all the CrossTabs, I usually have to build the "linking"
(summary) query which joins them all together. After that, it's not
uncommon for me to build yet another SQL query with selects off a
column or two of the summary query so that I can export the data to
excel or some other reporting instrument (HTML page or something).
Those final queries generally don't make it into a QueryDef since
there's really no need.

Anyways, yes, it's possible. And it's not all that big of a deal.

When the order of the rows is important, you may want to add in your
own ordering "column", which is included as an Expression of the query.
Just remember to Order By that column in your final query! (Yeah,
silly fingers don't always type what I tell them too....)
sm***************@hotmail.com wrote:

I was wondering if it is possible to create simple queries with the
data included in the select statement, similar to how you use the word
VALUES (x1,x2,...) in in INSERT statement.
The reason I want to do this is for simple graphs. For example, a pie
chart where there are only 2 values needed and they are calculated at
runtime. I can create a one-row query by doing something like:
Select "value1" as Field1, "Value2" as Field2, etc.
but I want to add an extra row or two, because the rowsource for the
chart object requires at least two rows, the first being for labels I
believe. Right now I am having to use temporary tables, which I do not
like, especially for something so small.
Also, I would like to express my thanks to this group. It has made me a
better developer. If you were all in one place, I would send you a box
of donuts, and Krispy Kremes too, not the cheap grocery store kind!

[Bob Quintal]

sm***************@hotmail.com wrote in
news:11*********************@g43g2000cwa.googlegro ups.com:

I was wondering if it is possible to create simple queries
with the data included in the select statement, similar to how
you use the word VALUES (x1,x2,...) in in INSERT statement.

The reason I want to do this is for simple graphs. For
example, a pie chart where there are only 2 values needed and
they are calculated at runtime. I can create a one-row query
by doing something like:

Select "value1" as Field1, "Value2" as Field2, etc.

but I want to add an extra row or two, because the rowsource
for the chart object requires at least two rows, the first
being for labels I believe. Right now I am having to use
temporary tables, which I do not like, especially for
something so small.

Also, I would like to express my thanks to this group. It has
made me a better developer. If you were all in one place, I
would send you a box of donuts, and Krispy Kremes too, not the
cheap grocery store kind!

You can use a union query operator to string toghether several
one-liners.

Select "label1" as Field1, "label2" as Field2, etc.
UNION
Select "value1" as Field1, "Value2" as Field2, etc.

--
Bob Quintal

PA is y I've altered my email address.

[smith_lannynospam]

Actually that is not quite what I meant, but I really like this
suggestion. It combines the flexibility of building SQL strings in code
on the fly with the power of daisy chaining access queries. I assume
you delete the queries when you are done with them. Does this cause
your .MDB file to bloat?

As far as what I was trying to do, all I wanted was to provide a chart
object a dataset like:

Type Percent
-------------- -----------
Apples 30
Oranges 70

without having to refer to tables or queries. I was trying to build a
query such as:

"Select 'Apples' as Type,'30' as Percent UNION Select 'Oranges' as
Type,'70
as Percent"

that doesn't refer to another table or query, but it didn't work. I
also tried setting the RowSourceType to Value List, and did some
experimenting there. I haven't been able to get it to work right yet,
because I can't figure out how the value list corresponds to columns
for the chart.

[smith_lannynospam]

Bob,
I had that idea, but I get the error message:

"Query input must contain at least one table or query."

I am using Access 2000. Perhaps someone out there with access to SQL
Server could try it there.

[Bob Quintal]

sm***************@hotmail.com wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

Bob,
I had that idea, but I get the error message:

"Query input must contain at least one table or query."

I am using Access 2000. Perhaps someone out there with access
to SQL Server could try it there.

Please quotethe text to which you reply above your reply. It
makes it much easier to respond.

Essentially you can spoof a select query by including the name
of any table in the databse.

so
SELECT "label1" as field1, "label2" as field2 from [sometable]
UNION

should not give the error message that you would get from
SELECT "label1" as field1, "label2" as field2 UNION

And Access will only return 1 row no matter the number of rows
in the table.

--
Bob Quintal

PA is y I've altered my email address.

[smith_lannynospam]

Bob Quintal wrote:

Essentially you can spoof a select query by including the name
of any table in the databse.

so
SELECT "label1" as field1, "label2" as field2 from [sometable]
UNION

should not give the error message that you would get from

Bob,

You are correct sir! That did work. Thank you for sharing that.

Perhaps it is the Redneck way of doing things, but in this situation it
saves me from having to use temporary tables.

Advice on quoting replies was well taken too.

Lanny