sql syntax vs access syntax problem

I have a query that I have working in MS SQL, but when I bring it back to Access SQL the syntax fails.  I have change the ' to #, and some changes in the DateDiff.  Each sub-query works Access SQL.

Syntax error (missing operator) in query expression.

MS Accesss SQL
SELECT main.[Supplier Name], [Supplier Number], sub.OpsHours, sub.OpsCalcHours, sub2.PunchHours, sub2.PunchCount
FROM Suppliers AS Main

LEFT JOIN
(
SELECT tblops.OpsResource as [Item Number]
	
	,Sum(DateDiff("h", [tblops].[opstimestart], [opstimeend])) AS OpsHours
	,Sum((DATEDIFF("d", tblops.opstimestart, opstimeend) * 10)) AS OpsCalcHours
FROM tblops
INNER JOIN Suppliers ON tblops.OpsResource = Suppliers.[Supplier Number]
WHERE (
		((Suppliers.[Rtype]) = "Staff")
		AND (
			(tblops.[OpsTimeStart]) BETWEEN #10/4/2015#
				AND #10/10/2015#
			)
		)
	OR (
		((Suppliers.[Rtype]) = "Staff")
		AND (
			(tblops.[OpsTimeStart]) BETWEEN #10/4/2015#
				AND #10/10/2015#
			)
		)
GROUP BY tblops.OpsResource
) sub ON Main.[Supplier Number] = sub.[Item Number]

LEFT JOIN
(
SELECT tblPunches.EmployeeID, Sum(DateDiff("n",[PunchIn],[PunchOut])/60) AS PunchHours, Count([PunchID]) As PunchCount
FROM tblPunches
WHERE (((tblPunches.PunchIn) Between #10/4/2015# And #10/10/2015#))
GROUP BY tblPunches.EmployeeID
) sub2 ON main.[Supplier Number] = sub2.EmployeeID

WHERE rtype = "Staff" 

Open in new window


SQL Server

SELECT main.[Supplier Name], [Supplier Number], sub.OpsHours, sub.OpsCalcHours, sub2.PunchHours, sub2.PunchCount
FROM Suppliers AS Main

LEFT JOIN
(
SELECT tblops.OpsResource as [Item Number]
	
	,Sum(DateDiff(HH, [tblops].[opstimestart], [opstimeend])) AS OpsHours
	,Sum((DATEDIFF(D, tblops.opstimestart, opstimeend) * 10)) AS OpsCalcHours
FROM tblops
INNER JOIN Suppliers ON tblops.OpsResource = Suppliers.[Supplier Number]
WHERE (
		((Suppliers.[Rtype]) = 'Staff')
		AND (
			(tblops.[OpsTimeStart]) BETWEEN '10 / 4 / 2015'
				AND '10 / 10 / 2015'
			)
		)
	OR (
		((Suppliers.[Rtype]) = 'Staff')
		AND (
			(tblops.[OpsTimeStart]) BETWEEN '10/4/2015'
				AND '10/10/2015'
			)
		)
GROUP BY tblops.OpsResource
) sub ON Main.[Supplier Number] = sub.[Item Number]

LEFT JOIN
(
SELECT tblPunches.EmployeeID, Sum(DateDiff("n",[PunchIn],[PunchOut])/60) AS PunchHours, Count([PunchID]) As PunchCount
FROM tblPunches
WHERE (((tblPunches.PunchIn) Between '10/4/2015' And '10/10/2015'))
GROUP BY tblPunches.EmployeeID
) sub2 ON main.[Supplier Number] = sub2.EmployeeID

WHERE rtype = 'Staff' 

Open in new window

pressMacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
Instead of a dot as separator for table.column, try to use ! which is the separator in Access for this.

So instead of:
   SELECT main.[Supplier Name],
use:
   SELECT main![Supplier Name]

and so on.

Also, why don't you try to use design editor to create the query on Microsoft Access? This will help you, and then you can obtain the syntax just moving on SQL view.

I hope this can help you. Regards.
0
PortletPaulfreelancerCommented:
"sub" probably isn't a good alias name in a product that supports VBA

"sub1" might be better?

also:
there's a missing table alias before [Supplier Number]

what is the purpose of repeating the same filtering conditions?
WHERE (
		((Suppliers.[Rtype]) = "Staff")
		AND (
			(tblops.[OpsTimeStart]) BETWEEN #10/4/2015#
				AND #10/10/2015#
			)
		)
	OR (
		((Suppliers.[Rtype]) = "Staff")
		AND (
			(tblops.[OpsTimeStart]) BETWEEN #10/4/2015#
				AND #10/10/2015#
			)
		)

Open in new window


How does the final where clause find [rtype]? (what table does it come from?)
0
typetoitCommented:
In Access, joins must have parentheses around them.  For example:
SELECT * FROM ((Table1 INNER JOIN Table2 ON Table1.field = Table2.field)
INNER JOIN Table3 ON Table1.field2 = Table3.field2)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pressMacAuthor Commented:
Here are some extended notes.  First off top kudos to typetoit, for the correct solution.

It is worth noting that using the bang separators in the JOIN part of the syntax does not work and raises "JOIN EXPRESSION NOT SUPPORTED", the bang separators seem fine in select part

So, this does not work:    ON main![Supplier Number] = sub2!EmployeeID)
but this does:   sub2 ON main.[Supplier Number] = sub2.EmployeeID)

But only, after adding proper parentheses.

Thanks for the help

working SQL
SELECT main.[Supplier Name], [Supplier Number], sub1.OpsHours, sub1.OpsCalcHours, sub2.PunchHours, sub2.PunchCount
FROM ((Suppliers AS Main

LEFT JOIN
(
SELECT tblops.OpsResource as [Item Number]
	
	,Sum(DateDiff("h", [tblops].[opstimestart], [opstimeend])) AS OpsHours
	,Sum((DATEDIFF("d", tblops.opstimestart, opstimeend) * 10)) AS OpsCalcHours
FROM tblops
INNER JOIN Suppliers ON tblops.OpsResource = Suppliers.[Supplier Number]
WHERE (
		((Suppliers.[Rtype]) = "Staff")
		AND (
			(tblops.[OpsTimeStart]) BETWEEN #10/4/2015#
				AND #10/10/2015#
			)
		)
	OR (
		((Suppliers.[Rtype]) = "Staff")
		AND (
			(tblops.[OpsTimeStart]) BETWEEN #10/4/2015#
				AND #10/10/2015#
			)
		)
GROUP BY tblops.OpsResource
) sub1 ON Main.[Supplier Number] = sub1.[Item Number])

LEFT JOIN
(
SELECT tblPunches.EmployeeID, Sum(DateDiff("n",[PunchIn],[PunchOut])/60) AS PunchHours, Count([PunchID]) As PunchCount
FROM tblPunches
WHERE (((tblPunches.PunchIn) Between #10/4/2015# And #10/10/2015#))
GROUP BY tblPunches.EmployeeID
) sub2 ON main.[Supplier Number] = sub2.EmployeeID)

WHERE rtype = "Staff"

Open in new window

0
PortletPaulfreelancerCommented:
((those darn parentheses - I know this) ... double darn it)

You still need to adopt the practice of prefixing ALL column references by their source, and you still appear to have an unnecessary duplication of where condition in your query....

cheers
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.