2.1 Технология создания таблиц базы данных продажа

1. Создайте файл новой базы данных. Для этого выполните команду меню Файл – Создать. В окне Создание выберите Новая база данных. В открывшемся окне задайте имя новой базы данных ПРОДАЖИ_фамилия (студента). Определите папку, в которой будет сохранен этот файл.

2. Опишите структуры записей таблиц базы данных и назовите их следующим образом: ТОВАРЫ, ПОКУПАТЕЛИ, РЕЕСТ, НАКЛАДНАЯ. Описания полей приведены в таблице 12.6.

При создании структуры таблицы руководствуйтесь следующим порядком действий:

1. Выберите закладку Таблица и режим Конструктор.

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

3. Для каждого поля определите характеристики на вкладке Свойства полей:

• размер поля;

• подпись поля (не путать с описанием);

• для составных ключевых полей установите в свойстве «Индексированное поле» значение «Совпадения допускаются».

4. Для каждой таблицы укажите ключевое поле, нажав кнопку на Панели инструментов.

Примечание. При указании ключевого поля для несмежных полей используется клавиша <Ctrl>.

5. Сохраните структуру записи, присвоив таблице имя.

2.2 Технология создания схемы данных

1. Добавьте таблицы в схему данных: нажмите кнопку Схема данных на панели инструментов, в окне Добавление таблиц выберите таблицы, включаемые в схему данных, и нажмите кнопку Добавить.

Таблица 12.6 – Описание таблиц БД ПРОДАЖА

Имя таблицы	Имя поля	Ключевое поле	Тип данных	Размер	Подпись
ТОВАРЫ	НТОВ	Да	Текстовый	30	Наименование товара
	ЕД		Текстовый	3	Ед. измер.
	ЦЕНА		Числовой		Цена
	СНДС		Числовой		Ставка налога
	СТР		Текстовый	12	Страна
	ГТД		Текстовый	15	Номер тамож. декл.
ПОКУПАТЕЛИ	НПОК	Да	Текстовый	20	Наименование покупателя
	АДР		Текстовый	30	Адрес
	РСЧ		Текстовый	20	Р/счет
	ИНН		Текстовый	10	Идентиф. номер налогопл.
РЕЕСТР	НДОК	Да	Счетчик		Номер накладной
	ДВЫПН		Дата/время		Дата
	НПОК		Текстовый	30	Наименование покупателя
НАКЛАДНАЯ	НДОК	Да	Счетчик		Номер счета
	НТОВ	Да			Наименование товара
	КОЛ		Числовой		Количество
	СТОВ		Числовой		Сумма
	СНАЛ		Числовой		Сумма налога
	СТсНАЛ		Числовой		Стоимость с учетом налога

При выделении нескольких таблиц держите нажатой клавишу CTRL или SHIFT, нажмите кнопку Закрыть.

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

3. Установите связи между таблицами БД, руководствуясь таблицей 12.5.

Создайте связь между таблицами ТОВАРЫ и НАКЛАДНАЯ.

Для этого установите курсор мыши на ключевое поле НТОВ главной таблицы ТОВАР и перетащите его на поле НТОВ таблицы НАКЛАДНАЯ. В открывшемся окне Изменение связи в строке «Тип отношения» установится один ко многим. Проверьте правильность устанавливаемых отношений (имена связываемых полей должны быть одинаковыми). Нажмите кнопку Создать.

Аналогичные действия выполняются с другими парами таблиц РЕЕСТР – ПОКУПАТЕЛИ, РЕЕСТР – НАКЛАДНАЯ.

Включите целостность БД.

Контрольные вопросы

1. Что такое объект типа “Таблица”, как его можно создать?

2. Что отображает инфологическая модель базы данных?

3. Что такое логическая модель базы данных?

4. Что задает Формат поля?

5. Что позволяет осуществить Условие на значение?

6. Что такое “Ключ” для объекта типа “Таблица” и как его можно задать, если он состоит из одного или нескольких полей таблицы? Может ли быть задано для таблицы несколько ключей?

7. Что такое связи между таблицами, какого типа связи между таблицами поддерживает MS Access?

8. Как можно задать связи между таблицами и где эти связи будут зафиксированы? Какие изменения можно внести в связи между таблицами?

9. Что такое основная таблица и подчиненная таблица, как они связаны друг с другом?

10. Могут ли быть у одной основной таблицы несколько подчиненных? Если могут – приведите примеры, если нет, то почему.

11. Какие типы данных Вы знаете?

Лабораторная работа № 13. Основы создания запросов и отчетов средствами системы управления базами данных MS Access

Цель работы: изучение приемов выборки данных и конструирование различного типа запросов; формирование отчетов

Задачи:

• построение запросов к базе данных «ПРОДАЖИ»;

• конструирование отчетов на их основе и анализ результатов средствами MS Excel.

Основные сведения

Одним из основных инструментов обработки данных в СУБД являются запросы, с помощью которых можно выполнить следующие виды обработки данных:

• выбрать записи, удовлетворяющие условиям отбора;

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

• произвести вычисления в каждой из полученных записей;

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

Простой запрос или запрос на выборку. Самый распространенный тип запроса. Извлекает данные из одной или нескольких таблиц.

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

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

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

Для составления запроса следует перейти в окно базы данных, выбрать пиктограмму «ЗАПРОС» и нажать кнопку «Создать», а затем кнопку «Новый запрос». В следующем окне диалога следует выбрать одну из таблиц БД и нажать кнопку «Добавить», MS Access поместит в окно запроса список полей указанной таблицы. Теперь можно выбрать одно или несколько полей и отбуксировать их в бланк запроса QBE.

В строке «Условие отбора» можно ввести теперь критерии выбора. Для обозначения произвольного количества символов используйте в качестве символа-заменителя звездочку (*), а для обозначения одного произвольного символа знак вопроса (?). Сформулируйте критерии запроса. Например, если для текстового поля будет задан только критерий "А*", то в результате выполнения запроса будут отображены только те записи, текстовое поле которых начинается с буквы "А" (или "а").

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

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

Выражение 1: [Поле1] "оператор" [Поле2]

В квадратные скобки заключаются только имена полей. MS Access автоматически использует «Выражение 1» в качестве имени вычисленного выражения. Это имя можно изменить на более содержательное.

При построении сложных выражений MS Access предлагает использовать утилиту, называемую «Построитель выражений». Для перехода в "Построитель выражений" необходимо щелкнуть по пустому полю в бланке QBE, а затем - по кнопке «Построить» панели инструментов.

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

Чтобы установить параметр необходимо ввести в строку «Условие отбора» имя или фразу, заключенную в квадратные скобки ([]). То, что заключено внутри квадратных скобок, MS Access рассматривает как имя параметра. Это имя выводится в диалоговом окне при выполнении запроса, поэтому разумно в качестве имени параметра использовать содержательную фразу. В одном запросе можно задать несколько параметров; при этом имя каждого параметра должно быть уникальным и информативным.

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

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

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

Лишь в том случае, если в результате запроса присутствуют только подлежащие удалению записи, следует преобразовать запрос-выбора в запрос на удаление. Для этого в строке меню выберите команду Запрос/Удаление. После этого MS Access выключает в бланке запроса строки «Сортировка» и «Вывод на экран». Для запросов на удаление эти строки не нужны. Кроме того, для всех колонок бланка запроса в новой строке «Удаление» отображается текст «Условие». Однако критерии при этом не изменяются. Если выполнить запрос выбором пиктограммы «ВЫПОЛНИТЬ», MS Access не отобразит на экране результат выполнения запроса. Вместо него появится сообщение о том, сколько записей будет удалено в исходной таблице. Вы можете прервать процесс удаления нажатием кнопки «Отмена». Однако нажатие ОК приводит к безвозвратному удалению записей, удовлетворяющих критериям запроса.

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

Порядок выполнения лабораторной работы

Макеты отчетов приведены в таблицах 13.1, 13.2.

Таблица 13.1 – Отчет о количестве проданной продукции по покупателям

Наименование продукции	Единица измерения	Всего	в том числе по покупателям

Таблица 13.2 – Отчет о продажах в разрезе стран-изготовителей

Наименование продукции	Единица измерения	Всего	в том числе по странам-изготовителям

Технология создания запросов

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

2. В окне Создание сформируйте поля запроса, используя схему, приведенную в таблице 13.3.

3. Сохраните запрос с именем «Запрос 1».

4. Откройте Запрос 1 в режиме конструктора и введите с помощью Построителя выражений расчетные формулы для полей: Стоимость товара, Сумма налога, Стоимость товара с учетом налога.

Таблица 13.3 – Схема формирования полей запроса

Наименование поля	Источник
Номер накладной	РЕЕСТР
Дата выписки	РЕЕСТР
Наименование покупателя	РЕЕСТР
Наименование товара	НАКЛАДНАЯ
Единица измерения	ТОВАРЫ
Количество	НАКЛАДНАЯ
Цена	ТОВАРЫ
Ставка НДС	ТОВАРЫ
Стоимость товара	НАКЛАДНАЯ (выражение)
Сумма налога	НАКЛАДНАЯ (выражение)
Стоимость товара с учетом налога	НАКЛАДНАЯ (выражение)
Страна-изготовитель	ТОВАРЫ

5. Проверьте, правильно ли работает запрос.

6. На основе Запроса 1, в котором присутствуют все необходимые данные для создания Отчета о количестве проданной продукции по покупателям, сформируйте Перекрестный запрос 1.

7. Для этого в окне Создание перекрестных таблиц установите переключатель Показать запросы, выберите Запрос 1 и нажмите кнопку Далее. Затем из списка Доступные поля выберите поля, включаемые в качестве заголовков строк (Наименование товара и Единица измерения) и нажмите кнопку Далее.

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

9. Определите поле, над значениями которого будут производиться вычисления (Стоимость товара) и выберите соответствующую функцию (Сумма).

10. Для формирование столбца Всего параметр Вычислить итоговое значение для каждой строки должен иметь значение Да. Нажмите кнопку Готово и сохраните запрос с именем Перекрестный запрос 1.

11. Используя технологию Анализ MS EXCEL опции Связи с офис из меню Сервис, произведите анализ данных, полученных в Перекрестном запросе 1:

• подведите итоги по каждому Наименованию покупателя;

• измените внешний вид таблицы (отформатируйте значения, введите заголовок таблицы, выделите итоги и заголовки любым цветом);

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

12. Сохраните полученный отчет в своем рабочем каталоге, присвоив ему соответствующее имя.

13. На основе Запроса 1 самостоятельно создайте Отчет о продажах товаров в разрезе стран-изготовителей.

14. На основе Перекрестных запросов самостоятельно создайте Отчет о продажах товаров в разрезе стран-изготовителей и Отчет о количестве проданной продукции по покупателям с итоговыми суммами соответственно по странам и по покупателям.

15. На основе Запроса 1 самостоятельно создайте две диаграммы о структуре совокупности по товарам и по покупателям.

16. На основе Запроса 1 самостоятельно создайте два информативных списочных отчета с двумя (не менее) уровнями группировки и подведением промежуточных итогов по каждому уровню и по отчету в целом.

17. Сравните информацию в Перекрестных запросах и в списочных. Сделайте выводы.

Контрольные вопросы

1. Что такое запрос и какие типы запросов поддерживает MS Access?

2. Где находится шаблон вывода при конструировании запроса-выборки?

3. Как можно добавить или удалить таблицу при конструировании запроса?

4. Как определить, из каких таблиц взяты поля в шаблон вывода при конструировании запроса?

5. Можно ли вводить данные в таблицы через запросы? Если можно, то в каких случаях, каковы ограничения, накладываемые на взаимосвязанные таблицы, на ввод данных через запросы в таблицы?

6. Запрос, содержащий вычисляемое поле, – что это?

7. Как задать параметры при создании запроса-выборки с параметрами?

8. В чем смысл создания запроса с групповыми операциями?

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

10. Можно ли соединять запросы? Если да, то для чего?

Лабораторная работа № 14. Создание экранных форм

для ввода и просмотра данных

Цель работы: научиться создавать простую, сложную и кнопочные формы, а также освоить порядок работы с фильтрами; освоить порядок работы с кнопочной формой

Задачи:

• для базы данных ПРОДАЖА сформировать экранные формы для ввода и просмотра сведений о товарах, покупателях и продажах;

• создать кнопочную форму.

Основные сведения

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

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

Автоформы. Формы удобно готовить средствами автоматизации –автоформами. Существует три вида автоформ: «в столбец», «ленточные» и «табличные».

Автоформа «в столбец» отображает все поля одной записи – она удобна для ввода и редактирования данных. «Ленточная» автоформа отображает одновременно группу записей – ее удобно использовать для оформления вывода данных. «Табличная» автоформа по внешнему виду ничем не отличается от таблицы, на которой она основана.

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

Автоформа основывается только на одном объекте.

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

Возможны два способа построения таких форм:

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

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

Создание форм с помощью мастера. Мастер форм – специальное программное средство, создающее структуру формы в режиме диалога с разработчиком. Мастер форм можно запустить из окна Базы данных пунктом меню Создать с помощью мастера на панели Формы.

На первом этапе работы Мастера форм выбирают таблицы и поля, которые войдут в будущую форму. Затем выбирают внешний вид формы, стиль оформления формы. На последнем этапе выполняется сохранение формы под заданным именем.

Структура формы. Форма имеет три основных раздела: область заголовка, область данных и область примечания. Линии, разделяющие разделы, перетаскиваются по вертикали с помощью мыши – это позволяет изменить размеры разделов так, как требуется.

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

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

Все элементы управления форм должны быть аккуратно выровнены. Это обеспечивается командой Формат – Выровнять. Если нужно равномерно распределить элементы управления по форме, используют средства меню Формат: Интервал по горизонтали и Интервал по вертикали.

Проведенный анализ информации, хранящейся в базе данных ПРОДАЖА, показал, что для ведения справочников ТОВАРЫ и ПОКУПАТЕЛИ целесообразно использовать простые экранные формы ленточного типа. Для ввода данных о продажах необходимо создать составную многотабличную форму СЧЕТ-ФАКТУРА на основании таблиц РЕЕСТР и НАКЛАДНАЯ.

В форме СЧЕТ-ФАКТУРА для полей, значения которых выбираются из справочников ТОВАРЫ и ПОКУПАТЕЛИ, предусмотреть свойство – поле со списком.

В таблицах 14.1, 14.2 содержится информация для ввода данных в справочники.

Таблица 14.1 – Список товаров

Наименование товара	Ед. измерения	Цена, руб.	Ставка НДС, %	Страна	Номер таможенной декларации
Кофеварка	шт.	370,00	20	Германия	21546445642872
Миксер	шт.	420,00	20	Польша	75653898215440
Тостер	шт.	240,00	20	Германия	10024543651245
Утюг	шт.	850,00	20	Франция	15187423418978
Фен	шт.	330,00	20	Польша	45086523897142
Чайник	шт.	620,00	20	Германия	50820056435080

Таблица 14.2 – Список покупателей

Наименование покупателя	Адрес	Расчетный счет	Идентификаци-онный номер налогоплательщика
Моран	СПб, пр. Космонавтов, 80	24516045494804040465	616358826428
Опал	СПб, ул. Садовая, 38	40404650054217015650	616345898525
Прибой	СПб, ул. Береговая, 31	85895000648005056422	616310425304