471,085 Members | 1,071 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,085 software developers and data experts.

Need help with converting Access Query to MSDE

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
May 21 '07 #1
5 1465
Vidhura
99
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
May 22 '07 #2
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"
May 22 '07 #3
almaz
168 Expert 100+
Does !='None' mean not equal to None?
No, "NOT EQUAL" operator is "<>".
Try to run following script, please post error message (if any):
Expand|Select|Wrap|Line Numbers
  1. SELECT distinct
  2. Empfname +''+ Emplname AS Employee
  3. ,TimeEntry.TEdate
  4. ,case when Engrelated <> 'None' then [Engrelated] + ':' + [Cltname] else [Cltname] end AS Client
  5. , ServiceCodes.SCDesc
  6. , ([TEhours]*360) AS [Time]
  7. , TimeEntry.TEbillable
  8. , TimeEntry.TEref
  9. , Clients.Engrelated
  10. FROM Employee 
  11.     INNER JOIN TimeEntry ON Employee.ID = TimeEntry.TEempID
  12.     INNER JOIN Clients ON Clients.ID = TimeEntry.TECltID
  13.     INNER JOIN ServiceCodes ON ServiceCodes.ID = TimeEntry.TECodeID
  14.  
May 22 '07 #4
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.
May 22 '07 #5
almaz
168 Expert 100+
... 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.
May 23 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by *no spam* | last post: by
6 posts views Thread by baramee | last post: by
1 post views Thread by Eva | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.