Filter, calculate and restructure data in Access SQL

Question

I have just began my trip to database languages and encountered the following problem. I have created a table that look like this

tbl1

Year    Account   AccValue
2014    Sales     1000
2014    Cost      -400
2014    Tax       -200
2015    Sales     1200
2015    Cost      -900

What I want to do is to create a query that calculates financial indicators like Gross Profit (Sales-Cost), Net profit (Sales-Cost-Tax), Gross Margin ([Sales-Cost]/Sales) year-wise, which means eventually I hope to access to a query like this:

Year    FAccount       FAccValue
2014    Gross profit   600
2014    Net profit     400
2014    Gross margin   60%
2015    Gross profit   300
2015    Net profit     300
2015    Gross margin   25%

I referred to some rudimentary SQL materials I have at hand, but they just covered how to filter and calculate based on data from different columns, such as how to calculate sales from given columns of price and quantity, so basically the calculation output has an equal number of rows as the input.

In this case, I need to select rows of data for calculation based on particular columns ("Year" and "Account"), and store results with specific columns.

It does occur to me that maybe I should store results in separate queries like "tbl_grossprofit", "tbl_grossmargin" etc. and create another query to summarize them, but I am not sure which one is "conventional" in this case.

Sorry if this is a basic question, help is greatly appreciated!

--EDIT--

I have been trying to figure how to use sub queries in Access but I cannot figure out a way to use them in this case. So a workaround I did is to create separate queries on sales and profit directly from the raw data with GROUP BY and WHERE like this:

qry_Sales

Year    Sales
2014    1000
2015    1200

qry_Profit

Year    Gross Profit
2014    400
2015    300

Then I created another query on gross profit margin by

SELECT qry_Profit.[Gross profit]/qry_Sales.[Sales] AS [Gross margin], qry_Profit.[Year]
FROM qry_Profit,qry_Sales
GROUP BY [Year]

This did end up with my desired outcome, but if I am to follow this idea, it would mean creating tens of queries. I am sure there is a better way. Could you give me some more advice?

Besides, honestly I wish to store the account names (such as "Gross profit", "Net profit") in rows (like shown in the original post) so I can display them easier in Excel. Thank you.

EDIT 2 I combined some great ideas here and this question and eventually came out with a workaround like

SELECT [Year], 
(SELECT Sum(AccValue) FROM tbl AS Cal WHERE Cal.[Year]=tbl.[Year] AND Account=Sales) AS Sales,
(SELECT Sum(AccValue) FROM tbl AS Cal WHERE Cal.[Year]=tbl.[Year] AND Account=Cost) AS Cost,
Sales-Cost AS [Gross profit]
[Gross profit]/Sales AS [Gross Margin]
FROM tbl1
GROUP BY [Year]
--The Sum function is unnecessary here, but in the real case Sales data is divided into many rows.

I haven't finished the entire query, but it seems that I have made one big step forward.

SchmitzIT · Accepted Answer · 2017-07-04T10:17:50.760

0

This will hopefully get you started.

First create the example table and fill it with your data:

CREATE Table tbl1 (Year int, account nvarchar(25), AccValue int)

INSERT INTO tbl1 
VALUES (2014, 'Sales', 1000), (2014, 'Cost', -400), (2014, 'Tax', -200), (2015, 'Sales', 1200), (2015, 'Cost', -900)
;

Then, define CTEs to grab Cost, Tax and Sales per year. We join the three CTEs on Year, and can then perform calculations on them. I use a LEFT JOIN to take into account Tax can be unknown (as in 2015).

WITH Sales AS (
        SELECT [Year], AccValue FROM tbl1 WHERE Account = 'Sales'
    ), Cost AS (
        SELECT [Year], AccValue FROM tbl1 WHERE Account = 'Cost'
    ), Tax AS (
        SELECT [Year], AccValue FROM tbl1 WHERE Account = 'Tax'
    )
    SELECT 
        a.[Year] 
        , SUM(a.AccValue + b.accValue) AS GrossProfit
        , SUM(a.AccValue + b.accValue - ISNULL(c.AccValue, 0)) AS GrossProfit
    FROM 

        Sales a
        LEFT JOIN Cost b ON a.[Year] = b.[Year]
        LEFT JOIN Tax c ON a.[Year] = c.[Year]
    GROUP BY a.[Year]

Output:

Year    GrossProfit GrossProfit
2014    600         800
2015    300         300

For Access, instead of using CTEs, you should be able to store the individual sub-queries as queries in Access, and then build the final query that way.

edited Jul 04 '17 at 10:17

answered Jul 04 '17 at 08:49

SchmitzIT

9,227
9
65
92

Nice answer but I have the impression that you missed the *MS access* tag. This solution looks like SQL Server. For instance Access doesnt support WITH (subquery factoring) and the CREATE table statement is invalid in MS Access for many reasons – Thomas G Jul 04 '17 at 09:03
Ah, so I did miss that. The `CREATE TABLE` part was mostly for creating my own dataset. As for the CTE bit, I imagine it would be fairly straightforward to instead save the individual statements as queries (if that is the correct name. It's been ages since I used Access). I will edit my answer to reflect the individual queries. – SchmitzIT Jul 04 '17 at 09:26
Thank you for your answer. It seems that `WITH` is not supported by Access, I tried a workaround using explicit sub queries, but I am almost sure I am not doing it in the correct way. Please kindly see my edited question. – Aster37a Jul 05 '17 at 07:44
@Aster37a I don't really see a way to go around having to create queries. One easier way would be to not store them, but just code them into the `select` you do (ie. `SELECT – SchmitzIT Jul 05 '17 at 10:56
@SchmitzIT Thanks for the advice. Based on your idea and another question on calculating rows, I have tried another workaround and posted it above. I am not certain that this will eventually work, but it seems to be a good step ahead! – Aster37a Jul 06 '17 at 07:58

Filter, calculate and restructure data in Access SQL

1 Answers1