VBA для чайников

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

Это позволяет сделать приведенная ниже инструкция:

SELECT UCase(Name) AS [ Clerk's name] FROM Clerks

При использовании обобщенных функций SQL инструкция SELECT позволяет получить набор записей, который будет содержать только одно результирующее значение, например, количество записей, содержащих заданное значение в определенном поле:

SELECT Count (Recyclable) AS [Can Recycle] From Toys или среднее значение всех полей:

SELECT Avg(Price) AS [Average Price] FROM Toys

После этого вы можете передать значение поля этой записи переменной в вашем коде VBA для использования в вычислениях или отображения в форме: intRecyclabl eCount = rstRecyclabl eToys![ Canrecycle]

К обобщенным функциям относятся следующие: Count, Avg, Sum, Min, Max, а также несколько статистических функций.

Используйте предикаты DISTINCT, DISTINCTROW и ТОР в инструкции SELECT в качестве простых инструментов получения определенных наборов записей из баз данных. Эти специальные слова необходимо указывать сразу после инструкции SELECT, как показано в табл. 17.3.

Таблица 17.3. Предикаты SQL для выбора записей

Предикат	Использование	Пример
DISTINCT	Выбирает только одну запись, если база данных содержит несколько записей с идентичными данными в указанных полях	SELECT DISTINCT Address FROM Members Возвращает набор записей, содержащий только одну запись для каждого адреса, даже если в таблице Member s содержится по несколько записей для каждого адреса
DISTINCT ROW	Выбирает все уникальные записи, базируясь на значениях всех полей. Если две записи отличаются всего одним символом, они все равно будут включены в набор; если они полностью идентичны, в набор будет включена только одна из них	SELECT DISTINCTROW Name, Address FROM Members Возвращает набор записей, содержащий записи с полями Name и Address, Набор может содержать дублирующиеся записи, но только в том случае, если отличаются значения других полей
ТОР л	Выбирает указанное число записей в верхней или нижней части определяемого ключевым словом ORDER BY	SELECT TOP 10 ToyName FROM Toys ORDER BY Units Sold Возвращает набор записей, содержащий сведения о 10 диапазона, лучше всего продаваемых игрушках. Для определений 10 хуже всего продаваемых игрушек вам следует добавить ключевое слово ASC (ascending - no убыванию) после слова Units Sold

Настройка набора записей: задаем критерии

Для ограничения набора записей только теми записями, которые удовлетворяют определенным критериям, добавьте к инструкции SELECT ключевое слово WHERE, как показано в приведенных ниже примерах:

SELECT * FROM Toys WHERE Price <= 20

SELECT Customer, Date FROM Sales WHERE Date = #10/24/2000#

SELECT Name, Rank, CerealNumber FROM Kids WHERE Rank = 'Queen'

SELECT Name, Age, [Shoe Size] FROM Kids WHERE Age Between 3 And 6

Как легко видеть, ключевое слово WHERE указывается после ключевого слова FROM и содержит выражение, определяющее критерий, которому должны соответствовать записи, чтобы попасть в набор. Кроме того, эти выражения не похожи на обычные выражения VBA. Во-первых, строковые значения заключаются в одинарные, а не двойные кавычки. Во-вторых, вы можете определять диапазоны с помощью конструкции Between ... And, которая в VBA отсутствует. И в SQL оператор Like функционирует совсем не так, как в VBA.

Вы можете объединить несколько выражений, используя логические операторы (And, Or и т.д.), как показано ниже;

SELECT * FROM Toys WHERE Price > 20 And Category = 'Action Figures'

В коде VBA принято использовать одинарные кавычки для определения строки в инструкции SQL, которая целиком является строкой с точки зрения VBA, a значит, заключается в двойные кавычки. Например, вы можете настроить объект Command следующим образом:

strSQL = "SELECT Name FROM Kids WHERE Hates =

'Brocolli'" cmdEr.CommandText = strSQL

Очень часто, особенно при использовании ключевого слова WHERE, вам необходимо, чтобы часть инструкции SQL основывалась на переменной; например, если вы выполняете запрос, базирующийся на данных, введенных пользователем в текстовом поле формы. Добавьте значение переменной к остальной части строки. Если переменная представляет строковое значение, не забудьте заключить ее в одинарные кавычки, как показано в следующем примере:

strSQL = "SELECT Name FROM Kids WHERE Hates = ' " _

& frmInputForm.Text Box l & "'"

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

Ключевое слов GROUP BY позволяет вам объединять записи, содержащие одинаковые значения в указанных полях, преобразуя их в одну запись в полученном наборе записей. Обычно это ключевое слово используется в том случае, если вам необходим набор записей, содержащий общие сведения о данных. Например, вам понадобилось узнать, сколько записей содержится в базе данных для каждого значения указанного поля. Соответствующий пример приведен ниже:

SELECT Category, Countf[Category]) AS [Number of Items] FROM Toys

GROUP BY Category;

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

Категория	Количество элементов
Солдатики	42
Куклы	37
Игры	29
Мягкие игрушки	23
Головоломки	17
Спортивные товары	31

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

Ключевое слово HAVING следует после ключевого слова GROUP BY и позволяет определить критерии для сгруппированных записей. Оно работает практически так же, как и ключевое слово WHERE; вы можете использовать его отдельно или в комбинации со словом WHERE для того, чтобы наложить на полученные записи дополнительные ограничения. В этом примере ключевое слово HAVING включает только те категории, которые содержат как минимум пять записей, удовлетворяющих критериям, определенным с помощью ключевого слова WHERE:

SELECT Category, Count(Category) As [Number cf Items] FROM Toys

WHERE Price > 100 GROUP BY Category HAVING Count(Category) > 4

Используйте оператор ORDER BY для сортировки записей, полученных с помощью инструкции SELECT, в соответствии со значениями одного или нескольких полей. Оператор ORDER BY указывается в конце инструкции, как показано ниже:

SELECT Toy, Price, InStock FROM ToyInventory ORDER BY Toy

В полученном наборе записей список игрушек будет упорядочен по именам.

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

SELECT Toy, Price FROM ToyInventory ORDER BY Price DESC, Toy

По умолчанию сортировка всегда проводится по возрастанию. Для явного указания порядка сортировки используйте ключевое слово DESC (descending- убывание) или ASC (ascending- возрастание), после которого необходимо указать имя соответствующего поля.

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