Convert Access queries to SQL - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Convert Access queries to SQL

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-10-15 : 09:21:07

I have an Access database that used to produce a mass of Performance Indicators from Access tables. The data is now held on SQL Server and I run the Access queries from the SQL tables. I wouldlike to move all the queries over to SQL but not sure if I can do that. Here's an example of one of the queries (the SQL view)

SELECT tblCalls.*
FROM tblCalls
WHERE (((tblCalls.Call_date)>=[Forms]![ReportParams]![SDate] And (tblCalls.Call_date)<=[Forms]![ReportParams]![EDate]) AND ((tblCalls.NotAccepted)=False) AND ((tblCalls.Completed_time) Is Not Null) AND ((tblCalls.Category)="fly tipping"));

SELECT Month([Call_date]) AS Mnth, DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0) AS Qtr, Sum(Work_Days([Call_date],[Completed_time])) AS RespTime, Sum(1) AS Count, Sum(Work_Days([Call_date],[Completed_time]))/[Count] AS AvgTime
FROM qryFlyTippingStatsSummary1
GROUP BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0)
ORDER BY Month([Call_date]), DateSerial(Year([Call_date]),Int((Month([Call_date])-1)/3)*3+4,0);

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-10-15 : 10:43:23

This may help a little:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

But you really need to know and understand BOTH Access and SQL Server *completely* before rewriting your code to work; it does you no good to have working T-SQL code if you cannot understand it.

Also, the first SELECT references an object on a form, which is an Access-specific thing that references the Access UI; in SQL Server, there is no such concept, you'd have to convert that to a parameter.

- Jeff
http://weblogs.sqlteam.com/JeffS

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-10-16 : 04:53:20

Thanks for your link. I'm wondering if it may be better to use Crystal reports and formulas...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources