SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert access sql - sql server 2005
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dr223
Constraint Violating Yak Guru

417 Posts

Posted - 07/21/2010 :  07:01:45  Show Profile  Reply with Quote
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");

Edited by - dr223 on 07/21/2010 07:30:35

Devart
Posting Yak Master

102 Posts

Posted - 07/21/2010 :  08:20:14  Show Profile  Visit Devart's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000