Combine queries

K

KD

I have the following three queries - qry 1 & 2 are called by qry 3
and
joined. If I combine them into one query (by using sub-queries) it
takes forever. Can
someone please suggest a simple way of combining them since I'm new
to
SQL. I would like to eventually run the combined query from Excel
using VBA and would need a single query.

QUERY 1
SELECT "CRI-" & CRST.REFERENCE AS INV_NUM
, CRST.REFERENCE_LINE
, CRST.TRANS_TYPE
, CRST.SECOND_REF
, CRST.GL_JOURNAL
, CRST.DATE
, CRST.CREDITOR_CODE
, CRST.TAX_VALUE
, CRST.NET_VALUE
FROM CRST
WHERE (CRST.DATE>=#6/15/2009# And CRST.DATE<=#8/31/2009#) and
(CRST.CREDITOR_CODE="6174") and (CRST.TRANS_TYPE<>"CBPAY");
QUERY 2
SELECT DISTINCTROW ACCOUNT_NUMBER, SECOND_REF, REFERENCE
FROM GENT
WHERE ((LEFT(ACCOUNT_NUMBER,4)<>"zzzz") And (SECOND_REF<>""));
QUERY 3
SELECT CRED.INV_NUM
, CRED.TRANS_TYPE
, CRED.SECOND_REF
, CRED.GL_JOURNAL
, CRED.DATE
, CRED.CREDITOR_CODE
, CRED.TAX_VALUE
, CRED.NET_VALUE
, GL.ACCOUNT_NUMBER
, GL.SECOND_REF
FROM CRST_Query_2 AS CRED LEFT JOIN GENT_Query_2 AS GL ON
(CRED.GL_JOURNAL=GL.REFERENCE) AND (CRED.INV_NUM=GL.SECOND_REF);
 
D

Dale Fye

KD,

The first thing I would look at is the DISTINCTROW predicate in query 2.

DISTINCTROW compares ALL of the fields in GENT to identify distinct records.
Try using DISTINCT, which only compares the fields in the SELECT statement.
Depending on the number of fields in GENT, that could make a huge difference.
It could also significantly reduce the number of records in your result set.

As an example, assume you have a table with an ID (autonumber) field, and
two other fields (LastName, FirstName) (I know stupid example) with values:

ID LastName
1 Smith John
2 Smith Susan
3 Jones David
4 Jones Sarah

If you wrote:

SELECT DISTINCT LastName from yourTable

your results would be:
Smith
Jones

However, if your wrote:
SELECT DISTINCTROW LastName from yourTable

your results would be:
Smith
Smith
Jones
Jones
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine queries 13

Top