Практична робота № 13

Тема: Створення запитів і звітів в СУБД Access. Використання побудовника виразів.

Мета: Навчитися створювати запити до баз даних у MS Access, звіти і працювати з ними в різних режимах.

Час: 40 хв.

Завдання: Розробіть програму, що дозволяє бухгалтерії підприємства вести облік відпрацьованого часу та нараховувати зарплату співробітникам. Створіть запити за заданими умовами.

Література:

Зміст звіту:

• Відповіді на запитання, поставлені в пунктах опису послідовності виконання роботи.

• Висновки по роботі (що вивчили, чому навчилися)

Послідовність виконання роботи:

1. Запустіть програму MS Access, створіть нову базу даних «Підприємство».

2. З допомогою імпорту листів книги kadri.xlsx створіть таблиці, що збігаються з назвами аркушів цієї книги: «Кадри», «Табель1», «Табель2», «Табель3».

• Виконайте команду Зовнішні дані → Імпорт та зв'язок → Excel, вкажіть джерело, потім – лист з даними.

• Вкажіть поле «Табельний номер» як ключове;

• Після імпорту таблиці змініть розмір ключового поля, за замовчуванням воно буде не цілим, а дійсним, подвійним з плаваючою точкою:

• Далі здійсніть імпорт інших аркушів книги Excel kadri.xlsx, поле «Табельний номер» має бути ключовим:

3. Створіть схему даних, встановіть зв'язки між таблицями. Тип відношень повинен бути «один-до-одного», забезпечте цілісність даних, каскадне оновлення зв'язаних полів і каскадне видалення зв'язаних записів:

4. Створіть запит, що відображає нараховану зарплату співробітникам підприємства за перший місяць (січень 2015). В якості вихідних даних взяти таблиці «Кадри» і «Табель 1». На бланку конструктора запитів запишіть назви полів і вирази для відображення в режимі перегляду таблиці наступних даних: табельний номер, прізвище, посада, оклад, категорія, нараховано, премія, всього нараховано (ФОП).

• Для використання побудовника виразів полів «Дні», «Святкові», «Лікарняні», «Відпустка» їх потрібно додати в таблицю на бланку конструктора запитів. Після того, як запит буде створений, можна буде з цих полів зняти прапорець «Вывод на екран».

Вираз для «Нараховано» має виглядати так:

• «Нараховано: Round([Оклад]/22*([Дні]+2*[Святкові]+0,8*[Лікарняні]+[Відпустка]);2)». Тут [Оклад]/22 – «вартість» одного робочого дня, 22 – кількість робочих днів у місяці. Святкові дні оплачується за подвійним тарифом, тому +2*[Святкові]. Лікарняний оплачується за ставкою 0,8. Функція Round округлює результат до 2-х знаків після коми.

Вираз для премії буде виглядати так:

• Премія: Round(IIf([Категорія]=1;[Нараховано]*0,1;IIf([Категорія]=2;[Нараховано]*0,05;0));2)

Тобто якщо у працівника 1-я категорія, премія 10% від нарахованої суми, 2-я – 5%, для 3-ї категорії премія не передбачена.

• Збережіть запит під ім'ям «Нараховано 1».

5. Створіть запит, формує розрахунковий лист. Запит повинен відображати табельний номер співробітника, його прізвище, посаду, нараховану зарплату (ФОП, «всього нараховано»), єдиний соціальний внесок, військовий збір, податок на доходи фізичних осіб, «всього утримано», «до видачі».

• ЄСВ = 3,6% від ФОП

• Військовий збір = 1,5% від ФОП

• Податок на доходи фізичних осіб = 15% від ФОП – ЄСВ)

• Збережіть запит під ім'ям «Розрахунковий лист 1».

6. Створіть запити, що відображають нараховану заробітну плату та податки (розрахунковий лист) за лютий.

• Для цього немає необхідності повторювати всі вищеописані операції. Можна зробити копії існуючих запитів та змінити в них Табель 1 на Табель 2:

7. З допомогою майстра звітів створити звіт для друку розрахункового листа співробітників підприємства. У режимі конструктора звітів змініть звіт так, щоб при друку документ виглядав наступним чином:

• Для цього в режимі конструктора змініть звіт так, як вказано в зразку:

8. Створіть звіт, що видає відомості для конкретного працівника (за табельним номером):

• Відкрити сторінку властивостей звіту в режимі конструктора), на вкладці «Дані» знайдіть «Джерело записів», натисніть на кнопку . Відкриється «Конструктор запитів». Додайте умову відбору:

9. Сповістіть викладача про закінчення роботи, вимкніть комп'ютер.

Додаткові завдання.

1. Створіть запит на вибірку, на основі таблиці Кадри бази даних Підприємство. На бланку конструктора запитів запишіть назви полів і умови для відображення в режимі перегляду таблиці з даними про співробітників третьої категорії. Таблиця повинна містити прізвища, імена та по батькові співробітників, а також посада і категорію.

2. Створіть запит на вибірку, на основі таблиці Кадри бази даних Підприємство. На бланку конструктора запитів запишіть назви полів і умови для відображення в режимі перегляду таблиці з даними про співробітників. народилися не пізніше 1.01.1998 р. Таблиця повинна містити прізвища. імена, по батькові співробітників, а також їх дату народження.

3. Створіть запит на вибірку, на основі таблиці Кадри бази даних Підприємство. На бланку конструктора запитів запишіть назви полів і умови для відображення в режимі перегляду таблиці з даними про співробітників першої категорії. Таблиця повинна містити прізвища, імена та по батькові співробітників, а також їх посаду і категорію.

4. Створіть запит на вибірку, на основі таблиці Кадри бази даних Підприємство. На бланку конструктора запитів запишіть назви полів і умови для відображення в режимі перегляду таблиці з даними про співробітників, які мають пільги. Таблиця повинна містити прізвища, імена та по батькові співробітників, а також їх посаду та наявність пільг.

5. Створіть запит на вибірку, на основі таблиці Кадри бази даних Підприємство. На бланку конструктора запитів запишіть назви полів і умови для відображення в режимі перегляду таблиці з даними про співробітників, ім'я яких починається з літери «П». Таблиця повинна містити прізвища, імена та по батькові співробітників, а також їх посада та оклад.

6. Створіть запит на вибірку, на основі таблиці Кадри бази даних Підприємство. На бланку конструктора запитів запишіть назви полів і умови для відображення в режимі перегляду таблиці з даними про співробітників, зарплата яких перевищує 2400 грн. Таблиця повинна містити прізвища, імена та по батькові співробітників, а також їх посада та оклад.

7. На бланку конструктора запитів запишіть назви полів і умови для відображення в режимі перегляду таблиці з даними про співробітників, які народилися не раніше 01.01.1978. Таблиця повинна містити прізвища, імена та по батькові співробітників, а також дату їх народження.

8. На бланку конструктора запитів запишіть назви полів і умови для відображення в режимі перегляду таблиці з даними про працівників, які працюють на посаді робітника. Таблиця повинна містити прізвища, імена та по батькові співробітників, а також їх оклад.