×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Convert Access query to SQL query

Convert Access query to SQL query

Convert Access query to SQL query

(OP)
I'm working with Access 2016 and SQL 2012. I'm trying to convert the following query from Access to SQL but I keep getting errors. I've listed the statement and the errors below. Any help would be very much appreciated.

CODE

SELECT dbo_Employee.Employee, dbo_Employee.Last_Name, dbo_Transaction_Data.Transaction_Start, dbo_Transaction_Data.Transaction_End, dbo_Transaction_Detail.Job, dbo_Transaction_Detail.Work_Center, dbo_Job_Operation.Operation_Service, (DateDiff(minute,dbo_Transaction_Data.Transaction_Start,dbo_Transaction_Data.Transaction_End)/60) AS Hrs Open, dbo_Employee.Status
FROM ((dbo_Transaction_Data INNER JOIN dbo_Employee ON dbo_Transaction_Data.Employee = dbo_Employee.Employee) INNER JOIN dbo_Transaction_Detail ON dbo_Transaction_Data.Transaction_Data = dbo_Transaction_Detail.Transaction_Data) LEFT JOIN dbo_Job_Operation ON (dbo_Transaction_Detail.Job = dbo_Job_Operation.Job) AND (dbo_Transaction_Detail.Work_Center = dbo_Job_Operation.Work_Center)
WHERE (((dbo_Transaction_Data.Transaction_Start) Between gedate()-2 And getdate()) AND (((DateDiff(minute,dbo_Transaction_Data.Transaction_Start,dbo_Transaction_Data.Transaction_End)/60))>11 Or ((DateDiff(minute,dbo_Transaction_Data.Transaction_Start,dbo_Transaction_Data.Transaction_End)/60)) Is Null) AND ((dbo_Employee.Status)='active'))
ORDER BY dbo_Transaction_Data.Transaction_Start DESC , dbo_Transaction_Data.Transaction_End; 

Errors

CODE

Incorrect syntax near 'DateDiff"
Incorrect syntax near 'dbo_Transaction_Data'
Incorrect syntax near ',' 

Thanks for your help!!

RE: Convert Access query to SQL query

If your fields:
dbo_Transaction_Data.Transaction_Start
and
dbo_Transaction_Data.Transaction_End
are Dates, to get the difference in Days you may simply subtract one from the other. To get Hours and / or minutes, you can just multiply the outcome by 24 or 24 * 60
AS Hrs Open - you need to enclose your alias in "" since you have a space in it.


---- Andy

There is a great need for a sarcasm font.

RE: Convert Access query to SQL query

(OP)
Andy,
Thanks for the reply and suggestion. I've fixed all but one error which I don't understand why this error is coming up. Below is the second version of the code. The reason I didn't use your suggestion for the DateDiff is because it's a DateTime field and some of the entries are only a few minutes long.

CODE

SELECT dbo_Employee.Employee, dbo_Employee.Last_Name, dbo_Transaction_Data.Transaction_Start, dbo_Transaction_Data.Transaction_End, dbo_Transaction_Detail.Job, dbo_Transaction_Detail.Work_Center, dbo_Job_Operation.Operation_Service, (DateDiff(minute,dbo_Transaction_Data.Transaction_Start,dbo_Transaction_Data.Transaction_End)/60) AS "Hrs Open", dbo_Employee.Status
FROM ((dbo_Transaction_Data INNER JOIN dbo_Employee ON dbo_Transaction_Data.Employee = dbo_Employee.Employee) INNER JOIN dbo_Transaction_Detail ON dbo_Transaction_Data.Transaction_Data = dbo_Transaction_Detail.Transaction_Data) LEFT JOIN dbo_Job_Operation ON (dbo_Transaction_Detail.Job = dbo_Job_Operation.Job) AND (dbo_Transaction_Detail.Work_Center = dbo_Job_Operation.Work_Center)
WHERE (((dbo_Transaction_Data.Transaction_Start) Between Getdate()-2 AND '{%Current Date%}') AND (((DateDiff(minute,dbo_Transaction_Data.Transaction_Start,dbo_Transaction_Data.Transaction_End)/60))>11 Or ((DateDiff(minute,dbo_Transaction_Data.Transaction_Start,dbo_Transaction_Data.Transaction_End)/60)) Is Null) AND ((dbo_Employee.Status)='active'))
ORDER BY dbo_Transaction_Data.Transaction_Start DESC , dbo_Transaction_Data.Transaction_End; 


The only error is:

CODE

Invalid object name dbo_Transaction_Data 

Thanks for your help!!

RE: Convert Access query to SQL query

Do you have an object (a table?) named dbo_Transaction_Data ?

You may try to use an alias for your object, something like:

SELECT dbo_Employee.Employee, 
dbo_Employee.Last_Name, 
TD.Transaction_Start, 
TD.Transaction_End, 
dbo_Transaction_Detail.Job, 
dbo_Transaction_Detail.Work_Center, 
dbo_Job_Operation.Operation_Service, 
(DateDiff(minute,TD.Transaction_Start,TD.Transaction_End)/60) AS "Hrs Open", 
dbo_Employee.Status
FROM ((dbo_Transaction_Data TD. INNER JOIN dbo_Employee 
ON TD.Employee = dbo_Employee.Employee) 
INNER JOIN dbo_Transaction_Detail 
ON TD.Transaction_Data = dbo_Transaction_Detail.Transaction_Data) 
LEFT JOIN dbo_Job_Operation 
ON (dbo_Transaction_Detail.Job = dbo_Job_Operation.Job) 
AND (dbo_Transaction_Detail.Work_Center = dbo_Job_Operation.Work_Center)
WHERE (((TD.Transaction_Start) 
Between GeTD.ate()-2 AND '{%Current Date%}') 
AND (((DateDiff(minute,TD.Transaction_Start,
TD.Transaction_End)/60))>11 
Or ((DateDiff(minute,TD.Transaction_Start,
TD.Transaction_End)/60)) Is Null) 
AND ((dbo_Employee.Status)='active'))
ORDER BY TD.Transaction_Start DESC , 
TD.Transaction_End; 
 


---- Andy

There is a great need for a sarcasm font.

RE: Convert Access query to SQL query

(OP)
Andy,
Thanks so much for your reply. I found the issue with the SQL statement. Because the tables are linked to the Access database the table name has a "dbo" prefix which of course isn't needed in the SQL statement. Below is the code that works.

CODE

SELECT Employee.Employee, Employee.Last_Name, Transaction_Data.Transaction_Start, Transaction_Data.Transaction_End, Transaction_Detail.Job, Transaction_Detail.Work_Center, Job_Operation.Operation_Service, (DateDiff(minute,Transaction_Data.Transaction_Start,Transaction_Data.Transaction_End)/60) AS "Hrs Open", Employee.Status
FROM ((Transaction_Data INNER JOIN Employee ON Transaction_Data.Employee = Employee.Employee) INNER JOIN Transaction_Detail ON Transaction_Data.Transaction_Data = Transaction_Detail.Transaction_Data) LEFT JOIN Job_Operation ON (Transaction_Detail.Job = Job_Operation.Job) AND (Transaction_Detail.Work_Center = Job_Operation.Work_Center)
WHERE (((Transaction_Data.Transaction_Start) Between Getdate()-2 AND '{%Current Date%}') AND (((DateDiff(minute,Transaction_Data.Transaction_Start,Transaction_Data.Transaction_End)/60))>11 Or ((DateDiff(minute,Transaction_Data.Transaction_Start,Transaction_Data.Transaction_End)/60)) Is Null) AND ((Employee.Status)='active'))
ORDER BY Transaction_Data.Transaction_Start DESC , Transaction_Data.Transaction_End; 

Thanks again for all your help!!

RE: Convert Access query to SQL query

>Invalid object name

I'd suspect it may well be because you are using SQL Management Studio to run the query, but actually have Master as the default DB. And Master doesn't have dbo_Transaction_Data

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close