Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access / Microsoft Access

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Microsoft Access [игнор отключен] [закрыт для гостей] / Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

25 сообщений из 97, страница 1 из 4

все

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39161430

Ainur.sm

Гость

1. «Поступившие заявки». Количество всех записей с разбивкой по заданным периодам (например, по месяцам).

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.

SELECT 'Поступившие заявки' AS OPERATION, count(qdfDeals.DealStage * (Month(DateClosed) = 1)) AS Январь, 
count(qdfDeals.DealStage * (Month(DateClosed) = 2 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Февраль, 
count(qdfDeals.DealStage * (Month(DateClosed) = 3 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Март, 
count(qdfDeals.DealStage * (Month(DateClosed) = 4 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Апрель, 
count(qdfDeals.DealStage * (Month(DateClosed) = 5 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Май, 
count(qdfDeals.DealStage * (Month(DateClosed) = 6 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Июнь, 
count(qdfDeals.DealStage * (Month(DateClosed) = 7 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Июль, 
count(qdfDeals.DealStage * (Month(DateClosed) = 8 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Август, 
count(qdfDeals.DealStage * (Month(DateClosed) = 9 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Сентябрь, 
count(qdfDeals.DealStage * (Month(DateClosed) = 10 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Октябрь, 
count(qdfDeals.DealStage * (Month(DateClosed) = 11 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Ноябрь, 
count(qdfDeals.DealStage * (Month(DateClosed) = 12 AND (qdfDeals.DealStage='Реализация' or qdfDeals.DealStage='Отказ'))) AS Декабрь, 
Январь+ Февраль + Март + Апрель + Май + Июнь + Июль + Август + Сентябрь + Октябрь + Ноябрь + Декабрь AS Итого
FROM qdfDeals
WHERE Year(qdfDeals.DateClosed)=2015

2. «Реализация». Количество записей со значением поля «Стадия проекта» равным «Реализация*». Процент – как отношение количества реализованных заявок к общему количеству поступивших заявок (значение стр.2/ значение стр.1 * 100%).
3. «Отказ». Количество записей со значением поля «Стадия проекта» равным «Отказ». Процент – как отношение количества отказных заявок к общему количеству поступивших заявок (значение стр.3/ значение стр.1 * 100%).

...

Рейтинг:

0 / 0

02.02.2016, 19:15

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39161605

Rivkin Dmitry

Участник

Откуда: Israel

Сообщения: 5 521

Рейтинг: 0 / 0

Ainur.sm,

Копай в сторону перекрестных запросов (crosstab), но по дороге посмотри суммирующие запросы (group by)

...

Рейтинг:

0 / 0

03.02.2016, 09:02

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39162090

__Michelle

Участник

Сообщения: 3 247

Рейтинг: 0 / 0

Код: sql

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.

SELECT 'Поступившие заявки' AS OPERATION, Month(DateClosed) AS M, 
-sum(DealStage * (M = 1)) AS Январь, 
-sum(DealStage * (M = 2)) AS Февраль, 
-sum(DealStage * (M = 3)) AS Март, 
-sum(DealStage * (M = 4)) AS Апрель, 
-sum(DealStage * (M = 5)) AS Май, 
-sum(DealStage * (M = 6)) AS Июнь, 
-sum(DealStage * (M = 7)) AS Июль, 
-sum(DealStage * (M = 8)) AS Август, 
-sum(DealStage * (M = 9)) AS Сентябрь, 
-sum(DealStage * (M = 10)) AS Октябрь, 
-sum(DealStage * (M = 11)) AS Ноябрь, 
-sum(DealStage * (M = 12)) AS Декабрь, 
sum(DealStage) AS Итого,
-sum(DealStage * (M = 1 AND DealStage='Отказ')) AS Январь_Отказ, 
-sum(DealStage * (M = 2 AND DealStage='Отказ')) AS Февраль_Отказ, 
-sum(DealStage * (M = 3 AND DealStage='Отказ')) AS Март_Отказ, 
-sum(DealStage * (M = 4 AND DealStage='Отказ')) AS Апрель_Отказ, 
-sum(DealStage * (M = 5 AND DealStage='Отказ')) AS Май_Отказ, 
-sum(DealStage * (M = 6 AND DealStage='Отказ')) AS Июнь_Отказ, 
-sum(DealStage * (M = 7 AND DealStage='Отказ')) AS Июль_Отказ, 
-sum(DealStage * (M = 8 AND DealStage='Отказ')) AS Август_Отказ, 
-sum(DealStage * (M = 9 AND DealStage='Отказ')) AS Сентябрь_Отказ, 
-sum(DealStage * (M = 10 AND DealStage='Отказ')) AS Октябрь_Отказ, 
-sum(DealStage * (M = 11 AND DealStage='Отказ')) AS Ноябрь_Отказ, 
-sum(DealStage * (M = 12 AND DealStage='Отказ')) AS Декабрь_Отказ, 
sum(DealStage) AS Итого_Отказ,
Январь - Январь_Отказ AS Январь_Реализация, Февраль - Февраль_Отказ AS Февраль_Реализация,
Март - Март_Отказ AS Март_Реализация, Апрель - Апрель_Отказ AS Апрель_Реализация,
Май - Май_Отказ AS Май_Реализация, Июнь - Июнь_Отказ AS Июнь_Реализация,
Июль - Июль_Отказ AS Июль_Реализация, Август - Август_Отказ AS Август_Реализация,
Сентябрь - Сентябрь_Отказ AS Сентябрь_Реализация, Октябрь - Октябрь_Отказ AS Октябрь_Реализация,
Ноябрь - Ноябрь_Отказ AS Ноябрь_Реализация, Декабрь - Декабрь_Отказ AS Декабрь_Реализация,
Итого - Итого_Отказ AS Итого_Реализация
FROM qdfDeals
WHERE Year(DateClosed)=2015 AND (DealStage='Реализация' OR DealStage='Отказ')

При этом фрагмент, выделенный желтым, не является обязательным.
Результаты запроса идут в одну строку.
Размещение этих результатов в виде таблички легко выполняется в отчете или форме.
Эти значения всегда можно будет получить в отчете (или форме) из предыдущих данных запроса.
Если же хотите видеть табличку при открытии запроса,
но не хотите (или не можете) сделать перекрестный запрос,
можно разбить запрос на части (добавив вспомогательные поля для совпадения числа полей), объединив их затем через UNION ALL.

...

Рейтинг:

0 / 0

03.02.2016, 15:26

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39162094

__Michelle

Участник

Сообщения: 3 247

Рейтинг: 0 / 0

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

...

Рейтинг:

0 / 0

03.02.2016, 15:29

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39162172

ПЕНСИОНЕРКА

Участник

Откуда: Владимирская обл

Сообщения: 4 809

Рейтинг: 0 / 0

Ainur.sm,

возникли вопросы
--это производственная задача или студенческая
--вы десяток раз нарисовали отчетную форму, но ни слова о структуре исходной и порядке записей

я подобные отчеты делаю в НТМ-формате
каждая строка отдельным запросом(обычно генерируется в коде)

...

Рейтинг:

0 / 0

03.02.2016, 16:12

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39162179

непоймучка

Гость

Ainur.sm,

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

...

Рейтинг:

0 / 0

03.02.2016, 16:16

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164099

Ainur.sm

Гость

непоймучка,

Спасибо. А как добавить год ? чтоб он и по годам выводил:?

...

Рейтинг:

0 / 0

05.02.2016, 15:34

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164230

непоймучка

Гость

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

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

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

...

Рейтинг:

0 / 0

05.02.2016, 16:44

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164312

Ainur.sm

Гость

непоймучка,
Сделала как у вас. Создала SRC и в запрос вставила коды. вышла ошибка

...

Рейтинг:

0 / 0

05.02.2016, 17:34

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164315

Ainur.sm

Гость

Ainur.sm,

...

Рейтинг:

0 / 0

05.02.2016, 17:35

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164337

Ainur.sm

Гость

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

...

Рейтинг:

0 / 0

05.02.2016, 17:52

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164339

непоймучка

Гость

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

...

Рейтинг:

0 / 0

05.02.2016, 17:54

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164349

непоймучка

Гость

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

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

...

Рейтинг:

0 / 0

05.02.2016, 18:05

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164359

непоймучка

Гость

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

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

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

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

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

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

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

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

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

...

Рейтинг:

0 / 0

05.02.2016, 18:15

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164369

непоймучка

Гость

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

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

Код: vbnet

Year(DateClosed) AS Y

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

...

Рейтинг:

0 / 0

05.02.2016, 18:22

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164374

непоймучка

Гость

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

...

Рейтинг:

0 / 0

05.02.2016, 18:26

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164387

непоймучка

Гость

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

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

тогда

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

Код: vbnet

Format(DateClosed,"mmmm") AS M

на

Код: vbnet

Format(DateClosed,"yyyy") AS M

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

Код: vbnet

1.
2.
3.
4.
5.

PIVOT M IN (
'Январь','Январь%',
...
'ИТОГО','ИТОГО%'
)

на

Код: vbnet

PIVOT M

...

Рейтинг:

0 / 0

05.02.2016, 18:34

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164393

Ainur.sm

Гость

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

кроме "Реализация" и "Отказа" мне надо добавить
Причины. перечислены в скриншоте. И тоже самое посчитать кол-во. и в %. кол-во причины "Неустроило время ответ" разделить на кол-во "поступившие заявки" * 100%

...

Рейтинг:

0 / 0

05.02.2016, 18:38

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164400

Ainur.sm

Гость

Ainur.sm,

Причины перечислены в столбце RefuseReasons. Это отдельный столбец.

...

Рейтинг:

0 / 0

05.02.2016, 18:46

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164412

Ainur.sm

Гость

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

кроме "Реализация" и "Отказа" мне надо добавить
Причины. перечислены в скриншоте. И тоже самое посчитать кол-во. и в %. кол-во причины "Отказ" разделить на кол-во "поступившие заявки" * 100%

...

Рейтинг:

0 / 0

05.02.2016, 18:57

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164416

Ainur.sm

Гость

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

кроме "Реализация" и "Отказа" мне надо добавить
Причины. перечислены в скриншоте. И тоже самое посчитать кол-во. и в %. кол-во причины "Неустроило время ответа" разделить на кол-во "поступившие заявки" * 100%

ОШИБКА

кроме "Реализация" и "Отказа" мне надо добавить
Причины. перечислены в скриншоте. И тоже самое посчитать кол-во. и в %. кол-во причины "Неустроило время ответа" разделить на кол-во "ОТКАЗ" * 100%[/quot][/quot]

...

Рейтинг:

0 / 0

05.02.2016, 18:58

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164426

непоймучка

Гость

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...)

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

...

Рейтинг:

0 / 0

05.02.2016, 19:07

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164427

__Michelle

Участник

Сообщения: 3 247

Рейтинг: 0 / 0

Бонус.
"Не устроило" пишется раздельно.
Ведь эти пункты клиенту демонстрируются?)))

...

Рейтинг:

0 / 0

05.02.2016, 19:08

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164433

Ainur.sm

Гость

непоймучка,

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

...

Рейтинг:

0 / 0

05.02.2016, 19:15

| Ответить | Цитировать | Написать

Как использовать Count "с условиями" с разбивкой по заданным периода(по месяцам) Access

#39164440

непоймучка

Гость

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

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

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

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

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

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

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

...

Рейтинг:

0 / 0

05.02.2016, 19:26

| Ответить | Цитировать | Написать

25 сообщений из 97, страница 1 из 4

все

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=45&msg=39164359&tid=1613813]:	0ms
get settings:	8ms
get forum list:	14ms
check forum access:	3ms
check topic access:	3ms
track hit:	41ms
get topic data:	10ms
get forum data:	3ms
get page messages:	59ms
get tp. blocked users:	1ms
others:	341ms

total:	483ms