Как сделать даты в access чтобы сама

Как добавить метку даты или времени в базу данных Access 2007

Существует множество приложений, в которых вы можете добавить отметку даты/времени к каждой записи, определяющую время, когда запись была добавлена ​​в базу данных. Это легко сделать в Microsoft Access с помощью функции Now (). В этом руководстве описан пошаговый процесс.

Примечание. Эти инструкции предназначены для Access 2007. Если вы используете более позднюю версию Access, ознакомьтесь с разделом Добавление меток времени. к базе данных Access 2010.

Добавление меток даты/времени в базу данных Access 2007

1. Откройте базу данных Microsoft Access, содержащую таблицу, в которую вы хотите добавить отметку даты или времени.
2. В левой части окна дважды щелкните таблицу, в которую вы хотите добавить отметку даты или времени.
3. Переключите таблицу в режим конструктора, выбрав «Вид конструктора» в раскрывающемся меню «Вид» в верхнем левом углу ленты Office.
4. Нажмите на ячейку в столбце «Имя поля» первой пустой строки таблицы. Введите имя для столбца (например, «Дата добавления записи») в этой ячейке.
5. Нажмите стрелку рядом со словом «Текст» в столбце «Тип данных» той же строки и выберите «Дата/Время» в раскрывающемся меню.
6. В области окна «Свойства поля» в нижней части экрана введите «Сейчас ()» (без кавычек) в поле Значение по умолчанию.
7. Также на панели «Свойства поля» щелкните стрелку в ячейке, соответствующей свойству «Выбор даты», и выберите «Никогда» в раскрывающемся меню.
8. Сохраните вашу базу данных, нажав кнопку Microsoft Office и выбрав пункт меню Сохранить.
9. Убедитесь, что новое поле работает правильно, создав новую запись. Access должен автоматически добавить метку времени в поле «Дата добавления записи».

Добавление метки даты без времени

Функция Now () добавляет текущую дату и время в поле. Кроме того, вы можете использовать функцию Date (), чтобы добавить дату без времени.

Вставка текущей даты при создании записи?

Вопрос.
1. Как сделать чтобы при создании записи автоматически проставлялась текущая дата в поле «data_sozd»?
2. Как сделать чтобы при смене статуса автоматически проставлялась текущая дата в поле «data_status»?

Ссылка на файл

В Access для этого используются т.н. макросы данных: в конструкторе таблиц на вкладке Конструктор выбираете пункт Создать макросы данных > После вставки (После обновления) и там строите выражение Изменить запись > Задать поле, указываете имя поля, значение = Date().

Я, правда, не уверен, что такой подход работает с внешними таблицами. Даже так, скорее всего, не работает. В этом случае надо внести изменения на стороне MySQL: для поля data_sozd установить значение по умолчанию ALTER TABLE table_name CHANGE data_sozd DATE NULL DEFAULT CURRENT_TIMESTAMP ; для обновления data_status требуется создать триггер на обновление данных:

Здесь же, во избежание перезаписи значения data_sozd, можно добавить строчку SET NEW.data_sozd = OLD.data_sozd;

Дизайн сайта / логотип © 2023 Stack Exchange Inc; пользовательские материалы лицензированы в соответствии с CC BY-SA . rev 2023.3.13.43308

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Как сделать дату в access?

Всем привет, сегодня разбираем функцию DateAdd в запросах Access. Если вы не знаете, как работает функция DateAdd в базе данных Microsoft Access, то дочитайте данную статью до конца.
На простых запросах мы разберем принцип работы встроенной функции DateAdd. Функция DateAdd в запросах Access применяется для добавления (вычитания) указанного интервала времени к дате.

Функция DateAdd в запросах Access

Синтаксис: DateAdd («интервал», число, дата).

Функция DateAdd в запросах Access содержит 3 аргумента. Все аргументы обязательные.
1 аргумент — ИНТЕРВАЛ. Интервал это тот период времени, который необходимо добавить (вычесть) к требуемой дате.
2 аргумент — ЧИСЛО.

Это то количество заданных интервалов, которое будет добавлено к дате.
3 аргумент — ДАТА. Дата, к которой добавляются интервалы с помощью функции DateAdd.

Все параметры первого аргумента — интервала представлены ниже:

Приведем простой пример и посмотрим как работает функция DateAdd в запросах Access.
Создадим таблицу, которая будет состоять из 2 полей: «начальная дата» и «начальное время». Оба поля имеют тип «Дата/Время». Поле «начальная дата» — формат «краткий формат даты», «начальное время» — формат «краткий формат времени».

Заполним таблицу произвольными данными.

Далее создадим простой запрос, где к полю «начальная дата» добавим 10 дней.
Добавим в запрос поле «начальная дата». Добавим новое вычисляемое поле:

Данный пример демонстрирует основной принцип работы функции DateAdd в запросах Access: к дате прибавляется 10 дней.

Рассмотрим следующий пример, где используется функция DateAdd в запросах Access.
Добавим к дате 10 месяцев. для этого достаточно поменять значение интервала с «d» на «m».

Рассмотрим следующий пример, где используется функция DateAdd в запросах Access.
Добавим к дате 10 лет. для этого достаточно поменять значение интервала с «m» на «yyyy».

Рассмотрим следующий пример, где используется функция DateAdd в запросах Access.
Добавим к дате 1 квартал. для этого достаточно поменять значение интервала с «yyyy» на «q».

• Функция DateAdd в запросах Access не только прибавляет указанный интервал к дате, но и вычитает указанный интервал из даты.
  Пример вычитания из даты 10 лет представлен ниже:

Хотите больше примеров, где разбирается функция DateAdd в запросах Access? Например как можно использовать DateAdd в формах Access? Переходите по ССЫЛКЕ.

Если вам нужна готовая база данных Access, то ознакомьтесь со списком представленным ЗДЕСЬ.

Запросы в Access 2013 с помощью элементов формы

Создание формы Пошук

Создадим пустую форму. Для этого выберем в меню вкладку Создание и кликнем на Пустая форма.

Получим форму в режиме макета. Кликнем правой кнопкой мышки по форме и выберем в контекстном меню Свойства формы (или Работа с макетами форм — Конструктор — Сервис — Страница свойств).

Справа появится Окно свойств. В выпадающем списке строки Источник записей вкладки Данные выберем Для форми пошуку

После этого в сервисе выберем Добавить поля. Окно свойств сменится на Список полей.

Добавим на форму следующие элементы (перетягивание мышкой из списка полей на форму):

Дата (дважды), Найменування клієнта, Адреса, Телефон, Рахунок, Найменування товару, Ціна (дважды), Кількість (дважды), Передоплата, Сума передоплати (дважды).

• Изменим надписи для даты, цены, количества и суммы предоплаты.
• Дата: → Дата з:
• Дата: → Дата по:
• Ціна: → Ціна від:
• Ціна: → Ціна до:
• Кількість: → Кількість від:
• Кількість: → Кількість до:
• Сума передоплати: → Сума передоплати від:
• Сума передоплати: → Сума передоплати до:

Выделим поле напротив надписи Дата з:. В инструментах конструктора форм выберем вкладку Конструктор. Откроем окно свойств для выделенного элемента (Сочетание клавиш ALT+ENTER; Конструктор — Сервис — Страница свойств или в контекстном меню выбрать Свойства)

В окне свойств перейдем на вкладку Другие и в строке Имя впишем Дата з

Перейдем на вкладку Данные и в строке Данные сотрем Дата

В строке Значение по умолчанию впишем #02.01.2010#; в строке Правило проверки впишем >#01.01.2010# и в строке Сообщение об ошибке — Введіть дату після 1 січня 2010 року! (см. свойства таблицы Данні про замовлення)

Аналогично поступим и с другими полями формы.

Дата по: вписываем имя Дата по в строке Имя вкладки Другие; очищаем строку Данные вкладки Данные; в строке Значение по умолчанию вписываем Date(); в строке Правило проверки впишем >#01.01.2010# и в строке Сообщение об ошибке — Введіть дату після 1 січня 2010 року!.

1. Найменування клієнта:, Адреса:, Телефон:, Рахунок:, Найменування товару: очищаем строку Данные вкладки Данные.
2. Ціна від: вписываем имя Ціна від в строке Имя вкладки Другие; очищаем строку Данные вкладки Данные; в строке Значение по умолчанию вписываем 1; в строке Правило проверки впишем >0 и в строке Сообщение об ошибке — Введіть суму > нуля!
3. Ціна до: вписываем имя Ціна до в строке Имя вкладки Другие; очищаем строку Данные вкладки Данные; в строке Значение по умолчанию вписываем 1000 (можно вписать любое число, ориентируйтесь на максимальную цену); в строке Правило проверки впишем >0 и в строке Сообщение об ошибке — Введіть суму > нуля!
4. Кількість від: вписываем имя Кількість від в строке Имя вкладки Другие; очищаем строку Данные вкладки Данные; в строке Значение по умолчанию вписываем 1; в строке Правило проверки впишем >0 и в строке Сообщение об ошибке — Введіть кількість > нуля!
5. Кількість до: вписываем имя Кількість до в строке Имя вкладки Другие;очищаем строку Данные вкладки Данные; в строке Значение по умолчанию вписываем 1000 (можно вписать любое число, ориентируйтесь на максимальное количество); в строке Правило проверки впишем >0 и в строке Сообщение об ошибке — Введіть кількість > нуля!
6. Передоплата: очищаем строку Данные вкладки Данные.

Сума передоплати від: вписываем имя Сума передоплати від в строке Имя вкладки Другие; очищаем строку Данные вкладки Данные; в строке Значение по умолчанию вписываем . ; в строке Правило проверки впишем >=0 и в строке Сообщение об ошибке — Введіть суму, яка більше або дорівнює нулеві!

Сума передоплати до: вписываем имя Сума передоплати до в строке Имя вкладки Другие; очищаем строку Данные вкладки Данные; в строке Значение по умолчанию вписываем 1000 (можно вписать любое число, ориентируйтесь на максимальную сумму предоплаты); в строке Правило проверки впишем >=0 и в строке Сообщение об ошибке — Введіть суму, яка більше або дорівнює нулеві!

Преобразуем поле Найменування клієнта в поле со списком (контекстное меню поля)

Выполним следующее: Окно свойств — Данные — Источник строк — … — Довідник клієнтів — Найменування клієнта — закрыть построитель запросов и подтвердить сохранение.

Аналогично преобразуем в поле со списком Найменування товару. В качестве источника выбрать Найменування товару.

В результате получим

Закроем и сохраним форму под именем Пошук

Условия отбора записей — Базы данных

Литералы — конкретные значения, воспринимаемые Access так, как они записаны. В качестве литералов могут быть использованы числа, текстовые строки, даты. Текстовые строки заключаются в двойные кавычки, даты — в знаки (#). Например, 567, «Информатика», #1-Января-99#.

Константы — не изменяющиеся значения, которые определены в Access, например, True, False, Да, Нет, Null.

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

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

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

Например, ссылка на поле в таблице примет вид: [Имя таблицы]! [Имя поля], а ссылка на свойство DefaultValue элемента управления Дата рождения в форме СТУДЕНТ: Forms! [СТУДЕНТ]! [ Дата рождения].DefaultValue

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

Эти операторы определяют операцию над одним или несколькими операндами.

Если выражение в условии отбора не содержит оператора, то по умолчанию используется оператор =.

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

Допускается использование операторов шаблона — звездочка (*) и вопросительный знак (?).

• Оператор Between позволяет задать интервал для числового значения и даты. Например:
• Between 10 And 100
• задает интервал от 10 до 100; можно задать интервал дат:

Between #01.01.1997* And #31.12.1997*

1. Оператор in позволяет выполнить проверку на равенство любому значению из списка, который задается в круглых скобках. Например:
2. In («Математики»;»Информатики»; » Истории»)

Лабораторная №9(Запросы в Access)

• Лабораторная
  работа №9.
• Тема: Запросы
  в Access.
• С помощью запросов
  можно

• просматривать,
• анализировать,
• изменять данные.

Существуют
способы формулирования информационных
требований пользователя формальным
способом. Обычно, мы формулируем наши
требования по отбору информации так:

• Найти все продукты типа Мюсли.
• Найти продукты с калорийностью меньше 200

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

Чтобы
создать запрос в Access
необходимо
1) …в окне базы данных
открыть вкладку Запросы

1. 2) …и создаете запрос
   с помощью конструктора.
2. Графический
   способ задания запросов.
3. Конструктор
   позволяет задавать запросы на QBE (Query
   by
   Example)–
   это графический способ указания, какую
   информацию мы хотим отобрать.
4. Окно
   графического редактора будет выглядеть
   в режиме конструктора так:
5. Рис.1
6. Сам
   бланк запрос представляет собой две
   части:
7. — в верхней
   части указываются
   таблицы, участвующие в запросе;
8. —
   в нижней части
   размещаются поля из таблиц, которые
   должны быть в результирующей таблице
   или на которые накладывается ограничение
   (условие отбора).
9. Для
   включения
   требуемых
   полей можно
   воспользоваться следующими способами:
• В первой строке бланка запроса Поле щелчком мыши вызвать появление кнопки списка и выбрать из списка нужное поле;
• Переместить с помощью мыши требуемое поле из списка полей таблицы в схеме данных запроса в первую строку бланка запроса;
• Дважды щелкнуть на имени поля таблицы в схеме данных запроса
• Для включения в запрос всех полей таблицы можно переместить с помощью мыши в соответствующую строку бланка запроса все поля из списка полей в схеме данных запроса или дважды щелкнуть на символе * в этом списке

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

Условие
записывается в строке Условие
отбора!

• Когда вы задаете условия, вам необходимо
  помнить каким типом данных у вас задано
  поле.
• Примеры задания условий:
• Для
  числовых полей.
• = 100
• >10
• 20
1. функция позволяет получить из даты любую часть, первый параметр задает тип того, что хотим получить
2. “YYYY” – год
3. “Q” – квартал
4. “WW” – номер недели в году
5. “M” — месяц
6. “Y” – день года
7. “D” — день
8. “W” день недели

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

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

Например, синтаксис (правило обращения)
функции DateDiff следующий:

DateDiff(interval;date1;
date2[,
firstdayofweek[,
firstweekofyear]])

Первые три параметра объяснялись выше,
а параметры firstdayofweek
и firstweekofyear необязательные (в синтаксисе
это указывается тем, что они располагаются
за квадратными скобками). Если эти
параметры опущены, то их значения
подставятся из значений по умолчанию.

Этипараметры позволяют задать
следующую информацию:

Firstdayofweek (задает, что считать первым днем недели)
может иметь значения от 0 до 7

0	Использует установки системы по умолчанию
1	Воскресенье (по умолчанию)
2	Понедельник
3	Вторник
4	Среда
5	Четверг
6	Пятница
7	Суббота

Firstweekofyear (задает, что считать
первой неделей года) может иметь
значения от 0 до 7

0	Использует установки системы по умолчанию
1	Начинается с недели, в которой есть день 1 января (по умолчанию).
2	Начинается с первой недели, в которой есть, по крайней мере, 4 дня нового года
3	Начинается с первой полной недели года

Проще всего получить справочную
информацию по синтаксису функций можно
следующим образом:

Вызвать построитель выражений с помощью кнопки (курсор должен находиться в области Поле или Условие на значение)

В левом окне Построителя раскрыть папку Функции –> Встроенные функции

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

Выбрать в последнем столбце нужную функцию и нажать кнопку Справка в окне построителя выражений.

Справка выдается на английском языке.

Задание 7.

Найдите
срок исполнения заказов. Создайте вычисляемое поле Срок исполнения как
показано в примере.

• Задание 8.
• Измените сделанный запрос и найдите
  заказы, которые исполнялись больше
  10 дней (для вычисляемого поля Срок
  исполнения наложите ограничение >
  10) .
• Задание 9.
• Найдите заказы, которые не оплатили
  (наложить условие IS NULL
  на поле ДатаОплаты)
• Задание 10.

Добавьте
к основным полям таблицы Заказы
названия дня недели для даты размещения
заказа. Для этого надо воспользоваться
двумя функциямиWeekDayName()
и функциейWeekDay().

Посмотрите какие параметры у этих
функций – и вы увидите, что функция
WeekDayNameимеет
числовой параметр (то есть ей надо задать
значение номер дня недели от 1 до 7),
поэтому и необходимо из даты сначала
выделить номер дня недели, а затем по
номер определить имя дня недели. В
результате выражение будет записываться
так:

Для тех, кто понимает. Вычисления для дат и времени

Используя функции для дат, всегда следует помнить о датах, содержащих информацию о времени. (Напоминаю, все даты могут содержать данные о времени суток. Но, выбирая подходящий формат для поля с датами, вы сообщаете программе Access о том, нужно ли отображать временной компонент даты и разрешать пользователям вводить его, как объясняется в разд. «Дата/время » главы 2. Чаще всего вы будете пользоваться форма­том, скрывающим любую информацию о времени суток.)

Проблема: функция Date ( ) возвращает текущую дату со значением времени суток, равным 0. Другими словами, если текущая дата — 4 июля 2008 г., то функция Date ( ) возвращает первую секунду 4 июля 2008 г. — момент, когда часы показывают полночь (12:00 a.m.)

Если вы не храните значения времени суток, эта проблема не важна, поскольку у всех ваших дат время суток равно 0. Но что произойдет, если вы используете Полный фор­мат даты (см. табл. 2.3) в поле DueDate, разрешающий пользователям вводить и дату, и время. Теперь у условия отбора =<Date ( ) несколько иной смысл — вы сообщаете Access о необходимости отобрать, как совпадающие, все поля с датами, наступившими до первой секунды текущего дня. Это условие не выберет запись со сроком платежа, назна­ченным на 16:00 текущего дня.

В данной ситуации, возможно, нужно изменить условие отбора на следующее: <(Date()+l)

Date () +1 — это завтра. Другими словами, это условие отбирает любые записи со сро­ком платежа, истекшим до первой секунды завтрашнего дня.

Между прочим, у программы Access есть функция Now (), возвращающая текущую дату и время. Таким образом, следующее условие фильтрации отбирает все записи, соответ­ствующие текущему моменту (текущего дня) или любому моменту времени во все предшествующие дни:

Компоненты дат применяются в нескольких функциях обработки дат, включая функции DatePart ( ), DateAdd ( ) и DateDiff ( ). В табл. 7.6 приведены эти и дополнительные по­лезные функции, относящиеся к датам.

Таблица 7.6. Функции обработки дат

Глава 7. Основные хитрости, применяемые в запросах

Получает текущую дату

Получает текущую дату и время

1/20/2006 10:16:26 PM

Извлекает часть даты (например, год, месяц или день в месяце)

DatePart(#1/20/ 2006#, «d»)

Преобразует год, месяц и день в значение даты Access

DateSerial(2006, 5, 4)

Сдвигает дату на заданный интервал

DateAdd(«yyyy», 2, #22/ll/2006#)

Определяет интервал между двумя датами

DateDiff(«w», #10/l5/2006#, #l/ll/2007#)

Получает название, соответствую­щее номеру месяца (от 1 до 12)

Получает название, соответствую­щее номеру дня в неделе (от 1 до 7)

Преобразует дату в форматирован­ный текст (используя любой формат даты, описанный в табл. 2.3)

Format(#27/04/ 2008#, «Long Date»)

У программы Access есть другие функции обработки дат, выполняющие часть алгоритма функции DatePart ( ). Примером может служить функция Month ( ), извлекающая номер месяца из да­ты. К другим аналогичным функциям относятся Year(), Day ( ), Hour ( ), Minute ( ) и Second ( ). Эти функции не дают никаких преимуществ, но вы можете встретить их в запросах других пользователей, применяющих их для получения аналогичных результатов.

Обработка пропущенных или неопределенных значений

В БД есть два типа полей: обязательные и необязательные. Как правило, в БД поля необяза­тельные (как обсуждалось в разд. «Запрет незаполненных полей» главы 4), что означает для неаккуратного пользователя возможность пропуска большого числа значений. Эти пропу­щенные значения называют неопределенными (null), и их следует тщательно обрабатывать.

Если вы хотите написать условие отбора, вылавливающее неопределенные значения, просто введите в ячейку Условие отбора следующий текст: Is Null

Это условие выберет все записи с пропущенными значениями. Воспользуйтесь им в поле CustomerID таблицы Orders для поиска всех заказов, не связанных с клиентом. Или игно­рируйте несвязанные записи, заменив условие отбора на обратное:

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

У функции Nz () два аргумента. Первое значение (как правило, поле запроса) может со­держать неопределенное значение. Второй параметр — это значение, которое вы хотите ото­бразить в результатах запроса, если программа Access найдет неопределенное значение. Да­лее приведен пример, использующий функцию Nz ( ) для преобразования в 0 неопределенных значений в поле Quantity:

Преобразование в 0 — стандартное поведение для функции Nz ( ), поэтому можно опус­тить второй параметр, если это как раз то, что вам нужно:

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

OrderItemCost: [Quantity] * [Price]

Это выражение приведет к ошибке, если значение поля Quantity будет неопределенным. Неопределенные значения странным образом распространяются наподобие инвазивного грибка. Если у одного из операндов в вычислении неопределенное значение, результат автоматически становится неопределенным. В данном примере это означает, что иоле OrderItemCost для данной записи становится неопределенным. Хуже того, если OrderItemCost ввести в другое вычисление или промежуточный итог, они тоже станут не­определенными. Прежде чем вы об этом узнаете, ваши значимые данные запроса превратят­ся в кучу ячеек с неопределенными значениями.

Для устранения этой проблемы очистите необязательные ноля от неопределенных зна­чений с помощью функции Nz ( ): OrderItemCost: Nz([Quantity]) * Nz([Price])

Наконец, функцию Nz ( ) можно использовать для замены всех неопределенных значе­ний другой величиной, В текстовое поле можно ввести что-то более информативное. Далее приведен пример, отображающий текст («Not Entered» — не введено) рядом с каждой записью, не содержащей имени и фамилии:

Name: Nz([FirstName] & [LastName], «[Not Entered]»)

Итоговые данные

Все запросы, которые вы применяли до этого момента, имели дело с отдельными записями. Если вы выбирали 143 записи из таблицы Orders, то видели все 143 записи в ваших резуль­татах. Но вы также можете группировать записи для получения промежуточных и общих итогов. В этом случае легче просматривать большие объемы информации и делать важные далеко идущие выводы.

Далее приведены примеры полезных подводящих итоги запросов:

подсчет студентов в каждом классе;

подсчет количества заказов, сделанных каждым клиентом;

сумма, потраченная на один продукт;

общая сумма долга или платежа клиента;

подсчет среднего объема заказа, сделанного каждым клиентом;

поиск самого дорогостоящего или самого дешевого заказа, сделанного клиентом.

Эти операции — подсчет, суммирование, определение среднего и поиск максимального и минимального значений — основные варианты в итоговом запросе (totals query). Итоговый запрос — это вид запроса, который должен перелопатить большое количество записей и вы­дать лаконичные итоги.

Для создания итогового запроса выполните следующие действия:

Создайте новый запрос, выбрав Создание → Другие → Конструктор запросов (Create → Other → Query Design).

Добавьте нужные вам таблицы с помощью диалогового окна Добавление таблицы (Show Table) и щелкните мышью кнопку Закрыть (Close).

В приведенном далее примере используется таблица Products из БД Boutique Fudge.

3. Вставьте поля, которые хотите использовать.

В этом примере используется поле Price, но хитрым образом: поле Price вставляется три раза для того, чтобы в запросе отображались результаты трех разных вычислений.

4. Выберите Работа с запросами → Показать или скрыть → Итоги (Query Tools → Show/Hide → Totals).

Программа Access вставляет ячейку Групповая операция (Total) для каждого поля сразу под ячейкой Таблица.

5. Для каждого поля задайте вариант из списка Групповая операция. Этот вариант определяет использование поля для вычисления итога или для группировки.

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

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

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

Поле используется для фильтрации или отбора. В этом случае в ячейке Групповая операция нужно выбрать Условие (WHERE). (Фанаты БД, возможно, помнят, что WHERE — это ключевое слово, применяющееся для определения условия в языке SQL, как было описано в разд. «Анализ запроса» главы 6). Нужно также сбросить флажок Вывод на экран, поскольку программа Access не может выводить отдельные значения в итоговых сводках.

Если вы попытаетесь вставить в итоговый запрос поле, которое не используется для вычисле­ния или группировки и не скрыто, то получите ошибку при попытке выполнить запрос.

В этом примере (рис. 7.8) в поле Price применяются три разные групповые операции: Max, Min и Avg.

Рис.7.8. Вверху: в данный итоговый запрос поле Price включено трижды и использует три разных вычисления. Обратите внимание на то, что в каждое поле применяется выражение, в котором дано более информативное название поля. Внизу: результаты отображают одну запись с максимальной ценой, минимальной ценой и средней ценой продуктов, проданных компанией Boutique Fudge

В табл. 7.7 не указаны два варианта, предназначенные для статистиков — StDev и Var — ко­торые вычисляют стандартное отклонение и дисперсию ряда чисел.

Таблица 7.7. Варианты получения итоговых данных

Значение в ячейке Групповая операция

Группировка

Группирует записи, основываясь на значениях в этом поле

Суммирует значения этого поля

Находит среднее для значений этого поля

Возвращает наименьшее значение в этом поле

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

Подсчитывает количество записей (независимо от того, какое поле вы используете)

Возвращает первое значение в этом поле

Возвращает последнее значение в этом поле

Разрабатывая итоговые запросы, можно использовать все приобретенные ранее в этой гла­ве навыки написания запросов. Если вы хотите суммировать только продукты в конкретной категории, можно использовать в поле CategoryID, например, такое условие отбора:

Это условие отбирает записи, у которых CategoryID равно 3 (что означает их включение в категорию Candies (конфеты)).

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

Группировка в итоговом запросе

Наипростейший итоговый запрос суммирует все выбранные записи в одну строку результа­тов, как показано на рис. 7.8. В более сложном итоговом запросе применяется группировка для вычисления промежуточных итогов.

Для корректного применения группировки следует помнить о том, что поле, которое вы используете, должно содержать много повторяющихся значений. Например, хорошо груп­пировать клиентов по штатам, в которых они живут. Поскольку в каждом штате много кли­ентов, у вас получатся осмысленные промежуточные итоги. Глупо группировать клиентов по номерам социального обеспечения, поскольку в результате получится столько групп, сколько у вас клиентов. На рис. 7.9 показан итоговый запрос с применением группировки.

Рис.7.9. Вверху: продукты сгруппированы по категории продукта. Внизу: результат — отдельная строка с итогами по каждой категории продуктов

В итоговом запросе можно использовать многоуровневую группировку, вставив несколь­ко полей со значением Группировка в ячейку Групповая операция. Но результаты могут оказаться неожиданными. Предположим, что вы группируете длинный список записей о продажах по наименованиям товаров и по именам клиентов. В результате вы получите от­дельную группу для каждой комбинации «клиент — товар». В табл. 7.8 приведена часть ре­зультатов запроса, подобного описанному, в котором группируются записи из таблицы OrderDetails БД Boutique Fudge, а затем они сортируются по CustomerID.

Таблица 7.8. Результаты запроса с многоуровневой группировкой

Таблица 7.8 (окончание)

Из этой таблицы видно, что клиент с номером 10 потратил в целом $432.12 на товар с номером 108 во всех заказах. Этот же клиент потратил $16.79 на товар с номером 134, $53.30 — на товар с номером 210 и т. д. (Вы можете взять эти данные и отсортировать их по коду товара ProductID, чтобы увидеть объемы продаж каждого товара для разных клиентов. У вас все та же информация, но анализировать ее можно по-разному.)

Это результат, который вы хотели получить. Но ему недостает хороших промежуточных итогов. Было бы полезно узнать, сколько клиент с номером 10 потратил на каждый тип про­дукта и сколько он потратил всего. Но из-за жесткой табличной структуры итогового запро­са получить такой результат невозможно.

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

Объединения в итоговом запросе

Итоговые запросы невероятно полезны, когда они комбинируются с объединениями (см. разд. «Запросы и связанные таблицы» главы 6) для получения связанной информации из нескольких таблиц. В БД Boutique Fudge таблица OrderDetails хранит отдельные компо­ненты каждого заказа. Вы можете сгруппировать эту информацию (как показано в преды­дущем разделе) для поиска самых ходовых товаров или самых активных клиентов. Но, к сожалению, вы увидите только значения кодов клиентов и товаров, которые малоинформативны.

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

Если вставить в запрос объединение или два, можно извлечь подчиненную информацию из связанных таблиц (таких как Customers, Products и Orders) и добавить ее в ваши ре­зультаты. На рис. 7.10 показан пример вычисления общей стоимости каждого заказа. Затем результаты отсортированы по коду клиента CustomerID.



Рис. 7.10. Вверху: итоговый запрос можно усовершенствовать, дополнив его информацией из трех связанных таблиц: Customers, Orders и OrderDetails, для отображения списка сумм заказов, упорядоченных по клиентам. В запросе игнорируются заказы стоимостью меньше $25. Можно вставить условие отбора в поле DatePlaced, чтобы узнать, сколько потратили клиенты до нынешнего дня текущего года, сколько они потратили в прошлом году, на прошлой неделе и т. д. Внизу: результаты сгруппированы по OrderID и отсортированы по LastName и FirstName, что сохраняет хороший уровень детализации

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

Создайте новый запрос, выбрав Создание → Другие → Конструктор запросов (Create → Other → Query Design).

Вставьте нужные вам таблицы с помощью диалогового окна Добавление таблицы (Show Table) и затем щелкните мышью кнопку Закрыть (Close).

В примере на рис. 7.10 используются таблицы Customers, Orders и OrderDetails. После добавления этих таблиц программа Access вставит линии объединения между ними на основе связей, установленных в вашей БД.

3. Выберите Работа с запросами | Конструктор → Показать или скрыть → Итоги (Choose Query Tools | Design → Show/Hide → Totals).

У каждого поля появится ячейка Групповая операция.

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

Поля можно выбирать из любых связанных таблиц. В данном примере применяются следующие поля.

OrderID — это поле используется для группировки результатов. Другими словами, вы хотите подытожить все записи в таблице OrderDetails с одинаковым значением поля OrderID. Для выполнения этой работы в ячейке Групповая операция выберите вариант Группировка. (Между прочим, неважно, какое поле вы выберите — OrderID в таблице OrderDetails или ГО в таблице Orders — они связаны.)

OrderTotal — это вычисляемое поле, использующее выражение [Price] * [Quantity] для перемножения двух полей из таблицы OrderDetails. В результате получится итог для отдельной строки заказа. Программа Access суммирует эти строчные итоги для получения общего итога, поэтому задайте в ячейке Групповая операция вариант Sum. В поле OrderTotal включено условие отбора >=25, скрывающее любые заказы с общей стоимостью ниже $25.

LastName и FirstName — эти поля идентифицируют клиента, сделавшего заказ. Но здесь есть хитрость. Для отображения в итоговом запросе любого поля нужно вста­вить для него вычисление (как для поля OrderTotal) или использовать его для груп­пировки (как поле OrderID). Это означает, что вы должны установить в ячейке Груп­повая операция вариант Группировка для обоих полей. Эта установка на самом деле не будет оказывать никакого влияния, поскольку каждый заказ делается всегда одним клиентом. (Другими словами, вы никогда не найдете в таблице OrderDetails группу записей из одного заказа, но для разных клиентов. Это просто невозможно.) В ре­зультате программа Access не выполнит никакой группировки полей LastName и FirstName, они просто будут отображаться рядом с каждым заказом.

Этот трюк с группировкой немного странный, но широко применяется в итоговых запросах. Просто запомните, что программа Access создает самые маленькие доступные ей группы. Если вы хотите сгруппировать только по клиентам (так можно посмотреть, сколько потратил каждый клиент), нужно только удалить группировку в поле OrderID и установить ее в поле CustomerID. Или если вы хотите подсчитать объем продаж конкретного товара, удалите всю информацию о клиенте, сгруппируйте данные по полю ProductID и затем вставьте дополнительные поля из таблицы Products, которые вы хотите видеть на экране (например, ProductName и Description).

5. Теперь можно выполнить запрос.

Параметры запроса

Параметры запроса — секретное оружие программы Access. Они позволяют создавать сверхгибкие запросы за счет умышленного пропуска одной или нескольких порций информации. При каждом запуске запроса Access запрашивает у вас пропущенные значения. Эти недос­тающие значения и называют параметрами запроса.

Обычно параметры запроса применяют в условиях отбора. Допустим, вы хотите вывести на экран список клиентов, живущих в конкретном штате. Можно создать целый набор за­просов, таких как New York Customers, CaliforniaCustomers, Ohio Customers и т. д. Если вас

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

Для создания запроса с параметрами выполните следующие действия:

Создайте новый запрос, выбрав на ленте Создание → Другие → Конструктор запросов (Create → Other → Query Design).

Из диалогового окна Добавление таблицы (Show Table) вставьте нужные вам таблицы и щелкните мышью кнопку Закрыть (Close).

Б данном примере используется таблица Customers.

3. Выберите Работа с запросами | Конструктор→Показать или скрыть→Параметры(Choose Query Tools | Design → Show/Hide → Parameters).

На экране появится диалоговое окно Параметры запроса (Query Parameters).

Рис. 7.11. Можно определить столько параметров, сколько нужно. В окне задан один параметр, названный CustomerState и содержащий текст

4. Выберите имя и тип данных для вашего параметра (рис. 7.11).

Вы можете использовать любое понравившееся вам имя (но не применяйте имя, которое используется для обозначения поля в вашем запросе). Тип данных должен соответство­вать типу данных поля, для которого используется параметр. Тип данных задается выбо­ром одного из вариантов в раскрывающемся списке. Самые распространенные варианты: Текстовый, Целый, Денежный и Дата/время.

5. Щелкните мышью кнопку ОК для закрытия окна Параметры запроса.

Теперь можно ссылаться на параметр по имени так же, как вы ссылаетесь на поле в своем запросе. Например, можно добавить следующее условие отбора для поля State:

Убедитесь, что вы не забыли вставить квадратные скобки, чтобы программа Access знала, что вы не пытаетесь ввести фрагмент текста.

Во время выполнения запроса Access откроет диалоговое окно Введите значение пара­метра (Enter Parameter Value) для ввода конкретного значения (рис. 7.12). Введите интере­сующий вас штат и щелкните мышью кнопку ОК. Программа использует ваше значение для отбора в поле State.



Рис. 7.12. При каждом выполнении запроса можно ввести другой штат. В данном случае будут отобраны клиенты из штата Нью-Йорк

Несмотря на то, что такая возможность есть, лучше не применять несколько параметров в од­ном запросе. Когда запрос выполняется, программа Access отображает отдельное диалоговое окно Введите значение параметра для каждого значения. Если у вас несколько параметров, вам придется сопровождать выполнение запроса щелчками мышью в назойливых диалоговых окнах.

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

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

Похожие публикации:
1. Как работает процессор компьютера книга
2. Как разрушать рокировки шахматы
3. Как узнать форм фактор жесткого диска
4. Как установить pandas в jupiter notebook