CODEWITHSUNDEEP
sqldatabasems-access
dedalus_rex
dedalus_rex

Reputation: 469

Consolidating 2 queries with an INNER JOIN and Get a calculated column from them

First off, I am working in Access 2007.

I have two queries that both aggregates by date.

The first one returns an aggregated daily weighted return

SELECT Records.RecDate, Sum([YTD]*[Weight]) AS UnadjReturn
FROM Records
WHERE (((Records.[Class1])="eq") AND ((Records.[Class2])="ce"))
GROUP BY Records.RecDate;

Result:

RecDate UnadjReturn
11/27/2012  0.060778036742704
12/11/2012  0.075592752895318
12/14/2012  7.47574641136453E-02

I also have another query that returns daily total class portfolio weight by summing the weight of all the assets in an asset class with respect to the entire portfolio:

SELECT Records.RecDate, Sum(Records.Weight) AS SumOfWeight
FROM Records
WHERE (((Records.[Class1])="eq") AND ((Records.[Class2])="ce"))
GROUP BY Records.RecDate;

which returns:

RecDate SumOfWeight
11/27/2012  0.479327081236988
12/11/2012  0.483075813390315
12/14/2012  0.482791727874428

Now I would like to write a consolidated query that multiplies each day's UnadjReturn by SumOfWeight. I have tried INNER JOIN the two and then multiply the two variables as follows,

SELECT joined.RecDate, joined.UnadjReturn * joined.SumOfWeight as AdjReturn
FROM joined
(
SELECT Records.RecDate, Sum([YTD]*[Weight]) AS UnadjReturn
FROM Records
WHERE (((Records.[Class1])="eq") AND ((Records.[Class2])="ce"))
GROUP BY Records.RecDate
INNER JOIN
(
SELECT Records.RecDate, Sum(Records.Weight) AS SumOfWeight
FROM Records
WHERE (((Records.[Class1])="eq") AND ((Records.[Class2])="ce"))
GROUP BY Records.RecDate;
) t2
ON Records.RecDate = t2.RecDate
AS joined
)

This is not looking good at all. Any suggestions? I could do it by querying the two queries with another query, but I would like to take care of it in just one.

Upvotes: 1

Views: 324

Answers (2)

John Spiegel
John Spiegel

Reputation: 1903

I don't know Access, but are you ultimately just looking for the product of the sums or is there some requirement (Access-based or class project or???) that you need to use subqueries.

Here's a SQL Server answer...

SELECT
    RecDate,
    (Sum([YTD]*[Weight]) * Sum(Records.Weight)) AS AdjReturn
FROM Records
        WHERE Records.[Class1]='eq' AND Records.[Class2]='ce'
        GROUP BY Records.RecDate

Upvotes: 0

HansUp
HansUp

Reputation: 97111

Use each of your 2 existing queries as subqueries and INNER JOIN them together. (Make sure to discard the semi-colon from the end of each SELECT statement first.)

SELECT
    sub1.RecDate,
    (sub1.UnadjReturn * sub2.SumOfWeight) AS AdjReturn
FROM
    (
        SELECT Records.RecDate, Sum([YTD]*[Weight]) AS UnadjReturn
        FROM Records
        WHERE Records.[Class1]="eq" AND Records.[Class2]="ce"
        GROUP BY Records.RecDate
    ) AS sub1
    INNER JOIN
    (
        SELECT Records.RecDate, Sum(Records.Weight) AS SumOfWeight
        FROM Records
        WHERE Records.[Class1]="eq" AND Records.[Class2]="ce"
        GROUP BY Records.RecDate
    ) AS sub2
    ON sub1.RecDate = sub2.RecDate;

Upvotes: 1

Related Questions