I need to query the result set of another tquery already opened. The tables
are paradox and must remain so.

Here is the SQL for the 1st TQuery ( I'll call it "QryDrugCount"):

    SELECT DISTINCT organism, DrugAbbr, SUM(NCCLStotal) AS Total
    FROM "BugTable.db"
    GROUP BY Organism, DrugAbbr
    HAVING SUM(NCCLStotal) > 10
    ORDER BY Organism, DrugAbbr

(I'll call the result set "DrugCount.db" even though it doesn't actually
exist as a table.)

"DrugCount.db" looks like this:

Organism     DrugAbbr     Total
-----------------------------------------------
C.freundii     Ak                  60
C.freundii     Aug                60
C.koseri       A/S                 20
C.koseri       Ak                  40
C.koseri       Aug                40

Now, I want to do another GROUP BY on the result (I'll call the 2nd Tquery
"QryIsolateCount").

    SELECT DISTINCT Organism, MAX(Total) as Isolates
    FROM {!!DrugCount.db!!}                                { doesn't really
exist!!}
    GROUP BY Organism

This would give me the result:

Organism      Isolates
---------------------------------
C.freundii      60
C.koseri        40

That is, it would if I had a table called "DrugCount.db"!  But, since it is
the result set of a previous query, it doesn't!
(NOTE: I know I could change the first query and skip the whole issue, but
right now I'm more interested in how to chain TQuerys.)

I tried setting the data source property of "QryIsolateCount" to
"DSQryDrugCount" (the datasource of the first query) and using parameters
for the fields, but I can't get past the FROM clause.  (Error msg: "Table
does not exist")

How do I use the result set of one tquery as the source for a 2nd Tquery
using Paradox tables!

Hello Alan,

I don't think you can query a query in your context.  What you can do,
though, is to use the TBatchMove component to save the contents of the Query
to another Paradox table, then issue a second query on that.

Make the new Paradox table sit on a local drive for speed, and if you use
the BatchMove.Mode := batCopy way of doing things, BatchMove will create the
Paradox table for you, so you don't have to worry about it existing
beforehand.

Hope this helps.

Paul Harding.

If you choose to do this, you might like to look at the BatchMove method of
TTable which will eliminate the need for a separate TBatchMove component.

HTH,

Conor

news:87kbhp$t11$1@news7.svr.pol.co.uk...

I found the answer accidently while browsing the borland web page.

To create temporary tables for use by further queries, you must use either
tbatchmove or Ttable.batchmove. Ttable.batchmove moves data from any dataset
(including queries) into the table it is a method of.

I found this in http://community.borland.com/article/0,1410,20563,00.html
Titled: Top 10 tricks for Delphi and C++Builder VCL Database Developers.

...

If I understand it correctly you want to base a query on another query. I've
never tried this with TQuery components, but it will work with the help of
database desktop however if you do the following :
1. Create a SQL file containing your first query in the DB you're bugtable's
in. Call it for example 'MySQL.sql'.
2. Get the TQuery component loaded with the following SQL statement
    SELECT DISTINCT Organism, MAX(Total) as Isolates
    FROM 'MySQL.sql'
    GROUP BY Organism
This works well most of the time. I've used this approach lots of times with
Pdox. Always worked!

Bart

Alan Aipperspach heeft geschreven in bericht
<87hmir$6...@bornews.borland.com>...