Convert access sql - sql server 2005

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 sql - sql server 2005

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-07-21 : 07:01:45

Hi,

Please can someone convert the access select statement to Sql server 2005 equivalent.. many thanks

SELECT tblProjectPractices.ProjectPracticeID, Format([tblProjectPractices].[PracticeGPID],"000000") AS GPID,
tblODSPractices.MainContact, qryReminderLetter_StartDates.EvTypeID AS s, 
tblProjectPractices.nAg AS x 
FROM (tblProjects INNER JOIN (((tblProjectPatients INNER JOIN 
tblProjectPatientEvents ON tblProjectPatients.ProjectPatientID = tblProjectPatientEvents.ProjectPatientID) INNER JOIN
tblProjectPractices ON tblProjectPatients.PracticeEID = tblProjectPractices.PracticeEID) LEFT JOIN
qryReminderLetter_StartDates ON tblProjectPractices.ProjectPracticeID = qryReminderLetter_StartDates.ProjectPracticeID) ON
(tblProjects.ProjectID = tblProjectPatients.ProjectID) AND (tblProjects.ProjectID = tblProjectPractices.ProjectID)) INNER JOIN
tblODSPractices ON tblProjectPractices.PracticeEID = tblODSPractices.PracticeEID 
WHERE (((tblProjectPractices.ProjectID)=[forms]![frmScheduler]![txtProjects]) AND 
((tblProjectPractices.PracticeGPID) Like [forms]![frmScheduler]![txtGPID] & "*") AND
((tblProjectPatients.[No])=-1)) OR (((tblProjectPractices.ProjectID)=[forms]![frmScheduler]![txtProjects]) AND
((tblProjectPractices.PracticeGPID) Like [forms]![frmScheduler]![txtGPID] & "*") AND ((tblProjectPatients.InD)=-1)) 
GROUP BY tblProjectPractices.ProjectPracticeID, Format([tblProjectPractices].[PracticeGPID],"000000"), 
tblODSPractices.MainContact, qryReminderLetter_StartDates.EvTypeID, tblProjectPractices.nAg 
ORDER BY Format([tblProjectPractices].[PracticeGPID],"000000");

Devart
Posting Yak Master

102 Posts

Posted - 2010-07-21 : 08:20:14

Hello,

What does FORMAT function do?
I used Access some yeas ago and can be wrong, but you can try this:

CREATE PROCEDURE <your_procedure_name>
(@txtProjects varchar(255),@txtGPID varchar(6),@txtProjects varchar(255))
SELECT
tblProjectPractices.ProjectPracticeID,
replicate('0',6-datalength([tblProjectPractices].[PracticeGPID]))+[tblProjectPractices].[PracticeGPID] AS GPID,
tblODSPractices.MainContact,
qryReminderLetter_StartDates.EvTypeID AS s,
tblProjectPractices.nAg AS x
FROM tblProjectPatients
INNER JOIN tblProjectPatientEvents
ON tblProjectPatients.ProjectPatientID = tblProjectPatientEvents.ProjectPatientID
INNER JOIN tblProjectPractices
ON tblProjectPatients.PracticeEID = tblProjectPractices.PracticeEID)
INNER JOIN tblProjects
ON tblProjects.ProjectID = tblProjectPatients.ProjectID AND tblProjects.ProjectID = tblProjectPractices.ProjectID
LEFT JOIN qryReminderLetter_StartDates
ON tblProjectPractices.ProjectPracticeID = qryReminderLetter_StartDates.ProjectPracticeID)
INNER JOIN tblODSPractices ON
tblProjectPractices.PracticeEID = tblODSPractices.PracticeEID
WHERE
(((tblProjectPractices.ProjectID)=@txtProjects) AND
((tblProjectPractices.PracticeGPID) Like @txtGPID + "%") AND
((tblProjectPatients.[No])=-1)) OR (((tblProjectPractices.ProjectID)=@txtProjects) AND
((tblProjectPractices.PracticeGPID) Like @txtGPID + "%") AND ((tblProjectPatients.InD)=-1))
GROUP BY
tblProjectPractices.ProjectPracticeID,
replicate('0',6-datalength([tblProjectPractices].[PracticeGPID]))+[tblProjectPractices].[PracticeGPID],
tblODSPractices.MainContact,
qryReminderLetter_StartDates.EvTypeID,
tblProjectPractices.nAg
ORDER BY
replicate('0',6-datalength([tblProjectPractices].[PracticeGPID]))+[tblProjectPractices].[PracticeGPID]

Best regards,

Devart Team

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources