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..

 

[AccessVandal via AccessMonster.com]

Why not just use both tables in a single query? Syntax untested, so you'll
have to correct them if neccessary. Like.....

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

((LEFT(GL.ACCOUNT_NUMBER,4)<>"zzzz") And (GL.SECOND_REF<>""));

Warning! Date is a reserved word in Access, rename it if it is possible of
face the problem in the future. You may have to use the square brackets to
enclose the reserved name if it's not possible.

CRED.[DATE]>=#6/15/2009#

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.

snip.....

 

[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?

 

[AccessVandal via AccessMonster.com]

Well, I have no ideas to suggest as we can see your database. The only thing
I can suggest is to check the primary keys, are they Indexed? Is the tables
relationship correct? This might be the reasons of slow down.

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

I don't know it will work as your query is already slow. But to access a temp
table would be faster.

 

[AccessVandal via AccessMonster.com]

Typos!
Well, I have no ideas to suggest as we can't see your database.