SQL Server - Problem with query when i try to convert from Access SQL to SQL Server 2005

Asked By Migena on 02-Apr-10 08:33 AM

SELECT Project.ProjectName, ProjectStatusReport.StatusReportDate, Project.ProjectStart, Project.ProjectEnd, Project.ProjectSubstatus, Project.ProjectStatus, ProjectPhase.PhaseName, Project.BusinessPM, Project.OwnerOrganisation, ProjectStatusReport.Comments

FROM Project INNER JOIN (ProjectPhase INNER JOIN ProjectStatusReport ON ProjectPhase.ProjectPhaseId = ProjectStatusReport.ProjectPhase) ON (Project.ProjectName = ProjectPhase.ProjectName) AND (Project.ProjectName = ProjectStatusReport.ProjectId)

GROUP BY Project.ProjectName, ProjectStatusReport.StatusReportDate, Project.ProjectStart, Project.ProjectEnd, Project.ProjectSubstatus, Project.ProjectStatus, ProjectPhase.PhaseName, Project.BusinessPM, Project.OwnerOrganisation, ProjectStatusReport.Comments, Project.ProjectDescription, Project.ProjectType, Project.ProjectSize, Project.ProjectOfficePriority, Project.ProjectCriticalFactor, Project.Notes, Project.IsActive, Project.BusinessPMAditional, Project.OwnerSponsor, Project.SteeringCommittee, Project.StakeHolders

HAVING (((Project.ProjectName) Like [forms]![Search Project Comments History]![ProjectName]))

ORDER BY ProjectStatusReport.StatusReportDate DESC;

I have tried to convert this access query to SQL server but I found it impossible. Please could you help me?
Thank you in advance!

Mohan Raj Aryal replied to Migena on 02-Apr-10 11:42 AM
Two problems in your query:

- GROUP BY : there is no aggregation function so no need of group by function here.
- HAVING: No need of HAVING if you don't use GROUP BY. Also, [forms]![Search Project Comments History]![ProjectName])) is the value from your MS Access form would not be available on SQL Server 2005. So you will need to pass this as parameter to Stored procedure or variable in query.

The query should look something like this: 

SELECT Project.ProjectName, 
ProjectStatusReport.StatusReportDate, 
Project.ProjectStart, 
Project.ProjectEnd, 
Project.ProjectSubstatus, 
Project.ProjectStatus, 
ProjectPhase.PhaseName, 
Project.BusinessPM, 
Project.OwnerOrganisation, 
ProjectStatusReport.Comments

FROM Project 
INNER JOIN ProjectPhase 
ON Project.ProjectName = ProjectPhase.ProjectName
INNER JOIN ProjectStatusReport
ON ProjectPhase.ProjectPhaseId = ProjectStatusReport.ProjectPhase
AND Project.ProjectName = ProjectStatusReport.ProjectId

WHERE Project.ProjectName Like '%'+@ProjectName+"'%'
ORDER BY ProjectStatusReport.StatusReportDate DESC