Access Query Conversion to SQL

Posted on 2005-05-17
Last Modified: 2010-03-19
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
Question by:polynominal
    LVL 24

    Expert Comment

    by:Jeff Certain
    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
    LVL 3

    Expert Comment

    what is the error message?
    LVL 2

    Accepted Solution

    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

    Author Comment

    its the sum(quantity * price) that doesnt tally
    LVL 2

    Assisted Solution

    One problem I see is that you are grouping on a Null value.  Why not just GROUP BY job.jobid?
    LVL 24

    Expert Comment

    by:Jeff Certain
    You might have to explicitly identify those fields:
    e.g. SELECT job.jobid, Sum(job.quantity*priceset.price) AS value

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now