Help with converting Query from Access to SQL server

I need help with this beast of a query.  I am tryin to covert it to where it will run on the SQL server instead of in access.  Everytime that I get something that looks right, I get a syntax error.

Thanks in advance for your help!

See Below
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));

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:
To me it seems like you are asking an expert a lot more than answering a question--you want them to do your job for you.  Sorry if that seems blunt.  You are right, you have a beast of a query there and it probably includes about 3 or more actual expert questions which you would have to understand and then apply to create a query that works.

First, the dcount() and dsum() functions don't exist in T-SQL--those are access constructions.  In general you can replace those by subqueries, for example DSUM("totalamount", "mytable", "posted=1") would roughly translate to (SELECT SUM(totalamount) FROM mytable WHERE posted=1).  With dcount you would just use COUNT() inside the subquery instead of SUM()

The IIF() function does not exist in T-SQL.  You can use CASE/WHEN inside of a TSQL statement to replicate the functionality:

CASE
  WHEN condition THEN trueresult
  [...n]
[ELSE elseresult]
END

You can have multiple WHEN... THEN..'s inside of the CASE statement.  Here's a Microsoft example:

SELECT   ProductNumber, Category =
      CASE ProductLine
         WHEN 'R' THEN 'Road'
         WHEN 'M' THEN 'Mountain'
         WHEN 'T' THEN 'Touring'
         WHEN 'S' THEN 'Other sale items'
         ELSE 'Not for sale'
      END,
   Name
FROM Production.Product

You probably get the idea.   I would also recommend using a SQL formatter to unmangle your large statement and make it easier to read.  Googling will work great for that.
0

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
garyoallenAuthor Commented:
I didn't want someone to do it for me, I just need some help to see the major things that I need to make sure I address with this query.  But thanks for your help! I didn't know that I could use the CASE statement.
0
jmoss111Commented:
You might find this recent article from Database Journal to be of interest:

http://www.databasejournal.com/features/msaccess/article.php/3865076/T-SQL-Equivalents-for-Microsoft-Access-VBA-Functions.htm

Best Regards,

Jim
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

kmslogicCommented:
Why don't you take a stab at converting it, then, and then post your query again if you're still having a problem/getting errors.
0
garyoallenAuthor Commented:
Ok this is what I have, but I am still getting errors.

See below


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

0
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.