473,242 Members | 1,461 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,242 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 1638
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

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

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
3
by: *no spam* | last post by:
I want to move my Access 2K database into MSDE. The Access Upsizing Wizard crashes (a known bug wi A2K), so I'm using the following suggested method: Access --> New --> Project (Existing...
4
by: Vikrant | last post by:
Hey friends Can someone please suggest that how many recordset can be opened simultaneously for a MS-Access 2000 Database.Does that make any difference if we use ODBC or some other way of...
3
by: swingingming | last post by:
Hi, for 5 weeks, I finished my mdb project. Thanks to all you guys. Now, I would like to put it on a server then 5-6 people can share it. I heard about the splitting back-end database, put it on a...
0
by: Lokkju | last post by:
I am pretty much lost here - I am trying to create a managed c++ wrapper for this dll, so that I can use it from c#/vb.net, however, it does not conform to any standard style of coding I have seen....
10
by: Wolfgang Kaml | last post by:
Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows...
6
by: baramee | last post by:
I develop web application by asp.net with ms access. In general , it work fine. But if hit rate of web is very much, it occure error 'Unspecified error'. it error on conn.open. My code will be...
12
by: Dan V. | last post by:
Since an ASP.NET/ADO.NET website is run on the server by a single "asp_net worker process", therefore doesn't that mean that even 50 simultaneous human users of the website would appear to the...
1
by: Eva | last post by:
Can anyone plz direct me on a useful article on upgrading my access databae to a msde database. I also have a question. is msde a dbms just like access? is it simple to use like access? I...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

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.