Referencing field values between queries

Question

Im trying to create a calculation in Access through the use of queries. At the moment one query calculates the value of 'MPP Oil' (max production potential) and another query needs to use this value to calculate 'Unallocated losses'. These calculations use company/asset/year data from a base query 'PEBaseQuery'. Other input values to calculate Unallocated losses are referenced using IDs... There seems to be something off with my code though, please help!

SELECT 
    qb1.CompanyName, 
    qb1.AssetName, 
    qb1.Year, 
    (qb3.MPPOilRevised 
     - SUM(qb1.DatapointValue) 
     - SUM(qb2.DatapointValue * 1000000)) AS Result
FROM 
    ((PEBaseQuery AS qb1 
    INNER JOIN PEBaseQuery AS qb2 
    ON qb1.Year = qb2.Year AND qb1.AssetName=qb2.AssetName)
    INNER JOIN PE_MPPOilRevised AS qb3 
    ON qb1.Year = qb3.Year AND qb1.AssetName=qb3.AssetName)
WHERE 
    qb1.DatapointID in (2033, 2035, 2043, 2037, 2031) 
AND qb2.DatapointID=2003
GROUP BY qb1.CompanyName, qb1.AssetName, qb1.Year;

Are you getting any error? What is the other query that needs the value of MPPOil? — Vikdor, Aug 26 '12 at 16:47
the error 'You tried to execute a query that does not include the specified expression 'CompanyName' as part of an aggregate function' - however ive used the same structure to begin other queries and they worked.. — Magda, Aug 26 '12 at 17:02

Vikdor · Accepted Answer · 2012-08-27T18:02:54.810

1

From the error you mentioned in the comments:

the error 'You tried to execute a query that does not include the specified expression 'CompanyName' as part of an aggregate function'

Use of aggregate functions require you to group by the columns that appear in the SELECT list other than the aggregated columns.

Edit:

I think this is what you are looking for:

SELECT
    qb1.CompanyName, 
    qb1.AssetName, 
    qb1.Year, 
    qb3.MPPOilRevised - TotalDataPointValue - TotalDataPointValueFactor
FROM
    ((
        SELECT 
            qb1.CompanyName, 
            qb1.AssetName, 
            qb1.Year, 
            SUM(qb1.DatapointValue) 'TotalDataPointValue',
            SUM(qb2.DatapointValue * 1000000) 'TotalDataPointValueFactor'
        FROM 
            (PEBaseQuery AS qb1 
            INNER JOIN PEBaseQuery AS qb2 
            ON qb1.Year = qb2.Year AND qb1.AssetName = qb2.AssetName)
        WHERE 
            qb1.DatapointID in (2033, 2035, 2043, 2037, 2031) 
        AND qb2.DatapointID = 2003
        GROUP BY qb1.CompanyName, qb1.AssetName, qb1.Year
    ) qb1
    INNER JOIN PE_MPPOilRevised AS qb3 
    ON qb1.Year = qb3.Year AND qb1.AssetName=qb3.AssetName)

edited Aug 27 '12 at 18:02

answered Aug 26 '12 at 17:04

Vikdor

23,934
10
61
84

With the addition of 'GROUP BY' i still have a similar error for the calculated Result field, it uses 'MPPOilRevised' from a calculated column in the PE_MPPOilRevised query.. not sure how to get rid of the error in this instance – Magda Aug 27 '12 at 15:07
I have edited my response with your probable requirement as per my understanding. Please check. – Vikdor Aug 27 '12 at 15:16
that was due to the unbalanced parenthesis in the inner query definition. – Vikdor Aug 27 '12 at 16:38
It doesnt appear to have fixed anything.. Im still getting the same error :( – Magda Aug 27 '12 at 17:06
Now I got access to SQL Server Mgmt Studio and verified this version and it parsed successfully. Since, I don't have the schema, only you would know if the query does what you intend to do. – Vikdor Aug 27 '12 at 17:22
The fact that SQL Server Mgmt Studio successfully parses a SQL statement is not a reliable indication the same SQL statement will be accepted by Access' db engine. These are 2 different SQL dialects. In this case, Access' db engine requires proper use of parentheses in the FROM clause when dealing with more than 2 data sources. – HansUp Aug 27 '12 at 17:59
Ah! My bad, all the while I was thinking that this query is for MSSQL! Sorry @Magda for the confusion :( I tried to put back the parenthesis as much as i can (don't have a way to verify the syntax) – Vikdor Aug 27 '12 at 18:01

score 0 · Answer 2 · answered Aug 26 '12 at 17:04

When you create an aggregate query, every selected field must either be aggregated or grouped by.

Try:

SELECT 
    qb1.CompanyName, 
    qb1.AssetName, 
    qb1.Year, 
    (qb3.MPPOilRevised 
     - SUM(qb1.DatapointValue) 
     - SUM(qb2.DatapointValue * 1000000)) AS Result
FROM 
    PEBaseQuery AS qb1 
    INNER JOIN PEBaseQuery AS qb2 
    ON qb1.Year = qb2.Year AND qb1.Assetname=qb2.AssetName
    INNER JOIN PE_MPPOilRevised AS qb3 
    ON qb1.Year = qb3.Year AND qb1.Assetname=qb3.AssetName
WHERE 
    qb1.DatapointID in (2033, 2035, 2043, 2037, 2031) 
AND qb2.DatapointID=2003;
GROUP BY
    qb1.CompanyName, 
    qb1.AssetName, 
    qb1.Year

Referencing field values between queries

2 Answers2

Linked

Related