Подсчет данных при помощи запроса

Ввод примеров данных вручную

1. на вкладке Создание в группе Таблицы нажмите кнопку Таблица.

   Access добавит в базу данных новую пустую таблицу.

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

2. Дважды щелкните первую ячейку в строке заголовков и введите имя поля из примера таблицы.

   По умолчанию в Access пустые поля обозначаются надписью Добавить поле в строке заголовков:

   

3. С помощью клавиш со стрелками перейдите в следующую пустую ячейку заголовка и введите имя второго поля (чтобы перейти в другую ячейку, можно также нажать клавишу TAB или щелкнуть эту ячейку два раза). Повторите эти действия, чтобы ввести имена всех полей.

4. Введите данные в пример таблицы.

   По мере ввода данных Access определяет их тип для каждого поля. Если вы плохо знакомы с реляционными базами данных, для каждого поля таблицы следует задать конкретный тип данных, например "Число", "Текст" или "Дата/время". Это обеспечивает точный ввод данных и помогает предотвратить ошибки, например использование цифр номера телефона в вычислениях. Для этих примеров таблиц можно определить тип данных автоматически.

5. Завершив ввод данных, нажмите кнопку Сохранить.

   (Сочетание клавиш: нажмите CTRL+S.)

   Откроется диалоговое окно Сохранение документа.

6. В поле Имя таблицы введите имя примера таблицы и нажмите кнопку ОК.

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

7. Повторяйте эти шаги, пока не создадите каждый из примеров таблиц, приведенных в начале этого раздела.

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

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

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

2. Скопируйте первый пример таблицы и вставьте его на первый лист, начиная с первой ячейки.

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

4. Повторяйте шаги 2 и 3, чтобы скопировать каждый пример таблицы на пустой лист и переименовать этот лист.

   Примечание: К файлу электронной таблицы может понадобиться добавить листы. Сведения о том, как сделать это, см. в справке редактора электронных таблиц.

5. Сохраните книгу в нужной папке на компьютере или в сети и переходите к следующей процедуре.

Создание таблиц базы данных на основе листов

1. На вкладке Внешние данные в группе Импорт ссылки & щелкните Создать источник данных > из файла > Excel.

   Откроется диалоговое окно Получение внешних данных — электронная таблица Excel .

2. Нажмите кнопку Обзор, откройте файл электронной таблицы, созданный на предыдущих этапах, и нажмите кнопку ОК.

   Откроется окно мастера импорта электронных таблиц.

3. По умолчанию мастер выбирает первый лист в книге (в этом примере — лист Клиенты), и данные из этого листа появляются в нижней части страницы мастера. Нажмите кнопку Далее.

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

5. Если нужно, вы можете изменить имена полей и типы данных или пропустить некоторые поля, воспользовавшись текстовыми полями и списками в группе Параметры поля. В противном случае нажмите кнопку Далее.

6. Оставьте параметр автоматически создать ключ выбранным и нажмите кнопку Далее.

7. По умолчанию Access использует имя листа для новой таблицы. Оставьте это имя или введите другое, а затем нажмите кнопку Готово.

8. Повторите шаги с 1 по 7 для каждого листа книги Excel, чтобы создать для него таблицу.

Переименование полей первичного ключа

1. В области навигации щелкните правой кнопкой мыши каждую таблицу, созданную на предыдущем шаге, и выберите команду Конструктор.

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

3. В столбце Имя поля каждого поля первичного ключа добавьте имя таблицы.

   Например, поле идентификатора для таблицы Категорий следует переименовать в "Идентификатор категории", а поле таблицы "Заказы" — в "Идентификатор заказа". Для таблицы Сведения о заказе переименуйте поле в "Идентификатор сведений". Для таблицы Products переименуйте поле в "Идентификатор продукта".

4. Сохраните изменения.

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

К началу страницы

Создание простого запроса на выборку

1. На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.

2. Дважды щелкните таблицу или таблицы, которые вы хотите использовать в запросе.

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

3. Дважды щелкните поля таблицы, которые вы хотите использовать в запросе.

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

   Каждое поле отображается в ячейке в бланке запроса.

4. Щелкните Выполнить , чтобы выполнить запрос.

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

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

6. Сохраните запрос.

Добавление строки итогов

1. Убедитесь в том, что запрос открыт в режиме таблицы. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Режим таблицы.

   -или-

   Дважды щелкните запрос в области навигации. Запрос будет выполнен, а его результаты будут загружены в таблицу.

2. На вкладке Главная в группе Записи нажмите кнопку Итоги.

   В таблице появится новая строка Итог.

3. В строке Итог щелкните ячейку в поле, по которому вы хотите вычислить сумму, и выберите в списке функцию Sum.

Скрытие строки итогов

• На вкладке Главная в группе Записи нажмите кнопку Итоги.

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

К началу страницы

Вычисление простого общего итога

1. На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.

2. Дважды щелкните таблицу, которую вы хотите использовать в запросе.

   При использовании примера данных нужно дважды щелкнуть таблицу "Заказы".

   Таблица появится в окне в верхней части конструктора запросов.

3. Дважды щелкните поле, для которого вы хотите найти сумму. Убедитесь, что поле имеет тип данных "Число" или "Денежный". При попытке суммировать значения в нечисловых полях, например в текстовом поле, Access выводит сообщение об ошибке Несоответствие типов данных в выражении условия отбора.

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

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

4. На вкладке Конструктор запросов в группе Показать и скрыть щелкните Итоги.

   В бланке появится строка Итог, а в ячейке столбца "Стоимость доставки" будет указано Группировка.

5. Измените значение в ячейке строки Итог строки на Sum.

6. Нажмите кнопку Выполнить , чтобы выполнить запрос и отобразить результаты в режиме таблицы.

   Совет: Обратите внимание, что Access добавляет "SumOf" в начало имени поля, которое вы суммируете. Чтобы изменить заголовок столбца на более осмысленный, например Total Shipping, вернитесь в режим конструктора и щелкните поле столбца Плата за доставку в конструкторской сетке. Поместите курсор рядом с полем Плата за доставку и введите слова Total Shipping, а затем двоеточие, например: Total Shipping: Shipping Fee.

7. При необходимости вы можете сохранить запрос и закрыть его.

Вычисление общего итога за исключением нескольких записей

1. На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.

2. Дважды щелкните таблицу Order и Order Details(Сведения о заказе).

3. Добавьте поле "Дата заказа" из таблицы "Заказы" в первый столбец на бланке запроса.

4. В строке Условие отбора первого столбца введите Date() -1. Это выражение исключает записи с текущей датой из вычисляемого итогового значения.

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

   Общее значение продаж: (1-[Сведения о заказах].[Скидка]/100)*([Сведения о заказах].[Цена за единицу]*[Сведения о заказах].[Количество])

   Убедитесь, что выражение ссылается на поля с типами данных "Число" или "Денежный". Если оно ссылается на поля с другими типами данных, то при попытке выполнения запроса появится сообщение Несоответствие типов данных в выражении условия отбора.

6. На вкладке Конструктор запросов в группе Показать и скрыть щелкните Итоги.

   В бланке появится строка Итог, а в первом и втором столбцах будет указано Группировка.

7. Во втором столбце измените значение в ячейке строки Итог строки на Sum. Функция Sum суммирует отдельные показатели продаж.

8. Нажмите кнопку Выполнить , чтобы выполнить запрос и отобразить результаты в режиме таблицы.

9. Сохраните запрос с именем Продажи за день.

   Примечание: При следующем открытии запроса в Конструкторе можно заметить небольшие изменения в значениях, указанных для строк Поле и Итог в столбце "Общее значение продаж". Выражение заключено в функцию Sum, а в строке Итог выводится Выражение вместо функции Sum.

   Например, если вы использовали пример данных и создали запрос, как показано выше, будет отображено:

   Общее значение продаж: Sum((1-[Сведения о заказах].Скидка/100)*([Сведения о заказах].Цена*[Сведения о заказах].Количество))

К началу страницы

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

1. На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.

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

   Таблица появится в виде окна в верхней части конструктора запросов.

   При использовании примеров таблиц, указанных выше, добавьте таблицы "Товары" и "Сведения о заказах".

3. Дважды щелкните поля таблицы, которые вы хотите использовать в запросе.

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

   1. Добавьте поле "Категория" из таблицы "Товары" в бланк запроса.

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

      Общее значение продаж: (1-[Сведения о заказах].[Скидка]/100)*([Сведения о заказах].[Цена за единицу]*[Сведения о заказах].[Количество])

      Убедитесь, что поля, на которые ссылается выражение, имеют типы данных "Число" или "Денежный". Если оно ссылается на поля с другими типами данных, то при попытке переключения в режим таблицы появится сообщение об ошибке Несоответствие типов данных в выражении условия отбора.

   3. На вкладке Конструктор запросов в группе Показать и скрыть щелкните Итоги.

      В бланке появится строка Итог, в первом и втором столбцах которой будет указано Группировка.

   4. Во втором столбце измените значение в строке Итог строки на Sum. Функция Sum суммирует отдельные показатели продаж.

   5. Нажмите кнопку Выполнить , чтобы выполнить запрос и отобразить результаты в режиме таблицы.

   6. Оставьте запрос открытым, чтобы использовать его в следующем разделе.

      Использование условий в итоговом запросе

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

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

   7. Условия, игнорирующие некоторые группы при вычислении итогов.    Например, можно вычислить итоги только для категорий товаров "Видеоигры", "Картины и рамы" и "Спортивное снаряжение".

   8. Условия, скрывающие некоторые итоговые значения после их вычисления.    Например, можно отобразить только итоговые значения свыше 150 000 ₽.

   9. Условия, исключающие некоторые записи при вычислении итогового значения.    Например, можно исключить отдельные транзакции, в которых значение (Цена за единицу * Количество) меньше 100 ₽.

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

      Добавление условий в запрос

   10. Откройте запрос из предыдущего раздела в Конструкторе. Для этого щелкните правой кнопкой мыши вкладку документа для запроса и выберите команду Конструктор.

       -или-

       Щелкните правой кнопкой мыши запрос в области навигации и выберите Конструктор.

   11. В строке Условия отбора столбца "Код категории" введите =Куклы Or Спортивное снаряжение or Картины и рамы.

   12. Нажмите кнопку Выполнить , чтобы выполнить запрос и отобразить результаты в режиме таблицы.

   13. Вернитесь в Конструктор и в строке Условия отбора столбца "Общее значение продаж" введите >100.

   14. Выполните запрос, чтобы просмотреть результаты, а затем переключитесь в Конструктор.

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

       Примечание: Невозможно указать третье условие для столбца "Общее значение продаж". Любое условие, указанное для этого столбца, будет применено к итоговому значению, а не к отдельным значениям.

   16. Скопируйте выражение из второго столбца в третий столбец.

   17. В строке Итог нового столбца выберите Условие, а в строке Условия отбора введите >20.

   18. Выполните запрос, чтобы просмотреть результаты, а затем сохраните его.

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

       Общее значение продаж: Sum((1-[Сведения о заказах].Скидка/100)*([Сведения о заказах].Цена*[ Сведения о заказах].Количество))

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

К началу страницы

Создание перекрестного запроса

1. На вкладке Создание в группе Запросы нажмите кнопку Конструктор запросов.

2. Дважды щелкните таблицы, которые нужно использовать в запросе.

   Таблица появится в виде окна в верхней части конструктора запросов.

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

3. Дважды щелкните поля, которые вы хотите использовать в запросе.

   Каждое имя поля появится в пустой ячейке в строке Поле на бланке.

   При использовании примеров таблиц добавьте поля "Город назначения" и "Дата отгрузки" из таблицы "Заказы".

4. В следующую пустую ячейку строки Поле скопируйте и вставьте или введите следующее выражение: Итоги продаж: Sum(CCur([Сведения о заказах].[Цена за едницу]*[Количество]*(1-[Скидка])/100)*100)

5. На вкладке Конструктор запроса в группе Тип запроса щелкните Перекрестная вкладка.

   В бланке запроса будут отображены строки Итог и Перекрестный.

6. Щелкните ячейку в строке Итог в поле "Город назначения" и выберите Группировка. Выполните те же действия для поля "Дата отгрузки". Измените значение в ячейке Итоги поля "Итоги продаж" на Выражение.

7. В строке Перекрестный присвойте ячейке в поле "Город назначения" значение Заголовки строк, полю "Дата отгрузки" — значение Заголовки столбцов, а полю "Итоги продаж" — Значение.

8. На вкладке Конструктор запросов в группе Результаты нажмите кнопку Выполнить.

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

К началу страницы