Perplexed said:
Here are the fields the i have
AccountNumber X00000
Name Joe Smith
ContributionMay $4,548.24
ContributionJune $6,576.85
What i need is a return Value to say "Check" if the difference between the
previous month and the current month are greater then 35%. Any advise is
very much apreciated. Thanks
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
First off, the table is incorrectly designed. A better design is:
Contributions table:
PersonID - links to the PersonsAccounts table
AccountNumber - links to the PersonsAccounts table
ContributionDate
ContributionAmount
There should be another table of Personnel info, that would include the
person's name and other demographic info. If there is more than one
account per person, there should be an PersonsAccounts table that holds
the person ID number & the Accounts they have; otherwise, you can stored
the AccountNumber in the Persons table:
PersonsAccounts table:
PersonID - PK
AccountNumber - PK
Then there should be an Accounts table that holds the Account numbers &
other info that may be needed about each account. In accounting
practices, this is called the Table of Accounts in the General Ledger.
Finally, you'd have data like this in the Contributions table:
PersonID AccountNumber ContributionDate ContributionAmount
225 X00000 2-May-2005 $4,548.24
225 X00000 1-Jun-2005 $6,576.85
This would allow a View (query) like the following:
View name: CurrentPriorContributions
SELECT PersonID, AccountNumber, ContributionDate, ContributionAmount,
(SELECT ContributionAmount
FROM Contributions
WHERE PersonID = C.PersonID
AND AccountNumber = C.AccountNumber
AND ContributionDate =
(SELECT MAX(ContributionDate)
FROM Contributions
WHERE PersonID = C.PersonID
AND AccountNumber = C.AccountNumber
AND ContributionDate < C.ContributionDate)) As
PriorContribution
FROM Contributions As C
The query to check the percentage change would be:
SELECT PersonID, AccountNumber, ContributionDate, PriorContribution,
ContributionAmount As CurrentContribution
FROM CurrentPriorContributions
WHERE (ContributionAmount-PriorContribution)/PriorContribution > 0.35
ORDER BY PersonID, AccountNumber, ContributionDate
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQw4XzoechKqOuFEgEQIS9wCfUgl4VeBsi9X4OFnjGWBg4UpLvKcAn125
FhbwquEguW+s6ZZHWQn6NQFS
=fPWt
-----END PGP SIGNATURE-----
