Задание № 2 Анализ данных с помощью запросов

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

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

2а) в диалоговом окне «Новый запрос» из предложенных вариантов создания запроса выберите «Конструктор» и нажмите на кнопку «ОК» в нижней части окна, появится бланк запроса и диалоговое окно «Добавление таблицы» (рис. 7); в этом диалоговом окне вы должны выбрать таблицы, на основе которых будет создаваться запрос;

3а) выделите таблицу «Отделы» и нажмите кнопку «Добавить» (выделить – навести указатель мыши на слово «Отделы» и щелкнуть по левой кнопке мыши), затем выделите таблицу «Табель» и нажмите «Добавить», так же добавьте таблицу «Список»; после этого нажмите на кнопку «Закрыть»; диалоговое окно закроется, а в верхней части бланка запроса появятся выбранные вами таблицы (рис. 8);

4а) (следующий момент заключается в формировании полей запроса) наведите указатель мыши на поле «Табельный номер» из таблицы «Список» и нажмите на левую кнопку мыши, не отпуская кнопки, ведите указатель мыши в бланк запроса, т.е. перетащите поле «Табельный номер» в первое поле бланка запроса и отпустите кнопку мыши (рис. 8);

5а) из этой же таблицы в бланк запроса перетащите поля «Фамилия», «Имя», «Отчество», а из таблицы «Отделы» перетащите поле «Название».

П ервый этап по созданию запроса закончен, приступаем к следующему этапу.

Установление связи между таблицами

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

• Объединение – объединение информации из нескольких таблиц или запросов на основе совпадающих значений определенных полей. Например, информация из таблицы «Список» может быть объединена с данными из таблицы «Табель» на основе поля «Табельный номер».

• «Один к одному» - тип связи, использующийся для объединения двух таблиц на основе соответствия одной записи в первой таблице одной записи в другой таблице.

• «Один ко многим» – тип связи, когда одной записи в главной таблице соответствует несколько записей в подчиненной или связанной таблице. Например, один вид работы может выполнять несколько сотрудников, но конкретный сотрудник выполняет только один вид работы.

• «Многие ко многим» – тип связи при соотношении нескольких записей в одной таблице с несколькими записями в другой таблице.

Организация запроса на основе объединения полей таблиц выглядит таким образом:

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

2б) такие же действия проведите для объединения поля «Номер отдела» из таблицы «Табель» и поля «Номер отдела» из таблицы «Отделы» (рис. 8). Из рисунка 8 видно, что таблицы «Список» и «Отделы» связаны посредством таблицы «Табель»; запрос готов, для его просмотра выберите «Режим таблицы» в пункте меню «Вид»;

3б) после просмотра запроса, закройте его, для чего нажмите на крестик в верхнем правом углу запроса, причем на вопрос о сохранении макета запроса ответьте «Да», а на следующий вопрос об имени запроса – согласитесь с предложенным вариантом или дайте свое название сформированному запросу (условное название этого запроса «Учебный1»).

Примечание. Как удалить связь между таблицами ? Чтобы удалить связь, нужно навести указатель мыши на линию связи и нажать на левую кнопку мыши. При этом линия будет выделена, она станет толще, не передвигая мышь, нажмите на ее правую кнопку для вызова контекстного меню. В контекстном меню выберите пункт «Удалить связь».

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

Для организации запроса на основе связей «один к одному» и «один ко многим» необходимо определить ключевое поле в каждой таблице. Ключевым называют поле, которое однозначно определяет каждую запись в таблице и является для нее уникальной. Опираясь на ключевое поле текущей записи, можно получить все данные из нее. Очевидно, что для таблицы «Табель» и «Список» ключевым будет являться поле «Табельный номер», а для таблицы «Отделы» таким полем будет – «Номер отдела».

Чтобы создать ключевые поля нужно выйти на закладку «Таблицы» выделить первую из требующихся таблиц, а именно «Отделы», и открыть ее в режиме конструктора, для чего нажать на кнопку «Конструктор», находящуюся в правой части закладки. Откроется макет таблицы (рис. 9). Если указатель

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

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

1в) Откройте окно схемы данных, в котором отражаются все наложенные связи между таблицами базы данных; открыть это окно можно или с помощью команды «Схема данных», находящейся в пункте меню «Сервис», или с помощью кнопки «Схема данных» на панели инструментов; при открытии окна «Схема данных» появится диалоговое окно «Добавление таблицы», аналогичное тому, что продемонстрировано на рис. 7;

2в) с помощью этого окна добавьте в окно схемы данных три требуемых таблицы – «Отделы», «Табель» и «Список»; добавление таблиц осуществляется абсолютно так же, как рекомендуется в пункте 3а настоящего описания к лабораторной работе № 2;

3в) закройте диалоговое окно «Добавление таблицы» с помощью клавиши «Закрыть»;

4 в) находясь в окне «Схема данных», наведите указатель мыши на поле «Табельный номер» таблицы «Табель» и нажмите на левую кнопку мыши, не отпуская кнопки перетащите указанное поле на поле «Табельный номер» таблицы «Список»; появится диалоговое окно «Связи» (рис. 10);

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

6в) теперь таким же образом соедините поле «Номер отдела» из таблицы «Отделы» и поле «Номер отдела» из таблицы «Табель»; после закрытия диалогового окна «Связи» между таблицами появится связь в том виде, как показано на рис. 11; символы единички и бесконечности, расположенные у окончаний линии, – символизируют наличие связи «один ко многим»; созданная связь действительно является связью «один ко многим», так как в таблице «Табель» содержится информация о сотрудниках, которые могут работать в одном отделе; в этом случае несколько записей о сотрудниках из таблицы «Табель» будут соответствовать одной записи в таблице «Отделы» и именно той, что собственно указывает на номер отдела, где работают сотрудники.

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

Организация запроса на базе связей между таблицами «один к одному» и «один ко многим»

Д ля создания указанного запроса можно воспользоваться режимом конструктора, так как это было описано в пунктах 1а – 5а настоящего описания к лабораторной работе № 2. Причем, в верхней части запроса при выполнении этих пунктов будут отражены уже установленные связи. Поэтому останется только просмотреть запрос в режиме таблицы, для чего активизировать команду «режим таблицы» в пункте меню «Вид» и сохранить его.

Но можно воспользоваться и другим способом – запустить программу-мастер, специально предназначенную для создания запросов. Как это сделать ?

1г) Находясь на закладке запросы, нажать на кнопку «Создать», откроется диалоговое окно «Новый запрос», в котором содержится перечень вариантов для организации запроса;

2г) следует выбрать вариант «Простой запрос» и нажать кнопку «ОК» в нижней части окна; будет запущена программа-мастер, которая после небольшой паузы предъявит первое диалоговое окно «Создание простых запросов» (рис. 12);

3г) в поле выбора таблицы надо нажать на кнопку прокрутки и выбрать таблицу, содержащую поле, требуемое для запроса, в данном случае эта таблица «Список»;

4г) в окошке «Доступные поля» выбрать поле «Табельный номер» и нажать на кнопку выбора поля (рис. 12); поле «Табельный номер» появится в окошке «Выбранные поля»;

5г) аналогично выбрать поля «Фамилия», «Имя», «Отчество», а из таблицы «Отделы» поле «Название»; после выбора полей запроса нажать кнопку «Далее» в нижней части диалогового окна «Создание простых запросов»; появится второе диалоговое окно «Создание простых запросов»;

6г) в этом окне следует дать имя запросу и нажать на кнопку «Готово».

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

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

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