Complex query SQL ...
Err... the SQL is larger than the 10000 characters that the forum allows so heres some of it ...
The whole thing is in the attchment ... but this might give you some idea..
you know how to take on a challenge....
Cheers
Paul
SELECT Data_Dynamic_Properties.PropertyID, Data_Dynamic_Properties.AuctionID, Data_Dynamic_Properties.[Number/Name], Data_Dynamic_Properties.Address_1, Data_Dynamic_Properties.Town, Data_Dynamic_Properties.Pcode, Data_Dynamic_Properties.[Max Potential Beds], Data_Dynamic_Properties.Bedletcalc, Data_Dynamic_Properties.expectedrentperbed, Data_Dynamic_Properties.RentalMonthsSafety, [Bedletcalc]*[expectedrentperbed]*[RentalMonthsSafety] AS [CALC-RENTAL-CURRENT-PA], [Max Potential Beds]*[expectedrentperbed]*[RentalMonthsSafety] AS [CALC-RENTAL-MAX-PA], Data_Dynamic_Properties.[Guide Price], Data_Dynamic_Properties.[Market Value], Data_Dynamic_Properties.Expectedsaleprice, Data_Dynamic_Properties.DepositSelected, ([guide price]*([DepositSelected]/100)) AS [CALC-DEPOSIT], [guide price]-([guide price]*([DepositSelected]/100)) AS [CALC-LOAN-AMOUNT], Data_Dynamic_Properties.[Expected Devcost], [Expected Devcost]+[CALC-DEPOSIT]+[StampDutyApplicableMarketValue] AS [CALC-TOTAL-OUTLAY], ([Expectedsaleprice]*([DepositSelected]/100)) AS [CALC-DEPOSIT-EXP], [Expectedsaleprice]-([Expectedsaleprice]*([DepositSelected]/100)) AS [CALC-LOAN-AMOUNT-EXP], [Expected Devcost]+[CALC-DEPOSIT-EXP]+[StampDutyApplicableMarketValue] AS [CALC-TOTAL-OUTLAY-EXP], [market value]-[Guide Price] AS [CALC-MARGIN-OFF-MARKET_ASKING], [market value]-[Expectedsaleprice] AS [CALC-MARGIN-OFF-MARKET_EXP], Int(([market value]-[Expectedsaleprice])/[ExpectedsalePrice]*100) AS [CALC-MARGIN-OFF-MARKET_EXP-per], Int(([market value]-[Guide Price])/[Guide Price]*100) AS [CALC-MARGIN-OFF-MARKET_ASKING-per], Data_Dynamic_Properties.LoanRateSelected, [CALC-LOAN-AMOUNT-EXP]*([LoanRateSelected]/100) AS [CALC-REPAY-EXP], [CALC-LOAN-AMOUNT-EXP]*([LoanRateSelected]/100)/12 AS [CALC-REPAY-EXP-Mnth], [CALC-LOAN-AMOUNT]*([LoanRateSelected]/100) AS [CALC-REPAY-ASK], [CALC-LOAN-AMOUNT]*([LoanRateSelected]/100)/12 AS [CALC-REPAY-ASK-mnth], [CALC-RENTAL-CURRENT-PA]-[CALC-REPAY-ASK]-[lettingsreduction]-[taxation] AS [CALC-FINANCE-MARG], [CALC-RENTAL-MAX-PA]-[CALC-REPAY-EXP]-[lettingsreductionexp]-[taxationmax] AS [CALC-FINANCE-MARG-EXP], Round((([calc-finance-marg]/[CALC-RENTAL-current-PA])*100),1) AS [Calc-Finance-margin-per], Round((([Calc-Finance-Marg-exp])/[CALC-RENTAL-max-PA]*100),1) AS [CALC-FINANCE-MARG-EXP-PER], Int(([CALC-FINANCE-MARG]/[calc-total-outlay])*100) AS [CALC-RETURN-ON-EQUITY-ASK], Int(([CALC-FINANCE-MARG-EXP]/[calc-total-outlay-exp])*100) AS [CALC-RETURN-ON-EQUITY-EXP], Int(([CALC-RENTAL-CURRENT-PA]/[calc-loan-amount])*100) AS [CALC-GROSS-YEILD], Round((([CALC-RENTal-CURRENT-PA]/[calc-loan-amount-EXP])*100),1) AS [CALC-GROSS-YEILD-EXP], Round((([CALC-FINANCE-MARG]/[calc-loan-amount])*100),1) AS [CALC-Net-YEILD], Round((([CALC-FINANCE-MARG-EXP]/[calc-loan-amount-EXP])*100),1) AS [CALC-Net-YEILD-EXP], Data_Dynamic_Properties.[Ref/Lot], IIf([CALC-FINANCE-MARG-EXP-PER]>=[Margins_Low_Rental_Margin] And [CALC-FINANCE-MARG-EXP-PER]<[Margins_Medium_Rental_Margin],[Score_Low_Rental_Margin],IIf([CALC-FINANCE-MARG-EXP-PER]>=[Margins_medium_Rental_Margin] And [CALC-FINANCE-MARG-EXP-PER]<[Margins_high_Rental_Margin],[Score_medium_Rental_Margin],IIf([CALC-FINANCE-MARG-EXP-PER]>=[Margins_high_Rental_Margin],[Score_high_Rental_Margin],0))) AS [Rental Score], IIf([CALC-GROSS-YEILD-EXP]>=[Margins_Low_Gross_yeild] And [CALC-GROSS-YEILD-EXP]<[Margins_medium_Gross_yeild],[Score_Low_Gross_yeild],IIf([CALC-GROSS-YEILD-EXP]>=[Margins_medium_Gross_yeild] And [CALC-GROSS-YEILD-EXP]<[Margins_high_Gross_yeild],[Score_medium_Gross_yeild],IIf([CALC-GROSS-YEILD-EXP]>=[Margins_high_Gross_yeild],[Score_high_Gross_yeild],0))) AS Gross_Yeild_Score, IIf([CALC-RETURN-ON-EQUITY-EXP]>=[Margins_Low_return_on_equity] And [CALC-RETURN-ON-EQUITY-EXP]<[Margins_Medium_return_on_equity],[Score_Low_return_on_equity],IIf([CALC-RETURN-ON-EQUITY-EXP]>=[Margins_Medium_return_on_equity] And [CALC-RETURN-ON-EQUITY-EXP]<[Margins_High_return_on_equity],[Score_medium_return_on_equity],IIf([CALC-RETURN-ON-EQUITY-EXP]>=[Margins_High_return_on_equity],[Score_high_return_on_equity],0))) AS Return_on_equity_score, IIf([CALC-FINANCE-MARG-EXP]>=[Margins_Low_Required_Income] And [CALC-FINANCE-MARG-EXP]<[Margins_Medium_Required_Income],[Score_Low_Required_Income],IIf([CALC-FINANCE-MARG-EXP]>=[Margins_Medium_Required_Income] And [CALC-FINANCE-MARG-EXP]<[Margins_High_Required_Income],[Score_medium_Required_Income],IIf([CALC-FINANCE-MARG-EXP]>=[Margins_High_Required_Income],[Score_high_Required_Income],0))) AS Required_income_Score, IIf([Capital_Gain_1_yr]>=[MArgin_Low_Capital_Gain] And [Capital_Gain_1_yr]<[MArgin_medium_Capital_Gain],[Score_Low_Capital_Gain],IIf([Capital_Gain_1_yr]>=[MArgin_medium_Capital_Gain] And [Capital_Gain_1_yr]<[MArgin_high_Capital_Gain],[Score_medium_Capital_Gain],IIf([Capital_Gain_1_yr]>=[MArgin_high_Capital_Gain],[Score_high_Capital_Gain],0))) AS Capital_gain_Score, IIf([CALC-TOTAL-OUTLAY-EXP]>=[Margins_Low_Total_Outlay] And [CALC-TOTAL-OUTLAY-EXP]<[Margins_medium_Total_Outlay],[Score_medium_Total_Outlay],IIf([CALC-TOTAL-OUTLAY-EXP]>=[Margins_medium_Total_Outlay] And [CALC-TOTAL-OUTLAY-EXP]<[Margins_high_Total_Outlay],[Score_high_Total_Outlay],IIf([CALC-TOTAL-OUTLAY-EXP]<=[Margins_low_Total_Outlay],[Score_low_Total_Outlay],0))) AS Total_Outlay_score, IIf([NextPurchaseYr1]>=([GrowthYear1]*([MArgins_low_%_of_next_Purchase]/100)) And [NextPurchaseYr1]<([GrowthYear1]*([MArgins_medium_%_of_next_Purchase]/100)),[Score_low_%_of_next_Purchase],IIf([NextPurchaseYr1]>=([GrowthYear1]*([MArgins_medium_%_of_next_Purchase]/100)) And [NextPurchaseYr1]<([GrowthYear1]*([MArgins_high_%_of_next_Purchase]/100)),[Score_medium_%_of_next_Purchase],IIf([NextPurchaseYr1]>=([GrowthYear1]*([MArgins_high_%_of_next_Purchase]/100)),[Score_high_%_of_next_Purchase],0))) AS Refinancing_score, IIf([RedressedCapital_Exp]>=[MArgin_Low_redress] And [RedressedCapital_Exp]<[MArgin_medium_redress],[Score_Low_redress],IIf([RedressedCapital_Exp]>=[MArgin_medium_redress] And [RedressedCapital_Exp]<[MArgin_high_redress],[Score_medium_redress],IIf([RedressedCapital_Exp]>=[MArgin_high_redress],[Score_high_redress],0))) AS Redress_Score, IIf([CALC-MARGIN-OFF-MARKET_EXP-per]>[MArgin_Low_off_market_value] And [CALC-MARGIN-OFF-MARKET_EXP-per]<=[MArgin_medium_off_market_value],[Score_Low_off_market_value],IIf([CALC-MARGIN-OFF-MARKET_EXP-per]>[MArgin_medium_off_market_value] And [CALC-MARGIN-OFF-MARKET_EXP-per]<=[MArgin_high_off_market_value],[Score_medium_off_market_value],IIf([CALC-MARGIN-OFF-MARKET_EXP-per]>[MArgin_high_off_market_value],[Score_high_off_market_value],0))) AS Off_Market_Value_score, [Rental Score]+[Gross_yeild_Score]+[Return_on_Equity_score]+[Required_income_Score]+[Total_Outlay_score]+[Refinancing_score]+[Off_Market_Value_score]+[Capital_gain_Score]+[Redress_Score] AS Total_score, Format([Entry_date],"yyyy") AS [Entry Year], [Entry Year]+1 AS [Year 1], Int([market value]*((1+[Annual_Price_Growth]/100)^1)) AS GrowthYear1, [Entry Year]+2 AS [Year 2], Int([market value]*((1+[Annual_Price_Growth]/100)^2)) AS GrowthYear2, [Entry Year]+5 AS [Year 5], Int([market value]*((1+[Annual_Price_Growth]/100)^5)) AS GrowthYear5, [Entry Year]+10 AS [Year 10], Int([market value]*((1+[Annual_Price_Growth]/100)^10)) AS GrowthYear10, [Entry Year]+15 AS [Year 15], Int([market value]*((1+[Annual_Price_Growth]/100)^15)) AS GrowthYear15, [GrowthYear1]-[CALC-LOAN-AMOUNT-EXP] AS PotEquityYr1, [GrowthYear2]-[CALC-LOAN-AMOUNT-EXP] AS PotEquityYr2, [GrowthYear5]-[CALC-LOAN-AMOUNT-EXP] AS PotEquityYr5, [GrowthYear10]-[CALC-LOAN-AMOUNT-EXP] AS PotEquityYr10, [GrowthYear15]-[CALC-LOAN-AMOUNT-EXP] AS PotEquityYr15, Int([PotEquityYr1]*[Margins_REFINANCING_Maximum_percentage]/100) AS RelEquityYr1, Int([PotEquityYr2]*[Margins_REFINANCING_Maximum_percentage]/100) AS RelEquityYr2, Int([PotEquityYr5]*[Margins_REFINANCING_Maximum_percentage]/100) AS RelEquityYr5, Int([PotEquityYr10]*[Margins_REFINANCING_Maximum_percentage]/100) AS RelEquityYr10, Int([PotEquityYr15]*[Margins_REFINANCING_Maximum_percentage]/100) ;
