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

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

1. Создайте запрос на выборку и откройте его в режиме конструктора.

2. В строке Условия отбора поля, для которого нужно применить параметр, введите Like " *" & [, текст, который должен отображаться в сообщении, а затем ]& " *".

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

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

 

Знак	Описание	Пример
*	Соответствует любому количеству знаков. Звездочку (*) можно использовать в любом месте текстовой строки.	чт* – поиск слов «что», «чтобы» и «чтение», но не «почта» или «чат».
?	Соответствует одному из знаков алфавита.	д? м – поиск слов «дом», «дым» и «дам».
[ ]	Соответствует одному из заключенных в скобки знаков.	д[оы]м – поиск слов «дом» и «дым», но не «дам».
!	Соответствует одному из знаков, кроме заключенных в скобки.	д[! оы]м – поиск слова «дам», но не «дом» или «дым».
-	Соответствует любому знаку из диапазона. Необходимо указывать этот диапазон по возрастанию (от А до Я, но не от Я до А).	б[а-в]г – поиск сочетаний «баг», «ббг» и «бвг».
#	Соответствует любой цифре.	1#3 – поиск значений 103, 113, 123 и т. п.

 

3. Когда запрос получит значение параметра, он сравнит его со значениями, которые содержат строку параметра. Например, для строки параметра си будут найдены строки, в которых поле параметра имеет значение «Сингапур» и «Россия».

Также можно использовать ключевое слово Like и подстановочные знаки для указания того, что параметр должен совпадать с началом или окончанием значения поля. Чтобы определить совпадение с началом значения поля, не вводите кавычки, подстановочный символ и амперсанд (& ), которые стоят перед открывающей квадратной скобкой. Чтобы определить совпадение с окончанием поля, не вводите кавычки, подстановочный символ и амперсанд (& ), которые стоят после закрывающей квадратной скобки.

Совпадение на основе неравных значений

Может потребоваться, чтобы запрос принимал параметр, но возвращал не строки с совпадающими значениями, а строки, для которых истинно другое выражение условия. Например, можно предлагать ввести год и возвращать все записи, в которых значение года будет больше, чем введенное в строке параметра. Для этого введите оператор сравнения слева от квадратных скобок, в которые заключена строка сообщения, например > [Год: ].

 

 

Запросы с критериями поиска

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

Например, выражение " < 10" в соответствующем столбце ограничивает результат теми записями, числовое значение соответствующего поля которых меньше 10.

Условия отбора записей могут задаваться для одного или нескольких полей в строке Условие отбора (Criteria) бланка запроса.

Условием отбора является выражение, которое состоит из, операторов сравнения и операндов.

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

Литералами являются конкретные значения, воспринимаемые Access так, как они записаны. В качестве литералов могут быть использованы числа, строковые значения, даты. Текстовые строки заключаются в двойные кавычки, даты – в знаки " #" (или одинарные кавычки (') в кодировке ANSI- 92). Например: 567, " Информатика", #1-Января-2004# ('1- Января-2004 ' В ANSI-92).

Константами являются неизменяющиеся значения, которые определены в Access, например, Истина ( True ), Ложь ( False ), Null.

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

Во многих случаях ссылка на конкретное значение должна указывать точное его местоположение в иерархии объектов базы данных, начиная с объекта верхнего уровня. Если необходимо указать ссылку на поле в конкретной таблице, форме, отчете, то перед именем поля ставится имя таблицы, формы, отчета, также заключенное в квадратные скобки и отделенное от имени поля восклицательным знаком. Например, ссылка на поле в таблице примет ВИД: [Имя таблицы]! [Имя поля].

Операторами сравнения и логическими операторами, использование которых допускается в выражении условия отбора, являются: =, <, >, < =, > =, < >, Like, And, Or, Not.

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

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

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

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

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

Like " Иванов*"

Логические операторы and, or, not. Условия отбора, заданные в одной строке, связываются по умолчанию с помощью логической операции " И", заданные в разных строках– с помощью логической операции " ИЛИ". Эти операции могут быть также заданы явно в выражении условия отбора с помощью операторов and и or соответственно. Оператор not задает логическое отрицание условия.

После ввода выражения в бланк и нажатия клавиши < Enter> Access выполняет синтаксический анализ выражения.

 

1 Создайте запрос: расчет зарплаты, добавив таблицы:

« Врачи »: поля - ID Врача, ФИО Врача, Должность;

« Штатное расписание »: поля – Зарплата, Надбавка.

В свободной столбце в строке Поле: нажмите правой кнопкой мыши, выберите .

В появившемся диалоговом окне Построитель выражения:

- выберите из списка таблицу «Штатное расписание»,

- из открывшегося списка полей щелкните по полю [Оклад], затем знак «+» и поле [Надбавка],

- нажмите < ОК> (см. рис. 29).

Рис. 29. Создание запроса с вычисляемым полем

 

- щелкните по кнопке ,

- сохраните запрос «Итого зарплата».

- в режиме Конструктора в строке Поле: выделите Выражение1: и переименуйте на «Итого: ».

 

2. Создайте запрос: выборка данных за определенный период,  добавив в таблицу «Журнал» поля:  ДатаПриема,  ФИО Врача, Услуга, Количество, ФИО Пациента.

 

В свободной столбце в строке Поле: нажмите правой кнопкой мыши, выберите .

В появившемся диалоговом окне Построитель выражения выберите:

Функции→ Встроенные функции→ Дата/время. Щелкните два раза кнопкой мыши < Month>. Выделите «number».

Далее выберите таблицу «Журнал» из списка окошка Построителя для выборки поля ДатаПриема, щелкнув по раскрывающемуся списку:

Таблицы → Журнал, ДатаПриема.

В поле построителя должно появиться следующее выражение:

Month([Журнал]! [ДатаПриема])

Нажмите < ОК> (см. рис. 30).

В этом же столбце в строке Условие отбора вести: [Введите номер месяца].

Нажмите кнопку .

Рис. 30. Создание запроса

 

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

 

3 Для создания запроса на выборку фамилий пациентов, рожденных после 1990 года:

добавьте поле ДатаРождения в таблицу «Пациенты» в формате Краткий формат даты.

Поле Year([Пациенты]! [ДатаРождения])

Условие отбора < [Год рождения]

 

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

 

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

 

Функция	Описание	Пример	Результат
Date ()	Получает текущую дату	Date ()	1/20/2006
Now ()	Получает текущую дату и время	Now ( )	1/20/2006 10: 16: 26 PM
DatePart ()	Извлекает часть даты (например, год, месяц или день в месяце)	DatePart(#1/20/ 2006#, " d" )	20
DateSerial()	Преобразует год, месяц и день в значение даты Access	DateSerial(2006, 5, 4)	5/4/2006
DateAdd ( )	Сдвигает дату на заданный интервал	DateAdd(" yyyy" , 2, #22/11/2006#)	22/11/2008
DateDiff ( )	Определяет интервал между двумя датами	DateDiff(" w", #10/15/2006#, #1/11/2007#)	12
MonthName ( )	Получает название, соответствующее номеру месяца (от 1 до 12)	MonthName (1)	" January"
WeekdayName ( )	Получает название, соответствующее номеру дня в неделе (от 1 до 7)	WeekdayName (1)	" Sunday"
Format ()	Преобразует дату в форматированный текст (используя любой формат даты, описанный в табл. 2.3)	Format(#27/04/ 2008#, " Long Date" )	" April 27, 2008"

 

В следующей таблице приведены примеры выражений и типичные варианты их применения:

Выражение	Назначение
=[ДатаРазмещения]-[ДатаИсполнения]	Вычисление в отчете разности между значениями даты в двух элементах управления «поле» («ДатаРазмещения» и «ДатаИсполнения»)
Date()	Задание текущей даты в качестве значения по умолчанию для поля даты/времени в таблице
ОбщаяСтоимость: CCur([Заказано].Цена*[Количество]*(1-[Скидка])/100)*100	Создание вычисляемого поля в запросе   Примечание: Функция Ccur , используемая в выражении, преобразует результаты вычислений в денежный формат.
Between #01.01.05# And #31.12.05#	Используется для задания условия отбора в запросе на поле даты/времени
=[Заказано].Form! ОтпускнаяЦена	Получение значения элемента управления «ОтпускнаяЦена» из подчиненной формы «Заказано» в форме «Заказы»
> 0	Задание условия на значение для числового поля в таблице – пользователи должны будут вводить положительные значения.

 

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

Для этого выберите поля ФИО врача и Количество. На панели инструментов нажмите  - Групповые операции. В столбце Количество в строке «Групповая операция » выбрать из выпадающего списка Sum. Просмотреть запрос и проверить правильность, используя таблицу Журнал и фильтр записей.

 

Таблица 1 – Назначение агрегатных функций

 

Функция	Назначение
Sum	Вычисляет сумму значений, содержащихся в поле
Avg	Вычисляет среднее арифметическое для всех значений поля
Count	Определяет число записей, содержащих значение в поле, не считая пустых (Null) значений
Min	Находит наименьшее значение поля
Max	Находит наибольшее значение поля
StDev	Определяет среднеквадратичное отклонение от среднего значения
Var	Вычисляет дисперсию значений поля

 

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

Для этого в режиме Конструктора выберите таблицу Врачи (поле: Должность добавьте два раза).

Выберите групповую операцию.

Во втором столбце в строке «Групповая операция» из выпадающего списка выберите функцию Count.

В результате запроса должны получиться следующие данные:

 

Вычисляемые поля в запросах

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

Создадим запрос с вычисляемым полем на примере отношения ПРЕДМЕТ.

Пусть необходимо выбрать из отношения кортежи, содержащие информацию о предметах, в которых общее число часов по предмету не совпадает с суммой часов лекций и практики. Для решения задачи рассчитаем разность между общим числом часов по предмету (атрибут ЧАСЫ) и суммой часов лекций (поле ЛЕК) и практики (поле ПР). В результирующее отношение включим только те записи, для которых эта разность не равна нулю.

Создадим запрос на выборку для отношения ПРЕДМЕТ. Для этого в окне базы данных выберем закладку Запросы и нажмем кнопку Создать. В окне Новый запрос выберем строку Конструктор . В окне Добавление таблицы выберем таблицу ПРЕДМЕТ и нажмем кнопку Добавить. Перетащим в бланк запроса поля НП, ПР, ЛЕК, ЧАСЫ.

Рис. 31. Вид бланка запроса

Создание вычисляемого поля. Для получения разности создадим вычисляемое поле в пустой ячейке строки ПОЛЕ, записав туда выражение: [ЧАСЫ]-[ПР]-[ЛЕК].

Для отбора записей с ненулевым значением разности в вычисляемое поле в строку Условие отбора введем: < > 0.

Рис. 32. Вычисляемое поле бланка запроса

После ввода выражения система формирует имя вычисляемого поля по умолчанию – Выражение №. Это имя вставится перед выражением. Для изменения имени установим курсор мыши в вычисляемое поле бланка запроса и нажмем правую кнопку мыши. В открывшемся контекстном меню выберем Свойства поля и в строку Подпись введем новое имя поля – ЧАСЫ не равны ПР+ЛЕК. Имя поля может быть исправлено также непосредственно в бланке запроса.

Рис. 33. Изменение имени вычисляемого поля

Рис. 34. Результат запроса с вычисляемым полем

 

 

Запросы на обновление

 

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

Необходимо помнить приведенные ниже правила.

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

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

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

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

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

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

Поля с типом данных «Счетчик». Значения в полях с типом данных «Счетчик» изменяются только при добавлении записи в таблицу.

Поля в запросах на объединение.

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

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

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

 

Пример создания запроса на обновление.

Создайте таблицы: Товар и Отгрузка.

Заполните поля таблиц. Поля ЦенаЗаЕд и Стоимость оставьте незаполненным! Они буду заполнены в результате выполнения запроса на обновление, который данные из таблицы « Товар» перенесет в таблицу « Отгрузка».

 

Запрос 1.Создайте запрос на обновление. Для этого:

Добавьте обе таблицы в запрос.

Выберите поле ЦенаЗаЕд (табл. « Отгрузка» ). Щелкните на панели инструментов кнопку   (обновление).

В строке «Обновление » щелкните правой кнопкой Построить.

В открывшемся диалоговом окне Построителя выражений выберите команду, используя таблицу « Товар»: [Товар]! [Цена].

В результате запроса данные поля Цена (таблица «Товар» ) отобразятся в поле ЦенаЗаЕд (таблица «Отгрузка» ).

 

Запрос 2. Создайте запрос на обновление поля Стоимость, данные для которого являются результатом Цена*Количество товара (см.рис. 35):

Рис. 3 5. Окно создания запроса на обновление

 

 

 7 Запросы на удаление.

Запрос на удаление удаляет определенные записи из таблиц БД!

Если требуется удалить данные из нескольких таблиц, и эти таблицы связаны, включите параметры Целостность данных и Каскадное удаление связанных записей для каждой из связей. Это позволит запросу удалять данные из таблиц со стороны отношения «один» и «многие».

Пример: Создание запроса на удаление записей из таблицы Отгрузка: количество отгруженного товара больше 5. (см. рис.).

Для этого:

a) добавьте таблицу Отгружено в запрос, выберите все поля, щелкнув по * («*» добавляет в запрос все поля таблицы),

b) добавьте поле Отгружено.

c) щелкните на панели инструментов по кнопке . Или выполните команду Запрос→ Удаление.

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

 

 

⇐ Предыдущая123Следующая ⇒

Поделиться: