Access Разница итогов строки "Выручка" и строки "Себестоимость" по месяцам

Ainur.sm
Дата: 28.01.2016 12:49:47
Добрый день!
Как сделать Разницу итогов строки "Выручка" и строки "Себестоимость" по месяцам
результат см.скриншот

код
SELECT 1 AS ID, 'Выручка' AS Operation,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=1)) AS Jun,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=2)) AS Feb,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=3)) AS March,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=4)) AS Apr,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=5)) AS May,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=6)) AS June,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=7)) AS Jul,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=8)) AS [Avg],
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=9)) AS Sept,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=10)) AS Oct,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=11)) AS Nov,
        -Sum(qdfPayments.IncomeAmount*(Month(PaymentDate)=12)) AS [Dec]
FROM qdfPayments
WHERE (((Year([PaymentDate]))=2015) AND ((qdfPayments.ManagerPayCalc)='Закрыт') AND ((qdfPayments.TransactionType)='Поступления'))
UNION
SELECT 2 AS ID, 'Себестоимость' AS Operation, -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=1)) As Jun,
        - Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=2)) As Feb,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=3)) As March,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=4)) As Apr,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=5)) As May, 
        -Sum(qdfPayments.PaymentAmountt*(Month(PaymentDate)=6)) As June,
        -Sum(qdfPayments.PaymentAmountt*(Month(PaymentDate)=7)) As Jul, 
        -Sum(qdfPayments.PaymentAmountt*(Month(PaymentDate)=8)) As Avg, 
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=9)) As Sept,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=10)) As Oct,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=11)) As Nov, 
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=12)) As Dec
FROM qdfPayments
WHERE Year(PaymentDate) = 2015  AND
 qdfPayments.ManagerPayCalc = 'Закрыт' AND qdfPayments.TransactionType = 'Затраты по заявке'
UNION
SELECT 3 AS ID, 'Расходы прочие' AS Operation, -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=1)) As Jun,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=2)) As Feb,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=3)) As March,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=4)) As Apr,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=5)) As May, 
        -Sum(qdfPayments.PaymentAmountt*(Month(PaymentDate)=6)) As June,
        -Sum(qdfPayments.PaymentAmountt*(Month(PaymentDate)=7)) As Jul, 
        -Sum(qdfPayments.PaymentAmountt*(Month(PaymentDate)=8)) As Avg, 
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=9)) As Sept,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=10)) As Oct,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=11)) As Nov, 
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=12)) As Dec
FROM qdfPayments
WHERE Year(PaymentDate) = 2015  AND  qdfPayments.TransactionType = 'Расходы прочие'

UNION
SELECT 4 AS ID, 'Распределенная прибыль' AS Operation, -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=1)) As Jun,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=2)) As Feb,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=3)) As March,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=4)) As Apr,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=5)) As May, 
        -Sum(qdfPayments.PaymentAmountt*(Month(PaymentDate)=6)) As June,
        -Sum(qdfPayments.PaymentAmountt*(Month(PaymentDate)=7)) As Jul, 
        -Sum(qdfPayments.PaymentAmountt*(Month(PaymentDate)=8)) As Avg, 
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=9)) As Sept,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=10)) As Oct,
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=11)) As Nov, 
        -Sum(qdfPayments.PaymentAmount*(Month(PaymentDate)=12)) As Dec
 FROM qdfPayments
WHERE Year(PaymentDate) = 2015  AND  qdfPayments.TransactionType = 'Прибыль,  дивиденды'


Модератор: Как мне оформить свое сообщение?
tanglir
Дата: 28.01.2016 13:11:17
Ainur.sm
Как сделать Разницу итогов строки "Выручка" и строки "Себестоимость" по месяцам
результат см.скриншот
Что-то я на скриншоте не вижу строки/столбца "разница итогов". Ни по названиям, ни по цифрам.
Ainur.sm
Дата: 28.01.2016 13:15:05
tanglir,

Надо создать, чтоб вычитал разницу Выручки и Себестоимость
tanglir
Дата: 28.01.2016 13:21:59
Ainur.sm,

ну так оберните весь этот код в подзапрос, а из него выберите что надо... что именно у вас не получается?
Ainur.sm
Дата: 28.01.2016 13:31:33
tanglir,
Всё. я не имею представления как сделать разницу строки "Выручка" и "Себестоимость" по месяцам.
январь февраль
Маржа "Выручка" - "Себестоимость" "Выручка" - "Себестоимость"

Модератор: Тема перенесена из форума "Обсуждение нашего сайта".