Passing Parameters 1

[Hap007]

Hi,

I am using Access as a pass thru db and calling queries in the access DB from HTML(VBscript), this works fine.

Here is my problem:
I have several queries that extract and sum data based on date and Customer Number. Call them underlying queries A and B.

Then, I have a third query (call it Final) that uses the results of the first 2 queries (A and B above) to provide my final result set.

I can pass the Customer Number and date to the 'Final/third' query and it works, but the first 2 queries must process all records, so it slows down.

Now, If I was in an Access program, I could set parameters for those 2 intial queries(A andB), I could also pass parameters.

But, I am calling from HTML, so:
My question: Is there a way to call my (Final) query, with parameters and have that query pass those parameters to the underlying (A and B) queries?

Thanks,
Hap...


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[PHV]

Can't you write a single query (instead of 3) to get the data you want ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hap007]

THanks for the reply

I wish it were as simple as that.

The 2 underlying Queries are doing some pretty invloved work such as looking up shipment dates and summing and grouping and validating totals.

Then, the final query uses the summary data, looks up customer info and provides the final data.

So, it may be possible, but not practical.

If the backend were SQL, I might use a stored procedure, but that is not available.

So, I need parameters passed to final query also sent to the 2 underlying queries, if it is possible.

Thanks,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121

 

[PHV]

Proof of concept.
Actual Query3:
SELECT ...
FROM Query1 INNER JOIN Query2 ON ...

New Query3:
SELECT ...
FROM (
SQL code of Query1
) AS Query1 INNER JOIN (
SQL code of Query2
) AS Query2 ON ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hap007]

Thanks,

I will give it a try and let you know

It just seems that with Parameters and querydefs, there should be a way to pass parameters between queries.

When/If I place all of that SQL code into VBScript inside HTML page, it gets very tough to manage and revise. And, I know that there is limits to the SQL statement I can pass.

Thanks,
Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/threadminder.cfm?pid=1121