Hello! I am trying to obtain different information from the same Access 

database using different SQL queries. For example I want to use COUNT, 

SUM, etc. and specify output based on a field's data being X,Y,or Z 

(whatever 3 options would fill that field). 



I know how I can create multiple recordsets to display the info, but is 

there a way to use multiple SQL queries to save time? For example:



sql1 = "SELECT * FROM My_Table"

sql2 = "SELECT SUM(Amount), field1 FROM My_Table WHERE field1 = option_x"

sql3 = "SELECT * FROM My_Table WHERE field1 = '" & OPTION & "' "



...well, you get the idea. So if I used that how would I actually use them 

in the DataConn? 



ie.. 'rsOrders.Open SQL, DataConn'.



Or is there a way to perform SQL type sorting on the data after it's 

already retrieved from the database, say if I just used a basic "SELECT * 

FROM My_Table".



Any and all ideas are welcome as I need to learn/know several methods. 

Thanks to all in advance.



BRgds,

Scott Taylor

    When you're using aggregate functions, such as SUM and AVG, any field names

not contained in an aggregate function must be included in a GROUP BY clause.  I

know this is extremely annoying and I can't remember why you have to do this,

but I know that if you don't, aggregate functions don't make sense.



    You could try creating a query (view) to display all the information you

need in the correct format so you won't have to make three seperate SQL calls to

the database.



- Chuck



----- Original Message -----

From: "Scott Taylor" <scott.taylor@e...>

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, April 23, 2001 10:02 PM

Subject: [asp_databases] HOW2? Multiple SQL Queries??





> Hello! I am trying to obtain different information from the same Access

> database using different SQL queries. For example I want to use COUNT,

> SUM, etc. and specify output based on a field's data being X,Y,or Z

> (whatever 3 options would fill that field).

>

> I know how I can create multiple recordsets to display the info, but is

> there a way to use multiple SQL queries to save time? For example:

>

> sql1 = "SELECT * FROM My_Table"

> sql2 = "SELECT SUM(Amount), field1 FROM My_Table WHERE field1 = option_x"

> sql3 = "SELECT * FROM My_Table WHERE field1 = '" & OPTION & "' "

>

> ...well, you get the idea. So if I used that how would I actually use them

> in the DataConn?

>

> ie.. 'rsOrders.Open SQL, DataConn'.

>

> Or is there a way to perform SQL type sorting on the data after it's

> already retrieved from the database, say if I just used a basic "SELECT *

> FROM My_Table".

>

> Any and all ideas are welcome as I need to learn/know several methods.

> Thanks to all in advance.

>

> BRgds,

> Scott Taylor

I guess I can do a Query in Access but I was looking for a way to do it 

just using SQL statements and VBScript. Is there a noticeable slowdown in 

response time by using multiple queries and recordsets?



Scott



>     When you're using aggregate functions, such as SUM and AVG, any 

field names

> not contained in an aggregate function must be included in a GROUP BY 

clause.  I

> know this is extremely annoying and I can't remember why you have to do 

this,

> but I know that if you don't, aggregate functions don't make sense.

> 

>     You could try creating a query (view) to display all the information 

you

> need in the correct format so you won't have to make three seperate SQL 

calls to

> the database.

> 

> - Chuck

> I guess I can do a Query in Access but I was looking for a way to do it

> just using SQL statements and VBScript. Is there a noticeable slowdown in

> response time by using multiple queries and recordsets?



    Its definately slower to make multiple calls rather than one.  Three

queries, as long as they're not working on huge tables, is not going to threaten

your server with a resource lock if you're worried about that.  You'll need to

weigh spending time to write a query (and learning!) vs. performance loss.



- Chuck



----- Original Message -----

From: "Scott Taylor" <scott.taylor@e...>

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, April 24, 2001 12:53 PM

Subject: [asp_databases] Re: HOW2? Multiple SQL Queries??





> I guess I can do a Query in Access but I was looking for a way to do it

> just using SQL statements and VBScript. Is there a noticeable slowdown in

> response time by using multiple queries and recordsets?

>

> Scott

>

> >     When you're using aggregate functions, such as SUM and AVG, any

> field names

> > not contained in an aggregate function must be included in a GROUP BY

> clause.  I

> > know this is extremely annoying and I can't remember why you have to do

> this,

> > but I know that if you don't, aggregate functions don't make sense.

> >

> >     You could try creating a query (view) to display all the information

> you

> > need in the correct format so you won't have to make three seperate SQL

> calls to

> > the database.

> >

> > - Chuck

>

>

You can use execute method of you command object...



-----Original Message-----

From: Scott Taylor [mailto:scott.taylor@e...]

Sent: Monday, April 23, 2001 10:03 PM

To: ASP Databases

Subject: [asp_databases] HOW2? Multiple SQL Queries??





Hello! I am trying to obtain different information from the same Access

database using different SQL queries. For example I want to use COUNT,

SUM, etc. and specify output based on a field's data being X,Y,or Z

(whatever 3 options would fill that field).



I know how I can create multiple recordsets to display the info, but is

there a way to use multiple SQL queries to save time? For example:



sql1 = "SELECT * FROM My_Table"

sql2 = "SELECT SUM(Amount), field1 FROM My_Table WHERE field1 = option_x"

sql3 = "SELECT * FROM My_Table WHERE field1 = '" & OPTION & "' "



...well, you get the idea. So if I used that how would I actually use them

in the DataConn?



ie.. 'rsOrders.Open SQL, DataConn'.



Or is there a way to perform SQL type sorting on the data after it's

already retrieved from the database, say if I just used a basic "SELECT *

FROM My_Table".



Any and all ideas are welcome as I need to learn/know several methods.

Thanks to all in advance.



BRgds,

Scott Taylor

> You can use execute method of you command object...



Hmm, can you expand that thought process? Do you mean create a function to 

call the SQL query or what? Thanks.