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.TEda te
, IIf([Engrelated]<>"None",([Engrelated] & ":" & [Cltname]),([Cltname])) AS Client
, ServiceCodes.SC Desc
, ([TEhours]*360) AS [Time]
, TimeEntry.TEbil lable
, TimeEntry.TEref
, Clients.Engrela ted

FROM (Employee INNER JOIN (Clients INNER JOIN TimeEntry ON Clients.ID = TimeEntry.TEClt ID)
ON Employee.ID = TimeEntry.TEemp ID)
INNER JOIN ServiceCodes
ON TimeEntry.TECod eID = ServiceCodes.ID

GROUP BY
([Empfname] & " " & [Emplname]), TimeEntry.TEdat e, IIf([Engrelated]<>"None",([Engrelated] & ":" & [Cltname]),([Cltname])), ServiceCodes.SC Desc, ([TEhours]*360), TimeEntry.TEbil lable, TimeEntry.TEref , Clients.Engrela ted;


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.TEda te
,case when Engrelated !='None' then [Engrelated]+ ':' + [Cltname] else [Cltname] end AS Client
, ServiceCodes.SC Desc
, ([TEhours]*360) AS [Time]
, TimeEntry.TEbil lable
, TimeEntry.TEref
, Clients.Engrela ted

FROM (Employee INNER JOIN (Clients INNER JOIN TimeEntry ON Clients.ID = TimeEntry.TEClt ID)
ON Employee.ID = TimeEntry.TEemp ID)
INNER JOIN ServiceCodes
ON TimeEntry.TECod eID = ServiceCodes.ID

GROUP BY
([Empfname] +''+ [Emplname]), TimeEntry.TEdat e, case when Engrelated !='None' then [Engrelated]+ ':' + [Cltname] else [Cltname] end , ServiceCodes.SC Desc, ([TEhours]*360), TimeEntry.TEbil lable, TimeEntry.TEref , Clients.Engrela ted

[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):
[CODE=sql]SELECT distinct
Empfname +''+ Emplname AS Employee
,TimeEntry.TEda te
,case when Engrelated <> 'None' then [Engrelated] + ':' + [Cltname] else [Cltname] end AS Client
, ServiceCodes.SC Desc
, ([TEhours]*360) AS [Time]
, TimeEntry.TEbil lable
, TimeEntry.TEref
, Clients.Engrela ted
FROM Employee
INNER JOIN TimeEntry ON Employee.ID = TimeEntry.TEemp ID
INNER JOIN Clients ON Clients.ID = TimeEntry.TEClt ID
INNER JOIN ServiceCodes ON ServiceCodes.ID = TimeEntry.TECod eID
[/CODE]

[silversubey]

thankyou very much for your help.
The Query works if I leave off the Distinct. What is Distinct?
Also, my text colum "TimeEntry.TEre f" 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.