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?
 
kmslogicConnect With a Mentor Commented:
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
 
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
 
jmoss111Connect With a Mentor Commented:
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.