Ainur.sm,

посмотрите запрос во вложении
на нем можно делать отчетную форму с вашей картинки

Ainur.smнепоймучка,

Спасибо. А как добавить год ? чтоб он и по годам выводил:?смотря куда добавить
1) чтобы каждый столбец месяца имел в названии еще и год
2) чтобы число столбцов месяцев оставалось неизменным + отдельный столбец ГОД, и для каждого года - по три строки с разными OPERATION

для каждого варианта есть свои нюансы

Ainur.smнепоймучка,
Сделала как у вас. Создала SRC и в запрос вставила коды. вышла ошибкау меня нет под рукой MSAccess, покажите SQL обоих запросов (только не картинкой а прямо SQL-текст вставьте сюда)

Ainur.smнепоймучка,
Сделала как у вас. Создала SRC и в запрос вставила коды. вышла ошибкатак. уже вижу, по крайней мере это насчет того, что ваш qdfActions спрашивает DateClosed - у вас там в первом union select'е откуда-то взялось DateClosed - не должно его там быть, видимо вы по ошибке его туда вписали (вместо M)

в Итоговом запросе исходного поля даты нет, он уже работает только с месяцами - которые делаются из этой даты в промежуточном запросе SRC -он для того и сделан, чтобы в каждом union сто раз Format(...) не повторять.

Ainur.smнепоймучка,Чтоб сгруппировать по годам.

вот смотрите,

непоймучка1) чтобы каждый столбец месяца имел в названии еще и год
2) чтобы число столбцов месяцев оставалось неизменным + отдельный столбец ГОД, и для каждого года - по три строки с разными OPERATION

В варианте(1) придется избавиться от явного перечисления имен столбцов в разделе PIVOT, где сейчас просто явно заданы имена месяцев в нужном порядке, т.к. число и имена месяцев постоянны - потому их и можно там перечислить. А вот если в именах столбцов будет присутствовать год, то заранее "предугадать" какие года есть в базе и перечислить их в PIVOT в общем случае нельзя (не прибегая к предварительному программному анализу)

В свою очередь, после отказа от явного перечисления имен столбцов в PIVOT, мы получим:

a) столбцы будут сформированы в зависимости от фактического наличия данных
(т.е. например, если данных за январь нет - не будет январского столбца вообще)

b) столбцы будут следовать в алфавитном порядке (т.е. не январь-декабрь, как сейчас, а Август-Январь)
и поэтому, чтобы получить привычный порядок столбцов-месяцев, их придется именовать не по именам, а по порядковым номерам

однако, самая большая засада в том, что в варианте(1) "заложено" ограничение, которое не даст вам обрабатывать записи более, чем за определенное количество лет. в MSAccess есть ограничение на число столбцов в таблице/запросе (255), а у вас каждый новый год добавляет в результат 24 столбца - поэтому, если не сделать в запросе доп.условие для отбора по годам, то лет так через десять ведения такой базы, ваш запрос просто перестанет работать.

поэтому вариант(2) предпочтительнее, имхо

ну и собственно, чтобы реализовать этот вариант(2), можно сделать так:


1) в запрос SRC в раздел SELECT добавить выражение вида


2) а в Итоговом запросе дописать в КАЖДОМ UNION-подзапросе в разделах SELECT и GROUP BY этот самый Y - везде первым полем по-порядку (в тех union-подзапросах, где GROUP BY вообще не было - дописать 'GROUP BY Y')

и в итоге получите примерно такое
ГОДOPERATIONянвянв%февфев%...ИТОГОИТОГО%2015Поступившие заявки2010040100...100001002015Реализация10502050...5000502015Отказ10502050...5000502014Поступившие заявки21004100...10001002014Реализация150250...500502014Отказ150250...50050

вдогонку - ну и само-собой, УБРАТЬ из запроса SRC условие для отбора по году WHERE Year(DateClosed) = 2015

если же вы, говоря "Чтоб сгруппировать по годам"
имеете ввиду такой итоговый результат:

OPERATION20142014%20152015%ИТОГОИТОГО%Поступившие заявки201004010060100Реализация105020503050Отказ105020503050

тогда

1) замените в запросе SRC

на

2) в Итоговом запросе замените

на

Ainur.smнепоймучка,
все получилось по годам.

кроме "Реализация" и "Отказа" мне надо добавить
Причины. перечислены в скриншоте. И тоже самое посчитать кол-во. и в %.
так если есть такая таблица-справочник Причин, то по идее, и данные у вас в базе должны быть нормализованы,
т.е. в основной таблице (там где поле DateClosed) поле причины (по сути DealStage) должно быть представлено НЕ текстом ('Реализация' и 'Отказ'), а ЧИСЛОМ - значением ключевого поля этого самого справочника ''Причин' (а если ничего такого нет - это стоило бы сделать)

наличие такой таблицы-справочника - это более правильно,
нежели всякие извращения типа IIF(DealStage='Реализация',2,3) AS ord
которые пришлось писать в запросе SRC

вместо этого в запросе SRC следует сделать INNER JOIN основной таблицы и этого справочника причин
и использовать, например его Код AS ord (чтобы в итоговом запросе строки OPERATION следовали в том же порядке как в этом справочнике -
именно для этого в запросе SRC и 'высасывается из пальца' это поле ord)

кроме того, если требуется в итоговый запрос вывести НЕ все причины, а только какие-то избранные,
то в условии WHERE DealStage IN ('Реализация','Отказ') в запросе SRC лучше использовать
коды этих избранных причин WHERE DealStage_ID IN (2,3,4,7,8...)

В смысле удобства оператора такой список кодов Причин можно получить в где-то форме, например из списка с мультивыбором,
основанном на этом справочнике причин

Ainur.smнепоймучка,

и как сделать? может кроме SRC создать новый запрос? Отдельно для причин ?так, стоп. я, видать, недопонял

то есть, Реализация и Отказ - это отдельная песня (в поле DealStage) - и с ней мы разобрались,
но КРОМЕ того, у вас есть ЕЩЕ одно поле (RefuseReasons), где пишутся причины отказа, так?

1) какого типа это поле?

2) показанная вами картинка со списком причин - еэто вы просто так, или у вас есть ОТДЕЛЬНАЯ таблица со списокм причин (типа справочник причин)

3) вы пишете "кроме Реализация и Отказа мне надо добавить" - добавить в ЭТОТ же запрос?
в какое место, в каком порядке?

можете как-то образец желаемого итогового результата показать для примера?

Ainur.sm,

если все так, то да - можно и так.
т.е. создаете еще один запрос (например REF) по аналогии с SRC - только для поля RefuseReasons

причем, чтобы меньше потом менять в итоговом запросе, лучше сделать там этому полю выходной алиас
с тем же именем, что и в запросе SRC (DealStage) вот в таком духе:

потом попробуйте (для начала) сделать ЕЩЕ один запрос,
скопируйте в него целиком текст запроса 'Итоговый'
замените там SRC на REF и посмотрите на результат (только суммы, проценты позже)

Ainur.smAinur.smпропущено...

кол-во причины "Неустроило время ответа" разделить на кол-во "ОТКАЗ" * 100%это можно сделать по аналогии с тем как, сейчас считается % к ОБЩЕМУ числу записей в Итоговом запросе
посмотрите - там в одном из UNION-подзапросов есть inner join на вложенный под(select), который считает это общее число записей, типа


и это полученное число потом идет как знаменатель в расчете % текушего показателя - Count(1)/Min(S.N)*100

А для случая запроса с причинами вам надо в Итоговом запросе
в аналогичном под-select'е посчитать НЕ общее число записей, а только отказы - т.е. добавить условие вида


и кстати, в ЭТОМ под-select'е надо выбирать именно FROM SRC (а не FROM REF), ибо отказы у вас - в SRC

Ainur.smAinur.smнепоймучка,

Создала REF для RefuseReasons.


SELECT RefuseReasons, Format(DateClosed,"mmmm") AS M, Format(DateClosed,"yyyy") AS Y
FROM qdfDeals
WHERE Year(DateClosed)=2015;

а Куда это писать.

SELECT RefuseReasons AS DealStage,...

вот так



а вот тут:
Ainur.sm1. Существует поле (RefuseReasons), где перечислены причины отказа как на скриншоте. (Тип String) выбирается галочкой.
а что это за "галочки"? (начинаю подозревать плохое)

уточните - вот у вас есть текстовое поле RefuseReasons
в него что - можно вписать НЕСКОЛЬКО этих самых причин для ОДНОЙ записи?
путем расстановки этих самых 'галочек' напротив нужных причин - и они все запишутся как значение этого поля для текущей записи?

Ainur.smнепоймучкапропущено...
это можно сделать по аналогии с тем как, сейчас считается % к ОБЩЕМУ числу записей в Итоговом запросе
посмотрите - там в одном из UNION-подзапросов есть inner join на вложенный под(select), который считает это общее число записей, типа


и это полученное число потом идет как знаменатель в расчете % текушего показателя - Count(1)/Min(S.N)*100

А для случая запроса с причинами вам надо в Итоговом запросе
в аналогичном под-select'е посчитать НЕ общее число записей, а только отказы - т.е. добавить условие вида


и кстати, в ЭТОМ под-select'е надо выбирать именно FROM SRC (а не FROM REF), ибо отказы у вас - в SRC


Добавить так?

SELECT T.DealStage,T.M & "%",T.ord,Round(Count(1)/Min(S.N)*100)
FROM SRC AS T INNER JOIN (SELECT M,Count(1) AS N FROM SRC GROUP BY M) (SELECT M,Count(1) AS N FROM SRC WHERE DealStage = 'Отказ' GROUP BY M) S ON T.M=S.M
GROUP BY T.M,T.DealStage,T.ord
Добавить - в смысле просто where добавить к тому, что было и все

Ainur.smнепоймучкапропущено...

вот так



а вот тут:
пропущено...

а что это за "галочки"? (начинаю подозревать плохое)

уточните - вот у вас есть текстовое поле RefuseReasons
в него что - можно вписать НЕСКОЛЬКО этих самых причин для ОДНОЙ записи?
путем расстановки этих самых 'галочек' напротив нужных причин - и они все запишутся как значение этого поля для текущей записи?


Галочка для выбора из перечисленных причин.это я понимаю

суть вопроса такова:
для ОДНОЙ (текущей) записи в qdfDeals таким образом можно выбрать (галочкой)
1) только одну причину или
2) неколько причин - столько сколько оператор поставт галочек?

Ainur.smнепоймучкапропущено...

Добавить - в смысле просто where добавить к тому, что было и все







Вот что получилось

Итоговый запрос...

в этом Итоговом надо заменить SRC на REF - везде кроме под-select'ов

Ainur.smнепоймучкапропущено...
это я понимаю

суть вопроса такова:
для ОДНОЙ (текущей) записи в qdfDeals таким образом можно выбрать (галочкой)
1) только одну причину или
2) неколько причин - столько сколько оператор поставт галочек?

Программа позволяет выбрать несколько причин.
Если так, то увы - похоже, ваше поле RefuseReasons - это т.н. МНОГОЗНАЧНОЕ поле,
и в этом случае я никак не могу вам помочь - я с ними не знаком.

Ainur.smнепоймучка,

А если только одну причину??
Дело не в том, что вы (как юзер) станете заносить туда всегда строго только одну причину.
Дело в том, что данное поле в СТРУКТУРЕ таблицы сделано как МНОГОЗНАЧНОЕ.
А это - не просто текстовое поле, с ним стандартный человеческий SQL работать не умеет.

Многозначные поля - это такая специальная 'ересь', попирающая реляционную стройность баз данных,
которую Microsoft придумал в MSAccess 2007 - специально чтобы позлить всех нормальных людей,
и чтобы работать с этими полями в запросах, добавил в свой офисный SQL какие-то невнятные костыли,
на которых тут мало кому охота ходить.

Вобщем, сочувствую - если найдете помощника по этим полям - считайте повезло

Ainur.smнепоймучка,

ПЛЗ. Если учитывать только одну причину...... ((((ну ОК.
чтобы как-то закруглить топик, давайте оформим решение с учетом вашего условия, слегка усугубив его:
если "учитывать только одну причину", которая записывается в обычное (НЕмногозначное) текстовое поле RefuseReasons А в этом случае у вас все уже почти готово.
Вот последний вариант вашего SQL для 'причин' (запрос 'Итоговый2'), что вы тут привели:
Ainur.sm
TRANSFORM MIN(N)
SELECT ord, DealStage
FROM (

SELECT DealStage,M,ord,Count(1) AS N FROM SRC GROUP BY M,DealStage,ord
UNION ALL
SELECT T.DealStage,T.M & "%",T.ord,Round(Count(1)/Min(S.N)*100)
FROM REF AS T INNER JOIN (SELECT M,Count(1) AS N FROM SRC WHERE DealStage = 'Отказ' GROUP BY M) S ON T.M=S.M
GROUP BY T.M,T.DealStage,T.ord
UNION ALL
SELECT DealStage,'ИТОГО',ord,Count(1) FROM REF GROUP BY DealStage,ord
UNION ALL
SELECT T.DealStage,'ИТОГО%',T.ord,Round(Count(1)/Min(S.N)*100)
FROM REF T,(SELECT Count(1) AS N FROM SRC) S
GROUP BY T.DealStage,T.ord

UNION ALL
SELECT 'Поступившие заявки',M,1,Count(1) FROM REF GROUP BY M
UNION ALL
SELECT 'Поступившие заявки',M & '%',1,100 FROM REF GROUP BY M
UNION ALL
SELECT 'Поступившие заявки','ИТОГО',1,Count(1) FROM REF
UNION ALL
SELECT 'Поступившие заявки','ИТОГО%', 1, 100 FROM REF

) AS X
GROUP BY ord, DealStage
ORDER BY ord
PIVOT M IN (
'Январь','Январь%',
'Февраль','Февраль%',
'Март','Март%',
'Апрель','Апрель%',
'Май','Май%',
'Июнь','Июнь%',
'Июль','Июль%',
'Август','Август%',
'Сентябрь','Сентябрь%',
'Октябрь','Октябрь%',
'Ноябрь','Ноябрь%',
'Декабрь','Декабрь%',
'ИТОГО','ИТОГО%'
);Я выделил тут цветом разные его части.

Черным цветом написано TRANSFORM-обрамление (часть запроса которая занимается 'разворотом' данных по столбцам) -
эта часть на 100% совпадает с первым Итоговым запросом - с которым в итоге мы будем объединять этот запрос (Итоговый2) -
и в едином конечном запросе эта (черная) часть и будет присутствовать (однократно) точно в таком же виде.

Синий и красный- это UNION-запрос, который формирует исходную выборку для предстоящего разворота данных.
И эта часть должна быть ДОБАВЛЕНА в конец аналогичной UNION-части первого Итогового запроса.

При этом, то что написано тут красным - УЖЕ есть в первом Итоговом (по сути, это доп.суммарная строка для двух итоговых строк 'Отказ' и 'Реализация' и она НЕ имеет отношения к причинам), поэтому повторно эту (красную) часть в конечный запрос включать не надо.

Итак, у нас остается только синяя часть запроса, которая и выбирает нужные нам данные по исходным причинам - и только эту часть мы и должны будем вставить в конечный объединенный запрос.

продолжение

Итак, рассмотрим дорабатываемую часть запроса:
SQLSELECT DealStage,M,ord,Count(1) AS N FROM REF GROUP BY M,DealStage,ord
UNION ALL
SELECT T.DealStage,T.M & "%",T.ord,Round(Count(1)/Min(S.N)*100)
FROM REF AS T INNER JOIN (SELECT M,Count(1) AS N FROM SRC WHERE DealStage = 'Отказ' GROUP BY M) S ON T.M=S.M
GROUP BY T.M,T.DealStage,T.ord
UNION ALL
SELECT DealStage,'ИТОГО',ord,Count(1) FROM REF GROUP BY DealStage,ord
UNION ALL
SELECT T.DealStage,'ИТОГО%',T.ord,Round(Count(1)/Min(S.N)*100)
FROM REF T,(SELECT Count(1) AS N FROM SRC WHERE DealStage = 'Отказ' ) S
GROUP BY T.DealStage,T.ord
тут я дополнительно выделил зеленым select-подзапросы, которые считают общее число записей для вычисления процентов, плюс жирным выделил во втором под-select'е условие (ранее нами упущенное) аналогичное первому.
*** красным исправил ошибку в вашем варианте

в этой части итогового запроса все union-select'ы обращаются к (под)запросу REF, который по вашей последней версии таков:
REFSELECT RefuseReasons AS DealStage, Format(DateClosed,"mmmm") AS M, Count(*) AS ord
FROM qdfDeals
WHERE Year(DateClosed)=2015
GROUP BY Format(DateClosed,"mmmm"), RefuseReasonsчто не совсем верно, т.к.
исходный запрос REF (как и запрос SRC) НЕ должен группировать данные.

Его задача (и сам смысл его создания) в другом:
1) выбрать записи из исходных таблиц (откуда надо) с нужным критерием (Year(DateClosed)=2015)
2) преобразовать дату в название месяца (Format(DateClosed,"mmmm"))
3) как-то сформировать (условно) порядковый номер для строки в итоговом запросе (ord)

В запросе SRC этот ord делается выражением IIF(DealStage='Реализация',2,3) AS ord
для того чтобы после сортировки ORDER BY ord в итоговом запросе итоговые строки шли в указанном порядке:
'Реализация' - второй строкой, 'Отказ' - третьей строкой
'Поступившие заявки' будут первой строкой, т.к. в union-select'ах для них везде явно указан ord=1

В случае запроса REF мы могли бы задать этот ord для каждой причины аналогичным выражением с несколькими IIF -
если важен какой-то конкретный порядок причин в итоговом запросе. Если же итоговый порядок причин НЕ важен - главное, чтобы они все оказались там ПОСЛЕ строки 'Отказ' - тогда можно им всем задать одинаковый ord=4 в этом запросе

Ну и наконец, поскольку основная задача данного запроса REF - выбрать записи с причинами отказа , т.е., если я правильно понимаю логику вашей задачи, у всех этих записей в поле DealStage должно быть записано "Отказ" - и значит мы должны добавить соответствующее условие в WHERE этого запроса.

Учитывая все вышесказанное, запрос REF должен выглядеть вот так:
REFSELECT RefuseReasons AS DealStage, Format(DateClosed,"mmmm") AS M, 4 AS ord
FROM qdfDeals
WHERE Year(DateClosed)=2015 AND DealStage = 'Отказ'

Учитывая, что теперь мы включили критерий DealStage = 'Отказ' в запрос REF, в верхнем SQL зеленые подзапросы можно также сделать на основе REF (а не на SRC с этим условием, как ранее), т.е. вот так:
SQLSELECT DealStage,M,ord,Count(1) AS N FROM REF GROUP BY M,DealStage,ord
UNION ALL
SELECT T.DealStage,T.M & "%",T.ord,Round(Count(1)/Min(S.N)*100)
FROM REF AS T INNER JOIN (SELECT M,Count(1) AS N FROM REF GROUP BY M) S ON T.M=S.M
GROUP BY T.M,T.DealStage,T.ord
UNION ALL
SELECT DealStage,'ИТОГО',ord,Count(1) FROM REF GROUP BY DealStage,ord
UNION ALL
SELECT T.DealStage,'ИТОГО%',T.ord,Round(Count(1)/Min(S.N)*100)
FROM REF T,(SELECT Count(1) AS N FROM REF ) S
GROUP BY T.DealStage,T.ord
Вот именно в таком виде данная часть запроса для ПРИЧИН и должна быть ДОБАВЛЕНА в (первый) Итоговый запрос

завершение

Итак, конечный текст запроса:
запрос(0) ИтоговыйTRANSFORM MIN(N)
SELECT ord, DealStage
FROM
(
SELECT DealStage,M,ord,Count(1) AS N FROM SRC GROUP BY M,DealStage,ord
UNION ALL
SELECT T.DealStage,T.M & "%",T.ord,Round(Count(1)/Min(S.N)*100)
FROM SRC AS T INNER JOIN (SELECT M,Count(1) AS N FROM SRC GROUP BY M) S ON T.M=S.M
GROUP BY T.M,T.DealStage,T.ord
UNION ALL
SELECT DealStage,'ИТОГО',ord,Count(1) FROM SRC GROUP BY DealStage,ord
UNION ALL
SELECT T.DealStage,'ИТОГО%',T.ord,Round(Count(1)/Min(S.N)*100)
FROM SRC T,(SELECT Count(1) AS N FROM SRC) S
GROUP BY T.DealStage,T.ord

UNION ALL
SELECT 'Поступившие заявки',M,1,Count(1) FROM SRC GROUP BY M
UNION ALL
SELECT 'Поступившие заявки',M & '%',1,100 FROM SRC GROUP BY M
UNION ALL
SELECT 'Поступившие заявки','ИТОГО',1,Count(1) FROM SRC
UNION ALL
SELECT 'Поступившие заявки','ИТОГО%', 1, 100 FROM SRC

UNION ALL
SELECT DealStage,M,ord,Count(1) AS N FROM REF GROUP BY M,DealStage,ord
UNION ALL
SELECT T.DealStage,T.M & "%",T.ord,Round(Count(1)/Min(S.N)*100)
FROM REF AS T INNER JOIN (SELECT M,Count(1) AS N FROM REF GROUP BY M) S ON T.M=S.M
GROUP BY T.M,T.DealStage,T.ord
UNION ALL
SELECT DealStage,'ИТОГО',ord,Count(1) FROM REF GROUP BY DealStage,ord
UNION ALL
SELECT T.DealStage,'ИТОГО%',T.ord,Round(Count(1)/Min(S.N)*100)
FROM REF T,(SELECT Count(1) AS N FROM REF) S
GROUP BY T.DealStage,T.ord

) AS X

GROUP BY ord, DealStage
ORDER BY ord, DealStage
PIVOT M IN (
'Январь','Январь%',
'Февраль','Февраль%',
'Март','Март%',
'Апрель','Апрель%',
'Май','Май%',
'Июнь','Июнь%',
'Июль','Июль%',
'Август','Август%',
'Сентябрь','Сентябрь%',
'Октябрь','Октябрь%',
'Ноябрь','Ноябрь%',
'Декабрь','Декабрь%',
'ИТОГО','ИТОГО%'
)

Черная часть - первый Итоговый запрос (из той БД, что я выкладывал)
Синий кусок - новая добавка, которую весь вечер мучали, и которая должна дать вам в результате строки со статистикой Причин отказа.
***Красным - опционная добавка, чтобы строки с причинами отсортировать по алфавиту

И для полной отчетности допишу сюда же тексты двух исходных запросов:
запрос(1) SRCSELECT DealStage, Format(DateClosed,"mmmm") AS M, IIF(DealStage='Реализация',2,3) AS ord
FROM qdfDeals WHERE Year(DateClosed)=2015 AND DealStage IN ('Реализация','Отказ')запрос(2) REFSELECT RefuseReasons AS DealStage, Format(DateClosed,"mmmm") AS M, 4 AS ord
FROM qdfDeals WHERE Year(DateClosed)=2015 AND DealStage = 'Отказ'
И еще раз важное замечание по конечному решению:
в этих запросах предполагается, что поле RefuseReasons - обычное текстовое поле, в котором записана ОДНА причина отказа (т.е. это НЕ то многозначное поле RefuseReasons, которое у вас есть сейчас)

И пожелание:
Если у вас есть такая возможность, рекомендую отказаться от использования многозначных полей.
Множественность атрибутов для одной записи некоторой таблицы (как это происходит в вашем случае: несколько причин одного отказа) чудесно реализуется более человечьим способом при помощи одной дополнительной таблицы

P.S.
запросы писал умозрительно, в данный момент проверить не на чем,
поэтому неизбежны ошибки/очепятки, будьте готовы

Успехов.
---

Ainur.smнепоймучка, Спасибо,
А если учитывать, то что причины меняется, точнее если нет из вышеперечисленных то они вводят вручную ??
во-первых, вы никак не пояснили -что это за список причин, есть ли он в отдельной таблице и т.п.
во-вторых, поскольку вы сказали что поле RefuseReasons - текстовое, значит, в любом случае причины ваши записаны там словами.

Как я оговорил выше, все запросы написаны исходя из того, что RefuseReasons - это обычное текстовое поле, которое хранит причину отказа, записанную в текстовом виде. Поэтому нам неважно как это значение там оказалось - выбрали его из какого-то списка значений, или записали туда программно или забили руками от балды - в нашем запросе группировка идет по полному текстовому значению этого поля.

Так что если кто-то туда запишет свою уникальную причину, которой нет в вашем "виртуальном списке" причин, - она на общих основаниях займет в итоговом отчете отдельную строчку, да и все.