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