Спасибо, что просмотрели мой блог

6. Запросы в Аccess 2007.

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

1. Запросы  могут  обеспечивать  не  только  поиск  данных,  которые  соответствуют  определенным  критериям,  как  это  осуществляется  во время фильтрации, но и одновременное выполнение операций над  данными и сохранение результатов поиска.
2. Следует помнить, что формировать запросы с использованием нескольких таблиц можно только при наличии связей между выбранными таблицами. Эти связи отображаются в верхней части бланка запроса на выборку

Основные  операции,  которые  может  осуществить  пользователь  с  использованием запросов:

1. создание  новых  таблиц  на  основе  анализа  данных  в  уже  существующих таблицах базы данных;
2. вычисление  обобщенных  данных  (суммы,  максимального  или  минимального значения и т. п.) для заданных полей;
3. нахождение  значений  новых  свойств  (проведение  вычислений),  используя  данные из  разных таблиц или запросов, например нахождение плотности  населения  определенной  страны  на  основе  площади  и  количества населения;
4. внесение изменений в уже существующие таблицы (обновление данных, вставка и удаление записей и т. п.).

В  зависимости  от  назначения  запросов их разделяют на: 

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

Способы создания запросов.

• создание  запросов с помощью мастера запросов ⇒ вкл. Создание - гр.Другие -  "Мастер запросов";
• второй способ создания запросов – в режиме конструктора ⇒ вкл. Создание - гр.Другие -  "Конструктор  запросов";

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

Выражения могут содержать:

• идентификаторы  -  имена  полей  таблиц  и  элементов  управления  в  формах и отчетах;
• операторы  -  последовательность  символов  для  обозначения  операций.

Различают  операторы:

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

При создании выражений следует соблюдать определенные правила:

• имена полей и другие идентификаторы записываются в квадратных скобках;
• при  ссылке  на  поле  определенной  таблицы  используется  восклицательный  знак,  например  выражение  [Товары]![Код  товара]  содержит  ссыл­ку на поле Код товара таблицы Товары;
•   текст записывается в кавычках " ".

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

I. Запросы на выборку.  Рассмотрим  создание  запроса  на  выборку  с  использованием  Мастера  запросов.( приложение - таблица  "Країна" базы данных "Страны мира")

 Для базы данных Страны мира создадим запрос, который бы содержал данные о площади стран.

Для создания запроса необходимо открыть базу  данных  Страны  мира  и  выбрать  кнопку  Мастер  запроса   группы  Другие  вкладки  Создание. 

 Далее  следует  выполнить  такую  последовательность действий:

1.  Выбрать в окне Новый запрос в списке видов мастеров запросов Простой запрос, после чего выбрать кнопку ОК.

2.  Выбрать  в  списке  Таблицы  и  запросы  окна  Создание  простых  запросов  таблицу, на основе которой будет создан запрос, например таблицу "Країни".

3.  Переместить из списка Доступные поля в список Выбранные поля нужные имена полей, например Название, Часть света, Площадь.

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

           

5.  Выбрать  при  необходимости  кнопку  Итоги  и  установить  для  каждого  из  полей  нужные  операции  сведения  установлением  соответствующих меток флажков. Выбрать кнопку ОК.
6.  Ввести имя запроса в поле "Задайте имя запроса", например Площадь стран мира.
7.  Выбрать  вариант  продолжения  работы  с  запросом  -  открыть  запрос для  просмотра  данных или  изменить  макет  запроса выбором  соответствующего переключателя.
8.  Выбрать кнопку Готово.

 

• Если  вы  выбрали  вариант  открыть  запрос  для  просмотра  данных, то  на экран  будет  выведена  таблица  результатов  запроса.  Это  новая  таблица,  которая содержит поля, выбранные при конструировании запроса.

итог: 

• Если  же  избран  второй  вариант  -  изменить  макет  запроса, то  новый  запрос откроется в представлении Конструктор. В этом представлении в верхней части рабочего поля размещен список полей таблицы, на основе которой был создан запрос.

  

5. Форматы типов данных в Access 2007.

Для определения поля таблицы обязательно задаются Имя поля (Field Name) и Тип данных (Data Type).

Имя поля (Field Name). Каждое поле в таблице должно иметь уникальное имя, удовлетворяющее соглашениям об именах объектов в Access. Оно является комбинацией из букв, цифр, пробелов и специальных символов, за исключением точки (.), восклицательного знака (!), надстрочного знака (`) и квадратных скобок ([ ]). Имя не может начинаться с пробела и содержать управляющие символы с кодами ASCII от 0 до 31. Максимальная длина имени 64 символа.



Тип данных в MS Access (Data Type). Тип данных определяется значениями, которые предполагается хранить в поле, и операциями, которые будут выполняться с этими значениями. В Access допускается использование двенадцати типов данных.

Рассмотрим вкратце типы данных в MS Access, виды, назначение и допустимый размер  данных, которые могут назначаться полям таблицы в Access.

• Текстовый (Text) — используется для хранения текста или комбинаций алфавитно-цифровых знаков, не применяемых в расчетах (например, код товара). Максимальная длина поля 255 знаков.
• Поле МЕМО (Memo) — используется для хранения обычного текста или комбинаций алфавитно-цифровых знаков длиной более 255 знаков. Поля с этим типом данных в базах данных формата Access 2007 поддерживают также форматирование текста. Это единственный в Access тип данных, обеспечивающий встроенную поддержку отображения и хранения форматированного текста. Максимальный размер поля 1 Гбайт знаков или 2 Гбайт памяти (2 байта на знак) при программном заполнении полей, и 65 535 знаков при вводе данных вручную в поле и в любой элемент управления, связанный с этим полем.
• Числовой (Number) — служит для хранения числовых значений (целых или дробных), предназначенных для вычислений, исключением являются денежные значения, для которых используется тип данных Денежный (Currency). Размер поля 1, 2, 4 и 8 байтов, или 16 байтов (если используется для кода репликации) зависит от типа чисел, вводимых в поле.
• Дата/время (Date/Time) — используется для хранения значений даты и времени в виде 8-байтовых чисел двойной точности с плавающей запятой. Целая часть значения, расположенная слева от десятичной запятой, представляет собой дату. Дробная часть, расположенная справа от десятичной запятой, — это время. Хранение значений даты и времени в числовом формате позволяет выполнять различные вычисления с этими данными.
• Денежный (Currency) — используется для хранения денежных значений в виде 8-байтовых чисел с точностью до четырех знаков после запятой. Этот тип данных применяется для хранения финансовых данных и в тех случаях, когда значения не должны округляться.
• Счетчик (AutoNumber) — используется для уникальных числовых 4-байтовых значений, которые автоматически вводит Access при добавлении записи. Вводимые числа могут последовательно увеличиваться на указанное приращение или выбираться случайно. Обычно используются в первичных ключах.
• Логический (Yes/No) — применяется для хранения логических значений, которые могут содержать одно из двух значений: Да/Нет, Истина/Ложь или Вкл/Выкл. (8 битов = 1 байт). Используется 1 для значений Да и 0 для значений Нет. Размер равен 1 биту.
• Поле объекта OLE (OLE Object) — используется для хранения изображений, документов, диаграмм и других объектов из приложений MS Office и других программ Windows в виде растровых изображений, которые затем отображаются в элементах управления форм или отчетов, связанных с этим полем таблицы.
  Чтобы в Access просматривать эти изображения, необходимо, чтобы на компьютере, использующем базу данных, был зарегистрирован OLE-сервер (про-грамма, поддерживающая этот тип файлов). Если для данного типа файлов OLE-сервер не зарегистрирован, отображается значок поврежденного изображения.


• Гиперссылка (Hyperlink) — применяется для хранения ссылок на Web-узлы (URL-адреса), на узлы или файлы интрасети или локальной сети (UNC-адреса — стандартного формата записи пути), а также на узлы или файлы локального компьютера. Кроме того, можно использовать ссылку на объекты Access, хранящиеся в базе данных. Может хранить до 1 Гбайт данных.
• Вложение (Attachment) — используется для вложения в поле записи файлов изображений, электронных таблиц, документов, диаграмм и других файлов поддерживаемых типов точно так же, как в сообщения электронной почты. Вложенные файлы можно просматривать и редактировать в соответствии с заданными для поля параметрами. Эти поля не имеют ограничений, связанных с отсутствием зарегистрированных OLE-серверов. Более рационально используют место для хранения, чем поля с типом данных Поле объекта OLE (OLE Object), поскольку не создают растровые изображения исходного файла. Максимальная длина поля для сжатых вложений — 2 Гбайт, для несжатых — примерно 700 Кбайт в зависимости от степени возможного сжатия вложения.
• Вычисляемый (Calculated) — предназначен для создания вычисляемых полей: числовых, текстовых, денежных, дата/время, логических. Значение вычисляемого поля определяется выражением, записанным в поле и использующим другие поля текущей записи, некоторые встроенные функции и константы, связанные арифметическими, логическими или строковыми операторами.
• Мастер подстановок (Lookup Wizard) или Подстановка и отношения (Lookup & Relationship) — вызывает мастера подстановок, с помощью которого можно создать поле, позволяющее выбрать значения из списка, построенного на основе значений поля другой таблицы, запроса или фиксированного набора значений. Такое поле отображается как поле со списком. Если список построен на основе поля таблицы или запроса, тип данных и размер создаваемого поля определяется типом данных и размером привязанного столбца; если на основе набора значений — размером текстового поля, содержащего значение. Кроме того, мастер подстановок позволяет определить связь таблиц и включить проверку связной целостности данных.

4. Фильтр в Access 2007.

Отбор данных с помощью фильтра

Фильтр - это набор условий, применяемых для отбора подмножества записей. В Access 2007 существуют фильтры четырех типов: фильтр по выделенному фрагменту, обычный, расширенный фильтр и фильтр по вводу.

Фильтр по выделенному фрагменту - это способ быстрого отбора записей по выделенному образцу. Например, нужно просмотреть в таблице записи только о доцентах. Для этого необходимо выделить слово доцент в любой из записей, щелкнуть мышью на стрелочке рядом с кнопкой в разделе Сортировка и фильтр вкладки <Главная>, и Ассеss выберет только те записи, для которых значение в столбце <Должность> равно Доцент.

Аналогичный результат получится, если использовать кнопку <Выделение> - , расположенной в разделе Сортировка и фильтр вкладки Главная. При выборе команды Равно “Доцент” (рис. 1.3) будет выделена таблица, содержащая только записи, в котором поле <<Должность>> имеет значение Доцент. При нажатии на кнопку <Удалить фильтр> вновь будет выведена вся таблица.

Рис. 1.3. Создание фильтра по выделению.

Обратите внимание, что в строке состояния окна таблицы присутствуют слова С Фильтром. В дополнение к этому кнопка <Применить фильтр> затенена, а это означает, что используется фильтр. При отключении этой кнопки все фильтры будут сняты.

Фильтр по выделенному может собирать вместе критерии выбора при каждом использовании кнопки <Выделение>. Например, можно выделить должность доцент, а затем дисциплину Информатика (конечно, если такая дисциплина присутствует в вашейтаблице). В этом случае появятся только записи о доцентах, которые преподают информатику.

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

После щелчка по кнопке -<Фильтр> - отображается диалоговое окно установки параметров фильтра (рис.1.4). Можно просто отменить установку флажкаВыделить все с последующей установкой флажка напротив той записи, которая будет отображена на экране. В результате на экране появится именно та запись, напротив которой был установлен флажок.

Рис.1.4. Выбор записей, которые будут отображены на экране

В окне установки параметров фильтра имеется также список Текстовые фильтры. После раскрытия этого списка отображается набор предопределенных текстовых фильтров (рис.1.5). Выбрать требуемый фильтр можно, щелкнув на соответствующем варианте мышью.

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

3. Создание форм в Access.

Создание форм

Для удобства ввода информации в таблицы применяются формы. В версии Access 2007 можно воспользоваться заготовками форм, соответствующие кнопки расположены на ленте <Создание>. Первая заготовка используется для создания формы, в которую можно будет вводить информацию только по одной строке соответствующей таблицы за один раз.

Созданная при помощи такой заготовки форма для таблицы <Продукты> выглядит следующим образом.

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

Более предпочтительна форма, созданная с помощью следующей заготовки.

Как видно из комментария к команде на создание такой формы, она позволяет видеть на экране данные уже введенные в таблицу и поля для ввода. Созданная при помощи такой заготовки форма для таблицы <Продукты> выглядит следующим образом. Обратите внимание на ошибку комментария: Таблица расположена снизу, а поля формы сверху окна формы.

Промежуточным вариантом по удобству работы среди вышеописанных форм можно считать форму, созданную из третьей заготовки (Рис. 02_3).

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

Используя команду <Разделенная форма> ленты <Создать> создаем форму для таблицы <Продукты> и <Заказчики>.

После создания формы продукты дополните данные о предлагаемых продуктах.После внесения данных в форму закройте ее, щелкнув правой кнопкой мыши по ярлыку формы и выбрав в открывшемся контекстном меню команду <Закрыть>.

Аналогичным образом дополните информацию о заказчиках.

После внесения данных в форму закройте её.

2. Создание отчетов в Access 2007.

Итак!

Для того чтобы иметь возможность распечатать данные, имеющиеся в базе данных, используются отчеты. На первом этапе освоения программы Access целесообразно воспользоваться командой <Мастер отчетов> расположенной на ленте <Создать>.

В процессе работы <Мастер отчетов> предложит выбрать из списка доступные поля (см. Рис. 03_2), для чего можно воспользоваться стрелками влево и вправо, выделяя нужную строку из списка курсором.

Мы переносим в правую часть все доступные поля.

На следующем шаге мы можем выбрать способ сортировки информации в будущем отчете. Выбираем сортировка в алфавитном порядке (по возрастанию) для поля <Наименование продукта>.

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

Следующий шаг <Мастера отчетов> предполагает выбор стиля (т.е. внешнего вида будущего отчета) - выберете тот, который вам больше подходит.

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

Данный выбор связан с тем, что Мастер отчетов создал поле <Наименование продукта> очень малой ширины при том, что справа на листе есть свободное место.

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

Аналогично создаем отчет для таблицы <Заказчики>. Если вы выполните все предыдущие этапы, то отчет <Заказчики> должен иметь такой вид, как на рисунке ниже, возможно отличаясь только выбранным стилем.

1. Создание базы данных (таблиц и связей между ними) в Access 2007

Приступим! Запускаем приложение Access 2007. Открывается окно приложение Access 2007 на странице Приступая к работе с Microsoft Access 2007. В разделе Новая пустая база данных щелкаем на пиктограмме Новая база данных. В правой части окна появится информация об имени файла и указана директория для его хранения. По умолчанию имя файла - База данных1.accdb.Далее необходимо щелкнуть на кнопке Создать, чтобы создать пустую базу данных. При создании новой пустой базы данных окно приложения Access 2007 открывается на контекстной вкладке "Режим таблицы". В окне отображается новая пустая таблица с именем Таблица 1 в режиме таблица, представленная ниже.

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

Откроется окно Сохранение, в котором надо указать имя и нажать кнопку ОК.

Откроется таблица в режиме Конструктор.

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

Поскольку мы создаем электронный магазин, то нам понадобиться таблица для учета наших продуктов. Для этого в первой строке второго столбца «Добавить поле» вы вписываете наименование первого информационного продукта. Допустим, это будет «Стратегия продаж в Интернете». После ввода текста в ячейку второго столбца с правой стороны появился третий столбец с наименованием «Добавить поле». Второй столбец получил по умолчанию наименование «Поле 1». Для удобства работы с таблицей переименуем этот столбец, для чего щелкните мышью на наименовании и после включения текстового курсора введите текст «Наименование продукта». Поскольку в ячейку таблицы был введен текст «Стратегия продаж в Интернете», то программа автоматически определила тип данных как «Текстовый».

Аналогично в третий столбец впишем цену продукта с точностью до четырех знаков 12,3456, а в четвертый размер файла в килобайтах – 1300 без указания единиц измерения. Переименуем соответствующие поля, назвав их «Цена» и «Размер файла». Для удобства чтения данных о размере файла установите формат отображения данных «С разделителями разрядов».

Изменить количество знаков после запятой можно, используя кнопки «Увеличить разрядность», «Уменьшить разрядность».

Сохраните таблицу, нажав кнопку «Сохранить» в верхнем левом углу окна или комбинацию клавиш (Ctrl + S) клавиатуры. В поле «Имя таблицы» укажите «Продукты».

Аналогичным образом создайте таблицу «Заказчики», используя команду «Таблица» ленты «Создание» 

Предполагаем, что наши заказчики будут рассчитываться за покупки с использованием системы электронных платежей WebMoney. Нам потребуется указать Для учета этой информации нам потребуются поля «Индекс кошелька» с текстовым типом данных, «№ кошелька» - с числовым типом данных и «Дата регистрации» с типом данных «Дата/время» в кратком формате даты.

Для удобства ввода дат справа от ячейки с типом данных «Дата/время» располагается кнопка электронного календаря. Щелчок мышкой по выбранной дате вводит дату в ячейку. Заполнив первую строку, сохраните таблицу «Заказчики».

Создание форм

Для удобства ввода информации в таблицы применяются формы. В версии Access 2007 можно воспользоваться заготовками форм, соответствующие кнопки расположены на ленте «Создание». Первая заготовка используется для создания формы, в которую можно будет вводить информацию только по одной строке соответствующей таблицы за один раз.

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

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

Более предпочтительна форма, созданная с помощью следующей заготовки.

Как видно из комментария к команде на создание такой формы, она позволяет видеть на экране данные уже введенные в таблицу и поля для ввода. Созданная при помощи такой заготовки форма для таблицы «Продукты» выглядит следующим образом. Обратите внимание на ошибку комментария: Таблица расположена снизу, а поля формы сверху окна формы.

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

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

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

После создания формы продукты дополните данные о предлагаемых продуктах.