I have the following SQL Query:
SELECT Clients.ClientRef as 'ID', Clients.Title + ' ' + Clients.Forename + ' ' + Clients.Surname as 'Name', CONVERT(nvarchar(10), Clients.Dob, 103) as 'Date of Birth', CASE WHEN Clients.IsMale = 1 THEN 'Male' WHEN Clients.IsMale = 0 THEN 'Female' END As 'Gender', Convert(nvarchar(10), Max(Assessments.TestDate),103) as 'Last Visit', CASE WHEN Max(Convert(integer,Assessments.Submitted)) = 1 Then 'Submitted' ELSE '' END AS 'Submission' FROM Clients Left JOIN Assessments ON Clients.Id = Assessments.ClientId
I want to convert it to be compatible with MSAccess.
I'm struggling with translating the 'Submission' column.
I've come up with the following access query, but it doesn't return anything when the corresponding SQL query returns some data.
SELECT Clients.ClientRef AS [ID] , Clients.Title & ' ' & Clients.Forename & ' ' & Clients.Surname AS [Name] , Format(Clients.Dob,'dd/mm/yy') AS [Date of Birth] , IIF(Clients.IsMale,'Male','Female') AS [Gender] , Format(MAX(Assessments.TestDate),'dd/mm/yy') AS [Last Visit] , IIF( Max( IIF(Assessments.Submitted,1,0)) >= 1 ,'Submitted',' ') AS [Submission] FROM Clients LEFT JOIN Assessments ON Clients.Id = Assessments.ClientId WHERE (ClientRef LIKE '%%' OR Forename LIKE '%%' OR Surname LIKE '%%') AND ClientRef NOT LIKE 'QA%' AND ClientRef NOT LIKE 'EHC%' GROUP BY Clients.ClientRef, Clients.Title, Clients.Forename, Clients.Surname, Clients.Dob, Clients.IsMale ORDER BY ClientRef
Any suggestions welcome.
Thanks, I was copy and pasting the query from my application into Access, as I am using the Jetoledb engine. That want's % instead of * for the wildcard. I have been caught out by this in the past and I had left myself a comment to overcome this issue but I have been foiled again....