Green-sell.info

Новые технологии
8 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функция max в access

Функция max в access

Подскажите пожалуйста как пользоваться функцией MAX в MS Access?
Читаю справку и вижу:

Прототип выражения представляет собой строковое выражение определяющее поле с данными,

Что за «прототип выражения» ? Покажите пожалуйста на примере.


atruhin © ( 2006-05-28 15:18 ) [1]

Чего максимум хочешь найти, то и пишешь вместо выражение.
Т.е. имя поля, или поле1+поле2/100


Strate © ( 2006-05-28 18:50 ) [2]

Есть простая БД, поля такие:

«Название завода»
«Количество рабочих»
«Число выпускаемых изделий»
«Заработная плата».

Мне нужно сделать запрос, который бы оставлял только те записи, в которых «Число выпускемых изделий» максимально. (таких может быть несколько).

Max(«Число выпускаемых изделий»).

Пишет что «Невозможно использование статистической функции в предложении WHERE (Главная.[Число выпускаемых изделий]=Max(«Число выпускаемых изделий»)»


Desdechado © ( 2006-05-28 18:56 ) [3]

Max надо писать в SELECT (или во вложенном SELECT)


Strate © ( 2006-05-28 19:08 ) [4]

Написал в режиме SQL
Select Max(Главная.[Число выпускаемых изделий]), Access вылетел с ошибкой :»(

Подскажите пожалуйста что писать, а то в SQL я ламер полнейший, и учебника нету 🙁


Desdechado © ( 2006-05-28 19:28 ) [5]

учебник есть в акцессе
описание его секретных ошибок — тоже


atruhin © ( 2006-05-28 20:31 ) [6]

select . from .
where «Число выпускаемых изделий» = (select max(«Число выпускаемых изделий») from . )


Strate © ( 2006-05-28 20:56 ) [7]

Desdechado © (28.05.06 19:28) [5]
atruhin © (28.05.06 20:31) [6]

Лекция 6 Тема: Запросы. Вычисления и групповые операциию

6.1. Вычисляемые поля.

6.2. Создание выражений с помощью Построителя выражений.

6.3. Обзор встроенных функций СУБД MS Access.

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

6.5. Перекрестные запросы.

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

В QBE СУБД MS Access такие возможности предоставляются через вычисляемые поля и групповые операции.

6.1. Вычисляемые поля

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

Стоимость: Товары! Цена * Количество * (1-Скидка)

КоличествоМужчин: Sum(IIf(Пол = «м»; 1; 0))

Стоимость, КоличествоМужчин и ФИО являются именами вычисляемых полей и отображаются в режиме таблицы в заголовке столбца, символ двоеточие исполняет роль разделителя между именем вычисляемого поля и выражением.

Если имя таблицы или поля содержит пробелы, то его идентификатор обязан в выражении заключаться в квадратные скобки, например

Стоимость: Цена*[Количество товара]

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

Обновить вычисленные результаты вручную невозможно.

Для построения сложных выражений в СУБД MS Access входит утилита, называемая Построитель выражений.

6.2. Создание выражений с помощью Построителя выражений

Построитель выражений можно запустить щелкнув по клавише Построить на панели инструментов Конструктор запросов или выбрав в контекстном меню поля бланка запроса QBE команду меню Построить….

Рис.6.1. Диалоговое окно Построитель выражений

Рис.6.2. Диалоговое окно Построителя выражений
со сформированным выражением

6.3. Обзор встроенных функций СУБД MS Access

СУБД MS Access содержит более 100 встроенных функций (рис.6.3), которые можно использовать при формировании вычисляемого поля или при задании условия отбора.

Рис.6.3. Диалоговое окно Построителя выражений
со списком встроенных функций

Все встроенные функции в Построителе выражений сгруппированы по функциональному назначению.

При выборе функции в правом окне построителя, в нижней области окна Построителя выражений отображается синтаксис этой функции. Для более детальной справки необходимо щелкнуть по клавише Справка.

Обзор всех встроенных функций выходит за рамки лекции, поэтому рассмотрим наиболее используемые.

Таблица 6.1

Функции категории Дата/время

Возвращает значение дня месяца от 1 до 31

Возвращает значение месяца от 1 до 12

Возвращает название месяца соответствующего номеру месяца: 1 – январь, 2 – февраль, и т. д. Если значение аргумента флаг Истина, то функция возвращает аббревиатуру месяца: 1 – янв, 2 – фев и т. д.

Возвращает значение года от 100 до 9999

Если аргумент число не указан, возвращает значение дня недели от 1 (воскресенье) до 7 (суббота). Если аргумент число имеет значение 0, то возвращает значение дня недели от 1 (понедельник) до 7 (воскресенье)

Возвращает целое число от 0 од 23, представляющее значение часа

Возвращает числовое значение в зависимости от значения аргумента интервал:

«q» – квартал (от 1 до 4);

«m» – месяц (от 1 до 12);

«yyyy» – год (от 100 до 9999);

«ww» – неделя (от 1 до 53);

и т. п. (см. справку по функции).

Возвращает текущую системную дату

Функции категории Проверка

Возвращает значение Истина если значение переменной (значение поля в источнике данных) равно Null

Возвращает значение Истина если значение переменной (значение поля в источнике данных) имеет один из числовых типов данных

Функции категории Управление

IIf(условие; выр1; выр2)

Возвращает значение выражения выр1 если значение аргумента условие Истина и возвращает значение выражения выр2 если значение аргумента условие Ложь. Данная функция аналогична функции ЕСЛИ в MS Excel.

Читать еще:  Установлен ли офис на макбуке

Продолжение таблицы 6.1

Функции категории Текстовые

Возвращает n левых символов аргумента текст

Возвращает n правых символов аргумента текст

Mid(текст; нач_поз[; n])

Возвращает n символов начиная с позиции нач_поз аргумента текст. Если аргумент n не указан, то возвращает все символы до конца строки начиная с позиции нач_поз аргумента текст.

Возвращает количество символов (длину строки) в аргументе текст

Возвращает строковое значение аргумента текст без начальных пробелов

Возвращает строковое значение аргумента текст без заключительных пробелов

Возвращает строковое значение аргумента текст без начальных и заключительных пробелов

Возвращает строковое значение аргумента число

Возвращает значение аргумента переменная в формате заданным аргументом формат

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

При анализе данных очень часто интересуют не отдельные записи, а итоговые значения по группам данных, например:

— количество сделок с Партнерами за определенный промежуток времени;

— средний объем продаж по каждому месяцу за предыдущий год.

Ответы на такие вопросы дает итоговый запрос.

Для вычисления итоговых значений необходимо нажать кнопку Групповые операции на панели инструментов Конструктор запросов, чтобы в бланке QBE появилась строка Групповая операция (после имени таблицы).

По умолчанию для каждого поля, занесенного в бланк запроса, устанавливается значение Группировка (итоги не подводятся).

Для подведения итогов необходимо заменить установку Группировка на конкретную итоговую функцию. В СУБД MS Access предусмотрено 9 функций (табл. 6.2), обеспечивающих выполнение групповых операций.

Таблица 6.2

Функции категории Статистические

Возвращает сумму набора значений

Возвращает среднее арифметическое набора значений

Возвращает наименьшее значение из набора значений

Возвращает наибольшее значение из набора значений

Возвращает количество записей в наборе значений отличных от Null

Возвращает первое значение поля в группе

Возвращает последнее значение поля в группе

Возвращает среднеквадратичное отклонение набора значений

Возвращает дисперсию набора значений

В раскрывающемся списке строки Групповая операция имеется установка Выражение. Данная установка применяется, когда в выражении (строка Поле) используется несколько итоговых функций.

В раскрывающемся списке строки Групповая операция имеется установка Условие. Данная установка применяется, когда в строке Условие отбора записано условие выборки, но данные столбца (поля) не должны участвовать в групповой операции.

Для решения более сложных статистических задач в СУБД MS Access предусмотрен специальный тип запроса – перекрестный запросов.

6.4. Перекрестные запросы

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

Для построения перекрестного запроса достаточно трех полей базового источника. По повторяющимся значениям одного поля формируются названия заголовков строк итоговой (сводной) таблицы (рис.6.4). По повторяющимся значениям другого поля формируются названия заголовков столбцов итоговой (сводной) таблицы. Результаты статистической обработки по третьему полю отображаются в ячейках сводной таблицы (область значений). Пример перекрестного запроса в режиме конструктора представлен на рис.6.5, а результаты выполнения запроса на рис. 6.6.

В области заголовков строк допускается размещение нескольких полей, в то время как в других областях может размещаться только по одному полю.

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

Агрегатные функции SQL — SUM, MIN, MAX, AVG, COUNT

Будем учиться подводить итоги. Нет, это ещё не итоги изучения SQL, а итоги значений столбцов таблиц базы данных. Агрегатные функции SQL действуют в отношении значений столбца с целью получения единого результирующего значения. Наиболее часто применяются агрегатные функции SQL SUM, MIN, MAX, AVG и COUNT. Следует различать два случая применения агрегатных функций. Первый: агрегатные функции используются сами по себе и возвращают одно результирующее значение. Второй: агрегатные функции используются с оператором SQL GROUP BY, то есть с группировкой по полям (столбцам) для получения результирующих значений в каждой группе. Рассмотрим сначала случаи использования агрегатных функций без группировки.

Функция SQL SUM

Функция SQL SUM возвращает сумму значений столбца таблицы базы данных. Она может применяться только к столбцам, значениями которых являются числа. Запросы SQL для получения результирующей суммы начинаются так:

После этого выражения следует FROM (ИМЯ_ТАБЛИЦЫ), а далее с помощью конструкции WHERE может быть задано условие. Кроме того, перед именем столбца может быть указано DISTINCT, и это означает, что учитываться будут только уникальные значения. По умолчанию же учитываются все значения (для этого можно особо указать не DISTINCT, а ALL, но слово ALL не является обязательным).

Если вы хотите выполнить запросы к базе данных из этого урока на MS SQL Server, но эта СУБД не установлена на вашем компьютере, то ее можно установить, пользуясь инструкцией по этой ссылке .

Сначала работать будем с базой данных фирмы — Company1. Скрипт для создания этой базы данных, её таблиц и заполения таблиц данными — в файле по этой ссылке .

Пример 1. Есть база данных фирмы с данными о её подразделениях и сотрудниках. Таблица Staff помимо всего имеет столбец с данными о заработной плате сотрудников. Выборка из таблицы имеет следующий вид (для увеличения картинки щёлкнуть по ней левой кнопкой мыши):

Для получения суммы размеров всех заработных плат используем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Этот запрос вернёт значение 287664,63.

А теперь упражнение для самостоятельного решения. В упражнениях уже начинаем усложнять задания, приближая их к тем, что встречаются на практике.

Читать еще:  Офис 2020 где посмотреть активацию

Пример 2. Вывести сумму комиссионных, получаемых всеми сотрудниками с должностью Clerk.

Функция SQL MIN

Функция SQL MIN также действует в отношении столбцов, значениями которых являются числа и возвращает минимальное среди всех значений столбца. Эта функция имеет синтаксис аналогичный синтаксису функции SUM.

Пример 3. База данных и таблица — те же, что и в примере 1.

Требуется узнать минимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Запрос вернёт значение 10505,90.

И вновь упражнение для самостоятельного решения. В этом и некоторых других упражнениях потребуется уже не только таблица Staff, но и таблица Org, содержащая данные о подразделениях фирмы:

Пример 4. К таблице Staff добавляется таблица Org, содержащая данные о подразделениях фирмы. Вывести минимальное количество лет, проработанных одним сотрудником в отделе, расположенном в Бостоне.

Функция SQL MAX

Аналогично работает и имеет аналогичный синтаксис функция SQL MAX, которая применяется, когда требуется определить максимальное значение среди всех значений столбца.

Пример 5. База данных и таблица — те же, что и в предыдущих примерах.

Требуется узнать максимальную заработную плату сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Запрос вернёт значение 18352,80

Пришло время упражнения для самостоятельного решения.

Пример 6. Вновь работаем с двумя таблицами — Staff и Org. Вывести название отдела и максимальное значение комиссионных, получаемых одним сотрудником в отделе, относящемуся к группе отделов (Division) Eastern. Использовать JOIN (соединение таблиц).

Функция SQL AVG

Указанное в отношении синтаксиса для предыдущих описанных функций верно и в отношении функции SQL AVG. Эта функция возвращает среднее значение среди всех значений столбца.

Пример 7. База данных и таблица — те же, что и в предыдущих примерах.

Пусть требуется узнать средний трудовой стаж сотрудников отдела с номером 42. Для этого пишем следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Результатом будет значение 6,33

В следующем упражнении для самостоятельного решения помимо агрегатной функции требуется использовать также предикат BETWEEN.

Пример 8. Работаем с одной таблицей — Staff. Вывести среднюю зарплату сотрудников со стажем от 4 до 6 лет.

Функция SQL COUNT

Функция SQL COUNT возвращает количество записей таблицы базы данных. Если в запросе указать SELECT COUNT(ИМЯ_СТОЛБЦА) . то результатом будет количество записей без учёта тех записей, в которых значением столбца является NULL (неопределённое). Если использовать в качестве аргумента звёздочку и начать запрос SELECT COUNT(*) . то результатом будет количество всех записей (строк) таблицы.

Пример 9. База данных и таблица — те же, что и в предыдущих примерах.

Требуется узнать число всех сотрудников, которые получают комиссионные. Число сотрудников, у которых значения столбца Comm — не NULL, вернёт следующий запрос (на MS SQL Server — с предваряющей конструкцией USE company1;):

Результатом будет значение 11.

Пример 10. База данных и таблица — те же, что и в предыдущих примерах.

Если требуется узнать общее количество записей в таблице, то применяем запрос со звёздочкой в качестве аргумента функции COUNT (на MS SQL Server — с предваряющей конструкцией USE company1;):

Результатом будет значение 17.

В следующем упражнении для самостоятельного решения потребуется использовать подзапрос.

Пример 11. Работаем с одной таблицей — Staff. Вывести число сотрудников в отделе планирования (Plains).

Агрегатные функции вместе с SQL GROUP BY (группировкой)

Теперь рассмотрим применение агрегатных функций вместе с оператором SQL GROUP BY. Оператор SQL GROUP BY служит для группировки результирующих значений по столбцам таблицы базы данных. На сайте есть урок, посвящённый отдельно этому оператору.

Работать будем с базой данных «Портал объявлений 1». Скрипт для создания этой базы данных, её таблицы и заполения таблицы данных — в файле по этой ссылке .

Пример 12. Итак, есть база данных портала объявлений. В ней есть таблица Ads, содержащая данные об объявлениях, поданных за неделю. Столбец Category содержит данные о больших категориях объявлений (например, Недвижимость), а столбец Parts — о более мелких частях, входящих в категории (например, части Квартиры и Дачи являются частями категории Недвижимость). Столбец Units содержит данные о количестве поданных объявлений, а столбец Money — о денежных суммах, вырученных за подачу объявлений.

Функция max в access

Вопрос

Доброе время суток!)

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

последовательности. Так же есть форма, в ней поле «номер дела» — тоже нужно чтобы был номер последнего дела (здесь попробовала в самом поле задать =Max([НОМ]) — выдается ошибка)

Заранее спасибо за помощь!!

Ответы

Спасибо, dmirk и MaximMalko!

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

  • Помечено в качестве ответа Иван Проданов Microsoft contingent staff, Moderator 17 мая 2013 г. 5:58

Все ответы

Необходимо в форме для поля с номером дела установить сортировку требуемого вида. Также можно установить многоуровневую сортировку.

Кроме всего, если активировать поле формы, а затем щелкнуть на кнопке сортировки в панели инструментов, то данные в форме отсортируются по данному полю

Читать еще:  Удалить ключ офис

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

Как именно данные «попадают» в таблицу? Если простое отображение — порядок сортировки можно задать щелкнув правой клавишей мыши на имени столбца.

Если вы хотите задать сортировку на этапе формирования запроса, то нужно использовать order by

Так же есть форма, в ней поле «номер дела» — тоже нужно чтобы был номер последнего дела (здесь попробовала в самом поле задать =Max([НОМ]) — выдается ошибка)

Всё верно, следует использовать MAX. Детальный текст ошибки — коммерческая тайна?

В таблицу «попадают» данные «номер дела», «автомобиль», «продавец» и «покупатель». и с сортировкой я разобралась, нашла

по поводу поля. детального текста ошибки не могу привести, и это не коммерческая тайна, просто потому что ошибка в том, когда я из режима конструктора возвращаюсь в рабочий, в рабочем режиме в этом поле и появляется «# Ошибка».

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

Функция max в access

На этом шаге будут рассмотрены групповые операции.

При обработке данных в таблицах зачастую бывает необходимо учитывать в запросах не отдельные записи, а итоги, которые вычисляются или формируются определенным образом для различных групп записей в таблице. Например, необходимо выполнить запрос, в котором для каждого студента, было бы подсчитано количество полученных им оценок, а также вычислен средний балл. И затем на основании среднего балла определена стипендия, например, из расчета 100 р. за каждый балл. Другими словами, для отличника (средний балл = 5) размер стипендии должен составить 500 р.

Чтобы выполнить подобные вычисления, необходимо создать новый запрос в режиме конструктора, добавить в него с помощью кнопки Отобразить таблицу таблицы Студенты и Успеваемость, и затем воспользоваться кнопкой Групповые операции . После этого среди параметров запроса появится строка Групповые операции.

Чтобы вычислить для каждого студента его средний балл, необходимо вначале добавить в запрос поле Фамилия таблицы Студенты. При этом в строке Групповые операции по умолчанию устанавливается значение Группировка.

Итак, для каждой фамилии студента во втором столбце необходимо расположить общее количество оценок, полученных каждым студентом, в третьем — средний балл, и в четвертом — размер начисленной стипендии. Для этого во второй и третий столбцы запроса следует добавить поле Оценки таблицы Успеваемость, так как на основании именно этого поля будет определяться общее число оценок, а также вычисляться средний балл каждого студента.

При выполнении групповых операций можно использовать так называемые итоговые функции (таблица 1), которые следует выбирать из списка в добавленном поле Групповые операции.

Для подсчета количества полученных оценок следует выбрать функцию Count, а для определения среднего балла — функцию Avg. Затем созданный запрос следует сохранить, указав ему имя СреднийБалл.

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

Следует отметить, что подобным полям в запросах, как и вычисляемым полям, Access автоматически присваивает имена. В частности, поле, содержащее количество оценок, было названо CountОценка, а поле со средним баллом было названо АvgОценка. Следовательно, в окне Построитель выражений необходимо в формуле для стипендии использовать поле AvgОценка сохраненного запроса СреднийБалл. Т.е. нужно вставить в формулу поле AvgОценка из папки СреднийБалл и умножить это значение на 100 для определения размера стипендии (рис. 1). Однако необходимо иметь в виду, что если бы рассматриваемый запрос не был сохранен, то в Построителе выражений не появились бы поля CоuntОценка и AvgОценка.


Рис. 1. Построитель выражений. Определение размера стипендии

После установки всех описанных параметров макет запроса будет иметь вид, который представлен на рисунке 2.


Рис. 2. Макет запроса с использованием групповых операций

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

Результатом выполнения запроса СреднийБалл будет набор записей, представленный на рисунке 3.


Рис. 3. Результат выполнения запроса СреднийБалл

Создание запроса с вычисляемым полем можно увидеть здесь, а взять клип здесь.

На следующем шаге вы познакомитесь с перекрестными запросами.

Предыдущий шаг Содержание Следующий шаг

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×