CODEWITHSUNDEEP
sqlms-access
J.H
J.H

Reputation: 181

How do I divide the sum of one column by the sum of another column when they are from different tables in Access?

I want to grab the sum of two identically named columns, except they are from two different tables (I have been told to combine them multiple times, unfortunately I do not have a choice in the matter...). I want to then divide these two summed values to find the percentage variation.

Below is the code I wrote, all it does is load infinitely. 

SELECT SUM(C.[Market Value]) AS CSUM, 
       SUM(P.[Market Value]) AS PSUM, 
       CSUM/PSUM AS Percentage_Variation
FROM JanReport AS P,
     FebReport AS C;

I have been unsuccessful in using UNION and JOIN. It seems like this function should be easily doable, as I have been able to sum and find % variation by asset class, I can even get the sum of both columns to show with the Totals function. However I just cannot seem to get them to divide.

Code that worked:

SELECT  SUM(FebReport.[Market Value]) AS Curr_Total_MV,
(
SELECT SUM(JanReport.[Market Value]) FROM JanReport
) AS Prior_Total_MV,
SUM(FebReport.[Market Value]) / 
(
SELECT SUM([Market Value]) FROM JanReport
) AS Percentage_Variation_In_Total_MV,
IIf(
0.9<Percentage_Variation_In_Total_MV AND 
Percentage_Variation_In_Total_MV<1.1,'Pass','Fail') AS Result
FROM FebReport;

Upvotes: 0

Views: 4153

Answers (1)

Y.B.
Y.B.

Reputation: 3586

Inline SELECT should work here:

SELECT SUM([Market Value]) / (SELECT SUM([Market Value]) FROM JanReport) AS Percentage_Variation, 
FROM FebReport;

Update: Two sub-queries each returning just one row can be cross-joined to provide the underlying values:

SELECT
    [Current Market Value],
    [Previous Market Value],
    [Current Market Value] / [Previous Market Value] As Percentage_Variation
FROM
    (SELECT SUM([Market Value]) As [Current Market Value]  FROM FebReport) AS C,
    (SELECT SUM([Market Value]) As [Previous Market Value] FROM JanReport) AS P;

Upvotes: 2

Related Questions