query to compare columns from two different rows

[Lis B.]

Hi. I have two tables, Contacts and Donors. The Donors table lists each
donation by date and amount with a foreign key that refers to the Contact ID
in the Contacts table. I want to write a query that tells me how many
people who are repeat donors increased the amount of their donation. So I
need to compare their last donation (by date) with the next to last
donation. I don't care about any donations previous to the last two
donations.

I've written this query but it's not really giving me what I want. What I
really want is one line for each person whose most recent donation is more
than their previous donation. I want their names and the date of the most
recent donation and the amounts of the most recent and the previous.

Can anyone help me?

Here's what I've written which is returning too many rows:

Select C.LastName, C.FirstName, C.[ID], max(D.DateofDonation) as
DonationDate, D.Amount
(select max(D1.DateofDonation) from Donations D1 where D1.[id] = C.[id]
and d1.dateofdonation < d.dateofdonation) as EarlierAmount
from Contacts C right join Donations D
on C.[id] = D.[id]
group by C.LastName, C.FirstName, C.[ID], D.Amount, D.DateofDonation


Thanks

Lis B.

 

[Ted Allen]

Hi Lis,

I think the following will give you what you are looking
for:

Start first with the basic select query which includes
the contact and donation information.

Then, for the donation date criteria, use a subquery to
select the max donation date for the contact ID. This
will limit the output to at most one record per contact
(the most recent)

Next, calculate the previous donation date. To do this,
use a similar subquery to retrieve the max date that is
less than the date of the current item, and that matches
the Contact ID (this time the subquery would be in the
field value row rather than the criteria row though).

Finally, use a subquery as the criteria for the amount
field such that the amount must be greater than the first
amount from the source table that matches the previous
donation date (and the contact ID of course).

This is all in general terms. You already had a
correlated subquery in your original query so I have
assumed that you are familiar with how to create them.
But, if you need more specifics please post back. Of
course, you could use domain aggregate functions in place
of one or more of the subqueries if you prefer.

HTH, Ted Allen

 

[ChrisJ]

Hi Lis,

Try this.
You can change qry3 to add names, or write a 4th qry using
qry3 and your contacts table


qry1
Select
ID,
Max(DateofDonation) as LastDate
from
Donations
Group By
ID;



qry2
SELECT
Donations.ID,
Max(Donations.[DateofDonation]) AS NextToLastDate
FROM
Donations
LEFT JOIN qry1 ON
(Donations.[DateofDonation] = qry1.LastDate) AND
(Donations.ID = qry1.ID)
WHERE
qry1.ID Is Null
GROUP BY
Donations.ID;



qry3
SELECT
Donations.ID,
qry2.NextToLastDate,
Donations_1.amount AS NextToLastAmount,
qry1.LastDate,
Donations.amount AS LastAmount
FROM
((qry1 INNER JOIN Donations ON (qry1.LastDate =
Donations.[DateofDonation]) AND (qry1.ID = Donations.ID))
INNER JOIN qry2 ON Donations.ID = qry2.ID)
INNER JOIN Donations AS Donations_1 ON
(qry2.NextToLastDate = Donations_1.[DateofDonation]) AND
(qry2.ID = Donations_1.ID)
WHERE
Donations.amount>[Donations_1]![amount];

 

[Lis B.]

Thank you so much Ted for breaking it down for me so clearly. That did the
trick!

Lis

Hi Lis,

I think the following will give you what you are looking
for:

Start first with the basic select query which includes
the contact and donation information.

Then, for the donation date criteria, use a subquery to
select the max donation date for the contact ID. This
will limit the output to at most one record per contact
(the most recent)

Next, calculate the previous donation date. To do this,
use a similar subquery to retrieve the max date that is
less than the date of the current item, and that matches
the Contact ID (this time the subquery would be in the
field value row rather than the criteria row though).

Finally, use a subquery as the criteria for the amount
field such that the amount must be greater than the first
amount from the source table that matches the previous
donation date (and the contact ID of course).

This is all in general terms. You already had a
correlated subquery in your original query so I have
assumed that you are familiar with how to create them.
But, if you need more specifics please post back. Of
course, you could use domain aggregate functions in place
of one or more of the subqueries if you prefer.

HTH, Ted Allen

-----Original Message-----
Hi. I have two tables, Contacts and Donors. The Donors table lists each
donation by date and amount with a foreign key that refers to the Contact ID
in the Contacts table. I want to write a query that tells me how many
people who are repeat donors increased the amount of their donation. So I
need to compare their last donation (by date) with the next to last
donation. I don't care about any donations previous to the last two
donations.

I've written this query but it's not really giving me what I want. What I
really want is one line for each person whose most recent donation is more
than their previous donation. I want their names and the date of the most
recent donation and the amounts of the most recent and the previous.

Can anyone help me?

Here's what I've written which is returning too many rows:

Select C.LastName, C.FirstName, C.[ID], max (D.DateofDonation) as
DonationDate, D.Amount
(select max(D1.DateofDonation) from Donations D1 where D1.[id] = C.[id]
and d1.dateofdonation < d.dateofdonation) as EarlierAmount
from Contacts C right join Donations D
on C.[id] = D.[id]
group by C.LastName, C.FirstName, C.[ID], D.Amount, D.DateofDonation


Thanks

Lis B.


.

 

[Ted Allen]

My Pleasure, glad it helped.

-Ted

-----Original Message-----
Thank you so much Ted for breaking it down for me so clearly. That did the
trick!

Lis

Hi Lis,

I think the following will give you what you are looking
for:

Start first with the basic select query which includes
the contact and donation information.

Then, for the donation date criteria, use a subquery to
select the max donation date for the contact ID. This
will limit the output to at most one record per contact
(the most recent)

Next, calculate the previous donation date. To do this,
use a similar subquery to retrieve the max date that is
less than the date of the current item, and that matches
the Contact ID (this time the subquery would be in the
field value row rather than the criteria row though).

Finally, use a subquery as the criteria for the amount
field such that the amount must be greater than the first
amount from the source table that matches the previous
donation date (and the contact ID of course).

This is all in general terms. You already had a
correlated subquery in your original query so I have
assumed that you are familiar with how to create them.
But, if you need more specifics please post back. Of
course, you could use domain aggregate functions in place
of one or more of the subqueries if you prefer.

HTH, Ted Allen

-----Original Message-----
Hi. I have two tables, Contacts and Donors. The

Donors
table lists each

donation by date and amount with a foreign key that refers to the Contact ID
in the Contacts table. I want to write a query that tells me how many
people who are repeat donors increased the amount of their donation. So I
need to compare their last donation (by date) with the next to last
donation. I don't care about any donations previous

to
the last two

donations.

I've written this query but it's not really giving me what I want. What I
really want is one line for each person whose most recent donation is more
than their previous donation. I want their names and the date of the most
recent donation and the amounts of the most recent and the previous.

Can anyone help me?

Here's what I've written which is returning too many rows:

Select C.LastName, C.FirstName, C.[ID], max (D.DateofDonation) as
DonationDate, D.Amount
(select max(D1.DateofDonation) from Donations D1 where D1.[id] = C.[id]
and d1.dateofdonation < d.dateofdonation) as EarlierAmount
from Contacts C right join Donations D
on C.[id] = D.[id]
group by C.LastName, C.FirstName, C.[ID], D.Amount, D.DateofDonation


Thanks

Lis B.


.

.