Thursday, June 28, 2012 9:37 AM
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.
Thursday, June 28, 2012 9:55 AM
actually in access sql the wildcard is *, not the % as in standard sql so use that in your query and I think you'll get some result.
- Marked As Answer by d347hm4n Thursday, June 28, 2012 10:01 AM
Thursday, June 28, 2012 10:00 AM
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....