Zwiększanie czytelności danych podsumowania za pomocą kwerendy krzyżowej

Tworząc zapytanie krzyżowe za pomocą widoku projektu, można użyć dowolnej liczby źródeł rekordów (tabel i zapytań). Można jednak zachować prostotę projektu, tworząc najpierw zapytanie wybierające, które zwraca wszystkie żądane dane, a następnie używając tego zapytania jako jedynego źródła rekordów zapytania krzyżowego. Więcej informacji o tworzeniu zapytania wybierającego można znaleźć w sekcji Zobacz też.

Podczas konstruowania zapytania krzyżowego w widoku projektu użytkownik za pomocą wierszy Suma i Krzyżowe w siatce projektu określa pola, których wartości staną się nagłówkami kolumn, staną się nagłówkami wierszy oraz zostaną użyte do obliczeń (sumowania, zliczania, obliczania średniej itd.).

1. Ustawienia w tych wierszach określają, czy pole jest nagłówkiem wiersza, nagłówkiem kolumny czy wartością podsumowania.

2. To ustawienie powoduje wyświetlanie wartości pola jako nagłówków wierszy.

3. To ustawienie powoduje wyświetlanie wartości pola jako nagłówków kolumn.

4. Te ustawienie powodują zwracanie wartości podsumowania.

Tworzenie zapytania

1. Na karcie Tworzenie w grupie Zapytania kliknij pozycję Projekt zapytania.

   

2. W oknie dialogowym Pokazywanie tabeli kliknij dwukrotnie każdą tabelę lub każde zapytanie, których chcesz użyć jako źródła rekordów.

   Jeśli używasz kilku źródeł rekordów, upewnij się, że te tabele lub zapytania są sprzężone za pomocą wspólnych pól. Aby uzyskać więcej informacji na temat sprzęgania tabel i zapytań, skorzystaj z linków w sekcji Zobacz też.

3. Zamknij okno dialogowe Pokazywanie tabeli.

4. Na karcie Projektowanie w grupie Typ zapytania kliknij przycisk Krzyżowe.

5. W oknie projektu zapytania kliknij dwukrotnie każde pole, którego chcesz użyć jako źródła nagłówków wierszy. Dla potrzeb nagłówków wierszy można wybrać maksymalnie trzy pola.

6. W siatce projektu zapytania w wierszu Krzyżowe dla każdego pola nagłówków wierszy wybierz pozycję Nagłówek wiersza.

   W wierszu Kryteria możesz wprowadzić wyrażenie, aby ograniczyć wyniki dla tego pola. Za pomocą wiersza Sortuj możesz też określić kolejność sortowania dla pola.

7. W oknie projektu zapytania kliknij dwukrotnie pole, którego chcesz użyć jako źródła nagłówków kolumn. Dla nagłówków kolumn możesz wybrać tylko jedno pole.

8. W siatce projektu zapytania w wierszu Krzyżowe dla pola nagłówka kolumny wybierz pozycję Nagłówek kolumny.

   W wierszu Kryteria możesz wprowadzić wyrażenie, aby ograniczyć wyniki dla pola nagłówka kolumny. Użycie wyrażenia ustalającego kryteria z polem nagłówka kolumny nie ogranicza jednak liczby kolumn zwracanych przez zapytanie krzyżowe. Zamiast tego takie wyrażenie ogranicza to, które kolumny zawierają dane. Załóżmy na przykład, że jest pole nagłówka kolumny zawierające trzy możliwe wartości: czerwony, zielony i niebieski. Jeśli do pola nagłówka kolumny zastosujesz kryterium ='niebieski', zapytanie krzyżowe nadal będzie wyświetlać kolumny dla wartości czerwony i dla wartości zielony, ale tylko kolumna dla wartości niebieski będzie zawierać dane.

   Aby ograniczyć wartości wyświetlane jako nagłówki kolumn, można określić listę stałych wartości, używając właściwości Nagłówki kolumn zapytania. Aby uzyskać więcej informacji, zobacz następną sekcję.

9. W oknie projektu zapytania kliknij dwukrotnie pole, za pomocą którego chcesz obliczać wartości podsumowania. Dla potrzeb wartości podsumowania można użyć tylko jednego pola.

10. W siatce projektu zapytania w wierszu Suma dla pola wartości podsumowania wybierz funkcję agregującą, za pomocą której mają być obliczane te wartości.

11. W wierszu Krzyżowe dla pola wartości podsumowania wybierz pozycję Wartość.

    Dla pola wartości podsumowania nie można określić kryteriów. Nie można też sortować według tego pola.

12. Na karcie Projektowanie w grupie Wyniki kliknij przycisk Uruchom.

Określanie stałych wartości dla nagłówków kolumn

Aby określić stałe wartości, które mają być używane dla nagłówków kolumn, można ustawić właściwość Nagłówki kolumn zapytania.

1. Otwórz zapytanie krzyżowe w widoku projektu.

2. Jeżeli arkusz właściwości jest niewidoczny, naciśnij klawisz F4, aby go wyświetlić.

3. Upewnij się, że w arkuszu właściwości tuż nad kartą Ogólne dla pozycji Typ zaznaczenia jest wybrana wartość Właściwości zapytania. Jeśli nie jest wybrana ta wartość, kliknij puste miejsce w obszarze nad siatką projektu zapytania.

4. W arkuszu właściwości na karcie Ogólne we właściwości Nagłówki kolumn wprowadź rozdzielaną średnikami listę wartości, których chcesz użyć jako nagłówków kolumn.

   Niektóre znaki (w tym większość znaków interpunkcyjnych) nie są dozwolone w nagłówkach kolumn. Jeśli użyjesz tych znaków na liście wartości, program Access podstawi podkreślenie (_) dla każdego takiego znaku.

Składnia SQL dla zapytania krzyżowego

Zapytanie krzyżowe jest wyrażane w języku SQL jako instrukcja TRANSFORM. Instrukcja TRANSFORM ma następującą składnię:

TRANSFORM aggfunction
selectstatement
PIVOT pivotfield [IN (wartość1[, wartość2[, ...]])]

Instrukcja TRANSFORM zawiera następujące segmenty:

Segment	Opis
funkcja_agregacji	Funkcja agregująca SQL, która wykonuje operacje na wybranych danych.
instrukcja_select	Instrukcja SELECT.
pole_przestawne	Polu lub wyrażenie które ma posłużyć do utworzenia nagłówków kolumn w zestawie wyników zapytania.
wartość1, wartość2	Stałe wartości używane do tworzenia nagłówków kolumn.

Widok SQL nie ogranicza liczby tabel ani zapytań, których można użyć jako źródeł rekordów dla zapytania krzyżowego. Można jednak zachować prostotę projektu, tworząc zapytanie wybierające zwracające wszystkie dane, które mają zostać użyte w zapytaniu krzyżowym, a następnie używając tego zapytania wybierającego jako źródła rekordów. Więcej informacji o tworzeniu zapytania wybierającego można znaleźć w sekcji Zobacz też.

1. Na karcie Tworzenie w grupie Inne kliknij polecenie Projekt zapytania.

2. Zamknij okno dialogowe Pokazywanie tabeli.

3. Na karcie Projektowanie w grupie Widoki kliknij pozycję Widok, a następnie kliknij polecenie Widok SQL.

4. Na karcie Obiekt SQL wpisz lub wklej poniższy kod języka SQL:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. W pierwszym wierszu po instrukcji TRANSFORM wpisz wyrażenie, za pomocą którego mają być obliczane wartości podsumowania — na przykład Sum([Liczba]).

   Jeśli źródłem rekordów jest więcej niż jedna tabela lub jedno zapytanie, dołącz nazwę tabeli lub zapytania jako część nazwy każdego pola — na przykład Sum([Koszt].[Liczba]).

6. W drugim wierszu po instrukcji SELECT wpisz listę pól lub wyrażeń pól, których chcesz użyć dla nagłówków wierszy. Rozdziel elementy listy przecinkami — na przykład [Budżet].[ID_działu], [Koszt].[Typ].

7. W trzecim wierszu po instrukcji FROM wpisz listę tabel lub zapytań, których chcesz użyć jako źródeł rekordów — na przykład Budżet, Koszt.

8. W czwartym wierszu po instrukcji GROUP BY wpisz tę samą listę pól, która została użyta w klauzuli SELECT w kroku 6.

9. W piątym wierszu po instrukcji PIVOT wpisz nazwę pola lub wyrażenie, których chcesz użyć dla nagłówków kolumn — na przykład PIVOT [Budżet].[Rok].

Czasami zamiast używać wszystkich wartości pola dla nagłówków wierszy lub kolumn, trzeba pogrupować wartości pola w zakresy, a następnie użyć tych zakresów dla nagłówków wierszy lub kolumn. Załóżmy na przykład, że dla nagłówków kolumn jest używane pole „Wiek”. Zamiast używać kolumny dla każdego wieku, można użyć kolumn reprezentujących zakresy wieku.

Za pomocą funkcji IIf w wyrażeniu można utworzyć zakresy, które będą używane dla nagłówków wierszy lub kolumn.

1. Otwórz zapytanie krzyżowe w widoku projektu.

2. W siatce projektu zapytania w wierszu Pole kliknij prawym przyciskiem myszy pustą kolumnę, a następnie w menu skrótów kliknij polecenie Powiększenie.

3. W polu Powiększenie wpisz alias pola, a po nim dwukropek (:).

4. Wpisz ciąg IIf().

5. W nawiasach po instrukcji IIf wpisz wyrażenie porównania definiujące pierwszy zakres wartości pola.

   Załóżmy na przykład, że są tworzone zakresy dla pola Wiek i każdy zakres ma mieć długość dwudziestu lat. Wyrażenie porównania dla pierwszego zakresu będzie następujące: [Wiek]<21.

6. Wpisz średnik po wyrażeniu porównania, a następnie wpisz nazwę zakresu ujętą w znaki cudzysłowu. Podana nazwa będzie nagłówkiem zapytania krzyżowego dla wartości należących do tego zakresu.

   Na przykład po ciągu [Wiek]<21 wpisz średnik, a następnie wpisz "0–20 lat".

7. Wpisz średnik po nazwie zakresu (na zewnątrz znaków cudzysłowu), a następnie wykonaj jedną z poniższych czynności:

   • Aby utworzyć następny zakres, wpisz ciąg IIf(), a następnie powtórz kroki 5, 6 i 7.

   • W przypadku ostatniego zakresu wpisz tylko jego nazwę.

     Na przykład kompletne zagnieżdżone wyrażenie IIf, które rozdziela pole Wiek na zakresy o długości dwudziestu lat, może wyglądać następująco (w celu zwiększenia czytelności dodano podziały wierszy):

     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"))))
     

     Uwaga: Podczas wyznaczania wartości wyrażenia program Access przestaje wyznaczać tę wartość zaraz, gdy jedna z instrukcji IIf okaże się prawdziwa. Nie trzeba określać dolnego krańca każdego zakresu, ponieważ każda wartość przypadająca poniżej dolnego krańca danego zakresu zostałaby już określona jako prawdziwa.

8. W siatce projektu zapytania w wierszu Suma wybierz pozycję Grupuj według.

9. W wierszu Krzyżowe określ, czy zakresy mają być używane jako nagłówki wierszy lub nagłówki kolumn. Pamiętaj, że możesz określić od jednego do trzech nagłówków wierszy i tylko jeden nagłówek kolumny.

Użytkownik może chcieć, aby zapytanie krzyżowe monitowało o podanie parametru po jego uruchomieniu. Załóżmy na przykład, że jest używanych kilka nagłówków wierszy, z których jeden to Kraj/region. Zamiast zawsze wyświetlać dane dla każdego kraju lub regionu, można skonfigurować zapytanie, aby monitowało o nazwę, a następnie wyświetlało dane zgodnie z wartością podaną przez użytkownika.

Monit o parametr można dodać do dowolnego pola nagłówka wiersza.

1. Otwórz zapytanie krzyżowe w widoku projektu.

2. W wierszu Kryteria pola nagłówka wiersza, w przypadku którego chcesz monitować użytkownika o podanie wartości, wpisz tekst pytania ujęty w nawiasy kwadratowe. Tekst pytania będzie wyświetlany jako monit po uruchomieniu zapytania.

   Na przykład w przypadku wpisania w wierszu Kryteria pytania [Jaki kraj lub region?] po uruchomieniu zapytania będzie wyświetlane okno dialogowe zawierające pytanie „Jaki kraj lub region?”, pole wejściowe oraz przycisk OK.

   Porada: Jeśli chcesz, aby parametr był elastyczny, połącz wyrażenie z symbolami wieloznaczowymi przy użyciu operatora Like . Na przykład zamiast kryterium [Jaki kraj lub region?] można użyć jako kryterium Like [Który kraj lub region?] &"*", aby parametr był zgodny z większym zakresem danych wejściowych. Użycie polecenia Like nie zmienia wyglądu monitu o podanie parametru.

3. Na karcie Projektowanie w grupie Pokazywanie/ukrywanie kliknij przycisk Parametry.

4. W oknie dialogowym Parametry zapytania w kolumnie Parametry wprowadź ten sam monit o parametr, który został użyty w wierszu Kryteria. Dołącz nawiasy kwadratowe, ale nie dołączaj żadnych połączonych symboli wieloznacznych ani operatora Like.

5. W kolumnie Typ danych wybierz typ danych dla parametru. Typ danych powinien być zgodny z typem danych pola nagłówka wiersza.

Jeśli pole używane do obliczania wartości posumowania w zapytaniu krzyżowym zawiera wartości null, są one ignorowane przez każdą używaną funkcję agregującą. W przypadku niektórych funkcji agregujących może to wpłynąć na wyniki. Aby na przykład obliczyć średnią, użytkownik dodaje wszystkie wartości i dzieli wynik przez liczbę wartości. Jeśli pole zawiera jakiekolwiek wartości null, nie są one uwzględniane w łącznej liczbie wartości.

W niektórych przypadkach lepiej będzie zamienić wszystkie wartości null na zera, aby te wartości były zliczane w obliczeniach agregujących. Za pomocą funkcji Nz można zamienić wartości null na zera.

Składnia funkcji Nz

Nz ( wariant [, wartość_jeśli_null ] )

Składnia funkcji Nz zawiera następujące argumenty:

Argument	Opis
wariant	Wymagany. Zmienna, której typ danych to Wariant.
wartość_jeśli_null	Opcjonalna (chyba że funkcja została użyta w zapytaniu). Wariant stanowiący wartość zwracaną, jeśli argument wariant ma wartość Null. Argument ten pozwala zwrócić wartość inną niż zero lub inną niż ciąg znaków zerowej długości. Uwaga: Jeśli funkcja Nz zostanie użyta w wyrażeniu w zapytaniu bez użycia argumentu wartość_jeśli_null, wyniki będą ciągami znaków zerowej długości w polach zawierających wartości Null.

1. Po otwarciu zapytania w widoku projektu w siatce projektu zapytania kliknij prawym przyciskiem myszy pole Wartość.

2. W menu skrótów kliknij polecenie Powiększenie.

3. W polu Powiększenie ujmij nazwę pola lub wyrażenie w nawiasy, a następnie przed nawiasami wpisz ciąg Nz.

4. Bezpośrednio przy wewnętrznej stronie prawego nawiasu wpisz ciąg ; 0.

   Jeśli na przykład w celu zamiany wartości null na zera użyto funkcji Nz z polem „Utracone godziny”, kompletne wyrażenie będzie wyglądało następująco:

Nz([Hours Lost], 0)

• Zachowaj prostotę    Zapytania krzyżowe mogą stawać się trudne do odczytania wraz ze wzrostem liczby kombinacji wierszy. Nie należy używać więcej nagłówków wierszy, niż jest potrzebnych.

• Rozbicie procesu konstruowania zapytań krzyżowych na etapy    Nie należy się ograniczać do używania tylko tabel. Często można zacząć od skonstruowania zapytanie podsumowujące, a następnie użyć tego zapytania jako źródła rekordów dla zapytania krzyżowego.

• Staranne wybieranie pola nagłówka kolumny    Arkusze danych zapytań krzyżowych zwykle są łatwiejsze do odczytania, gdy liczba nagłówków kolumn pozostaje względnie niewielka. Po zidentyfikowaniu pól, które mają zostać użyte jako nagłówki, należy użyć pola zawierającego jak najmniej różnych wartości do tworzenia nagłówków kolumn. Jeśli na przykład zapytanie oblicza wartość według wieku i płci, należy rozważyć użycie płci zamiast wieku dla nagłówków kolumn, ponieważ płeć ma zazwyczaj mniej możliwych wartości niż wiek.

• Używanie podzapytania w klauzuli WHERE    W klauzuli WHERE w zapytaniu krzyżowym można użyć podzapytania.

Początek strony