0

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

2
Contributors
1
Reply
2
Views
8 Years
Discussion Span
Last Post by gtd
0

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>

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.