subquery question

[sparkane]

I have two tables: Customers and Donations. I want to create a query of
Donation_Amt, Donation_Date, and Donation_Purpose that contains only the
most recent donations with purposes that fall under the "DUES" category,
by Customer ID number.

I can set this up using two queries no problem:

q1:
SELECT Cust_ID, Max(date_paid) AS LastDuesDate
FROM qwoDONATION
WHERE purpose Like "*dues*"
GROUP BY Cust_ID

q2:
SELECT qD.Cust_ID, qD.purpose, qD.date_paid, qD.amount_paid
FROM qwoDONATION qD INNER JOIN q1 ON (qwoDONATION.Cust_ID = q1.Cust_ID)
AND (qwoDONATION.date_paid = q1.LastDuesDate)
WHERE qD.purpose Like "*dues*"

(The WHERE clause in q2 is necessary to remove non-dues-related payments
made by a customer on the same date as their most recent dues payment.)

Now I'm trying to turn this into a single query, and I'm having no luck.
Basically all my attempts either return multiple dues payments per
customer, not just the most recent, or return nothing at all. Here's
are some examples of what I've tried:

basic consolidated query:
SELECT Cust_ID, purpose, date_paid, amount_paid
FROM qwoDONATION

where clause 1:
WHERE purpose Like "*dues*"
AND date_paid in(select max(date_paid) from qwoDONATION where purpose
like "*dues*")

-> returns only those records with the most recent dues payment date in
the entire donations table. Obviously wrong.

where clause 2:
WHERE purpose Like "*dues*"
AND date_paid in(select max(date_paid) from qwoDONATION where purpose
like "*dues*" GROUP BY Cust_ID)

-> returns records for each customer, but will return records other than
the most recent. When I run the subquery by itself, however, I get only
one date (the most recent) per customer. The problem is that some
customers' previous dues payments have dates identical to other
customers' most recent dues payments; so those payments also are
included.

The trick seems to require tying the ID result in the subquery to the ID
result in the main query. Is there a simple way to get Access SQL to do
this? Thanks for any help.

spark

 

[Guest]

-----Original Message-----
I have two tables: Customers and Donations. I want to create a query of
Donation_Amt, Donation_Date, and Donation_Purpose that contains only the
most recent donations with purposes that fall under the "DUES" category,
by Customer ID number.

I can set this up using two queries no problem:

q1:
SELECT Cust_ID, Max(date_paid) AS LastDuesDate
FROM qwoDONATION
WHERE purpose Like "*dues*"
GROUP BY Cust_ID

q2:
SELECT qD.Cust_ID, qD.purpose, qD.date_paid, qD.amount_paid
FROM qwoDONATION qD INNER JOIN q1 ON (qwoDONATION.Cust_ID = q1.Cust_ID)
AND (qwoDONATION.date_paid = q1.LastDuesDate)
WHERE qD.purpose Like "*dues*"

(The WHERE clause in q2 is necessary to remove non-dues- related payments
made by a customer on the same date as their most recent dues payment.)

Now I'm trying to turn this into a single query, and I'm having no luck.
Basically all my attempts either return multiple dues payments per
customer, not just the most recent, or return nothing at all. Here's
are some examples of what I've tried:

basic consolidated query:
SELECT Cust_ID, purpose, date_paid, amount_paid
FROM qwoDONATION

where clause 1:
WHERE purpose Like "*dues*"
AND date_paid in(select max(date_paid) from qwoDONATION where purpose
like "*dues*")

-> returns only those records with the most recent dues payment date in
the entire donations table. Obviously wrong.

where clause 2:
WHERE purpose Like "*dues*"
AND date_paid in(select max(date_paid) from qwoDONATION where purpose
like "*dues*" GROUP BY Cust_ID)

-> returns records for each customer, but will return records other than
the most recent. When I run the subquery by itself, however, I get only
one date (the most recent) per customer. The problem is that some
customers' previous dues payments have dates identical to other
customers' most recent dues payments; so those payments also are
included.

The trick seems to require tying the ID result in the subquery to the ID
result in the main query. Is there a simple way to get Access SQL to do
this? Thanks for any help.

spark


.
I wish somwone had answered your question. I would like

to know the answer. I can't even figure out subqueries.

 

[sparkane]

(e-mail address removed)

-----Original Message-----

[snip]

The trick seems to require tying the ID result in the subquery to the ID
result in the main query. Is there a simple way to get Access SQL to do
this? Thanks for any help.

spark


.
I wish somwone had answered your question. I would like

to know the answer. I can't even figure out subqueries.

It's possible that Access SQL can't do what I'd like, even if it should
be possible according to the SQL spec. Access is smooth like that,
sometimes.

spark

 

[sparkane]

Here's the answer. Unfortunately, it's very slow in Access (even if the
fields in question are indexed), but the two-tiered query isn't.

SELECT Cust_ID, purpose, date_paid, amount_paid
FROM qwoDONATION QD
WHERE purpose Like "*dues*"
AND date_paid in(select max(date_paid) from qwoDONATION
where purpose like "*dues*" and Cust_ID = QD.Cust_ID)

 

[sparkane]

Here's another way to do it in Access that runs quickly:

SELECT qD1.Cust_ID, qD1.purpose, qD1.date_paid, qD1.amount_paid
FROM qwoDONATION qD1 INNER JOIN qwoDONATION qD2 ON qD1.ID = qD2.ID
GROUP BY qD1.Cust_ID, qD1.purpose, qD1.date_paid, qD1.amount_paid
HAVING qD1.purpose Like "*dues*"
AND qwoDONATION.date_paid = Max(qD2.date_paid);

I learned these solutions at this site:

http://searchdatabase.techtarget.com/ateQuestionNResponse/0,289625,sid13
_cid564937_tax285649,00.html

 

[sparkane]

Here's another way to do it in Access that runs quickly:

I'm sorry. This doesn't work after all. The Max clause selects the
most recent payment date, which may not be a dues payment, so some
customers' dues payments vanish.

 

[John Spencer (MVP)]

I would TRY something like the following.

SELECT Cust_ID, purpose, date_paid, amount_paid
FROM qwoDONATION
WHERE purpose Like "*dues*"
AND date_paid =
(SELECT Max(Tmp.date_paid)
FROM qwoDONATION as TMP where Tmp.purpose
like "*dues*" AND TMP.CustId = qwoDonation.CustID )

-> returns only those records with the most recent dues payment date in
the entire donations table. Obviously wrong.

where clause 2:
WHERE purpose Like "*dues*"
AND date_paid in(select max(date_paid) from qwoDONATION where purpose
like "*dues*" GROUP BY Cust_ID)

 

[sparkane]

I would TRY something like the following.

SELECT Cust_ID, purpose, date_paid, amount_paid
FROM qwoDONATION
WHERE purpose Like "*dues*"
AND date_paid =
(SELECT Max(Tmp.date_paid)
FROM qwoDONATION as TMP where Tmp.purpose
like "*dues*" AND TMP.CustId = qwoDonation.CustID )

Thanks.. but I think I posted that I did. From an earlier post:

 

[John Spencer (MVP)]

Yes, Access (with JET - the .mdb database engine) is often slower using
subqueries than it is using "stacked" queries.

You can test this and find that sometimes one is faster than the other and other
times they are of about equal speed.

Another method that will usually work is the following method. This is, in my
opinion, more fragile and liable to break.

SELECT Cust_ID, purpose, date_paid, amount_paid
FROM qwoDONATION INNER JOIN
[SELECT CustID, Max(date_paid) as MaxDate
FROM qwoDONATION
WHERE Tmp.purpose LIKE *dues*"]. as Tmp
ON Tmp.CustID = qwoDonation.CustID AND
Tmp.MaxDate = qwoDonation.Date_Paid
WHERE purpose Like "*dues*"

NOTE that the sub-query is enclosed in brackets and followed by a period. Again
this technique varies in its effectiveness.