Parameter using field in query

[Stephanie]

Hi. I have a non-profit that receives donations (we hope!). Sometimes the
donation comes as "I'll give you $5,000 if you can match that $5,000 with
other donations between 05/01/06 and 07/01/06". Let's call this the matching
donation.

I have fields DateReceived, DollarAmtReceived, MatchBeginDate and
MatchEndDate. Difficulties I'm having: there really isn't a DateReceived for
the matching donation (unless we meet the match); the MatchBeginDate and
MatchEndDate are associated with the matching donation, not the other
donations. I'm not sure how to get the query I want, somthing that gives an
output:

John Doe matching donation $5,000; MatchBeginDate = 05/01/06; MatchEndDate =
07/01/06
Jane Smith DollarAmtReceived = $250, DateReceived = 05/05/06...

I appreciate suggestions for structuing the query. I've posted the query I
have so far below. Thanks!

SELECT Contacts.FirstName, Contacts.LastName,
ProjectPackage.DollarAmtReceived, ProjectPackage.DateReceived,
DonorType.DonorType, ProjectPackage.DonationTypeID,
ProjectPackage.MatchBeginDate, ProjectPackage.MatchEndDate
FROM (DonorType INNER JOIN Contacts ON DonorType.DonorTypeID =
Contacts.DonorTypeID) INNER JOIN (Status INNER JOIN ProjectPackage ON
Status.StatusType = ProjectPackage.StatusType) ON Contacts.ContactID =
ProjectPackage.ContactID
WHERE (((ProjectPackage.DateReceived)>=[MatchBeginDate] And
(ProjectPackage.DateReceived)<=[MatchEndDate]) AND
((ProjectPackage.DonationTypeID)="ca" Or (ProjectPackage.DonationTypeID)="cc"
Or (ProjectPackage.DonationTypeID)="ma"));

 

[MGFoster]

Hi. I have a non-profit that receives donations (we hope!). Sometimes the
donation comes as "I'll give you $5,000 if you can match that $5,000 with
other donations between 05/01/06 and 07/01/06". Let's call this the matching
donation.

I have fields DateReceived, DollarAmtReceived, MatchBeginDate and
MatchEndDate. Difficulties I'm having: there really isn't a DateReceived for
the matching donation (unless we meet the match); the MatchBeginDate and
MatchEndDate are associated with the matching donation, not the other
donations. I'm not sure how to get the query I want, somthing that gives an
output:

John Doe matching donation $5,000; MatchBeginDate = 05/01/06; MatchEndDate =
07/01/06
Jane Smith DollarAmtReceived = $250, DateReceived = 05/05/06...

I appreciate suggestions for structuing the query. I've posted the query I
have so far below. Thanks!

SELECT Contacts.FirstName, Contacts.LastName,
ProjectPackage.DollarAmtReceived, ProjectPackage.DateReceived,
DonorType.DonorType, ProjectPackage.DonationTypeID,
ProjectPackage.MatchBeginDate, ProjectPackage.MatchEndDate
FROM (DonorType INNER JOIN Contacts ON DonorType.DonorTypeID =
Contacts.DonorTypeID) INNER JOIN (Status INNER JOIN ProjectPackage ON
Status.StatusType = ProjectPackage.StatusType) ON Contacts.ContactID =
ProjectPackage.ContactID
WHERE (((ProjectPackage.DateReceived)>=[MatchBeginDate] And
(ProjectPackage.DateReceived)<=[MatchEndDate]) AND
((ProjectPackage.DonationTypeID)="ca" Or (ProjectPackage.DonationTypeID)="cc"
Or (ProjectPackage.DonationTypeID)="ma"));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your problem is a DB design problem rather than a data retrieval
problem. You need a new table for the matching donations. Something
like this:

CREATE TABLE MatchingProposals (
MatchID COUNTER UNIQUE NOT NULL ,
ContactID LONG NOT NULL REFERENCES Contacts ,
ProposedDonation CURRENCY NOT NULL ,
MatchStartDate DATETIME NOT NULL ,
MatchEndDate DATETIME NOT NULL ,
MatchMade CHAR(1) NOT NULL DEFAULT 'N'
CHECK (MatchMade IN ('Y','N')) ,
CONSTRAINT PK_MatchProps PRIMARY KEY (ContactID, ProposedDonation,
MatchStartDate, MatchEndDate)
)

To keep track of the donations that are going to match the
MatchingProposal you'd have to have a column in the Donations table for
the MatchID number. You'd periodically run a query that would update
the MatchingProposals.MatchMade column, by checking if the
ProposedDonation has been reached by the matching donations.

UPDATE MatchingProposals
SET MatchMade = "Y"
WHERE ProposedDonation
<= (SELECT SUM(Donation) FROM Donations As D
WHERE D.DonationDate BETWEEN MatchingProposals.MatchStartDate
And MatchingProposals.MatchEndDate
AND D.MatchID = MatchingProposals.MatchID)

This would be run after each donation w/ a MatchID.

That would only work if each donor said they were applying their
donation to "so & so's" matching donation challenge. Otherwise, if you
had more than 1 matching donation proposal, any donations that came in
could be applied to all matching donation proposals! IOW, if you had 3
proposals of $100 each (total $300) and $100 in donations came in,
during the match date ranges, then all of the proposals would be met
'cuz each of their proposals ($100) was met.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRFJjdIechKqOuFEgEQLdQgCfamGs6vswkWdjatmYC11DwJOHILYAnA5P
NFBrz0g7BFOmlQFocgDEFSCS
=hnio
-----END PGP SIGNATURE-----

 

[Stephanie]

Oh, my! I hate it when the answer is a desing change ;-)
I'll have to revisit and regroup. Thanks for the reply- I appreciate your
help!

Hi. I have a non-profit that receives donations (we hope!). Sometimes the
donation comes as "I'll give you $5,000 if you can match that $5,000 with
other donations between 05/01/06 and 07/01/06". Let's call this the matching
donation.

I have fields DateReceived, DollarAmtReceived, MatchBeginDate and
MatchEndDate. Difficulties I'm having: there really isn't a DateReceived for
the matching donation (unless we meet the match); the MatchBeginDate and
MatchEndDate are associated with the matching donation, not the other
donations. I'm not sure how to get the query I want, somthing that gives an
output:

John Doe matching donation $5,000; MatchBeginDate = 05/01/06; MatchEndDate =
07/01/06
Jane Smith DollarAmtReceived = $250, DateReceived = 05/05/06...

I appreciate suggestions for structuing the query. I've posted the query I
have so far below. Thanks!

SELECT Contacts.FirstName, Contacts.LastName,
ProjectPackage.DollarAmtReceived, ProjectPackage.DateReceived,
DonorType.DonorType, ProjectPackage.DonationTypeID,
ProjectPackage.MatchBeginDate, ProjectPackage.MatchEndDate
FROM (DonorType INNER JOIN Contacts ON DonorType.DonorTypeID =
Contacts.DonorTypeID) INNER JOIN (Status INNER JOIN ProjectPackage ON
Status.StatusType = ProjectPackage.StatusType) ON Contacts.ContactID =
ProjectPackage.ContactID
WHERE (((ProjectPackage.DateReceived)>=[MatchBeginDate] And
(ProjectPackage.DateReceived)<=[MatchEndDate]) AND
((ProjectPackage.DonationTypeID)="ca" Or (ProjectPackage.DonationTypeID)="cc"
Or (ProjectPackage.DonationTypeID)="ma"));

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your problem is a DB design problem rather than a data retrieval
problem. You need a new table for the matching donations. Something
like this:

CREATE TABLE MatchingProposals (
MatchID COUNTER UNIQUE NOT NULL ,
ContactID LONG NOT NULL REFERENCES Contacts ,
ProposedDonation CURRENCY NOT NULL ,
MatchStartDate DATETIME NOT NULL ,
MatchEndDate DATETIME NOT NULL ,
MatchMade CHAR(1) NOT NULL DEFAULT 'N'
CHECK (MatchMade IN ('Y','N')) ,
CONSTRAINT PK_MatchProps PRIMARY KEY (ContactID, ProposedDonation,
MatchStartDate, MatchEndDate)
)

To keep track of the donations that are going to match the
MatchingProposal you'd have to have a column in the Donations table for
the MatchID number. You'd periodically run a query that would update
the MatchingProposals.MatchMade column, by checking if the
ProposedDonation has been reached by the matching donations.

UPDATE MatchingProposals
SET MatchMade = "Y"
WHERE ProposedDonation
<= (SELECT SUM(Donation) FROM Donations As D
WHERE D.DonationDate BETWEEN MatchingProposals.MatchStartDate
And MatchingProposals.MatchEndDate
AND D.MatchID = MatchingProposals.MatchID)

This would be run after each donation w/ a MatchID.

That would only work if each donor said they were applying their
donation to "so & so's" matching donation challenge. Otherwise, if you
had more than 1 matching donation proposal, any donations that came in
could be applied to all matching donation proposals! IOW, if you had 3
proposals of $100 each (total $300) and $100 in donations came in,
during the match date ranges, then all of the proposals would be met
'cuz each of their proposals ($100) was met.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRFJjdIechKqOuFEgEQLdQgCfamGs6vswkWdjatmYC11DwJOHILYAnA5P
NFBrz0g7BFOmlQFocgDEFSCS
=hnio
-----END PGP SIGNATURE-----