[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Access Query Conversion to SQL

I have the following Access query that needs converting to SQL, I have tried it but keep getting wrong results, I just wandered if anyone else would be able to convert it.

SELECT dbo_job.jobid, Sum([quantity]*[price]) AS [value]
FROM (dbo_job INNER JOIN ((dbo_templateprice INNER JOIN dbo_template ON dbo_templateprice.templateid = dbo_template.templateid) INNER JOIN dbo_priceset ON dbo_templateprice.pricesetid = dbo_priceset.pricesetid) ON dbo_job.pricesetid = dbo_priceset.pricesetid) INNER JOIN dbo_workitem ON (dbo_job.jobid = dbo_workitem.jobid) AND (dbo_template.templateid = dbo_workitem.templateid)
GROUP BY dbo_job.jobid, dbo_workitem.dfeid
HAVING (((dbo_workitem.dfeid) Is Null))
ORDER BY dbo_job.jobid;

Thanks Poly
2 Solutions
Jeff CertainCommented:
replace dbo_ with dbo. everywhere. ;)

SELECT dbo.job.jobid, Sum([quantity]*[price]) AS [value]
FROM (dbo.job INNER JOIN ((dbo.templateprice INNER JOIN dbo.template ON dbo.templateprice.templateid = dbo.template.templateid) INNER JOIN dbo.priceset ON dbo.templateprice.pricesetid = dbo.priceset.pricesetid) ON dbo.job.pricesetid = dbo.priceset.pricesetid) INNER JOIN dbo.workitem ON (dbo.job.jobid = dbo.workitem.jobid) AND (dbo.template.templateid = dbo.workitem.templateid)
GROUP BY dbo.job.jobid, dbo.workitem.dfeid
HAVING (((dbo.workitem.dfeid) Is Null))
ORDER BY dbo.job.jobid
what is the error message?
Try this:

SELECT job.jobid, Sum(quantity*price) AS value
FROM job
INNER JOIN workitem ON job.jobid = workitem.jobid
INNER JOIN template ON workitem.templateid = template.templateid
INNER JOIN priceset ON job.pricesetid = priceset.pricesetid
INNER JOIN templateprice ON priceset.pricesetid = templateprice.pricesetid
WHERE workitem.dfeid Is Null
GROUP BY job.jobid, workitem.dfeid
ORDER BY job.jobid
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

polynominalAuthor Commented:
its the sum(quantity * price) that doesnt tally
One problem I see is that you are grouping on a Null value.  Why not just GROUP BY job.jobid?
Jeff CertainCommented:
You might have to explicitly identify those fields:
e.g. SELECT job.jobid, Sum(job.quantity*priceset.price) AS value

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now