SQL to Access question
 

Hello All,

I'm wondering if anyone can help me by either directing me towards a source that would help me understand Access syntax better or help me figure out if it's possible to convert this SQL syntax to Access. I've tried but can't seem to get it to work.

my SQL is

SELECT  

        substring(c.[DealerNumber],11,3) AS InsGrp, 

        substring(c.[DealerNumber],1,2) AS DlrSt, 

        c.DealerNumber, 

        c.ContractNumber, 

        substring(c.[DealerNumber],13,1) AS Expr1,

        c.ownerfname,

        c.ownerlname,

        c.address1,

        c.address2,

        c.city,

        c.state,

        c.zip,

        c.carrierid,

        carrier.carriername,

        dbo.InsuranceGroup.TPA,

        dbo.CLAIMS.ClaimNumber,                

        dbo.CLAIMSCOMP.Status,

        dbo.claims.date,        

        dbo.CLAIMS.CorrectiveAction



FROM 

        contract C

INNER JOIN 

        carrier 

ON 

        c.carrierid = carrier.carrierid

INNER JOIN 

        dbo.InsuranceGroup 

ON 

        substring(c.[DealerNumber],11,3) = dbo.InsuranceGroup.InsuranceGroup

INNER JOIN

        dbo.claims

ON

        dbo.claims.contractnumber = c.ContractNumber

INNER JOIN

        dbo.claimscomp

ON

        claims.claimnumber = claimscomp.claimnumber



WHERE 

        (((substring(c.[DealerNumber],1,2))= 'CA') 

AND 

        ((substring(c.[DealerNumber],13,1))='M')) 

OR 

        (((substring(c.[DealerNumber],1,2))='IA') 

AND 

        ((substring(c.[DealerNumber],13,1))<>'M'))

AND

        (((dbo.InsuranceGroup.TPA) In ('Dimension','NASC','NASC Old')))

AND 

        (((dbo.CLAIMSCOMP.Status) In ('07','93','94','de','du','dn')))

AND

        dbo.claims.[Date] > '2009-03-11 13:40:38.000'

and I seem to get errors when trying to put this into Access. I've tried the GUI but the join on substring I can't figure out. This query works fine in SQL, however, I'm trying to get it into an Access query so a user can run the report whenever they want without having to come to IT.
Thanks

Re: SQL to Access question
 

Hello,

What you could try to use is an ETL tool. It can perform the job efficiently.

An ETL tool is for data integration and migration experts. It allows the user to get a graphical and functional view of integration processes.

You can look at two comparable tools: Datastage and Talend (open source) , two good ETL tools.

For more information:
<FAKE SIGNATURE>