I have 2 tables,
MPW1400
LeaseRecNo
PropNo
BldgNo
UnitNo
LeaseStartDate
MPW1482
LeaseRecNo
EffectiveDate
AnnualAmount
CertNo
My Objective is to get the total of 'AnnualAmount' Where MPW1400.LeaseRecNo = MPW1482.LeaseRecNo and CertNo = '1'
Example:
MPW1400
-------------------------------------------------------
LeaseRecNo | PropNo | BldgNo | UnitNo | LeaseStartDate
-------------------------------------------------------
12345 | RG52 | 24 | 5A | 12/25/2008
12346 | RG52 | 24 | 5B | 01/22/2007
12347 | RG52 | 24 | 3A | 08/05/2004
12348 | RG52 | 24 | 2A | 10/19/2008
12349 | RG52 | 24 | 1A | 11/16/2006
12350 | RG52 | 24 | 1B | 04/09/2001
-------------------------------------------------------
MPW1482
-------------------------------------------------------
LeaseRecNo | CertNo | AnnualAmount | EffectiveDate
-------------------------------------------------------
12345 | 1 | 3015. | 12/25/2008
12345 | 1 | 400.45 | 12/25/2008
12345 | 1 | 6000. | 12/25/2008
12345 | 2 | 1800. | 12/25/2008
12345 | 3 | 24000. | 12/25/2008
12345 | 1 | 9000. | 12/25/2008
-------------------------------------------------------
So the Desired Query would result in the following output;
PropNo | BldgNo | UnitNo | LeaseStartDate | EffectiveDate | AnnualAmount
-------------------------------------------------------------------------
RG52 | 24 | 5A | 12/25/2008 | 12/25/2008 | 18415.45
I have put together many different combinations of select statemets and subselects to only get mixed results non of which is my desired result.... Help?