Запуск Access и создание пустой базы данных
Рассмотрим в качестве примера создание базы данных «Библиотека» на примере СУБД Microsoft Access. Допустим, в городе имеется библиотека, которая осуществляет выдачу книг читателям. База данных состоит из трех таблиц: «Книги», «Читатели» и «Учет выдачи и возврата книг».
Создаётся пустая база данных, затем в ней создаются необходимые таблицы в режиме конструктора. Для этого нужно открыть панель Таблицы и дважды щелкнуть на значке: Создание таблицы в режиме конструктора. Откроется бланк создания структуры таблицы. Для таблицы Книги вводятся поля, представленные в таблице 2.1.
Таблица 2.1 – Таблица «Книги» в режиме Конструктор
| Имя поля | Тип поля | Свойства поля |
| Регистрационный № книги | Числовой | Размер поля: целое Обязательное поле: Да Индексированное: Да (совпадения не допускаются) |
| Автор | Текстовый | Размер поля: 50 |
| Название | Текстовый | Размер поля: 50 |
| Тема | Текстовый | Размер поля: 50 |
| Издательство | Текстовый | Размер поля: 50 |
Для связи с будущими таблицами и обеспечения уникальности каждой строки таблицы необходимо задать ключевое поле. Ключевое поле – это поле базы данных, значение которого является уникальным. Критерию уникальности могут удовлетворять только записи в поле: Регистрационный № книги. Фамилии авторов, названия книг, а тем более тематика и издательство могут повторяться. Для задания ключевого поля необходимо выделить поле «Регистрационный № книги» и щелкнуть по нему правой кнопкой мыши. В открывшемся контекстном меню нужно выбрать пункт: Ключевое поле (рисунки 2.1-2.2).
Рисунок 2.1 – Задание ключевого поля
Слева от поля, заданного ключевым, появится значок 
.
Рисунок 2.2 – Ключевое поле
Сохраним таблицу Файл/Сохранить, задав ей имя: Книги.
Следующая таблица создаётся для учета читателей библиотеки. Структура таблицы Читатели представлена в таблице 2.2.
Таблица 2 – Таблица «Читатели» в режиме Конструктор
| Имя поля | Тип поля | Свойства поля |
| № читательского билета | Числовой | Размер поля: целое Обязательное поле: Да Индексированное: Да (совпадения не допускаются) |
| Фамилия | Текстовый | Размер поля: 50 |
| Адрес | Текстовый | Размер поля: 50 |
| Дата рождения | Дата/Время | |
| Паспортные данные | Текстовый | Размер поля: 15 |
В данной таблице явно претендуют на уникальность два поля – № читательского билета и Паспортные данные. Необходимо выбрать из них одно в качестве ключевого. Логично для таблицы, ведущей учет читателей библиотеки в качестве ключевого поля назначить ключевым поле «№ читательского билета».
Аналогично предыдущим создаётся таблица «Учет выдачи и возврата книг», представленная в таблице 2.3.
Таблица 2.3 – Таблица «Учёт выдачи и возврата книг» в режиме Конструктор
| Имя поля | Тип поля | Свойства поля |
| Регистрационный № книги | Числовой | Размер поля: целое Обязательное поле: Да Индексированное: Да (совпадения допускаются) |
| № читательского билета | Числовой | Размер поля: целое Обязательное поле: Да Индексированное: Да (совпадения допускаются) |
| Дата выдачи | Дата/Время | |
| Дата возврата | Дата/Время | |
| Фактическая дата возврата | Дата/Время |
Необходимо задать ключевое поле, прежде чем сохранить таблицу. Поскольку в данной таблице ни одно поле не может претендовать на уникальность, используем в качестве ключа комбинацию полей «Регистрационный № книги», «№ читательского билета» и «Дата выдачи». Такая комбинация носит название составного ключа. Выделите три поля, щелкая на поле левой клавишей мыши и, одновременно удерживая нажатой клавишу Ctrl, щелкните на кнопке ключевое поле панели инструментов 
. Составной ключ представлен на рисунке 2.3.
Рисунок 2.3 – Составной ключ таблицы «Учёт выдачи и возврата книг»
Прежде чем заполнять созданные таблицы данными, необходимо создать связи. Для этого нужно воспользоваться редактором связей Microsoft Access, который открывается в пункте главного меню Работа с базами данных/Схема данных. В открывшееся окно надо добавляются таблицы базы данных и связываются по определённым правилам: первичный ключ главной таблицы (справочника) связывается с внешним ключом подчинённой таблицы. В результате должны получиться две связи один-ко-многим, представленные на рисунке 2.4.
Рисунок 2.4 – Схема данных
Одну книгу могут брать много читателей в разное время и один читатель может брать в библиотеке много книг.
После сохранения схемы можно приступать к заполнению таблиц данными. Обратите внимание, что в таблицах-справочниках, представленных на схеме данных связью 1, заполняется пять – семь записей, а в подчинённой таблице, представленной на схеме данных связью многие, необходимо заполнить десять – пятнадцать записей.
Примеры заполнения таблиц для базы данных библиотека представлены на рисунках 2.5, 2.6, 2.7.
Рисунок 2.6 – Пример заполнения таблицы «Книги»
Рисунок 2.7 – Пример заполнения таблицы «Читатели»
Рисунок 2.8 – Пример заполнения таблицы «Учёт выдачи и возврата книг»
Разработка запросов
Запросы – это важнейший инструмент любой системы управления базами данных. Запросы служат для выборки данных из таблиц, удовлетворяющих заданному условию. Запросы позволяют комбинировать информацию, содержащуюся в различных таблицах. Такие запросы называются пассивными, или «запросами-выборками». Кроме этого, можно создавать запросы, модифицирующие данные в таблицах. Результат выполнения запроса отображается на экране в форме таблицы, внешне неотличимой от любой реальной таблицы базы данных.
Для создания запросов Microsoft Access предоставляет удобный в использовании «конструктор запросов», позволяющий сформировать запрос на базе одной или нескольких таблиц путем заполнения специального бланка запроса (рисунок 2.8). Окно конструктора запросов разделено на две панели. Верхняя панель содержит схему данных запроса, которая включает выбранные для данного запроса таблицы. Таблицы представлены списками полей. Нижняя панель является бланком запроса по образцу— QBE, который нужно заполнить.
При заполнении бланка требуется указать поля базовых таблиц, условия отбора данных и, при необходимости, задать выражения для их предварительной обработки. При этом можно использовать встроенный «построитель выражений», что еще более упрощает процесс создания запроса.
В Microsoft Access возможно создание четырех основных типов запросов на выбор данных из таблиц:
ü запрос на выборку извлекает данные из одной или несколько таблиц и отображает их на экране в виде таблицы;
ü запрос с параметром – разновидность запроса на выборку. При выполнении запроса Access отображает диалоговое окно, в которое пользователь должен ввести параметр – условие отбора;
ü итоговый – разновидность запроса на выборку. Используется в случае, когда интерес представляют не записи из таблицы, а итоговые данные по определенному полю, сгруппированные по критерию;
ü перекрестный запрос группирует данные из одной или нескольких таблиц и выводит их на экран в виде электронной таблицы.
Рисунок 2.8 – Бланк запроса
Бланк запроса по образцу (QBE) представлен в виде таблицы в нижней панели окна запроса. До формирования запроса эта таблица пуста. Каждый столбец бланка относится к одному полю, с которым нужно работать в запросе. Поля могут использоваться для включения их в результат выполнения запроса, для задания сортировки по ним, а также для задания условий отбора записей.
При заполнении бланка запроса необходимо:
· в строку Поле (Field) включить имена полей, используемых в запросе;
· в строке Вывод на экран (Show) отметить поля, которые должны быть включены в результирующую таблицу;
· в строке Условие отбора (Criteria) задать условия отбора записей;
· в строке Сортировка (Sort) выбрать порядок сортировки записей результата.
Каждый столбец бланка запроса соответствует одному из полей таблиц, на которых строится запрос. Кроме того, здесь может размещаться вычисляемое поле, значение которого вычисляется на основе значений других полей, или итоговое поле, использующее одну из встроенных групповых функций Access. Для включения нужных полей из таблиц в соответствующие столбцы запроса можно воспользоваться следующими приемами:
· в первой строке бланка запроса Поле(Field) щелчком мыши вызвать появление кнопки списка и, воспользовавшись ею, выбрать из списка нужное поле. Список содержит все поля таблиц, представленных в бланке запроса;
· перетащить нужное поле из списка полей таблицы в схеме данных запроса в первую строку бланка запроса. В списке полей каждой таблицы на первом месте стоит символ *, который означает «все поля таблицы». Этот пункт выбирается, если в запрос включаются все поля.
При модификации запроса для добавления поля в бланк запроса надо перетащить его с помощью мыши из таблицы в схеме данных в нужное место бланка. Все столбцы полей справа от него передвинутся на один столбец вправо. Для удаления поля в бланке запроса надо выделить удаляемый столбец, щелкнув кнопкой мыши в области маркировки столбца и нажать клавишу <DEL> или выполнить пункт меню Правка|Удалитьстолбец (Edit|Delete Column). Для перемещения поля в бланке надо выделить соответствующий столбец и перетащить его в новую позицию с помощью мыши. Столбец, на место которого перемещен новый, и все столбцы справа от него будут сдвинуты вправо. В ряде случаев удобно в бланке запроса наряду с именем поля отображать и имя соответствующей таблицы, например, когда поля имеют одинаковые имена в разных таблицах. Для отображения имен таблиц в строке бланка надо выполнить пункт меню Вид|Именатаблиц (View|Table Names) или нажать соответствующую кнопку на панели инструментов.
Условия отбора записей могут задаваться для одного или нескольких полей в соответствующей строке бланка запроса.
Условием отбора является выражение, которое состоит из операторов сравнения и операндов, используемых для сравнения.
В качестве операндов выражения могут использоваться: литералы, константы, идентификаторы (ссылки).
Литераломявляется значение, воспринимаемое буквально, а не как значение переменной или результат вычисления, например, число, строка, дата.
Константамиявляются неизменяющиеся значения, например, True, False, Да, Нет, Null (константы автоматически определяются в Access).
Идентификаторпредставляет собой ссылку на значение поля, элемент управления или свойство.
Идентификаторами могут быть имена полей, таблиц, запросов, форм, отчетов и т.д. Они должны заключаться в квадратные скобки. Во многих случаях Access производит автоматическую подстановку скобок.
В выражении условия отбора допускается использование операторов сравнения, логических и других операторов: =, <, >, ^, Between, In, Like, And, Or, Not, которые определяют операцию над одним или несколькими операндами.
Если выражение в условии отбора не содержит оператора, то по умолчанию используется оператор =. Текстовые значения в выражении вводятся в кавычках, если они содержат пробелы или знаки препинания. В противном случае кавычки можно не вводить, они будут добавлены автоматически. Допускается использование операторов шаблона - звездочка (*) и вопросительный знак (?).
Оператор Between позволяет задать интервал для числового значения. Например, Between 10 And 100 задает интервал от 10 до 100.
Оператор In позволяет выполнить проверку на равенство любому значению из списка, который задается в круглых скобках. Например, In («Математика», «Информатика», «История»).
Оператор Like позволяет использовать образцы, использующие символы шаблона, при поиске в текстовых полях. Например, Like «Иванов*».
Условия отбора, заданные в одной строке, связываются с помощью логической операции «И», заданные в разных строках - с помощью логической операции «ИЛИ». Эти операции могут быть заданы явно в выражении условия отбора с помощью операторов AND и OR соответственно. Сформировать условие отбора можно с помощью построителя выражений.
Перейти в окно Построитель выражений (Expression Builder) можно, нажав кнопку Построить(Build) на панели инструментов, или выбрав команду Построить(Build...) в контекстно-зависимом меню. Курсор мыши должен быть установлен в ячейке ввода условия отбора. После ввода выражения в бланк и нажатия клавиши <Enter> Access выполняет синтаксический анализ выражения и отображает его в соответствии с результатами этого анализа.
Также, как уже отмечалось ранее, Microsoft Access позволяет создавать запросы, модифицирующие данные в таблицах.
Рассмотрим в качестве примера создание запросов на выбор данных из одной и нескольких таблиц с условием отбора.
Например, нам нужно выбрать из всех имеющихся в фонде библиотеки книг только учебники и вывести их на экран. Для этого открываем бланк конструктора запроса Создание запроса в режиме конструктора и добавляем таблицу «Книги». Выбираем необходимые поля, например, Автор, Название, Тема. В строке Условие отбора в поле Тема пишем: Учебник. Бланк запроса представлен на рисунке 2.9.
Рисунок 2.9 – Бланк запроса на выбор данных из одной таблицы с условием отбора
В результате выполнения запроса на экране только книги – учебники. Поскольку мы знаем, что в этом запросе будут отображаться на экране только учебники, можно убирать флажок Вывод на экран (рисунок 2.10).
Рисунок 2.10 – Запрос на выборку
В результате выполнения запроса у вас должна получиться следующая таблица (рисунок 2.11).
Рисунок 2.11 – Результат выполнения запроса
Рассмотрим запрос, в котором нужно выбрать и вывести на экран фамилии и адреса читателей, получавших книги в библиотеке в феврале месяце. Поскольку в таблице «Учёт выдачи и возврата книг» да та выдачи представлена в формате «День/Месяц/Год», воспользуемся оператором Between, задающим диапазон отбора.
Запрос предусматривает выбор данных из двух таблиц «Читатели» и «Учет выдачи и возврата книг». Связь между таблицами отобразится в запросе автоматически (рисунок 2.12).
Рисунок 2.12 – Бланк запроса «Дата выдачи: февраль»
Добавьте в поле запроса поля Фамилия и Адрес из таблицы Читатели и поле Дата выдачи из таблицы Учет выдачи и возврата книг. Необходимо задать условие отбора по полю Дата выдачи – февраль. Но так мы записать не можем, воспользуемся оператором Between. Он позволяет задавать верхнюю и нижнюю границы значений поля. В поле Дата выдачи в условии отбора запишите Between 01.02.2007 and 28.02.2007 и запустите запрос на выполнение. Результат выполнения запроса показан на рисунке 2.13.
Рисунок 2.13 – Результат выполнения запроса «Дата выдачи: февраль».
Microsoft Access позволяет создавать параметрические запросы, когда пользователь вводит условие отбора в диалоговом окне при каждом запуске запроса. Например, нам нужно выдать на экран все данные по книгам, автора которых пользователь вводит с клавиатуры.
Добавьте в бланк запроса таблицу «Книги». Выберите все поля таблицы, как показано на рисунке 2.14.
Рисунок 2.14 – Бланк запроса «Книги одного автора»
В строке Условие отбора в поле Автор напишите [Введите автора книги] (рисунок 2.15).
Рисунок 2.15 –Условие отбора для запроса с параметром
После запуска запроса на выполнение открывается диалоговое окно для ввода параметра. Введите в него фамилию автора, например, Желязны (рисунок 2.16). Все данные о книгах указанного автора будут выведены на экран (Рисунок 2.17).
Рисунок 2.16 – Ввод параметра в диалоговое окно
Рисунок 2.17 – Результат выполнения параметрического запроса
Как уже упоминалось ранее, в Microsoft Access можно создавать перекрёстные запросы. По сути это те же запросы-выборки, но результат выводится на экран в виде электронной таблицы Excel. Например, нам необходимо подсчитать и вывести на экран данные, сколько раз каждый читатель библиотеки брал книги по определенным темам.
Для создания перекрестного запроса нужно создать запрос в режиме конструктора, добавив в запрос все три таблицы. Связи между таблицами отображаются автоматически (рисунок 2.18). Добавьте в запрос поля, как показано на рисунке.
Рисунок 2.18 – Бланк запроса
Затем нужно изменить тип запроса: меню Запрос/Перекрестный. Появились новые стоки – Групповые операции и Перекрестная таблица. В строке Перекрестная таблица назначьте: поле Фамилия – Заголовки строк, поле Тема – Заголовки столбцов, как показано на рисунке 2.19.
Рисунок 2.19 – Создание перекрёстного запроса
Для поля «№ читательского билета» в стоке Групповые операции выберите функцию Count, а в строке Перекрестная таблица – Значение. Запустите запрос на выполнение. Результат отобразится в виде таблицы (рисунок 2.20).
Рисунок 2.20 – Результат выполнения перекрёстного запроса
Рассмотрим создание итогового запроса. Для создания итогового запроса с использованием групповых операций формируется запрос на выборку. В бланк запроса включаются поля, по которым надо произвести группировку, и поля, по которым надо произвести статистические вычисления. Выполняется команда Вид|Групповые операции или на панели инструментов конструктора запросов нажимается кнопка Групповые операции. Также можно нажать правую кнопку мыши и выбрать в контекстном менюГрупповая операция. Курсор мыши при этом должен быть установлен на бланке запроса. В бланке запроса после выполнения указанных действий появляется строка Групповая операция (Total), в которой для всех полей записано Группировка. Для групповых вычислений по некоторому полю нужно заменить в нем слово Группировка на нужную статистическую функцию. Выбрать нужную функцию можно через раскрывающийся в поле список.
Групповые операции позволяют выделить группы записей с одинаковыми значениями в указанных полях и использовать для других полей этих групп определенную статистическую функцию. В Access предусматривается девять таких функций:
Sum – сумма значений некоторого поля для группы;
Avg – среднее арифметическое от всех значений поля в группе;
Max, Min – максимальное, минимальное значение поля в группе;
Count – число значений поля в группе без учета пустых значений;
StDev – среднеквадратичное отклонение от среднего значения поля в группе;
Var – дисперсия значений поля в группе;
First, Last – значение поля из первой, последней записи в группе.
Результат запроса с использованием групповых операций содержит по одной записи для каждой группы. В запрос включаются поля, по которым производится группировка, и поля, для которых выполняются статистические функции.
Рассмотрим технологию конструирования однотабличного запроса с групповой операцией на примере таблицы «Книги». Нам необходимо подсчитать количество книг каждого издательства в библиотеке.
Разработка форм
Для ввода данных в таблицы, корректировки данных и просмотра их на экране используются формы. Форма – удобное экранное средство для просмотра содержимого таблиц, а также для редактирования, пополнения и удаления содержащихся в них данных. Формы можно создавать как на основе таблиц, так и на базе запросов – виртуальных таблиц, формируемых в результате выполнения запросов-выборок.
Простейшая форма позволяет просматривать и редактировать записи единственной таблицы, более сложные формы могут иметь иерархическую структуру и отображать записи нескольких связанных таблиц одновременно. Например, на основе двух связанных таблиц можно создать форму, в которой при выборе определенной записи из главной таблицы автоматически будут выводиться все связанные с ней записи подчиненной таблицы.
Форма представляется на экране в виде окна, состоящего из трех основных разделов: заголовка формы, области данных и области примечаний. Заголовок располагается в верхней части формы и обычно содержит ее название и элементы графического оформления. В области примечаний, занимающей нижнюю часть окна, приводятся инструкции по работе с формой, а также располагаются элементы управления – кнопки, переключатели и пр. Область данных предназначена для отображения информации из таблиц, связанных с формой.
Формы можно создать с помощью мастера форм и при помощи конструктора форм. Воспользуемся при создании простой формы мастером форм. Форма «Книги», созданная с помощью мастера, представлена на рисунке 2.24.
Рисунок 2.24 – Форма «Книги»
Разработка отчётов
Для вывода данных на печать в Microsoft Access предусмотрены отчёты. Отчет – это удобное средство для представления информации, извлеченной из базы данных, в виде печатного документа. Отчет объединяет в себе функции экранных форм и запросов-выборок, предоставляя при этом более богатые возможности по оформлению, группированию данных, групповым вычислениям.
В отличие от итогового запроса, в отчете каждая группа представляется отдельно и может иметь свой заголовок и примечания. При этом допускается до 10 иерархических уровней группировки.
Групповые операции (подведение итогов) в отчете можно производить не только внутри одной группы, но и для нескольких групп одновременно. В отчет можно внедрить подчиненные отчеты или подчиненные формы.
Создание отчета производится на основе таблиц БД и (или) сформированных ранее запросов-выборок. Для этой цели в системе Access имеется специальный конструктор, а также мастер отчетов, позволяющий быстро сформировать отчет, используя один из типовых стилей. Рекомендуется создавать отчет в два этапа: вначале создать отчет с помощью мастера, а затем настроить его в режиме конструктора.
После завершения конструирования и сохранения отчета он готов к просмотру и печати. В режиме просмотра отчета предоставляется полноценный набор функций работы с печатными документами: выбор принтера, настройка параметров страницы, межстрочных интервалов, масштабирование, печать и др.
Отчёт, созданный на основе таблицы «Книги», представлен на рисунке 2.25.
Рисунок 2.25 – Отчёт
Список литературы
1. Волков, В. Б. Понятный самоучитель Excel 2010 [Текст] / В. Б. Волков. – Питер, 2011. – 256 с.
2. Стоцкий, Ю. А. Самоучитель Microsoft Office Excel 2010 [Текст] / Ю. А. Стоцкий, А. А. Васильев, И. С. Телина – СПб.: Питер, 2011. 432 с.
3. Уокенбах, Дж. Microsoft Excel 2010. Библия пользователя [Текст] / Дж.Уокенбах; Пер. с англ. – М. : Вильямс, 2011. – 912 с.
4. Сеннов, А. С. MS Access 2010. Учебный курс [Текст] / А. С. Сенов. М.; СПб. ; Нижний Новгород : Питер, 2010. – 266 с.
5. Информатика. Базовый курс / под ред. С. В. Симоновича. – 2-е изд. – СПб. : Питер, 2013. – 640 с.
6. Малыхина, М. П. Базы данных: основы, проектирование, использование [Текст] / М. П. Малыхина. – СПб. : БХВ-Петербург, 2007. – 512 с.
7. Бакаревич, Ю. Самоучитель Access 2010 [Текст] / Ю. Бакаревич, Н. Пушкина. – СПб. : Питер, 2011. – 432 с.
Содержание
1 Microsoft Excel 2010. 3
1.1 Знакомство с программой Microsoft Excel 2010. 3
1.2 Ввод и редактирование данных в ячейках. 3
1.3 Автозаполнение. 5
1.4 Работа с ячейками. 6
1.5 Работа с листами. 6
1.6 Форматирование ячеек. 6
1.7 Проведение расчетов. 6
1.8 Относительная и абсолютная адресация. 8
1.9 Построение графиков и диаграмм. 8
2 Microsoft Access 2010. 10
2.1. Последовательность действий при создании новой базы данных. 11
2.2. Запуск Access и создание пустой базы данных. 12
2.3. Разработка запросов. 15
2.4. Разработка форм. 24
2.5. Разработка отчётов. 25
Список литературы.. 26
Бекишева Марина Борисовна
Соколова Наталья Николаевна