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! 7 2672
Although I can't help you in your situation, thanks for the donuts :)
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! sm************* **@hotmail.com wrote in
news:11******** *************@g 43g2000cwa.goog legroups.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.
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.
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. sm************* **@hotmail.com wrote in
news:11******** **************@ g49g2000cwa.goo glegroups.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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Ramachandran Pramod |
last post by:
HI Group,
I have installed WAS 5.0 in my machine recently and would like to
configure Oracle JDBC thin driver. I tried it with the admin console,
with the following parametners
JDBC provider implementation classname :
oracle.jdbc.pool.OracleConnectionPoolDataSource
|
by: Mark Carter |
last post by:
Suppose I wanted to combine a thin client written in VB6 with a server
on a remote machine. Since this is the python group, assume for now
that it could be written in python (although VB6 would be a nice
alternative). What technologies would I use?
|
by: Harish |
last post by:
Hi,
Why is "jdbc:oracle:thin" called a thin driver?
Thanks in advance.
Harish.
|
by: The Plankmeister |
last post by:
If I set a border value to something like:
border : thin solid blue;
It will look perfect in all browsers except IE (No surprise there) where
'thin' borders are rendered 2 pixels wide. If I replace it with:
border : 1px solid blue;
It looks the same in all browsers. My question: Does one have advantages or
|
by: ak |
last post by:
Hi all
I figured I would ask here if somebody
done something similar to this since I am
new to .NET :
I need to implement a thin client; it is involves
communicating with a database and above all
displaying the information graphically allowing
the user to view the data in different ways like
| |
by: RBalbat |
last post by:
Hello all,
I have encountered an unexpected problem where if I render a table (in IE or
Opera) and there are cells that contains URLs with long query strings, even
though I specify the table element with the width attribute set as 90%, when
I render the document, it spans way off the right side of the screen. For
some reason, having a long query string in the cell does some funcky stuff.
Does anyone know how to *force* the table to only...
|
by: wnstnsmith |
last post by:
Dear all,
Maybe I'm stupid, in any case I'm stupefied by now. Please help.
Stored procedures in Access should be accessible from a java-applet throught
a callable statement, but all I get is error messages from Access, which
tell me nothing.
Can anybody tell me what is wrong with the following:
|
by: C# Learner |
last post by:
Here's a simple way to recreate my flicker problem.
- Create a new project.
- Put a ListBox on the form.
- Resize it so that it can accompany 20 items.
- Put a button on the form.
- Double-click the button to add a Click event handler.
- Insert the following code in the event handler:
{
|
by: Jeff |
last post by:
Hello everyone. I've searched through the archives here, and it seems
that questions similar to this one have come up in the past, but I was
hoping that I could pick your Pythonic brains a bit.
Here's a broad overview of what I need to do: cross-platform, client-
side GUI apps that interact with a server backed by a database. I'd
also like the possibility of having a web interface for small portions
of the app. It will be a fairly...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |