Converting access query to sql server

I am converting a query from access to sql server 2008.  This is the only query that I cannot get to run.  If someone would not mind taking a look and see what I am doing wrong.

Thanks in advance.

This is the orginal access query:
UPDATE LoansReceivable, ExecutedLoanData INNER JOIN TblCurrentBal ON ExecutedLoanData.LoanNumber = TblCurrentBal.[Loan Number] SET LoansReceivable.TotalCharges = DSum("[ExecutedLoanData]![4PERCENTFEE] +  [ExecutedLoanData]![INTER]  +  [ExecutedLoanData]![8PERCENTFEE] +   [ExecutedLoanData]![MAINT] +  [ExecutedLoanData]![LIFEINSPREMIUM] +  [ExecutedLoanData]![AANDHPREMIUM] +    [ExecutedLoanData]![cppPREMIUM] +  [ExecutedLoanData]![PROPPREMIUM] + [ExecutedLoanData]![AUTOPREMIUM]  +  [ExecutedLoanData]![ADANDDPREMIUM]  +  [ExecutedLoanData]![AUTOCLUBCHARGE]  +   [ExecutedLoanData]![NONFILEFEE] +  [ExecutedLoanData]![FILINGFEE]","ExecutedLoanData","(ExecutedLoanData.POsted = 'thismonth' or ExecutedLoanData.Posted is null) and ExecutedLoanData.LoanClass = 1"), LoansReceivable.NRICollected = IIf(DSum("[LoansRecNRIQuery]![TotalPymt] ","LoansRecNriQuery") Is Null,0,DSum("[LoansRecNRIQuery]![TotalPymt] ","LoansRecNriQuery")), LoansReceivable.PLCollected = IIf(DSum("[LoansRecPLCollQuery]![TotalPymt]","LoansRecPLCollQuery") Is Null,0,DSum("[LoansRecPLCollQuery]![TotalPymt]","LoansRecPLCollQuery")), LoansReceivable.AvgTerm = Int((DSum("ExecutedLoanData.Term","ExecutedLoanData","ExecutedLoanData.Posted is null or ExecutedLoanData.Posted = 'thismonth' and ExecutedLoanData.LoanClass = 1 and executedloandata.paydayyn = 'no'")+Int(DCount("ExecutedLoanData.Term","ExecutedLoanData","ExecutedLoanData.Posted is null or ExecutedLoanData.Posted = 'thismonth' and ExecutedLoanData.LoanClass = 1 and executedloandata.paydayyn = 'yes'")*100+0.5)/100)/DCount("ExecutedLoanData.Term","ExecutedLoanData","ExecutedLoanData.Posted is null or ExecutedLoanData.Posted = 'thismonth' and ExecutedLoanData.LoanClass = 1")*100+0.5)/100, LoansReceivable.NumLoansmotodate = DCount("[ExecutedLoanData]![TOTALPAYMENTS]","ExecutedLoanData","([ExecutedLoanData]![Posted] = 'thismonth' or ExecutedLoanData.Posted is null) and ExecutedLoanData.LoanClass = 1"), LoansReceivable.NumAutoClub = DCount("[ExecutedLoanData]![AUTOCLUBCHARGE]","ExecutedLoanData","([ExecutedLoanData]![Posted] ='thismonth' or ExecutedLoanData.Posted is null) and ExecutedLoanData.AutoClubCharge >0"), LoansReceivable.AutoClubCharges = DSum("[ExecutedLoanData]![AUTOCLUBCHARGE]","ExecutedLoanData","([ExecutedLoanData]![Posted] ='thismonth' or ExecutedLoanData.Posted is null) and ExecutedLoanData.AutoClubCharge >0"), LoansReceivable.Chargeoffs = IIf(DSum("TblCurrentBal.ChargeoffBal","TblCurrentBal"," TblCurrentBal.ChargeoffPosted = 'today'") Is Null,0,DSum("TblCurrentBal.ChargeoffBal","TblCurrentBal"," TblCurrentBal.ChargeoffPosted = 'today'")), LoansReceivable.NRICollPrinc = IIf(DSum("[LoansRecNRIQuery]![princpaymt] ","LoansRecNriQuery") Is Null,0,DSum("[LoansRecNRIQuery]![princpaymt] ","LoansRecNriQuery")), LoansReceivable.PLCollPrinc = IIf(DSum("[LoansRecPLCollQuery]![princPaymt]","LoansRecPLCollQuery") Is Null,0,DSum("[LoansRecPLCollQuery]![princpaymt]","LoansRecPLCollQuery"))
WHERE (((LoansReceivable.Posted) Is Null));

This is what I did:

UPDATE LoansReceivable
SET 
LoansReceivable.TotalCharges = (SELECT SUM([4percentfee] + [INTER] + [8percentfee] + [MAINT] + [LIFEINSPREMIUM] + [AANDHPREMIUM] + [CPPPremium])FROM ExecutedLoanData WHERE ExecutedLoanData.Posted = 'thismonth' or ExecutedLoanData.Posted IS NULL and ExecutedLoanData.LoanClass = 1),
LoansReceivable.NRICollected = ISNULL((SELECT SUM(TotalPymt) FROM LoansRecNRIQuery),0),
LoansReceivable.PLCollected = ISNULL((SELECT SUM(TotalPymt) FROM LoansRecPLCollQuery),0),
LoansReceivable.Avgterm = SELECT(INT( SUM(Term) FROM ExecutedLoanData Where Posted IS NULL or ExecutedLoanData.posted = 'thismonth' and ExecutedLoanData.LoanClass = 1 and ExecutedLoanData.paydayyn = 'no'))+
Int(COUNT(TERM FROM ExecutedLoanData Where ExecutedLoanData.posted IS NULL  or ExecutedLoanData.posted = 'thismonth' and ExecutedLoanData.LoanClass = 1 and executedloandata.paydayyn = 'yes')*100+0.5)/100)/ Count(term from executedloandata Where executedLoanData.posted is null or executedloandata.posted = 'thismonth' and executedloandata.loanclass = 1)*100+0.5)/100)
LoansReceivable.numloansmotodate = SELECT(COUNT([totalpayments] from executedloandata where posted = 'thismonth' or executedloandata.posted is null) and executedloandata.loanclass = 1),
Loansreceivable.numautoclub = SELECT(COUNT([Autoclubcharge] FROM executedloanData where posted = 'thismonth' or executedloandata.posted is null and executedloandata.autoclubcharge > 0),
LoansReceivable.autoclubcharges = SELECT((SUM([AutoClubCharge] FROM ExecutedLoanData Where Posted = 'thismonth' or executedloandata.posted is null) and ExecutedloanData.AutoClubCharge >0),
LoansReceivable.chargeoffs = ISNULL((SELECT SUM(chargeoffbal) from TblCurrentBal Where ChargeoffPosted = 'today'),0),
LoansReceivable.nricollprinc = ISNULL((SELECT SUM(princpaymt) FROM LoansRecNRIQuery),0),
LoansReceivable.nricollprinc = ISNULL((SELECT SUM(princpaymt) FROM LoansRecPLCollQuery),0)
Where LoansReceivable.Posted IS NULL

Open in new window

garyoallenAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kmslogicCommented:
Ok a few things:

1. Create a SELECT statement first instead of trying to run the update when you have errors.  After the SELECT runs it will be easy to convert it to an update.

2. All your subqueries should start with (SELECT ... ), so for example LoansReceivable.NRICollected = ISNULL((SELECT SUM(TotalPymt) FROM LoansRecNRIQuery),0) should be something like (SELECT ISNULL(SUM(TotalPymt), 0) FROM LoansRecNRIQuery).  Also that LoansRecNRIQuery should exist on your SQL server as a view for that to work.

3. Add one subquery at a time to your main SELECT statement to see where things fail.  Your main select should be something like this:

SELECT (SELECT SUM([4percentfee] + [INTER] + [8percentfee] + [MAINT] + [LIFEINSPREMIUM] + [AANDHPREMIUM] + [CPPPremium])FROM ExecutedLoanData WHERE ExecutedLoanData.Posted = 'thismonth' or ExecutedLoanData.Posted IS NULL and ExecutedLoanData.LoanClass = 1) as TotalCharges FROM LoansReceivable LR WHERE LR.Posted Is Null

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.