Complicated query

H

Helen

I am joining three tables: Contributor, Pledge, and Payment, all on
ContributorID. I want all Contributors, regardless of whether there are
Pledges and/or Payments, and Pledges whether or not there are Payments.
There will be only one Contributor and one Pledge, but may be multiple
Payments.

I need to display the following:

Pledge.PledgeDate and Pledge.Amount,
Cumulative total for all Payment.Amount
Payment.PaymentDate of the Last Payment

I'm able to display the Cumulative total in one query as follows:

SELECT Contributor.Name,
Pledge.PledgeDate,
Pledge.Amount,
Sum(Payment.Amount) AS [Total Contributions]

FROM Contributor
LEFT JOIN (Payment
LEFT JOIN Pledge
ON Payment.ContributorID=Pledge.ContributorID)
ON Contributor.ContributorID=Payment.ContributorID

I'm able to display the Last Payment Date in a separate query as follows:

SELECT Contributor.Name,
Payment.PaymentDate

FROM Contributor
INNER JOIN Payment
ON Contributor.ContributorID=Payment.ContributorID

WHERE (((Payment.PaymentDate)=
(SELECT MAX([P2].[PaymentDate]) FROM [Payment] AS P2

WHERE P2.[ContributorId] = Payment.[ContributorId])))

Is there any way that I can combine both of these into one query.

Thank you for any help you can give me.

Helen
 
J

John W. Vinson

I need to display the following:

Pledge.PledgeDate and Pledge.Amount,
Cumulative total for all Payment.Amount
Payment.PaymentDate of the Last Payment
Click to expand...

Um? Does this not work?

SELECT Contributor.[Name],
Pledge.PledgeDate,
Pledge.Amount,
Sum(Payment.Amount) AS [Total Contributions]
Max(Payment.PaymentDate) AS [Latest Payment]

FROM Contributor
LEFT JOIN (Payment
LEFT JOIN Pledge
ON Payment.ContributorID=Pledge.ContributorID)
ON Contributor.ContributorID=Payment.ContributorID

GROUP BY Contributor.ID, Contributor.[Name], Pledge.PledgeDate, Pledge.Amount;


I.e. just select the maximum (latest) grouping by the (one to one? what,
contributors can't make new pledges!?) contributor and pledge.
 
C

Clifford Bass

Hi Helen,

If there is only ever going to be one pledge at most, you can just add
the display of those columns into the initial query. Note that Name, while
allowed by Access, is not a good name for a column as it is a reserved word
and is used in other contexts. This can cause unexpected resulte. Search
for "reserved words" in your online help for more reserved words. Also, it
is handier to separate out the parts of the name into separate fields such as
LastName and FirstName and so on. If contributors include organizations you
can also have an OrganizationName. So you might end up with:

SELECT C.ContributorID, C.OrganizationName, C.LastName, C.FirstName,
Pledge.PledgeDate, Pledge.Amount, Sum(P.Amount) AS [Total Contributions],
Max(P.PaymentDate) AS [Last Payment Date]
FROM (Contributor AS C LEFT JOIN Payment AS P ON C.ContributorID =
P.ContributorID) LEFT JOIN Pledge ON C.ContributorID = Pledge.ContributorID
GROUP BY C.ContributorID, C.OrganizationName, C.LastName, C.FirstName,
Pledge.PledgeDate, Pledge.Amount;

However, if you might have more than one pledge per contributor and you
want to display only the latest pledge I would use a second query (named
qryLast Contributor Pledges):

SELECT A.ContributorID, A.PledgeDate, A.Amount
FROM Pledge AS A
WHERE (((Not Exists (select * from Pledge as B where B.ContributorID =
A.ContributorID and B.PledgeDate > A.PledgeDate))=True));

And change the first one to:

SELECT C.ContributorID, C.OrganizationName, C.LastName, C.FirstName,
LCP.PledgeDate AS LastPledgeDate, LCP.Amount AS LastPledgeAmount,
Max(P.PaymentDate) AS LastPaymentDate, Sum(P.Amount) AS TotalPaymentAmounts
FROM (Contributor AS C LEFT JOIN [qryLast Contributor Pledges] AS LCP ON
C.ContributorID = LCP.ContributorID) LEFT JOIN Payment AS P ON
C.ContributorID = P.ContributorID
GROUP BY C.ContributorID, C.OrganizationName, C.LastName, C.FirstName,
LCP.PledgeDate, LCP.Amount;

Hope that helps,

Clifford Bass

Helen said:
I am joining three tables: Contributor, Pledge, and Payment, all on
ContributorID. I want all Contributors, regardless of whether there are
Pledges and/or Payments, and Pledges whether or not there are Payments.
There will be only one Contributor and one Pledge, but may be multiple
Payments.

I need to display the following:

Pledge.PledgeDate and Pledge.Amount,
Cumulative total for all Payment.Amount
Payment.PaymentDate of the Last Payment

I'm able to display the Cumulative total in one query as follows:

SELECT Contributor.Name,
Pledge.PledgeDate,
Pledge.Amount,
Sum(Payment.Amount) AS [Total Contributions]

FROM Contributor
LEFT JOIN (Payment
LEFT JOIN Pledge
ON Payment.ContributorID=Pledge.ContributorID)
ON Contributor.ContributorID=Payment.ContributorID

I'm able to display the Last Payment Date in a separate query as follows:

SELECT Contributor.Name,
Payment.PaymentDate

FROM Contributor
INNER JOIN Payment
ON Contributor.ContributorID=Payment.ContributorID

WHERE (((Payment.PaymentDate)=
(SELECT MAX([P2].[PaymentDate]) FROM [Payment] AS P2

WHERE P2.[ContributorId] = Payment.[ContributorId])))

Is there any way that I can combine both of these into one query.

Thank you for any help you can give me.

Helen
Click to expand...
 
H

Helen

John,

Yes - it worked just fine!!! Thank you so very much. I didn't realize it
could be that simple. (BTW, I did have the GROUP BY in my first query, just
didn't include it in the question.)

Right now we just have one pledge per contributor - but yes, you are right,
as time goes on, we will have more than one. I just wasn't ready to deal
with it at this time, because I will need to count the contributions after
the most recent pledge date and wasn't sure how to deal with that.

You are very kind to take the time to answer questions from neophytes like me!

Helen

John W. Vinson said:
I need to display the following:

Pledge.PledgeDate and Pledge.Amount,
Cumulative total for all Payment.Amount
Payment.PaymentDate of the Last Payment
Click to expand...

Um? Does this not work?

SELECT Contributor.[Name],
Pledge.PledgeDate,
Pledge.Amount,
Sum(Payment.Amount) AS [Total Contributions]
Max(Payment.PaymentDate) AS [Latest Payment]

FROM Contributor
LEFT JOIN (Payment
LEFT JOIN Pledge
ON Payment.ContributorID=Pledge.ContributorID)
ON Contributor.ContributorID=Payment.ContributorID

GROUP BY Contributor.ID, Contributor.[Name], Pledge.PledgeDate, Pledge.Amount;


I.e. just select the maximum (latest) grouping by the (one to one? what,
contributors can't make new pledges!?) contributor and pledge.
Click to expand...
 
H

Helen

Clifford,

Thank you for the response. Regarding SELECT Name - That wasn't my actual
code (which does have multiple name parts and accomodates organizations) - I
was abreviating to keep it simple, but thanks for the tip about reserved
words.

At some point I am going to need to find the most current pledge, and the
payments after that date (see my response to John), so appreciate your
suggestions regarding that. I will save your code sample until I'm ready to
deal with it.

Thanks,

Helen

Clifford Bass said:
Hi Helen,

If there is only ever going to be one pledge at most, you can just add
the display of those columns into the initial query. Note that Name, while
allowed by Access, is not a good name for a column as it is a reserved word
and is used in other contexts. This can cause unexpected resulte. Search
for "reserved words" in your online help for more reserved words. Also, it
is handier to separate out the parts of the name into separate fields such as
LastName and FirstName and so on. If contributors include organizations you
can also have an OrganizationName. So you might end up with:

SELECT C.ContributorID, C.OrganizationName, C.LastName, C.FirstName,
Pledge.PledgeDate, Pledge.Amount, Sum(P.Amount) AS [Total Contributions],
Max(P.PaymentDate) AS [Last Payment Date]
FROM (Contributor AS C LEFT JOIN Payment AS P ON C.ContributorID =
P.ContributorID) LEFT JOIN Pledge ON C.ContributorID = Pledge.ContributorID
GROUP BY C.ContributorID, C.OrganizationName, C.LastName, C.FirstName,
Pledge.PledgeDate, Pledge.Amount;

However, if you might have more than one pledge per contributor and you
want to display only the latest pledge I would use a second query (named
qryLast Contributor Pledges):

SELECT A.ContributorID, A.PledgeDate, A.Amount
FROM Pledge AS A
WHERE (((Not Exists (select * from Pledge as B where B.ContributorID =
A.ContributorID and B.PledgeDate > A.PledgeDate))=True));

And change the first one to:

SELECT C.ContributorID, C.OrganizationName, C.LastName, C.FirstName,
LCP.PledgeDate AS LastPledgeDate, LCP.Amount AS LastPledgeAmount,
Max(P.PaymentDate) AS LastPaymentDate, Sum(P.Amount) AS TotalPaymentAmounts
FROM (Contributor AS C LEFT JOIN [qryLast Contributor Pledges] AS LCP ON
C.ContributorID = LCP.ContributorID) LEFT JOIN Payment AS P ON
C.ContributorID = P.ContributorID
GROUP BY C.ContributorID, C.OrganizationName, C.LastName, C.FirstName,
LCP.PledgeDate, LCP.Amount;

Hope that helps,

Clifford Bass

Helen said:
I am joining three tables: Contributor, Pledge, and Payment, all on
ContributorID. I want all Contributors, regardless of whether there are
Pledges and/or Payments, and Pledges whether or not there are Payments.
There will be only one Contributor and one Pledge, but may be multiple
Payments.

I need to display the following:

Pledge.PledgeDate and Pledge.Amount,
Cumulative total for all Payment.Amount
Payment.PaymentDate of the Last Payment

I'm able to display the Cumulative total in one query as follows:

SELECT Contributor.Name,
Pledge.PledgeDate,
Pledge.Amount,
Sum(Payment.Amount) AS [Total Contributions]

FROM Contributor
LEFT JOIN (Payment
LEFT JOIN Pledge
ON Payment.ContributorID=Pledge.ContributorID)
ON Contributor.ContributorID=Payment.ContributorID

I'm able to display the Last Payment Date in a separate query as follows:

SELECT Contributor.Name,
Payment.PaymentDate

FROM Contributor
INNER JOIN Payment
ON Contributor.ContributorID=Payment.ContributorID

WHERE (((Payment.PaymentDate)=
(SELECT MAX([P2].[PaymentDate]) FROM [Payment] AS P2

WHERE P2.[ContributorId] = Payment.[ContributorId])))

Is there any way that I can combine both of these into one query.

Thank you for any help you can give me.

Helen
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Helen,

Glad to help and you are much welcome!

To display a payment count add this into the select fields list part of
either SQL:

Count(*) AS PaymentCount

To show total payments (and payment counts) after the last pledge, add
this just before the group by clause of the final SQL:

WHERE (((P.PaymentDate)>=IIf(IsNull([PledgeDate]),#1/1/1900#,[PledgeDate])))

Good Luck,

Clifford Bass
 
J

John W. Vinson

John,

Yes - it worked just fine!!! Thank you so very much. I didn't realize it
could be that simple. (BTW, I did have the GROUP BY in my first query, just
didn't include it in the question.)

Right now we just have one pledge per contributor - but yes, you are right,
as time goes on, we will have more than one. I just wasn't ready to deal
with it at this time, because I will need to count the contributions after
the most recent pledge date and wasn't sure how to deal with that.
Click to expand...

That will require some thought. Are contributions related to a pledge, or to a
contributor? Do you want to sum all contributions by a contributor; all
concerned with each pledge by a contributor; or both? Is the Contributions
table related to the pledge (and only indirectly to the contributor)?
 
H

Helen

Hi John,

As you can probably tell, this database will be used to track donations to a
charitable organization. The data had been maintained in Excel since 1992,
but has become unwieldy and I volunteered to put it on Access. They had the
contributor and the pledge on one record in Excel - I have broken them apart
into two tables. The payments were maintained in a separate spreadsheet,
tied to the contributor spreadsheet by a contributor ID.

Contributions (Payments) and Pledges are both related to the contributor but
not to each other. We get many payments without pledges - and sometimes many
payments (over years) for a single pledge. A business may pledge to donate
$1000 and then take 10 years to make the payments at $100 a year.

Regarding the totals, we would want to have both a total for all years and
also a total for any payments on or since the date of the most recent pledge.

Thank you for your willingness to help.

Helen
 
H

Helen

Hi Clifford,

Thank you again for your help. It's amazing to me that such help is
available and so generously given. I'd be lost without it as I am trying to
learn Access details on my own (after a couple of 12 week on-line classes)
with no one around here to ask when I have questions.

The total that I'm looking for is the total payment amount (not count) since
the date of the most recent pledge (please see my response to John regarding
this same issue).

Thank you!

Helen


Clifford Bass said:
Hi Helen,

Glad to help and you are much welcome!

To display a payment count add this into the select fields list part of
either SQL:

Count(*) AS PaymentCount

To show total payments (and payment counts) after the last pledge, add
this just before the group by clause of the final SQL:

WHERE (((P.PaymentDate)>=IIf(IsNull([PledgeDate]),#1/1/1900#,[PledgeDate])))

Good Luck,

Clifford Bass

Helen said:
Clifford,

Thank you for the response. Regarding SELECT Name - That wasn't my actual
code (which does have multiple name parts and accomodates organizations) - I
was abreviating to keep it simple, but thanks for the tip about reserved
words.

At some point I am going to need to find the most current pledge, and the
payments after that date (see my response to John), so appreciate your
suggestions regarding that. I will save your code sample until I'm ready to
deal with it.

Thanks,

Helen
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Helen,

You are welcome! Just add the aforementioned where clause to the third
query and you will get what you want: total payment since last pledge or over
all time if no pledges. It includes the pledge date in case someone pledges
and contributes on the same day. The count part was an extra, if you wanted
to have a count of payments--something you had mentioned in an earlier post.

Clifford Bass
 
J

John W. Vinson

Contributions (Payments) and Pledges are both related to the contributor but
not to each other. We get many payments without pledges - and sometimes many
payments (over years) for a single pledge. A business may pledge to donate
$1000 and then take 10 years to make the payments at $100 a year.

Regarding the totals, we would want to have both a total for all years and
also a total for any payments on or since the date of the most recent pledge.
Click to expand...

I'd suggest, then, that you have a "v shaped" relationship: one Contributor
==> zero, one or more Pledges; and one Contributor ==> zero, one or more
Payment. There would be NO relationship between pledges and payments
(reflecting the real-life situation - contributors have checkbooks, pledges
don't!)

As such, you won't be able to get a single simple query showing contributors
along with pledges *and* payments. You'll get a Cartesian explosion - each
pledge will be paired with all payments and vice versa. But you can easily use
a Form with two Subforms (putting a textbox on the footer of the Payments
subform with a control source =Sum([Payment]) for example), or a Report with
two Subreports; or if you only care about the single most recent pledge, use
=DMax("[PledgeDate]", "[Pledges]", "[ContributorID] = " & [ContributorID])
as a calculated field in a query of the Contributor table.
 
D

Dessy

Hi All,
I am Dessy from china, i dont know why, i can download your email.
can you tell me, what the problem with my computer.
thank you.

Dessy
 

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

Select only most recent payment 2
Query question - limit amounts included in sum by date 5
show the most recent payment 5
Can't solve this query problem. 3
Table Design: common attributes in multiple entities 7
Autolookup between 3 tables 4
Query Confusion!!!!! 3
Customer Query 1

Top