Combine queries

[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);

 

[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

 

[KD]

Thanks, mate... I'll try this