Complex query with Union Query in process runs for ever

S

Stapes

Hi

I have a complex series of queries running of the back of each other.
They worked fine until I stuck a Union query in the sequence. Now it
just runs for ever.

The idea is that any cutomer who has not bought anything for 24 months
gets sent a last chance offer before bing dropped from the mailing
list.

1. The first query selects the last invoice for each customer
(TM_CompContact):

SELECT TM_Invoice.AcNo, Max(TM_Invoice.InvoiceDate) AS
MaxOfInvoiceDate, TM_CompContact.Archive, TM_CompContact.OnHold,
TM_CompContact.LCO, TM_CompContact.LCO_Memo
FROM TM_CompContact INNER JOIN TM_Invoice ON TM_CompContact.AcNo =
TM_Invoice.AcNo
GROUP BY TM_Invoice.AcNo, TM_CompContact.Archive,
TM_CompContact.OnHold, TM_CompContact.LCO, TM_CompContact.LCO_Memo
HAVING (((TM_Invoice.AcNo) Is Not Null) AND
((TM_CompContact.Archive)<>-1) AND ((TM_CompContact.OnHold)<>-1) AND
((TM_CompContact.LCO) Is Null) AND ((TM_CompContact.LCO_Memo) Is
Null))
ORDER BY Max(TM_Invoice.InvoiceDate) DESC;

2. The second query looks for those invoices that are exactly 24
months old:

SELECT DateDiff("m",[MaxOfInvoiceDate],Now()) AS Expr6,
MyLKQRY_AllInvoicesOver24m.AcNo,
MyLKQRY_AllInvoicesOver24m.MaxOfInvoiceDate AS [Last Purchase],
MyLKQRY_AllInvoicesOver24m.Archive, MyLKQRY_AllInvoicesOver24m.OnHold,
MyLKQRY_AllInvoicesOver24m.LCO, MyLKQRY_AllInvoicesOver24m.LCO_Memo
FROM MyLKQRY_AllInvoicesOver24m
WHERE (((DateDiff("m",[MaxOfInvoiceDate],Now()))=24));


3. The next query updates the customer record and sets a flag to say
that Last Chance Offer is due:

UPDATE TM_CompContact AS f1 SET f1.LCO_Due = -1
WHERE (((f1.LCO_Due)<>-1) AND ((Exists (SELECT [MyLKQRY_24].AcNo
FROM [MyLKQRY_24]
WHERE [MyLKQRY_24].AcNo =
f1.AcNo))<>False));

This all worked fine.

Only trouble is, customers sometimes buy the products at exhibitions
or shows, where they are not invoiced in the usual way. I had to
include these in the calculations too. So I select these in the same
way as in step 1:

SELECT TM_NoInvoice.AcNo, Max(TM_NoInvoice.TransactionDate) AS
MaxOfTransactionDate, TM_CompContact.Archive, TM_CompContact.OnHold,
TM_CompContact.LCO, TM_CompContact.LCO_Memo
FROM TM_CompContact INNER JOIN TM_NoInvoice ON
TM_CompContact.PK_Contact = TM_NoInvoice.PK_Contact
GROUP BY TM_NoInvoice.AcNo, TM_CompContact.Archive,
TM_CompContact.OnHold, TM_CompContact.LCO, TM_CompContact.LCO_Memo
HAVING (((TM_NoInvoice.AcNo) Is Not Null) AND
((TM_CompContact.Archive)<>-1) AND ((TM_CompContact.OnHold)<>-1) AND
((TM_CompContact.LCO) Is Null) AND ((TM_CompContact.LCO_Memo) Is
Null))
ORDER BY Max(TM_NoInvoice.TransactionDate) DESC;

and again, as step 2:

SELECT DateDiff("m",[MaxOfTransactionDate],Now()) AS Expr6,
MyLKQRY_AllNonInvoicesOver24m.AcNo,
MyLKQRY_AllNonInvoicesOver24m.MaxOfTransactionDate AS [Last Purchase],
MyLKQRY_AllNonInvoicesOver24m.Archive,
MyLKQRY_AllNonInvoicesOver24m.OnHold,
MyLKQRY_AllNonInvoicesOver24m.LCO,
MyLKQRY_AllNonInvoicesOver24m.LCO_Memo
FROM MyLKQRY_AllNonInvoicesOver24m
WHERE (((DateDiff("m",[MaxOfTransactionDate],Now()))=24));

Now comes the crunch, a Union query to join together the results of
the two Step 2s:

Select * FROM [MyLKQRY_24] UNION ALL Select * FROM
[MyLKQRY_24_OtherPurchases];

That worked OK.

Finally, my update query:

UPDATE TM_CompContact AS f1 SET f1.LCO_Due = -1
WHERE (((f1.LCO_Due)<>-1) AND ((Exists (SELECT [MyLKQRY_Simple].AcNo
FROM [MyLKQRY_Simple]
WHERE [MyLKQRY_Simple].AcNo =
f1.AcNo))<>False));

When I run this, it just hangs there forever.

What am I doing wrong?

Is there any way round this?

Stapes
 
S

Stapes

Hi

I have a complex series of queries running of the back of each other.
They worked fine until I stuck a Union query in the sequence. Now it
just runs for ever.

The idea is that any cutomer who has not bought anything for 24 months
gets sent a last chance offer before bing dropped from the mailing
list.

1. The first query selects the last invoice for each customer
(TM_CompContact):

SELECT TM_Invoice.AcNo, Max(TM_Invoice.InvoiceDate) AS
MaxOfInvoiceDate, TM_CompContact.Archive, TM_CompContact.OnHold,
TM_CompContact.LCO, TM_CompContact.LCO_Memo
FROM TM_CompContact INNER JOIN TM_Invoice ON TM_CompContact.AcNo =
TM_Invoice.AcNo
GROUP BY TM_Invoice.AcNo, TM_CompContact.Archive,
TM_CompContact.OnHold, TM_CompContact.LCO, TM_CompContact.LCO_Memo
HAVING (((TM_Invoice.AcNo) Is Not Null) AND
((TM_CompContact.Archive)<>-1) AND ((TM_CompContact.OnHold)<>-1) AND
((TM_CompContact.LCO) Is Null) AND ((TM_CompContact.LCO_Memo) Is
Null))
ORDER BY Max(TM_Invoice.InvoiceDate) DESC;

2. The second query looks for those invoices that are exactly 24
months old:

SELECT DateDiff("m",[MaxOfInvoiceDate],Now()) AS Expr6,
MyLKQRY_AllInvoicesOver24m.AcNo,
MyLKQRY_AllInvoicesOver24m.MaxOfInvoiceDate AS [Last Purchase],
MyLKQRY_AllInvoicesOver24m.Archive, MyLKQRY_AllInvoicesOver24m.OnHold,
MyLKQRY_AllInvoicesOver24m.LCO, MyLKQRY_AllInvoicesOver24m.LCO_Memo
FROM MyLKQRY_AllInvoicesOver24m
WHERE (((DateDiff("m",[MaxOfInvoiceDate],Now()))=24));

3. The next query updates the customer record and sets a flag to say
that Last Chance Offer is due:

UPDATE TM_CompContact AS f1 SET f1.LCO_Due = -1
WHERE (((f1.LCO_Due)<>-1) AND ((Exists (SELECT [MyLKQRY_24].AcNo
FROM [MyLKQRY_24]
WHERE [MyLKQRY_24].AcNo =
f1.AcNo))<>False));

This all worked fine.

Only trouble is, customers sometimes buy the products at exhibitions
or shows, where they are not invoiced in the usual way. I had to
include these in the calculations too. So I select these in the same
way as in step 1:

SELECT TM_NoInvoice.AcNo, Max(TM_NoInvoice.TransactionDate) AS
MaxOfTransactionDate, TM_CompContact.Archive, TM_CompContact.OnHold,
TM_CompContact.LCO, TM_CompContact.LCO_Memo
FROM TM_CompContact INNER JOIN TM_NoInvoice ON
TM_CompContact.PK_Contact = TM_NoInvoice.PK_Contact
GROUP BY TM_NoInvoice.AcNo, TM_CompContact.Archive,
TM_CompContact.OnHold, TM_CompContact.LCO, TM_CompContact.LCO_Memo
HAVING (((TM_NoInvoice.AcNo) Is Not Null) AND
((TM_CompContact.Archive)<>-1) AND ((TM_CompContact.OnHold)<>-1) AND
((TM_CompContact.LCO) Is Null) AND ((TM_CompContact.LCO_Memo) Is
Null))
ORDER BY Max(TM_NoInvoice.TransactionDate) DESC;

and again, as step 2:

SELECT DateDiff("m",[MaxOfTransactionDate],Now()) AS Expr6,
MyLKQRY_AllNonInvoicesOver24m.AcNo,
MyLKQRY_AllNonInvoicesOver24m.MaxOfTransactionDate AS [Last Purchase],
MyLKQRY_AllNonInvoicesOver24m.Archive,
MyLKQRY_AllNonInvoicesOver24m.OnHold,
MyLKQRY_AllNonInvoicesOver24m.LCO,
MyLKQRY_AllNonInvoicesOver24m.LCO_Memo
FROM MyLKQRY_AllNonInvoicesOver24m
WHERE (((DateDiff("m",[MaxOfTransactionDate],Now()))=24));

Now comes the crunch, a Union query to join together the results of
the two Step 2s:

Select * FROM [MyLKQRY_24] UNION ALL Select * FROM
[MyLKQRY_24_OtherPurchases];

That worked OK.

Finally, my update query:

UPDATE TM_CompContact AS f1 SET f1.LCO_Due = -1
WHERE (((f1.LCO_Due)<>-1) AND ((Exists (SELECT [MyLKQRY_Simple].AcNo
FROM [MyLKQRY_Simple]
WHERE [MyLKQRY_Simple].AcNo =
f1.AcNo))<>False));

When I run this, it just hangs there forever.

What am I doing wrong?

Is there any way round this?

Stapes
Click to expand...

OK - I inserted a make table query in after the union query - that
works.
 
P

Pieter Wijnen

you should change your having close to a where clause

cut

HAVING (((TM_NoInvoice.AcNo) Is Not Null) AND
((TM_CompContact.Archive)<>-1) AND ((TM_CompContact.OnHold)<>-1) AND
((TM_CompContact.LCO) Is Null) AND ((TM_CompContact.LCO_Memo) Is
Null))
and paste

WHERE (((TM_NoInvoice.AcNo) Is Not Null) AND
((TM_CompContact.Archive)<>-1) AND ((TM_CompContact.OnHold)<>-1) AND
((TM_CompContact.LCO) Is Null) AND ((TM_CompContact.LCO_Memo) Is
Null))

before the group by

General Tip:
always add the fields a seccond time
when you want to use criteria & change "sum line" to where for these
HAVING is the seccond latest Operation to occur when executing a Query
(Order by is last)
which means you pull all the data before filtering it!

HtH

Pieter


Stapes said:
Hi

I have a complex series of queries running of the back of each other.
They worked fine until I stuck a Union query in the sequence. Now it
just runs for ever.

The idea is that any cutomer who has not bought anything for 24 months
gets sent a last chance offer before bing dropped from the mailing
list.

1. The first query selects the last invoice for each customer
(TM_CompContact):

SELECT TM_Invoice.AcNo, Max(TM_Invoice.InvoiceDate) AS
MaxOfInvoiceDate, TM_CompContact.Archive, TM_CompContact.OnHold,
TM_CompContact.LCO, TM_CompContact.LCO_Memo
FROM TM_CompContact INNER JOIN TM_Invoice ON TM_CompContact.AcNo =
TM_Invoice.AcNo
GROUP BY TM_Invoice.AcNo, TM_CompContact.Archive,
TM_CompContact.OnHold, TM_CompContact.LCO, TM_CompContact.LCO_Memo
HAVING (((TM_Invoice.AcNo) Is Not Null) AND
((TM_CompContact.Archive)<>-1) AND ((TM_CompContact.OnHold)<>-1) AND
((TM_CompContact.LCO) Is Null) AND ((TM_CompContact.LCO_Memo) Is
Null))
ORDER BY Max(TM_Invoice.InvoiceDate) DESC;

2. The second query looks for those invoices that are exactly 24
months old:

SELECT DateDiff("m",[MaxOfInvoiceDate],Now()) AS Expr6,
MyLKQRY_AllInvoicesOver24m.AcNo,
MyLKQRY_AllInvoicesOver24m.MaxOfInvoiceDate AS [Last Purchase],
MyLKQRY_AllInvoicesOver24m.Archive, MyLKQRY_AllInvoicesOver24m.OnHold,
MyLKQRY_AllInvoicesOver24m.LCO, MyLKQRY_AllInvoicesOver24m.LCO_Memo
FROM MyLKQRY_AllInvoicesOver24m
WHERE (((DateDiff("m",[MaxOfInvoiceDate],Now()))=24));

3. The next query updates the customer record and sets a flag to say
that Last Chance Offer is due:

UPDATE TM_CompContact AS f1 SET f1.LCO_Due = -1
WHERE (((f1.LCO_Due)<>-1) AND ((Exists (SELECT [MyLKQRY_24].AcNo
FROM [MyLKQRY_24]
WHERE [MyLKQRY_24].AcNo =
f1.AcNo))<>False));

This all worked fine.

Only trouble is, customers sometimes buy the products at exhibitions
or shows, where they are not invoiced in the usual way. I had to
include these in the calculations too. So I select these in the same
way as in step 1:

SELECT TM_NoInvoice.AcNo, Max(TM_NoInvoice.TransactionDate) AS
MaxOfTransactionDate, TM_CompContact.Archive, TM_CompContact.OnHold,
TM_CompContact.LCO, TM_CompContact.LCO_Memo
FROM TM_CompContact INNER JOIN TM_NoInvoice ON
TM_CompContact.PK_Contact = TM_NoInvoice.PK_Contact
GROUP BY TM_NoInvoice.AcNo, TM_CompContact.Archive,
TM_CompContact.OnHold, TM_CompContact.LCO, TM_CompContact.LCO_Memo
HAVING (((TM_NoInvoice.AcNo) Is Not Null) AND
((TM_CompContact.Archive)<>-1) AND ((TM_CompContact.OnHold)<>-1) AND
((TM_CompContact.LCO) Is Null) AND ((TM_CompContact.LCO_Memo) Is
Null))
ORDER BY Max(TM_NoInvoice.TransactionDate) DESC;

and again, as step 2:

SELECT DateDiff("m",[MaxOfTransactionDate],Now()) AS Expr6,
MyLKQRY_AllNonInvoicesOver24m.AcNo,
MyLKQRY_AllNonInvoicesOver24m.MaxOfTransactionDate AS [Last Purchase],
MyLKQRY_AllNonInvoicesOver24m.Archive,
MyLKQRY_AllNonInvoicesOver24m.OnHold,
MyLKQRY_AllNonInvoicesOver24m.LCO,
MyLKQRY_AllNonInvoicesOver24m.LCO_Memo
FROM MyLKQRY_AllNonInvoicesOver24m
WHERE (((DateDiff("m",[MaxOfTransactionDate],Now()))=24));

Now comes the crunch, a Union query to join together the results of
the two Step 2s:

Select * FROM [MyLKQRY_24] UNION ALL Select * FROM
[MyLKQRY_24_OtherPurchases];

That worked OK.

Finally, my update query:

UPDATE TM_CompContact AS f1 SET f1.LCO_Due = -1
WHERE (((f1.LCO_Due)<>-1) AND ((Exists (SELECT [MyLKQRY_Simple].AcNo
FROM [MyLKQRY_Simple]
WHERE [MyLKQRY_Simple].AcNo =
f1.AcNo))<>False));

When I run this, it just hangs there forever.

What am I doing wrong?

Is there any way round this?

Stapes
Click to expand...

OK - I inserted a make table query in after the union query - that
works.
Click to expand...
 

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

What is the difference between these two stATEMENTS 2
Works in query, but not in VBA 1
How to avoid duplicates in an union query 9
Exported query Runs forever, creates huge spreadsheet with no data. 2
Union in query not working correctly 1
Saving complex query blows up Access 2002! 12
How to include a column with sequential numbering in a Union Query 4
Form input in query, Union and crosstab query gives error msg 2

Top