Редактирование структуры таблицы

Для редактирования структуры таблицы, т. е. добавление новых полей или удаление некоторых полей из таблиц необходимо из режима таблицы перейти в режим конструирования таблицы для этого необходимо выделить пиктограмму (значок) нужной таблицы и воспользоваться кнопкой «Конструктор» в окне объектов БД. Затем можно добавить новое поле в таблицу, если выполнить щелчок правой кнопкой «мыши» по полю перед которым необходимо вставить новое поле и в контекстном меню выбрать пункт «Добавить строки». Для удаления поля необходимо выполнить по нему щелчок правой кнопкой «мыши» и в контекстном меню выбрать пункт «Удалить строки». Также для добавления (удаления) полей можно пользоваться пунктами меню «Вставка» - «Строки» («Правка» – «Удалить строку») или кнопками «Добавить строки» («Удалить строки») .

 

Связи между таблицами

Структура БД в СУБД Access всегда разрабатывается таким об­разом, чтобы каждая таблица, которая в ней находится, не содер­жала избыточной информации. Например, в БД Образование не­обходимо хранить данные об оценках, которые получил каждый студент по тому или иному предмету. Как следствие, нужно так­же каким-то образом хранить и названия предметов, по которым эти оценки ставятся. Кроме этого, необходимо хранить фамилию, имя и отчество преподавателей с указанием предмета, который ведет каждый из них. Если для этих целей будет использоваться одна таблица, то станет очевидным нерациональное использова­ние памяти компьютера, так как для каждого студента придется хранить в соответствующей записи названия предметов и фами­лии преподавателей, которые будут повторяться много раз. По­этому указанную информацию необходимо разбить на несколько таблиц, которые будут между собой взаимосвязаны, а именно:

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

Преподаватели – информация о преподавателе, в частности уникальный код, используемый при хранении предметов;

Предметы – информация о предмете, в частности уникальный код преподавателя, который ведет этот предмет, а также уникальный код предмета, используемый при хранении оценок;

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

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

Для установки связей необходимо воспользоваться режимом Схема данных, который устанавливается командой Сер­вис – Схема данных или кнопкой Схема данных на панели инструментов. При выполнении данной команды на экране поя­вится диалого­вое окно Добавление таблицы в котором на вклад­ке Таблицы нужно добавить поочередно все таблицы между которыми необходимо установить связь в схему данных с помощью кнопки Добавить. Затем следует за­крыть окно Добавление таблицы. Чтобы установить связь ме­жду таблицами необходимо выделить в окне Схема данных поле в одной из таблиц БД и с помощью мыши «перетащить» его изображение на поле в другой таблице БД и затем отпустить кнопку мыши. При этом появится диалоговое окно Изменение связей, в котором для обеих таблиц должно быть указано поле, по которому устанавливается связь. Причем при установлении связи между двумя таблицами одна из таблиц всегда является «главной», а другая «подчиненной». «Главной» становится та таблица, из которой вы перетаскивали поле, «подчиненной» — та таблица, куда вы его перетаскивали. Одна и та же таблица может выступать в одной связи как главная, а в другой – как подчиненная. Может быть, как несколько подчиненных таблиц у одной таблицы, так и несколько главных таблиц для одной подчиненной. Стоит подчеркнуть, что связь между таблицами может быть установлена для полей имеющих одинаковые за­писи, типы данных, длины полей и значения.

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

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

Если вы хотите, чтобы Access поддерживал целостность связей между двумя таблицами (связь между таблицами будет сохранена и после закрытия СУБД Access), установите флажок «Обеспечение целостности данных». Если установлен флажок Обеспечение целостности данных, то в окне Изменение связей автоматически становятся доступны еще два флажка, связанных с обеспечением целостности данных: «каскадное об­новление связанных полей» и «каскадное удаление связанных записей». Установите оба этих флажка. В этом случае Access позволит производить изменения и удаление записей данных главной таблицы. Одновременно будут изменены (или удалены) свя­занные с ними записи подчиненной рабочей таблицы. Это оз­начает, что правила целостности, будут сохранены даже при изменении значений в связующих полях.

Так, например, если рассмотреть пример с БД «Студенты» то установление флажка «Обеспечение целостности данных» приведет к тому, что после этого в таблицу Успеваемость невозможно будет ввести запись с таким значением поля КодСтудента, для которого нельзя будет найти соответствующую запись в таблице Студенты. Флажок «каскадное обновление связанных полей». При изменении значе­ния ключевого поля в "главной" таблице (Студенты) автоматиче­ски изменяются значения связанного поля для соответствующих, записей в «подчиненной» таблице (Успеваемость). В рассматри­ваемом случае ключевое поле КодСтудента является счетчиком, следовательно, значения этого поля не могут быть изменены пользователем. Поэтому нет необходимости устанавливать дан­ный флажок. Флажок «каскадное удаление связанных записей». При удалении записи из «главной» таблицы (Студенты) автоматически удаляются те запи­си из «подчиненной» таблицы (Успеваемость), для которых зна­чение поля КодСтудента совпадает со значением в удаленной записи из таблицы Студенты. Так как в таблице Успеваемость не нужно хранить оценки несуществующих студентов, то флажок «каскадное удаление связанных записей» следует установить.

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

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

Подтаблицы БД

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

Однако далеко не во всех случаяхавтоматически создаваемые подтаблицы будут отвечать требованиям пользователя. Чтобы удалить имеющуюся подтаблицу_: следует воспользоваться ко­мандой «Формат» – « Подтаблица» – «Удалить». В этом случае подтаблица будет удалена и пользователь для данной таблицы может создавать подтаблицу по своему усмотрению.

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

Запросы

Как правило, нет необходимости в отображении всей информации, которая хранится в той или иной таблице БД. Для этого в Access используются запросы. Этиобъекты БД служат для извлечения данных из таблиц и предоставления их пользователю в удобном виде. Особенность запросов со­стоит в том, что они черпают данные из базовых таблиц и создают на их основе временную таблицу. С помощью запросов выполняют такие опера­ции как отбор данных, их сортировку и фильтрацию. С помощью запросов можно выполнять преобразование данных по заданному алгоритму, создавать новые таблицы, выполнять автоматическое наполнение таблиц данными, импортированными из других источников, выполнять простейшие вычисления в таблицах и многое другое.

Access позволяет создавать запросы двух типов: QBE-запросы, SQL-запросы.

QBE-запросы(Query By Example) – запросы, строя­щиеся с помощью конструктора запросов, представляю­щего собой графический инструмент для создания запро­сов по образцу.

SQL-запросы– запросы, строящиеся при помощи унифицированного набора инструкций SQL (Structured Query Language – структурированный язык запросов).

С другой стороны все запросы делятся на две группы: запросы-выбор­ки и запросы-действия.

Запросы-выборки осуществляют выборку данных из таблиц в соответствии с заданными условиями. К этой группе запросов относятся:

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

· Перекрестный запрос отображает итоговые данные с группировкой их по горизонтали и вертикали, выво­дя результаты их обработки в виде таблиц.

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

· Запрос с вычисляемым полем позволяет рассчитать данные на основе других полей из того же за­проса.

· Запрос с условием позволяет производить от­бор записей в соответствия с заданным критерием отбора.

· Итоговый Запрос позволяет произвести группировку записей с одинаковыми значениями заданных полей и провести математические вычис­ления по сгруппированным данным.

Запросы-действия позволяют модифицировать дан­ные в таблицах: удалять, обновлять, добавлять записи. К этой группе запросов относятся:

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

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

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

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

Чтобы создать новый запрос, необходимо нажать кнопку «За­просы» в окне объектов БД и после этого выбрать режим соз­дания запроса: с использованием конструктора или мастера, т.е. выполнить двойной щелчок по пункту «Создание запроса в режиме конструктора» или соответственно «Создание запроса с помощью мастера». Так­же для создания запроса можно воспользоваться кнопкой «Соз­дать», после чего выбрать режим создания нового запроса. В этом случае будет предложено:

· конструктор (создание запроса с помощью конструктора запросов)

· простой запрос (создание запроса с помощью мастер создания простого запроса)

· перекрестный запрос (создание запроса с помощью мастера создания перекрестного запроса)

· повторяющиеся записи (создание запроса с помощью мастера создания запросов на поиск повторяющихся записей)

· записи без подчиненных (создание запроса с помощью мастера создания запросов на поиск записей без под­чиненных записей).

Создание запроса в режиме конструктора. После двойного щечка по пункту «Создание запроса в режиме конструктора» будет отображено диалоговое окно Добавле­ние таблицы для выбора таблицы (иди таблиц), которая будет служить источником данных при выполнении этого запроса. Необходимо добавить все таблицы, которые будут служить источником данных. Для этого необходимо выделять название нужной таблицы и нажимать на кнопку «Добавить». Затем необходимо закрыть окно добавления таблицы (кнопка «Закрыть»). После этого на экране будет отображено окно конструк­тора нового запроса, в котором в верхней части окна конструктора располагаются макет(ы) таблицы (или таблиц). В нижней части окна содержится бланк запроса. Первой строкой бланка является строка поле в ней указывается название полей, содержимое которых должно отражаться в результаты запроса. Чтобы добавить нужное поле в бланк запроса, необходимо перетащить его из макета таблицы в строку поле бланказапроса или выполнить по нему двойной щелчок левой кнопкой мыши. Таким образом последовательно в столбцы бланка запроса необходимо добавить все нужные поля. Чтобы добавить в запрос все ноля из таблицы, следует дважды щелкнуть в макете таблицы на строке с символом «*». Рассмотрим остальные строки бланка запроса:

Имя таблицы – в этой строке содержится название таб­лицы, в которой находится поле. Это значение уста­навливается автоматически при занесении поля в бланк запроса.

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

• выделяют столбец щелчком на его заголовке (кнопку мыши отпускают);

• еще раз щелкают на заголовке уже выделенного столбца (но кнопку не отпускают);

• перетаскивают столбец в другое место.

Примечание:Для удаления столбца (поля) в бланке запроса его также выделяют и нажимают на клавишу Deleteна клавиатуре.

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

Условие отбора, или –указываются одно или несколько условий, в соответствии с которыми записи будут добавляться в выборку запроса. Данная строка бланка запроса позволяет сформировать два типа запросов Запрос с условием и Запрос с параметром.

Запрос с условием. Для формирования запроса с условием для необходимых полей в бланке запроса в строке «Условие отбора» задать индивидуальное условие. Для задания условий могут использоваться логические операторы (=, <, <=, >, =>, <>), операторы связи (AND(И), OR(ИЛИ)), операторы сравнения (NOT(НЕ), LIKE(КАК)).

Например, необходимо отобразить заказы на сумму более 100000 рублей, осуществленные ранее 12.03.05.

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

Поле	Выражение	Включить в выборку
город	«Москва»	Заказы клиентов из Москвы
город	«Москва» OR «Ростов»	Заказы клиентов, из Москвы или Ростова
город	(«Москва»; «Ростов»)	Заказы клиентов из Москвы и Ростова
город	NOT «Москва»	Заказы всех клиентов, кроме московских
дата заказа	=#01.03.2005#	Заказы, которые по­ступили 1 марта 2005 года
дата заказа	BETWEEN #01.03.2005# AND #01.05.2005#	Заказы, которые поступили с 1 марта по 1 мая 2005 года
дата заказа	<DATA() — 60	Заказы, которые посту­пили за последние 60 дней (Функция DATA() возвращает текущую системную дату)
дата заказа	YEAR([Дата заказа])=2005	Заказы 2005 года
фамилия	«П*»	Заказы клиентов, фа­милии которых начи­няются с буквы П
фамилия	LIKE «*дзе»	Заказы клиентов, фа­милии которых закан­чиваются на «дзе»
фамилия	LIKE «[П-Я]»	Заказы клиентов, фами­лии которых начинают­ся на буквы от П до Я
номер заказа	RIGHT([Hoмep заказа];3) = «21»	Заказы, номера которых заканчиваются на «21»

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

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

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

После заполнения бланка запроса запрос следует сохранить для этого необходимо закрыть окно конструирования запроса. На вопрос о «Сохранить изменение макета или структуры объекта «….»?» ответить «Да», в следующем окне указать названия запроса, нажать «Ок». В ре­зультате в окне объектов БД появится значок только что созданного запроса который можно открыть, выполнив по нему двойной щелчок или изменить с помощью кнопки Конст­руктор окна БД.

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

Например, если в таблице какого-либо магазина имеется поле цена на товар и поле количество этого товара, то, исходя из этого можно создать вычисляемое поле, в котором будет подсчитываться общая стоимость для каждого товара путем перемножения значении цена и КОЛИЧЕСТВО. Для этого в первом пустом столбце бланка запроса в начале вводиться название нового поля после которого ставится двоеточие и пробел «Общая стоимость:». Затем вводиться расчетная формула, в нашем случае это [ЦЕНА] * [КОЛИЧЕСТВО] (имена полей заключаются в квадратные скобки в формуле). В результате выполнения запроса, отображаемого в режиме таблицы, присутствует новое поле, в котором отображается «Общая стоимость».

В вычисляемом поле могут использоваться любые арифметические операторы (+,-,*,/) между полями, записанными в формуле, а также следующие операторы:

\ - Деление первого числа на второе с округлением результата;

^ - Возведение в степень;

MOD - Вычисление остатка от деления нацело;

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

Также в формуле могут использоваться числа или текстовые символы. Например, для расчета «Цена товара в рублях» при известной «Цена товара в долларах» необходимо записать формулу [Цена товара в долларах]*28,7

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

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

Например, если требуется узнать количество товаров на складе имеющих одинаковую цену за единицу, то в первый столбец бланка запроса следует поместить поле «Цена». Для этого столбца нужно задать функцию «Группировка», в результате чего все записи будут сгруппированы по ценам за единицу товара. Затем необходимо отбуксировать поле «На складе» во второй столбец бланка запроса. Для этого столбца следует установить фунцию "Sum" (Сумма) из списка "Групповая операция". Результатом запроса будет таблица, содержащая информацию о количестве товаров на складе, имеющих одинаковую цену.

Кроме функций «Группировка» и «Sum» в строке «Групповая операция» могут использоваться следующие функции:

Avg – нахождение среднее значение для группы записей;

Min – нахождение минимальное значение для группы записей;

Мах – нахождение максимальное значение для группы записей;

Count – подсчет числа значений в группе записей;

First – значение поля в первой записи группы записей;

Last – значение поля в последней записи группы записей.

Создание запроса с помощью мастера.

Чтобы создать запрос с помощью мастера, необходимо нажать кнопку «За­просы» в окне объектов БД и после этого выполнить двойной щелчок по пункту «Создание запроса с помощью мастера». Далее необходимо выбрать в списке «Таблицы и запросы» таблицы или запросы поля, из которых должны отображаться в формируемом запросе, затем из списка «Доступные поля» необходимо нужные поля перенести в список «Выбранные поля» для этого нужно пользоваться кнопками

Перенести выбранное поле в список «Выбранные поля»

Перенести все поля таблицы или запроса в список «Выбранные поля»

Также если вдруг поле нужно удалить из списка «Выбранные поля» можно воспользоваться кнопками

Удалить выбранное поле из списка «Выбранные поля»

Удалить все поля из списка «Выбранные поля»

 

Для перехода к следующему шагу необходимо нажать кнопку «Далее». На следующем шаге необходимо выбрать вид отчета и нажать кнопку «Далее». В следующем окне необходимо задать имя запроса и выбрать вид дальнейших действий:

· Открыть запрос для просмотра данных;

· Изменить макет запроса.

Затем нажать на кнопку «Готово».

Список литературы

 

1. Златопольский Д.М. 1700 заданий по Microsoft Excel. Основы информатики/ под ред. Д.М. Ремизова – С.Пб.: БХВ - Петербург, 2003 – 544с.

2. Замятина Е.Б., Лядова Л.Н., Мызникова Б.И., Фролова Н.В. Введение в Информатику. Учебное пособие. Часть 1. Теоретические основы/ под ред. Е.Б.

Замятиной. – Пермский государственный университет. Пермь: Пресс-мастер, 2004 – 144с.

3. Могилёв А.В., Пак Н.И., Хеннер Е.К. Практикум по информатике/ под ред. А.В. Могилёва – М.: Академия, 2001 – 608c.

4. Симонович С.В. Информатика. Базовый курс/ под ред. С.В. Симонович –

С.Пб.: Питер, 2005. – 640 с.