Need help with converting Access Query to MSDE

[silversubey]

I have a query that works in Access. It pulls time in a format that is understood by Quickbooks. We are upgrading our access-based program to MSDE. I have tried 3rd party converters and searched Google, but I still cannot get the proper syntax down. I am posting my access query in SQL view.

SELECT
([Empfname] & " " & [Emplname]) AS Employee
,TimeEntry.TEdate
, IIf([Engrelated]<>"None",([Engrelated] & ":" & [Cltname]),([Cltname])) AS Client
, ServiceCodes.SCDesc
, ([TEhours]*360) AS [Time]
, TimeEntry.TEbillable
, TimeEntry.TEref
, Clients.Engrelated

FROM (Employee INNER JOIN (Clients INNER JOIN TimeEntry ON Clients.ID = TimeEntry.TECltID)
ON Employee.ID = TimeEntry.TEempID)
INNER JOIN ServiceCodes
ON TimeEntry.TECodeID = ServiceCodes.ID

GROUP BY
([Empfname] & " " & [Emplname]), TimeEntry.TEdate, IIf([Engrelated]<>"None",([Engrelated] & ":" & [Cltname]),([Cltname])), ServiceCodes.SCDesc, ([TEhours]*360), TimeEntry.TEbillable, TimeEntry.TEref, Clients.Engrelated;


I created this query in access using "Design View" I greatly appreciate any help.

Marshall

[Vidhura]

try the following
SELECT
Empfname +''+ Emplname AS Employee
,TimeEntry.TEdate
,case when Engrelated !='None' then [Engrelated]+ ':' + [Cltname] else [Cltname] end AS Client
, ServiceCodes.SCDesc
, ([TEhours]*360) AS [Time]
, TimeEntry.TEbillable
, TimeEntry.TEref
, Clients.Engrelated

FROM (Employee INNER JOIN (Clients INNER JOIN TimeEntry ON Clients.ID = TimeEntry.TECltID)
ON Employee.ID = TimeEntry.TEempID)
INNER JOIN ServiceCodes
ON TimeEntry.TECodeID = ServiceCodes.ID

GROUP BY
([Empfname] +''+ [Emplname]), TimeEntry.TEdate, case when Engrelated !='None' then [Engrelated]+ ':' + [Cltname] else [Cltname] end , ServiceCodes.SCDesc, ([TEhours]*360), TimeEntry.TEbillable, TimeEntry.TEref, Clients.Engrelated

[silversubey]

Thankyou for the reply.
Does !='None' mean not equal to None?
I am now getting a text,ntext, image type..... error
I researched and found that 1 of the columns i need data from is a "text" column. All I can find to do is recreate the column "varchar" but the problem is there is data in that column. Any suggestions? thankyou for all of your help.

EDIT

the error that I am getting says that text,ntext ...cannot be compared or sorted. the column is TimeEntry.TEref in my script. I do not need to compare it with anything. it is only a description of what work was done. If I remove it from GROUP BY, I get an error that it isnt needed in SELECT. I am new to this so my question is, Is there a way to display the column but not use "GROUP BY"

[almaz]

Does !='None' mean not equal to None?

No, "NOT EQUAL" operator is "<>".
Try to run following script, please post error message (if any):

SELECT distinct
Empfname +''+ Emplname AS Employee
,TimeEntry.TEdate
,case when Engrelated <> 'None' then [Engrelated] + ':' + [Cltname] else [Cltname] end AS Client
, ServiceCodes.SCDesc
, ([TEhours]*360) AS [Time]
, TimeEntry.TEbillable
, TimeEntry.TEref
, Clients.Engrelated
FROM Employee 
    INNER JOIN TimeEntry ON Employee.ID = TimeEntry.TEempID
    INNER JOIN Clients ON Clients.ID = TimeEntry.TECltID
    INNER JOIN ServiceCodes ON ServiceCodes.ID = TimeEntry.TECodeID
 

[silversubey]

thankyou very much for your help.
The Query works if I leave off the Distinct. What is Distinct?
Also, my text colum "TimeEntry.TEref" only displays (MEMO) I will check this out further.

[almaz]

... What is Distinct? ...

Your query contains a GROUP BY clause that groups by all columns from SELECT clause. It means that you want to get rows with unique combination of values. That is exactly what DISTINCT does, so I removed GROUP BY clause and added DISTINCT.