Converting Query from Access to SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Converting Query from Access to SQL Server

Author

Topic

rkumar28
Starting Member

49 Posts

Posted - 2006-03-29 : 16:07:29

Hi,

I am working on a query that was written in MS Access by someone else. I am trying to convert that query to SQL Server as we will be running this query in sql server.
There are few functions in the query like “FIRST”,”LAST” AND “RIGHT” that is only recognized by MS Access and not SQL Server.
We were trying to grab the first and last record in the group by clause for the field mentioned in the query below. How can we achieve the same thing in SQL server. Are there are any alternative functions in sql server that does the same thing like “FIRST”,”LAST” AND “RIGHT” in MS Access query below.

Also, can we have the joins in sql server the same way it is joined MS Access.

Will appreciate any help.

Below is the query. I have cut it short to accomodate in the window.

SELECT
dbo_Agents.Agent,
dbo_Agents.Agent, dbo_Agents.AgentName,
dbo_SirconAgreement.ManagerLevelID,
dbo_SirconFirm.NationalProducerID,

First(DateDiff("d",dbo_SirconAgreement.begindate,Now())) AS Days,
First(DateDiff("d",dbo_SirconIndividual.filedate,Now())) AS IndividualDays,
First(DateDiff("d",dbo_SirconFirm.filedate,Now())) AS FirmDays,
First(dbo_SirconAgreement.FileDate) AS FirstOfFileDate,

Last(dbo_SirconIndividual.Action) AS LastOfAction1

FROM
((dbo_SirconAgreement RIGHT JOIN (dbo_Agents LEFT JOIN dbo_SirconIndividual ON dbo_Agents.Agent = dbo_SirconIndividual.NationalProducerID)
ON dbo_SirconAgreement.ID = dbo_Agents.Agent)
LEFT JOIN dbo_SirconFirm ON dbo_SirconAgreement.ExternalID = dbo_SirconFirm.NationalProducerID)
LEFT JOIN dbo_SirconAppointment ON dbo_SirconAgreement.KeyInternalID = dbo_SirconAppointment.KeyInternalID

WHERE (((Right([dbo_Agents].[Agent],3))<>"DEL"))

GROUP BY
dbo_Agents.Agent,
dbo_Agents.AgentName,
dbo_SirconAgreement.ManagerLevelID,
dbo_SirconFirm.NationalProducerID

Raj

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-03-29 : 17:06:17

Raj,

The FIRST and LAST concepts don't really exist in SQL Server, as you've probably just found. To find the first/last occurences of something, you need a point of reference (e.g. an auto-incrementing ID field). Then you can do something like this:
SELECT FileDate FROM dbo_SirconFirm WHERE ID = (SELECT MIN(ID) FROM dbo_SirconFirm )
Obviously this will need to be in a sub-query or similar to get what you want.
Personally, I would determine what the output is meant to be and re-write from scratch rather than porting potentially troublesome Access SQL.
RIGHT is much easier. Just use SUBSTRING and LEN in tandem:

SUBSTRING(myField, LEN(myField)-5, 5)

this should be equivalent to RIGHT(myField,5)

HTH,

Tim

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-29 : 17:20:00

Uh, guys, I know it's close to the end of the day, but:

declare	@MyString varchar(100)
set	@MyString = 'SQL Server does not have a built-in RIGHT() function.'
select	left(@MyString, 16) + right(@MyString, 33)

Output:

------------------------------------------------- 
SQL Server does have a built-in RIGHT() function.

pootle_flump

1064 Posts

Posted - 2006-03-29 : 18:21:06

It might be the double quotes rather than Right that is ...uh... wrong

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-03-29 : 22:44:59

May i know whether there is any such standard tool to convert the Access query to SQL or Oracle...

Thanks in advance

Regards,
satish.r
"Known is a drop, Unknown is an Ocean"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-29 : 23:22:08

quote:
Originally posted by a_r_satish

May i know whether there is any such standard tool to convert the Access query to SQL or Oracle...

Yes. It's called the left lobe, and you can access it from your right lobe via a corpus callosum connection. This should have been installed during your original setup routine. Check with your manufacturer.

rkumar28
Starting Member

49 Posts

Posted - 2006-03-30 : 13:18:41

Thanks for the reply. The RIGHT function is working fine now.

I will have to figure out the implementation of the FIRST and LAST on the function on SQL server side.

I am trying to work on the suggestion that Timmy made earlier and see if that works.

Thanks
Raj

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-03-30 : 14:43:00

As was pointed out, the concept of FIRST and LAST is specific to Access's implementation of SQL, and is not standard.

You will need to use the "TOP 1" clause along with an ORDER BY statement to find the first or last record in a dataset.

a_r_satish
Yak Posting Veteran

84 Posts

Posted - 2006-04-04 : 02:31:09

[/quote]Yes. It's called the left lobe, and you can access it from your right lobe via a corpus callosum connection. This should have been installed during your original setup routine. Check with your manufacturer.

[/quote]

Ya checked with my manufacturers. They suggest the settings have been already installed

Swiss is one of such tool for the db conversion relativly effective

Regards,
satish.r
"Known is a drop, Unknown is an Ocean"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-04-04 : 09:04:04

I guess I just don't trust any 3rd party tool to do this cleanly and reliably for anything but the simplest schemas and code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources