Улесняване на четенето на обобщени данни с помощта на кръстосана заявка

С помощта на изгледа за проектиране за създаване на вашата кръстосана заявка можете да използвате толкова източници на записи (таблици и заявки), колкото желаете. Можете обаче да запазите проекта прост, като първо създадете заявка за избиране, която връща всички данни, които искате, и след това използвате тази заявка като единствен източник на записи за вашата кръстосана заявка. За повече информация относно създаването на заявка за избиране вижте раздела Вж. също .

Когато съставяте кръстосана заявка в изглед за проектиране, можете да използвате редовете Общо и Кръстосана в мрежата за проектиране, за да зададете стойностите на кое поле ще станат заглавия на колони, стойностите на кои полета ще станат заглавия на редове и от стойностите на кое поле ще се изчислява сума, средна стойност, брой или нещо друго.

1. Настройките в тези редове определят дали полето е заглавие на ред, заглавие на колона, или обобщена стойност.

2. Тази настройка показва стойностите на полето като заглавия на редове.

3. Тази настройка показва стойностите на полето като заглавия на колони.

4. Тези настройки създават обобщени стойности.

Създаване на заявката

1. В раздела Създаване, в групата Заявки щракнете върху Проектиране на заявка.

   

2. В диалоговия прозорец Показване на таблица щракнете двукратно върху всяка таблица или заявка, която искате да използвате като източник на записи.

   Ако използвате повече от един източник на записи, уверете се, че таблиците или заявките са съединени в полета, които са общи за тях. За повече информация относно съединяването на таблици и заявки вижте раздела Вж. също .

3. Затворете диалоговия прозорец Показване на таблица.

4. В раздела Структура, в групата Тип на заявката щракнете върху Кръстосана.

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

6. В мрежата на заявката за проектиране, в реда Кръстосана за всяко поле за заглавия на редове изберете Заглавие на ред.

   Можете да въведе израз в реда Критерии, за да ограничите резултатите за това поле. Можете също да използвате реда Сортиране, за да зададете реда на сортиране за поле.

7. В прозореца за проектиране на заявки щракнете двукратно върху полето, което искате да използвате като източник на заглавия на колони. Можете да изберете само едно поле за заглавия на колони.

8. В мрежата на заявката за проектиране, в реда Кръстосана за полето за заглавия на колони изберете Заглавие на колона.

   Можете да въведете израз в реда Критерии, за да ограничите резултатите за полето за заглавия на колони. Използването обаче на израз за критерий с полето за заглавия на колони не ограничава броя на колоните, върнати от кръстосаната заявка. Вместо това то ограничава кои колони да съдържат данни. Нека например да имате поле за заглавия на колони, което има три възможни стойности: червено, зелено и синьо. Ако приложите критерий ="синьо" към полето за заглавия на колони, кръстосаната заявка пак ще показва колона за червено и колона за зелено, но само колоната за синьо ще съдържа данни.

   Ако искате да ограничите стойностите, които се показват като заглавия на колони, можете да зададете списък с фиксирани стойности с помощта на свойството Заглавия на колони на заявката. За повече информация вж. следващия раздел.

9. В прозореца за проектиране на заявки щракнете двукратно върху полето, което искате да използвате за изчисление на обобщени стойности. Можете да изберете само едно поле, което да използвате за обобщени стойности.

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

11. В реда Кръстосана за полето за обобщени стойности изберете Стойност.

    Не може да задавате критерии или да сортирате по поле за обобщени стойности.

12. В раздела Структура, в групата Резултати щракнете върху Изпълни.

Задаване на фиксирани стойности за заглавия на колони

Ако искате да зададете фиксирани стойности, които да използвате за заглавия на колони, можете да зададете свойството Заглавия на колони на заявката.

1. Отворете кръстосаната заявка в изглед за проектиране.

2. Ако списъкът на свойствата не се вижда, натиснете F4, за да го покажете.

3. В списък със свойства, точно над раздела Общи проверете дали Тип на селекцията е Свойства на заявката. Ако не е, щракнете върху празно място в пространството над мрежата на заявката за проектиране.

4. В списъка със свойства, в раздела Общи, в свойството Заглавия на колони въведете разделен със запетаи списък със стойности, които искате да използвате като заглавия на колони.

   Някои знаци (например повечето препинателни знаци) не са разрешени за заглавия на колони. Ако използвате тези знаци във вашия списък със стойности, Access замества всеки такъв знак със знака за подчертаване (_).

SQL синтаксис за кръстосана заявка

Кръстосаната заявка се изразява в SQL като команда TRANSFORM. Командата TRANSFORM има следния синтаксис:

Команда заизбор
на функция
TRANSFORM PIVOT обобщно поле [IN (стойност1[, стойност2[, ...]])]

Командата TRANSFORM има следните части:

Част	Описание
агр_функция	SQL агрегатна функция, която се изпълнява върху избраните данни.
select_команда	Команда SELECT.
обобщено_поле	Полето или израза, които искате да използвате за създаване на заглавия на колоните в набора резултати от заявката.
стойност1; стойност2	Фиксирани стойности, използвани за създаване на заглавия на колони.

SQL изгледът не ограничава броя на таблиците или заявките, които можете да използвате като източници на записи за кръстосана заявка. Можете обаче да улесните проектирането, като създадете заявка за избиране, която връща всички данни, които искате да използвате във вашата кръстосана заявка, и след това използвате тази заявка за избиране като източник на записи. За повече информация относно създаването на заявка за избиране вижте раздела Вж. също .

1. В раздела Създаване, в групата Други щракнете върху Проектиране на заявка.

2. Затворете диалоговия прозорец Показване на таблица.

3. В раздела Структура, в групата Изгледи щракнете върху Изглед, след което щракнете върху Режим SQL.

4. В раздела SQL обект въведете или поставете следната SQL команда:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. На първия ред, след TRANSFORM, въведете израза, който да се използва, за да се изчислят обобщените стойности; например Sum([Количество]).

   Ако използвате повече от една таблица или заявка като източник на записи, включете името на таблицата или заявката като част от всяко име на поле; например Sum([Разход]. [Количество]).

6. На втория ред, след SELECT въведете списъка с полета или изрази за полета, които искате да използвате за заглавията на редовете. Разделете елементите от списъка със запетаи; например [Бюджет].[ИД_отдел],[Разход].[Тип].

7. На третия ред, след FROM въведете списъка с таблиците или заявките, които използвате като източници на записи, напр. Бюджет,Разход.

8. На четвъртия ред, след GROUP BY въведете същия списък с полета, който използвахте в клаузата SELECT в стъпка 6.

9. На петия ред, след PIVOT въведете името на полето (или израза), което искате да използвате за заглавията на колоните, напр. PIVOT[Бюджет].[Година].

Понякога, вместо да използвате всяка стойност на поле за заглавията на редовете или колоните, искате да групирате стойностите на поле в обхвати и след това да използвате тези обхвати за заглавията на редовете или колоните. Да предположим например, че използвате поле "Възраст" за заглавия на колоните. Вместо да използвате по една колона за всяка възраст, можете да предпочетете да използвате колони, които представят възрастови групи.

Можете да използвате функцията IIf в израз, за да създадете обхвати, които да използвате за заглавия на редове или колони.

1. Отворете кръстосаната заявка в изглед за проектиране.

2. В мрежата на заявката за проектиране, в реда Поле щракнете с десния бутон върху празна колона и след това щракнете върху Мащабиране в контекстното меню.

3. В полето Мащабиране въведете псевдоним на полето, последван от двоеточие (:).

4. Въведете IIf().

5. В скобите след IIf въведете израз за сравнение, който дефинира първия диапазон от стойности на полета.

   Нека например да създавате обхвати за поле "Възраст" и искате всеки обхват да бъде от 20 години. Изразът за сравнение за първия обхват е [Възраст]<21.

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

   Например след [Възраст]<21 въведете запетая и след това въведете "0 – 20 години".

7. Въведете запетая след името на обхвата (извън кавичките) и след това направете едно от следните неща:

   • За да създадете друг обхват, въведете IIf() и след това повторете стъпки 5, 6 и 7.

   • За последния обхват въведете само име за обхвата.

     Например един завърши вложен израз IIf, който разделя поле "Възраст" на обхвати от 20 години, може да изглежда така (за по-лесно четене са добавени нови редове):

     IIf([Age]<21,"0-20 years",
      IIf([Age]<41,"21-40 years",
      IIf([Age]<61,"41-60 years",
      IIf([Age]<81,"61-80 years", "80+ years"))))
     

     Забележка: Когато Access оценява израза, той прекратява оценяването веднага щом една от инструкциите IIf даде като резултат True. Не трябва да задавате долна граница на всеки обхват, защото всяка стойност, която попадне под долната граница на даден обхват, вече ще даде като резултат True.

8. В мрежата на заявката за проектиране, в реда Общо изберете Групиране по.

9. В реда Кръстосана задайте дали ще използвате обхватите като заглавия на редове или заглавия на колони. Запомнете, че можете да зададете от едно до три заглавия на редове и едно заглавие на колони.

Може да искате вашата кръстосана заявка да подканва за въвеждане на данни, когато се изпълни. Нека например да използвате няколко заглавия на редове, едно от които е "Страна/регион". Вместо винаги да показвате данни за всяка страна или регион, може да искате вашата заявка да подканва за въвеждане на име и след това да показва данните в зависимост от въведеното.

Можете да добавите подканване за параметър за всяко поле за заглавия на редове.

1. Отворете кръстосаната заявка в изглед за проектиране.

2. В реда Критерии за полето за заглавия на редове, за което искате да подканвате потребителя да въвежда данни, въведете текст на въпрос, поставен в квадратни скоби. Текстът на въпроса ще се появи като подканване, когато заявката се изпълнява.

   Ако например въведете [Коя страна или регион?] в реда Критерии, когато заявката се изпълнява, се показва диалогов прозорец, съдържащ въпроса "Коя страна или регион? ", поле за въвеждане и бутон OK.

   Съвет: Ако искате вашият параметър да бъде гъвкав, включете във вашия израз заместващи символи с помощта на оператора Like. Например вместо да използвате [Коя страна или регион?] като критерий, можете да използвате Like [Коя страна или регион?]&"*", за да съответства параметърът на по-голям диапазон от входни данни. Използването на Like не променя облика на подканването за параметър.

3. В раздела Структура, в групата Покажи/скрий щракнете върху Параметри.

4. В диалоговия прозорец Параметри на заявката, в колоната Параметри въведете същото подканване за параметър, което използвахте в реда Критерии. Включете квадратните скоби, но не включвайте заместващи символи или оператора Like.

5. В колоната Тип на данните изберете типа на данните за параметъра. Типът на данните трябва да съответства на типа на данните на полето за заглавия на редове.

Ако полето, което използвате за изчисляване на обобщени стойности във вашата кръстосана заявка, съдържа стойности Null, тези стойности се игнорират от всяка използвана агрегатна функция. За някои агрегатни функции това може да се отрази на резултатите. Например, за да изчислите средна стойност, трябва да съберете всички стойности и да разделите резултата на броя на стойностите. Но ако полето съдържа стойности Null, тези стойности не се броят в общия брой стойности.

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

Синтаксис на Nz

Nz ( променлива_от_тип_Variant [, стойност_при_Null ] )

Синтаксисът на функцията Nz използва следните аргументи:

Аргумент	Описание
променлива_от_тип_Variant	Задължителен. Променлива от тип данни Variant.
стойност_при_Null	Незадължителен (освен когато се използва в заявка). Стойност от тип Variant, съдържаща стойността, която трябва да бъде върната, ако аргументът променлива_от_тип_Variant е Null. Този аргумент ви позволява да върнете стойност, различна от нула или от низ с нулева дължина. Забележка: Ако използвате функцията Nz в израз в заявка, без да използвате аргумента стойност_при_Null, резултатът ще бъде низ с нулева дължина в полетата, съдържащи стойности Null.

1. Отворете заявката в изглед за проектиране и в мрежата на заявката за проектиране щракнете с десния бутон върху полето Стойност.

2. В контекстното меню щракнете върху Мащабиране.

3. В полето Мащабиране заградете името на полето или израза със скоби и след това въведете Nz пред скобите.

4. Въведете , 0 вътре до дясната скоба.

   Ако например използвате Nz с поле, наречено "Загубени часове", за да преобразувате стойностите Null в нули, вашият израз ще изглежда така:

Nz([Hours Lost], 0)

• Стремете се да са прости    Кръстосаните заявки може да станат трудни за четене, когато броят на комбинациите от редове нарасне. Не използвайте повече заглавия на редове, отколкото имате нужда.

• Създавайте вашата кръстосана заявка на стъпки    Не се ограничавайте само до използването на таблици. Често можете да започнете със създаване на заявка за общи суми и след това да използвате тази заявка като източник на записи за кръстосана заявка.

• Изберете внимателно полето за заглавия на колони    Листовете с данни от кръстосани заявки обикновено са по-лесни за четене, когато броят на заглавията на колони остава относително малък. След като сте идентифицирали полетата, които искате да използвате като заглавия, обсъдете използването на полето, което има най-малко различни стойности, за да създадете заглавия на колони. Ако например вашата заявка изчислява стойност на базата на "Възраст" и "Пол", добре е да използвате "Пол" за заглавия на колоните вместо "Възраст", защото "Пол" има по-малко възможни стойности от "Възраст".

• Можете да използвате подзаявка в клаузата WHERE    Можете да използвате подзаявка като част от клаузата WHERE в кръстосана заявка.

Най-горе на страницата