Convert Access query to SQL query

[BradCustom]

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.

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

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

Thanks for your help!!

 

[Andrzejek]

If your fields:[tt]
dbo_Transaction_Data.Transaction_Start[/tt] and [tt]
dbo_Transaction_Data.Transaction_End[/tt] 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
[tt]AS Hrs Open[/tt] - you need to enclose your alias in "" since you have a space in it.


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

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.

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:

Invalid object name dbo_Transaction_Data

Thanks for your help!!

 

[Andrzejek]

Do you have an object (a table?) named [tt]dbo_Transaction_Data[/tt] ?

You may try to use [blue]an alias[/blue] for your object, something like:

[pre]
SELECT dbo_Employee.Employee,
dbo_Employee.Last_Name,
[blue]TD.[/blue]Transaction_Start,
[blue]TD.[/blue]Transaction_End,
dbo_Transaction_Detail.Job,
dbo_Transaction_Detail.Work_Center,
dbo_Job_Operation.Operation_Service,
(DateDiff(minute,[blue]TD.[/blue]Transaction_Start,[blue]TD.[/blue]Transaction_End)/60) AS "Hrs Open",
dbo_Employee.Status
FROM (([blue]dbo_Transaction_Data [blue]TD.[/blue] [/blue]INNER JOIN dbo_Employee
ON [blue]TD.[/blue]Employee = dbo_Employee.Employee)
INNER JOIN dbo_Transaction_Detail
ON [blue]TD.[/blue]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 ((([blue]TD.[/blue]Transaction_Start)
Between Ge[blue]TD.[/blue]ate()-2 AND '{%Current Date%}')
AND (((DateDiff(minute,[blue]TD.[/blue]Transaction_Start,
[blue]TD.[/blue]Transaction_End)/60))>11
Or ((DateDiff(minute,[blue]TD.[/blue]Transaction_Start,
[blue]TD.[/blue]Transaction_End)/60)) Is Null)
AND ((dbo_Employee.Status)='active'))
ORDER BY [blue]TD.[/blue]Transaction_Start DESC ,
[blue]TD.[/blue]Transaction_End;
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[BradCustom]

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.

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!!

 

[strongm]

>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