asked on

Convert Access query to SQL query

I copied the following query out of Access (I didn't write it) and I need to be able to run it in SQL, but I'm receiving error messages. Here is what I have so far:

SELECT DISTINCTROW dbo.Balances.CurrentBalance, dbo.Balances.Arrears1Balance,
dbo.Balances.Arrears2Balance, dbo.Balances.Arrears3Balance,
dbo.Balances.Arrears4PlusBalance, dbo.Customer.CustomerAcctNumber,
dbo.Customer.BillToName, dbo.Customer.BillToStreet1, dbo.Customer.BillToStreet2,
dbo.Customer.BillToCity, dbo.Customer.BillToState, dbo.Customer.BillToZip,
dbo.Customer.CreditLimit, dbo.Customer.SocialSecurityNumber, dbo.Customer.CustID,
dbo.Customer.BillingGroupID, dbo.Customer.BillToCountry,
IIf(Nz([CreditClass])="","*",[CreditClass]) AS CrClass, dbo.SalesPersonnel.SalesGroupID,
dbo.Customer.InvoiceResponsible, dbo.BillingGroup.CompanyName
FROM ((Customer RIGHT JOIN dbo.Balances ON dbo.Customer.CustID = dbo.Balances.CustID)
LEFT JOIN dbo.BillingGroup ON dbo.Customer.BillingGroupID = dbo.BillingGroup.BillingGroupID)
LEFT JOIN dbo.SalesPersonnel ON dbo.Customer.SalesID = dbo.SalesPersonnel.SalesID
WHERE (((dbo.Customer.CustomerAcctNumber)<>"") AND ((dbo.Customer.InvoiceResponsible)=True));

These are the errors I'm receiving:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.
Server: Msg 1038, Level 15, State 1, Line 8
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 13
Line 13: Incorrect syntax near 'dbo'.
Server: Msg 1038, Level 15, State 1, Line 13
Cannot use empty object or column names. Use a single space if necessary.

rafrancisco

Try this:

SELECT DISTINCT dbo.Balances.CurrentBalance, dbo.Balances.Arrears1Balance,
dbo.Balances.Arrears2Balance, dbo.Balances.Arrears3Balance,
dbo.Balances.Arrears4PlusBalance, dbo.Customer.CustomerAcctNumber,
dbo.Customer.BillToName, dbo.Customer.BillToStreet1, dbo.Customer.BillToStreet2,
dbo.Customer.BillToCity, dbo.Customer.BillToState, dbo.Customer.BillToZip,
dbo.Customer.CreditLimit, dbo.Customer.SocialSecurityNumber, dbo.Customer.CustID,
dbo.Customer.BillingGroupID, dbo.Customer.BillToCountry,
ISNULL([CreditClass], '*') AS CrClass,
dbo.SalesPersonnel.SalesGroupID,
dbo.Customer.InvoiceResponsible, dbo.BillingGroup.CompanyName
FROM ((Customer RIGHT JOIN dbo.Balances ON dbo.Customer.CustID = dbo.Balances.CustID)
LEFT JOIN dbo.BillingGroup ON dbo.Customer.BillingGroupID = dbo.BillingGroup.BillingGroupID)
LEFT JOIN dbo.SalesPersonnel ON dbo.Customer.SalesID = dbo.SalesPersonnel.SalesID
WHERE (((dbo.Customer.CustomerAcctNumber)<>'') AND ((dbo.Customer.InvoiceResponsible)=1));

lhedgecoth

ASKER

Almost! Down to one error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value '*' to a column of data type smallint.

rafrancisco

What should be the value of CrClass?

lhedgecoth

ASKER

CrClass can be a value of 1, 2, 3, or 4 or null. Is that what you needed to know?

ASKER CERTIFIED SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rafrancisco

If that works, just tell what this part should be doing?

IIf(Nz([CreditClass])="","*",[CreditClass])

lhedgecoth

ASKER

It works! All I can tell you is that when I run it in Access, I get another field called CCDescription that goes off the [CreditClass] so if creditclass = 1 then CCDescription = Good, if creditclass = 2 then CCDescription = Very Good, if creditclass = 3 then CCDescription = Bad, and if creditclass = 1 then CCDescription = Risk

SOLUTION

rafrancisco

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lhedgecoth

ASKER

You're really good! Thanks for your help!