Импортирование и экспортирование таблиц

 

Одним из самых основных преимуществ Microsoft Access является возможность работы с самыми разнообразными данными других баз, электронных таблиц или текстовых файлов. Кроме использования Access - баз данных, можно

импортировать или присоединять данные, хранящиеся в базах данных Access, в файлах dBase, Paradox, FoxPro, Btrieve или в любых других базах данных SQL, поддерживающих стандарт ODBC.

Можно также экспортировать данные из таблиц Access в базы данных, электронные таблицы или текстовые файлы других приложений.

Microsoft Access использует для чтения, вставки, обновления и удаления данных язык, называемый SQL (Structured Query Language - структурированный язык запросов). Язык SQL был утвержден в качестве официального стандарта для реляционных баз данных.

Для всех основных вариантов языка SQL был разработан стандартный интерфейс языка CLI (Common Language Interface). Фирма Microsoft формализовала интерфейс CLI для доступа к базам данных SQL в операционной среде Microsoft Windows. Это интерфейс получил название Open Database Connectivity (ODBC) - открытый доступ к данным.

Импорт имеет преимущества, если:

* необходимый файл сравнительно небольшого размера и пользователь исходной базы данных редко меняет его содержимое;

* данные импортируемого файла не используются совместно пользователями других приложений;

* необходимо обеспечить максимальную эффективность работы с другой СУБД.

Присоединение следует выбирать, если:

* файл по размеру больше максимально возможного размера БД Microsoft Access;

* данные этого файла часто меняются пользователем исходной БД;

* данные этого файла будут использоваться в режиме коллективного доступа.

 

Импорт файлов dBase, Paradox, FoxPro.

1. Открыть базу данных Microsoft Access, в которую нужно импортировать файл.

2. В окне БД выполнить команду Файл/Импорт.

3. Выбрать соответствующий формат и нужный файл.

4. Щелкнуть по кнопке Импорт.

 

Экспортирование таблиц

Если необходимо экспортировать данные из Access-таблиц в текстовый файл, файл электронных таблиц или таблицу базы данных с не-Access форматом, то следует обратиться в меню к директиве Экспорт. Появляющееся при этом диалоговое

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

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

 

Присоединение таблиц

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

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

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

* MS Access;

* Paradox;

* dBase III и dBase iV;

* Btrive (с файлом словаря Xtrive);

* Microsoft SQL-сервер.

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

 

ЗАПРОСЫ ДАННЫХ

 

Типы запросов

Выделяют два типа запросов: QBE - запросы (QBE - Query By Example - запрос по образцу), которые пользователь формирует, определяя отдельные параметры в окне проектирования с использованием подсказок (образцов), и SQL - запросы (SQL - Structured Query Language - структурированный язык запросов), при создании которых пользователь применяет инструкции и функции, выстраивая некоторое описание.

QBE - запрос Access легко транслирует в соответствующий SQL - запрос. Обратная операция тоже не составляет труда. Для Access безразлично, с какими типами запросов работает пользователь.

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

Запрос - выборка используется для выбора из базы данных информации, которая интересует пользователя в данный момент. Результат обработки программой Access такого запроса представляет cобой таблицу (последовательность записей)

и называется Dynaset (динамический, временный набор данных). В эту таблицу включены выбранные из основной таблицы блоки данных, которые удовлетворяют критериям запроса. Dynaset - динамический набор данных, т.е. при каждом выполнении запроса он строится вновь на основе "свежих" табличных данных. В большинстве случаев с этими наборами можно работать точно также как с таблицами: просматривать и выбирать информацию, печатать и даже обновлять записи, однако в отличие от реальной таблицы, этот набор данных физически не существует в базе данных. Access соэдает набор данных из данных таблиц и других запросов. Если изменяются данные в наборе данных, Access вносит соответствующие изменения в таблицы, на базе которых построен запрос.

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

* вычисляемые и итоговые поля;

* данные в перекрестном запросе;

* значения ключевых полей в таблице со стороны "один".

 

Создание запроса-выборки

 

Для создания запроса необходимо в окне базы выбрать корешок Запрос и выполнить команду Создание запроса / Конструктор.

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

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

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

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

Во второй строке бланка QBE Access выводит на экран имена таблиц, из которых выбраны поля.

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

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

Строки Условие отбора используются для ввода условий в качестве фильтра. Несколько условий отбора для нескольких полей, связанных логической связкой И, записываются в одну строку, а связанные логической связкой ИЛИ - в разные строки условий отбора. Несколько условий для одного поля связываются логическим оператором И (AND), если необходимо выполнение всех приведенных условий, и логическим оператором ИЛИ (OR), если достаточно выполнение хотя бы одного условия. Кроме обычных операторов сравнения:

> - больше, чем,

>= - больше или равно,

< - меньше, чем,

<= - меньше или равно,

= - равно,

<> - не равно,

Access предоставляет также специальные операторы:

BETWEEN - для задания диапазона значений,

IN - для сравнения со списком значений,

LIKE - для поиска образцов в текстовых полях.

 

Примеры создания запросов выбора

 

Пример 1. Показать названия и цену товаров, проданных в объеме более 10 единиц.

Поле Название Цена Количество

Имя таблицы ТОВАРЫ ТОВАРЫ ОТПУСК

Условие отбора >20

 

Пример 2. Показать фамилии покупателей Москвы или Ульяновска.

Поле Фамилия Город

Имя таблицы ПОКУПАТЕЛИ ПОКУПАТЕЛИ

Условие отбора IN("Москва";"Ульяновск")

или

Поле Фамилия Город

Имя таблицы ПОКУПАТЕЛИ ПОКУПАТЕЛИ

Условие отбора "Москва" Or "Ульяновск"

 

Пример 3. Вывести названия товаров, цена которых лежит в интервале от 100 до 200 рублей.

Поле Название Цена

Имя таблицы ТОВАРЫ ТОВАРЫ

Условие отбора BETWEEN 100 And 200

или

Поле Название Цена

Имя таблицы ТОВАРЫ ТОВАРЫ

Условие отбора >= 100 And <=200

 

Пример 4. Найти фирмы, название которых начинается с буквы А или Д.

Поле Фирма

Имя таблицы ПОКУПАТЕЛИ

Условие отбора Like "А" Or Like "Д"

 

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

Поле Фирма

Имя таблицы ПОКУПАТЕЛИ

Условие отбора Like "?[а-к]д[0-9]*"

 

Отдельно рассмотрим условия отбора для даты и времени.

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

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

Day(дата) - возвращает значение дня от 1 до 31;

Month(дата) - возвращает значение месяца от 1 до 12;

Year(дата) - возвращает значение года от 100 до 9999;

Weekday(дата) - возвращает целое число от 1 до 7, соответствующее дню недели;

Hour(дата) - возвращает число от 0 до 23 - значение часа;

Date() - возвращает системную дату.

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

 

Пример 6. Вывести список товаров, проданных 15 мая 1998 года.

Поле Название Дата

Имя таблицы ТОВАРЫ ОТПУСК

Условие отбора #15.05.98#

 

Пример 7. Вывести список товаров, проданных в мае 1998 года. В запросе создадим вычисляемые поля по формулам Month([ОТПУСК]![Дата]) и Year([ОТПУСК]![Дата]).

Поле Название Month([ОТПУСК]![Дата])

Имя таблицы ТОВАРЫ

Условие отбора 5

 

Поле Year([ОТПУСК]![Дата])

Имя таблицы

Условие отбора 1998

 

 

Создание вычисляемых полей

 

Можно задать вычисления над любым полем таблицы и сделать вычисляемые значения новым полем в наборе данных. В выражении можно использовать следующие операторы:

& - конкатенация (сцепление, склеивание) текстовых полей или символьных констант;

+, -, *, /, \, ^, mod - соответственно сложение, вычитание, умножение, деление, целочисленное деление, возведение в степень и остаток от деления арифметических выражений.

Также можно использовать множество встроенных функций Access. При построении сложных выражений Access предлагает использовать Построитель выражений.

Для того, чтобы воспользоваться Построителем выражений, следует в бланке запроса QBE щелкнуть по пустому полю, а затем по кнопке Построить панели инструментов. Microsoft Access откроет окно Построителя выражений.

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

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

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

 

Пример 8. Рассчитать общую стоимость покупок.

Поле Название Стоимость:[ТОВАРЫ]![Цена]*

[ОТПУСК]![Количество]

Имя таблицы ТОВАРЫ

 

Создание итоговых запросов

 

Для вычисления в запросе итоговых значений следует щелкнуть по кнопке Групповые операции панели инструментов конструктора запросов, чтобы в бланке QBE появилась строка Групповая операция. Для получения итогов нужно заменить в строке Групповая операция установку Группировка на определение групповых функций.

Access предоставляет девять функций, обеспечивающих выполнение групповых операций:

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

Avg - вычисляет среднее арифметическое всех значений данного поля в каждой группе;

Min - возвращает наименьшее значение, найденное в этом поле внутри каждой группы;

Max - возвращает наибольшее значение, найденное в этом поле внутри каждой группы;

Count - возвращает число записей в каждой группе;

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

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

First - возвращает первое значение этого поля в группе;

Last - возвращает последнее значение этого поля в группе.

 

Пример 9. Определить количество и стоимость товаров, проданных за каждый день. Стоимость вычисляется по формуле

Стоимость:[ТОВАРЫ]![Цена]*[ОТПУСК]![Количество].

Поле Дата Количество Стоимость:

Имя таблицы ОТПУСК ОТПУСК

Групповая операция группировка Sum Sum

 

Чтобы в группы включать только определенные записи, можно добавить в бланке QBE поля, которые будут использоваться в фильтре. Для создания фильтра следует выбрать установку Условие в строке Групповая операция, удалить флажок

Вывод на экран для этого поля и ввести условие отбора.

 

Пример 10. Показать общую сумму продаж каждого товара с 1 мая 1999 года.

 

Поле Название Стоимость Дата

Имя таблицы ТОВАРЫ ОТПУСК

Групповая операция группировка Sum Условие

Условие отбора >#01.05.99#

 

Пример 11. Определить товары, общая сумма продажи которых превысила 10 000 рублей.

 

Поле Название Стоимость

Имя таблицы ТОВАРЫ

Групповая операция группировка Sum

Условие отбора >10 000