Формирование производных таблиц – запросов, форм и отчетов

Разработка структуры базы данных, создание и заполнение исходных таблиц.

Задание 1

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

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

наименование,

цена товара,

количество каждого товара,

номенклатурный номер товара,

дата поставки,

изображения внешнего вида товара,

информация о поставщиках и т.д.

В процессе создания БД что-то может добавляться, а что-то не использоваться.

2. Структурируем информацию, то есть распределим ее по таблицам. которые называются исходными таблицами. Причем данные в таблицах не должны повторяться, за исключением полей по которым таблицы будут связываться. Этот процесс называется нормализацией. Для нашей задачи создадим две таблицы – Товар и Поставщики.

3. Таблица «Товар» содержит следующие поля (столбцы):

• Номенклатурный номер.
• Наименование товара.
• Количество.
• Единица измерения.
• Цена.
• Поставщик.
• Дата поставки.
• Внешний вид.

4. Таблица «Поставщики» содержит следующие поля (столбцы):

• Поставщик.
• Регион.
• Адрес.
• Телефон.
• Реквизиты.

5. В списках полей обоих таблиц жирным щрифтом выделены поля «Поставщик». По этим полям таблицы будут связаны.

Задание 2

Используя СУБД Microsoft Access создать базу данных с именем «Склад-фамилия», создать в базе данных исходные таблицы «Товар» и «Поставщики»

1. Загрузить Microsoft Access, для чего выполнить(Пуск, Программы, Microsoft Office,Microsoft Access).

2. В появившемся окне установить курсор мыши в строке Новая пустая база данных, нажать кнопку Новая база данных.

3. В правом нижнем углу ввести имя базы данных «Склад-фамилия», где фамилия – это имя или фамилия студента. В результате на экране появляется окно базы данных, например, «Склад-Иванова». Нажать кнопку Создать.

4. По умолчанию открывается исходная таблица в режиме «Таблица». Для ввода полей удобнее использовать режим «Коструктор». Для этого в левом верхнем углу откройте меню кнопки «Режим» и выберите «Конструктор».

5. Ввести имя таблицы – Товар. Нажмите ОК. В открывшемся окне удалитеустановленное по умолчанию ключевое поле Код (выделите строку полностью, щелкните правой кнопкой и выберите «Удалить строку»).

6. Ввести имена полей и их тип в столбцы «Имя поля» и «Тип данных» (Рис.1):

Внимание! В столбце «Имя поля» НЕЛЬЗЯ ставить точки, запятые и другие знаки.

· Номенклатурный номер, числовой;

· Наименование товара, текстовый, размер поля 25 (ставится на вкладке «Общие» в нижней части окна программы);

· Количество, числовой;

· Единица измерения, текстовый, размер поля 10;

· Цена, денежный, число десятичных знаков 2 (ставится на вкладке «Общие» в нижней части окна программы;

· Поставщик, текстовый;

· Дата поставки; дата/время. Для данного поля введите маску ввода, для чего на вкладке «Общие» в нижней части окна программы в строке «Маска ввода» щелкните левой кнопкой мыши и откройте кнопку ввода (см. рис.1). Выберите «Краткий формат даты» и затем «Готово».

Рис.1.

7. Закрыть окно таблицы, сохранив изменения структуры таблицы.

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

9. Ввести наименования полей и их тип:

· Поставщик, текстовый.

· Регион, текстовый.

· Адрес, текстовый.

· Телефон, числовой.

· Реквизиты, текстовый.

10. Закрыть окно таблицы, сохранив изменения структуры таблицы.

11. Ввести имя таблицы Поставщики и нажать ОК,

12. Отказаться от создания ключевого поля (нажать Нет)

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

Задание 3

Ввести данные в исходные таблицы.

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

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

3. Закрыть окно таблицы, согласившись с сохранением изменений.

4. Аналогичным образом ввести следующие записи в таблицу «Поставщики».

5. Закрыть окно таблицы, согласившись с сохранением изменений.

Задание 4

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

1. На вкладке Работа с базами данных нажать кнопку Схема данных.

2. В открывшемся окне Добавление таблиц выделить таблицы «Товар» и «Поставщики», нажав и удерживая клавишу [Shift], и нажать кнопку Добавить.

3. Закрыть окно Добавление таблиц.

4. В окне Схема данных должны отобразиться структуры таблиц «Товар» и «Поставщики».

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

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

6.ОкноСхема данныхзакрыть. Сохранить.

7. В списке таблиц выбрать таблицу «Товар» и открыть её.

8. В поле Номенклатурный №щелкнуть кнопкой мыши по кнопке вызова фильтра (рядом с именем поля).

9. Убрать галочку из поля «(Выделить все)», поставить галочку напротив номера 234, нажать ОК. В результате получим таблицу с записями о товаре с номенклатурным номером 234.

10. Аналогично верните таблицу в прежний вид.

11. Используя кнопку вызова фильтра, найдите записи с наименованием товара «Обои». Верните таблицу в прежний вид.

12. Используя кнопку вызова фильтра, отсортируйте записи в поле «Наименование товара» по алфавиту.

13. Окно таблицы закрыть

Формирование производных таблиц – запросов, форм и отчетов.

Задание 5

Создать запрос на выборку товара по заданному критерию (условию отбора). Например, выбрать только записи о товаре «Обои».

1. В окне базы данных выбрать вкладку Создание.

2. Нажать кнопку Конструктор запросов.

3. Выбрать таблицу «Товар», из которой поступят данные в запрос и добавить эту таблицу в окно Запрос кнопкой Добавить. Окно Добавление таблиц закрыть.

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

5. Для поля Номенклатурный №, по которому нужно произвести сортировку, необходимо из списка Сортировка выбрать способ сортировки По возрастанию(см. рисунок выше).

6. Для поля Наименование товара в строке Условие отбора правой кнопкой вызвать контекстное меню и выбрать построитель выражений, щелкнув по Построить.

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

8. Закрыть окно запроса, присвоив запросу имя Выборка товара - Обои.

9. Для выполнения (просмотра) запроса выбрать его имя и нажать кнопку Открыть.

Задание 6

Разработать запрос на вычисление стоимости каждого вида товара на складе.

1. В окне базы данных выбрать вкладку Создание. Нажать кнопку Конструктор запросов.

2. Выделить имя таблицы «Товар» и нажать кнопку Добавить. Закрыть это окно.

3. В бланка запроса последовательно переместить первые пять полей .

4. В шестом столбце бланка запроса в строке Поле правой кнопкой мыши вызовите меню и откройте построитель выражений. Постройте формулу (см. рисунок). Слово Стоимость: введите с клавиатуры. Нажмите ОК.

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

6. Закрыть окно запроса.

Если выходит сообщение об ошибке или запрос о количестве и цене, необходимо открыть запрос в режиме «Конструктор» и исправить ошибку в формуле.

7. Сохранить запрос с именем «Стоимость».

8. Просмотреть результат запроса, используя кнопку Открыть.

В результате откроется следующая таблица запроса.

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

Задание 7

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

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

1. В окне базы данных выбрать вкладку Создание.

2. Выбрать таблицу «Товар» и нажать на кнопку Конструктор форм, затем кнопкуДобавить поля. Нажать Показать все таблицы. Раскрыть поля таблицы Товар.

3. Форму дополнить названием «Просмотр и добавление товара», используя кнопку Надпись из панели элементов.

4. В списке полей последовательно выбрать поля Номенклатурный №, Наименование и Количество и перетащить их в макет формы.

5. Выделить поле Единица измерения в списке полей, нажать кнопку Поле со списком на панели инструментов (кнопку мыши отпустить) и перенести в форму.

6. Появится окно для создания поля со списком. Нажмите кнопку Далее и укажите таблицу Товар, нажмите Далее.

7. Выберите и перенесите поле Единица измерения, нажмите Далее, Далее, Далее, Готово.

8. Перетащите в форму поле Цена.

9. Аналогично п.п.5-7, сделайте поле со списком Поставщики (в качестве источника выберите таблицу Поставщики, а в ней поле Поставщик).

10. Перенесите в форму поле дата поставки.

11. Готовую форму закройте, сохранив с именем Просмотр и добавление товара.

12. Для просмотра результата выполнения формы откройте ее.

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

14. Закройте форму.

15. Откройте таблицу «Товар». На экране появятся все записи, содержащиеся в таблице, в том числе новые, которые были введены при помощи созданной формы. Закройте таблицу.

Задание 8

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

1. В окне базы данных выбрать вкладку Создание.

2. Отметить таблицу «Поставщики» и нажать на кнопку Разделенная форма.

3. Сохранить изменения макета, ввести имя формыПоставщики, затем ОК.

4. Откройте форму и ведите нового поставщика:

5. Закройте форму, сохранив изменения.

6. Откройте таблицу «Поставщики». Проверьте внесенные изменения, которые были введены при помощи созданной формы. Закройте таблицу

7. Откройте форму «Просмотр и добавление товара», найдите товары Провод и Кабель, замените поставщика на ЧП «Соколов». Закройте форму.

8. Откройте таблицу «Товар». Проверьте внесенные изменения, которые были введены при помощи формы. Закройте таблицу.

Задание 9

Создать отчет о поступлении товаров на склад по датам. В отчете подвести суммирующие итоги стоимости по каждой дате поставки и общую стоимость.

1. В окне базы данных выберите Создание, Мастер отчетов.

2. Выберите запрос Стоимостьв качестве источника данных.

3. Перенесите поля запроса Наименование товара, Количество, Единица измерения, Стоимость, Дата поставки из окна «Доступные поля» в окно «Выбранные поля», используя кнопку >и нажмите кнопку Далее.

4. В качестве уровней группировки выберите поле Дата поставки для группировки информации, нажмите кнопку «Группировка» и выберите интервалы группировки по дням, нажмите ОК.

5. Кнопкой Далееперейдите к следующему шагу формирования отчета.

6. Выберите порядок сортировки записей в пределах номенклатурного номера по Количеству. Только в окне 1.

7. Для подведения итогов необходимо нажать кнопку Итоги.Указать функцию SUMв строке Стоимость, нажать кнопку ОК и кнопкой Далее перейти к следующему шагу построения отчета. Итоги будут подводиться по стоимости товара.

8. Выберите макет отчета и ориентацию бумаги.

9. По своему желанию подберите стиль отчета.

10. В качестве имени отчета введите Стоимость товара, нажмите кнопку Готово. На экране появляется готовая отчетная форма.

Отчет необходимо отредактировать, поскольку некоторые поля могут не отражаться, например Стоимость. Кроме того, поле дата поставки явно лишнее, поскольку оно повторяется в отчете.

11. Перейдите в режим Конструктора (воспользуйтесь правой кнопкой мыши и меню).

12. Растяните поле «Стоимость» и поля «Sum», поле «Дата поставки» выделите и удалите кнопкой Del.

13. Закройте отчет, сохранив изменения.

14. Откройте отчет и убедитесь в его правильности: показаны все поля и ИТОГ0=235050.

15. Закройте отчет.