MS Access and FileMaker...

SQL to Access question

Please support our MS Access and FileMaker Pro advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jul 2007

Posts: 276

Reputation: rapture has a spectacular aura about

Solved Threads: 37

rapture

Offline

Posting Whiz in Training

SQL to Access question

Mar 16th, 2009

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

Help with Code Tags

(Toggle Plain Text)

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

Last edited by rapture; Mar 16th, 2009 at 9:38 am.

•

Join Date: Jul 2008

Posts: 31

Reputation: gtd is an unknown quantity at this point

Solved Threads: 4

gtd

Offline

Light Poster

Re: SQL to Access question

Jul 2nd, 2009

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>

Last edited by peter_budo; Jul 6th, 2009 at 11:03 am. Reason: Keep It On The Site - Do not manually post "fake" signatures in your posts.

Talend, provider of open source data integration software.