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

 

[TedMi]

Unless you made a typo, I don't see where Query 1 is referenced in Q3.
In any case, there is no need to combine all of this into 1 query. If you
run Q3 from Excel, it will perform Q1 & Q2 as needed to get its results. Siz
months down the road, I find it much easier to follow the logic of a complex
query if it is broken down into sub-queries which are then queried. As a
matter of fact, for some queries Access will tell you (I forget the exact
wording) that your query is too complex and needs to broken down into
multiple ones.
-TedMi

 

[KD]

Thanks, mate... i didn't know that it would call the others as well..

 

[KD]

thank you the response... I did end up with one query, but it still
took quite a while to run. I forgot the "Date-keyword" issue, so I'll
fix it.

 

[John W. Vinson]

thank you the response... I did end up with one query, but it still
took quite a while to run. I forgot the "Date-keyword" issue, so I'll
fix it.

One thing that might speed this up is to use the Like() query operator, rather
than calling the Left() function on every row - not only will calling the
function slow things down, but it will not take advantage of indexes on the
field. Try

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 AS CRED LEFT JOIN GENT AS GL ON
(CRED.GL_JOURNAL=GL.REFERENCE) AND (CRED.INV_NUM=GL.SECOND_REF)

WHERE (CRED.DATE>=#6/15/2009# And CRED.DATE<=#8/31/2009#) and
(CRED.CREDITOR_CODE="6174") and (CRED.TRANS_TYPE<>"CBPAY")
And GL.AccountNumber NOT LIKE "zzzz*"
And GL.SECOND_REF<>"";

Unless SECOND_REF has been (unwisely!) changed to Allow Zero Length Strings,
and loaded with "" in place of NULL, the last criterion should almost surely
be

AND GL.SECOND_REF IS NOT NULL

Looking again... you're using a LEFT JOIN. WHy? Given that you're applying a
criterion to fields in GENT (alias GL), you're defeating the left join; only
non-NULL records will be selected.

Also be sure that there are appropriate indexes on the fields DATE,
CREDITOR_CODE and TRANS_TYPE.

 

[KD]

Wow... very useful stuff for a beginner like me.... I did use NULL,
but did not get the desired results.... The Accounting database is
called "Arrow" and it has a few quirks about it. I will however try
your suggestions as it might have been another error that caused the
problem I had with the NULL option.

 

[KD]

when I run this revised query I get a dailog asking for a "Parameter
Value" for CRED.INV_NUM.

If I change the INNER to LEFT JOIN then I get an error message saying
that "JOIN operation 'CRED,INV_NUM' refers to a field that is not in
one of the joined tables."

SELECT "CRI-" & CRED.REFERENCE AS 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 AS CRED INNER JOIN GENT AS GL ON
(CRED.GL_JOURNAL=GL.REFERENCE) AND (CRED.INV_NUM=GL.SECOND_REF)
WHERE (CRED.DATE>=#6/15/2009# And CRED.DATE<=#8/31/2009#) And
(CRED.CREDITOR_CODE="6174") And
(CRED.TRANS_TYPE<>"CBPAY") And
(GL.ACCOUNT_NUMBER Not Like "zzzz*") And
(GL.SECOND_REF Is Not Null);

I have also removed the JOIN and only refer to the two tables, but
then I get the "parameter issue" again

SELECT "CRI-" & CRED.REFERENCE AS 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 AS CRED, GENT AS GL
WHERE (CRED.DATE>=#6/15/2009# And CRED.DATE<=#8/31/2009#) And
(CRED.CREDITOR_CODE="6174") And
(CRED.TRANS_TYPE<>"CBPAY") And
(GL.ACCOUNT_NUMBER Not Like "zzzz*") And
(GL.SECOND_REF <> "") AND
(CRED.GL_JOURNAL=GL.REFERENCE) AND
(CRED.INV_NUM=GL.SECOND_REF);

If I run the query removing the GL table then it returns the correct
values for "INV_NUM", so I think the problem lies in the join and not
in the CRED.INV_NUM part.

 

[KD]

I think my question should be:

How do I use a derived field [SELECT "CRI-" & CRED.REFERENCE AS
INV_NUM]

in a where clause like this [(CRED.INV_NUM=GL.SECOND_REF); ]

without using a sub-query first

 

[John W. Vinson]

when I run this revised query I get a dailog asking for a "Parameter
Value" for CRED.INV_NUM.

If I change the INNER to LEFT JOIN then I get an error message saying
that "JOIN operation 'CRED,INV_NUM' refers to a field that is not in
one of the joined tables."

You'll need to do the concatenation directly in the JOIN clause, not just in
the SELECT clause: try

SELECT "CRI-" & CRED.REFERENCE AS 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 AS CRED INNER JOIN GENT AS GL ON
(CRED.GL_JOURNAL=GL.REFERENCE) AND ("CRI-" & CRED.REFERENCE=GL.SECOND_REF)
WHERE (CRED.DATE>=#6/15/2009# And CRED.DATE<=#8/31/2009#) And
(CRED.CREDITOR_CODE="6174") And
(CRED.TRANS_TYPE<>"CBPAY") And
(GL.ACCOUNT_NUMBER Not Like "zzzz*") And
(GL.SECOND_REF Is Not Null);

Ideally, of course, you wouldn't store constant "label-type" data such as CRI-
in any table field (just introduce it in a Format at display time) and you'ld
have consistancy between tables, but I realize this isn't an ideal world!

 

[KD]

Thanks, John

I ran this query and it took 1:26 (mm:ss). Your code, although much
more elegant, took over 7 mins afterwhich I abandoned the run. I
appreciate the help, but for interest sake, can you think why it would
be? Even 1:26 is still a bit of a delay for my *impatient* users ....

SELECT CRED.INV_NUM
, CRED.TRANS_TYPE
, CRED.GL_JOURNAL
, CRED.DATE
, CRED.CREDITOR_CODE
, CRED.TAX_VALUE
, CRED.NET_VALUE
, GL.ACCOUNT_NUMBER
, GL.SECOND_REF
FROM
(SELECT "CRI-" & CRST.REFERENCE AS INV_NUM
, CRST.TRANS_TYPE
, 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")) AS CRED
LEFT JOIN
(SELECT DISTINCT ACCOUNT_NUMBER, SECOND_REF, REFERENCE
FROM GENT
WHERE ((ACCOUNT_NUMBER NOT LIKE "zzzz*") And (SECOND_REF IS NOT
NULL))) AS GL ON (CRED.GL_JOURNAL=GL.REFERENCE) AND
(CRED.INV_NUM=GL.SECOND_REF);

 

[KD]

Would the use of a TEMPORARY table help to speed things up?