Проектирование базы данных для Ехсе1:.

4. Создание базы данных в Access
4.1. Создание взаимосвязанных таблиц
Для реализации базы данных «Ремонт офиса (материалы)» в среде Access создаем сле-дующие взаимосвязанные таблицы: Помещения, Материалы, ВидыРабот.
Пояснения к таблицам базы данных
Таблица Помещения имеет следующие поля:
Код_помещения - идентификационный код помещения офиса, первичный ключ; тип числовой (целый);
Наименование_помещения наименование помещения; тип текстовый;
Длина длина помещения, м; тип числовой (действительный, с двумя знаками после запятой);
Ширина ширина помещения, м; тип числовой (действительный, с двумя знаками по-сле запятой);
Высота высота помещения, м; тип числовой (действительный, с двумя знаками после запятой);
Затраты_план плановые затраты на ремонт помещения; тип денежный (с двумя зна-ками после запятой).
Таблица Материалы содержит следующие поля:
Код_материала - иднтификационный код материала для ремонта, первичный ключ; тип числовой (целый);
Наименование_материала наименование материала; тип текстовый;
Ед_измерения единица измерения материала; тип текстовый;
Стоимость_единицы стоимость одной единицы материала, руб.; тип денежный (с двумя знаками после запятой).
Таблица ВидыРабот содержит два поля:
Помещение содержит идентификационный код помещения; тип поля числовой (це-лый);
Материал содержит идентификационный код материала, который расходуется на ре-монт соответствующего помещения; тип числовой (целый).
Таблицы в режиме конструктора представлены на рис.8, 9 и 10.

1. Текст задачи
В соответствии с индивидуальным заданием в курсовой работе необходимо:
Спроектировать базу данных. Для Ехсе1:
 подготовить таблицу и заполнить ее данными с использованием стандартной формы по тема-тике задания (не менее 10 строк в таблице);
 выполнить необходимые вычисления, фильтрацию и сортировку данных, подведение итогов.
Для Ассеss:
 разработать связанные таблицы;
 в соответствии с заданием сформировать и выполнить запросы;
 подготовить требуемые отчеты.
По результатам выполнения задания оформить пояснительную записку к курсовой работе.

Вариант 29. Ремонт офиса (материалы)
База данных должна содержать следующие элементы:
 наименование помещения офиса;
 длина помещения;
 ширина помещения;
 высота помещения;
 наименование материала;
 стоимость одной единицы наименования;
 количество единиц материала;
При создании и обработке базы данных выполнить:
1. Заполнение и редактирование таблиц базы данных.
2. Вычисление стоимости затрат по каждому виду материалов (использовать не менее двух видов материалов для каждого помещения).
3. Вывод информации о заданном помещении с указанием объема работ.
4. Формирование списка помещений, для которых стоимость материалов требующихся для ремонта, превышает заданную.
5. Формирование отчета, содержащего следующие данные: наименование помещения, стоимость материалов с группировкой затрат по наименованию помещений.

2. Состав входных и выходных данных с указанием их идентификаторов и типов

Состав офиса, размеры его помещений и планируемые затраты на ремонт каждого поме-щения приведены в таблице 1.
Наименование поме-щения офиса Длина по-мещения, м Ширина помещения, м Высота по-мещения, м Планируемые затраты на ремонт поме-щения, руб.
Офисное помещение 1 10 7,2 3,5 3 000
Офисное помещение 2 8 7,2 3,5 2 500
Офисное помещение 3 9 8 3,5 3 000
Приемная 5 4 3,5 1 000
Комната отдыха 4 3 3,5 500
Санузел 1 2 1,5 2,5 80
Санузел 2 3 2 2,5 90
Коридор 10 3 3,5 900


3. Структура таблицы при работе с Excel. Описание действий, выполняемых при решении задач в режиме вычислений средствами Excel с сопровождением необ-ходимым иллюстративным материалом

Структура таблицы с расчетными данными при работе с Excel имеет следующий вид (рис.1).
Приводим пояснение к составленной таблице. В графах «Наименование помещения офи-са»(столбец А), «Длина помещения, м» (столбец В), «Ширина помещения, м» (столбец С), «Вы-сота помещения, м» (столбец D), «Планируемые затраты, руб.» (столбец К) располагаем исход-ные данные согласно таблице 1. В графе «Наименование материала» (столбец E) располагаем на-звания и характеристики материалов для ремонта. Так, размеры одного рулона обоев имеют зна-чения 10х1,06м. В графе «Единица измерения» (графа F) указываем единицы измерения мате-риалов, а в графе «Стоимость одной единицы материала» (графа G) цену материалов в руб.
Количество единиц материала, необходимого для ремонта, определяем по формулам и ис-пользованием функций Excel. Для подсчета , например, целого количества рулонов обоев для офисного помещения 1 используем формулу
=ОКРУГЛВВЕРХ(D5*(2*B5+2*C5)/10/1,06;0),
где ОКРУГЛВВЕРХ(число; число разрядов) функция аппарата Excel, которая округляет число до ближайшего большего по модулю; D5*(2*B5+2*C5) выражение для вычисления сум-марной площади боковых стен помещения; 10; 1,06 размеры одного рулона. Аналогичную формулу используем для расчета числа рулонов обоев для ремонта офисного помещения 2 и комнаты отдыха, а также стеновых панелей для офисного помещения 3.
Для вычисления количества линолеума и потолочных плит используем формулу площади прямоугольника.
Стоимость материала на ремонт помещения определяем по формуле
= [ячейка столбца G]*[соответствующая ячейка столбца H].
Например, для ламината (офисное помещение 1) формула имеет вид =G7*H7.
Для определения суммарных затрат на ремонт по конкретному помещению суммируем соответствующие затраты для всех материалов по данному помещению. при этого используем функцию суммирования всех чисел в указанном диапазоне ячеек СУММ. Например, для мате-риала офисного помещения 1 формула имеет вид =СУММ(I5:I7).
Экономию (перерасход) средств на ремонт находим, вычитая из планируемого расхода средств (столбец K) итоговые затраты на ремонт помещения (столбец J). Так, для офисного по-мещения 1 формула имеет вид =K7-J7.
Для расчета итоговых значений таблицы (строка 26) используем функцию суммирования СУММ. Например, для расчета стоимости всего материала по офису в ячейку I26 вводим форму-лу =СУММ(I5:I25).
Назначаем следующие типы данных: столбцы A, E, F текстовый; B, C, D, H общий; G, I, J, K, L денежный с двумя знаками после запятой.
Для ввода информации в созданную базу данных можно воспользоваться формой ввода, которая вызывается командой меню . В результате получим следующую форму для ввода и отображения данных (рис.2).
Для отображения тех записей базы данных, которые соответствуют заданным условия, среда Excel позволяет использовать фильтры. При использовании фильтров (фильтрации) вре-менно скрываются строки, которые не требуется отображать. Для отображения строк, соответст-вующих одному из заданных условий или значений, служит Автофильтр. Для применения Авто-фильтра нужно установить курсор на заголовок любого поля базы данных и выполнить команду меню . Для заданного фильтра нужно нажать на кнопку рядом с тем столбцом, по которому фильтруются значения. В выпадающем списке следует вы-брать нужное значение или задать условие (рис.3).



Рис.1. Таблица базы данных в среде Excel

. Коцюбинский А.О., Грошев С.В. Excel для бухгалтера в примерах. М.: Главбух, 2003.
2. Б.Виллариал. Программирование на Access2002 в примерах. М.: КУДИЦ-ОБРАЗ, 2003.