Kereszttáblás lekérdezés használata az összesített adatok könnyebb értelmezése érdekében

Ha Tervező nézetet használva hoz létre kereszttáblás lekérdezést, annyi rekordforrást (táblát és lekérdezést) használ, amennyit csak szeretne. Egyszerű maradhat a lekérdezésterv, ha először létrehoz egy választó lekérdezést, amely visszaadja az összes kívánt adatot, majd azt a lekérdezést használja az egyedüli rekordforrásként a kereszttáblás lekérdezéshez. A választó lekérdezés létrehozásáról a Lásd még című szakasz hivatkozásait követve talál részletesebb információkat.

Amikor Tervező nézetben létrehoz egy kereszttáblás lekérdezést, a tervezőrács Összesen és Kereszttábla sorai segítségével adhatja meg, hogy mely mező értékei váljanak oszlopfejlécekké, illetve sorfejlécekké, valamint hogy mely mező értékeit kívánja összegezni, átlagolni, megszámlálni vagy más számításhoz felhasználni.

1. A sorok beállításai határozzák meg, hogy a mező sorfejléc, oszlopfejléc vagy összegzett érték-e.

2. Ez a beállítás sorfejlécekként jeleníti meg a mező értékeit.

3. Ez a beállítás oszlopfejlécekként jeleníti meg a mező értékeit.

4. Ez a beállítás az összesítő értékek előállítására szolgál.

A lekérdezés létrehozása

1. Kattintson a Létrehozás lap Lekérdezések csoportjának Lekérdezéstervező gombjára.

   

2. A Tábla megjelenítése párbeszédpanelen kattintson duplán a rekordforrásként használni kívánt minden táblára vagy lekérdezésre.

   Több rekordforrás használata esetén ügyeljen arra, hogy a táblák vagy a lekérdezések a közös mezőkön keresztül kapcsolódjanak. A táblák és lekérdezések összekapcsolásáról a Lásd még című szakasz hivatkozásait követve talál részletesebb információkat.

3. Zárja be a Tábla megjelenítése párbeszédpanelt.

4. A Tervezés lap Lekérdezés típusa csoportjában kattintson a Kereszttáblás gombra

5. A lekérdezéstervező ablakban kattintson duplán minden, a sorfejlécek forrásaként használni kívánt mezőre. Akár három mezőt is kijelölhet a sorfejlécekhez.

6. A lekérdezéstervező rács Kereszttábla sorában minden sorfejlécmezőnél válassza a Sorfejléc lehetőséget.

   A Feltétel sorba beírt kifejezéssel korlátozhatja az adott mezőhöz tartozó eredményeket. Emellett a Rendezés sorban megadhatja a mező rendezési sorrendjét is.

7. A lekérdezéstervező ablakban kattintson duplán az oszlopfejlécek forrásaként használni kívánt mezőre. Az oszlopfejlécekhez csak egy mezőt választhat.

8. A lekérdezés tervezőrácsának Kereszttábla sorában az oszlopfejléchez tartozó mezőben válassza az Oszlopfejléc lehetőséget.

   A Feltétel sorba beírhat egy kifejezést az oszlopfejlécmező eredményeinek korlátozásához. A feltételkifejezések oszlopfejlécmezővel való használata azonban nem korlátozza a kereszttáblás lekérdezés által visszaadott oszlopok számát. Ehelyett korlátozza, hogy mely oszlopok tartalmaznak adatokat. Tegyük fel például, hogy van egy oszlopfejlécmezője, amely három lehetséges értékkel rendelkezik: piros, zöld és kék. Ha a ='kék' feltételt alkalmazza az oszlopfejlécmezőre, a kereszttáblán továbbra is megjelenik egy piros és egy zöld oszlop, de csak a kék oszlop tartalmaz adatokat.

   Ha korlátozni szeretné az oszlopfejlécként megjelenített értékeket, a lekérdezés Oszlopfejléc tulajdonsága segítségével megadhat egy rögzített értékekből álló listát. További tudnivalók a következő szakaszban találhatók.

9. A lekérdezéstervező ablakban kattintson duplán arra a mezőre, amelyet az összegzett értékek számítására kíván használni. Összegzendő értékekhez való használatra csak egy mezőt jelölhet kit.

10. A lekérdezés tervezőrácsában az összegzett értékek mezőjének Összesen sorában válassza ki az értékek számítására használandó összesítő függvényt.

11. Az összegzett értékek mezőjének Kereszttábla sorában válassza az Érték lehetőséget.

    Az összegzett értékek mezőjéhez nem adhat meg feltételt és nem rendezheti.

12. A Tervezés lap Eredmények csoportjában kattintson a Futtatás gombra.

Rögzített értékek használata az oszlopfejlécekhez

A lekérdezés Oszlopfejléc tulajdonságában állíthatja be, hogy megadott rögzített értékeket szeretne használni az oszlopfejlécekhez.

1. Nyissa meg a kereszttáblás lekérdezést Tervező nézetben.

2. Ha a tulajdonságlap nem látható, jelenítse meg az F4 billentyű lenyomásával.

3. Az Általános fül felett található tulajdonságlapon ellenőrizze, hogy A kijelölés típusa beállítás értéke Lekérdezés tulajdonságai. Ha nem, kattintson egy üres helyre a lekérdezéstervező rács feletti területen.

4. A tulajdonságlap Általános lapján az Oszlopfej tulajdonságban, írja be az oszlopfejlécekként használni kívánt értékek vesszővel tagolt listáját.

   Bizonyos karakterek nem használhatók az oszlopfejlécekben (ilyen például a legtöbb írásjel). Ha ilyen karaktereket használ az értéklistában, az Access aláhúzásjellel (_) helyettesíti azokat.

Kereszttáblás lekérdezés SQL-szintaxisa

A kereszttáblás lekérdezés az SQL-ben TRANSFORM utasításként van kifejezve. A TRANSFORM utasítás szintaxisa:

TRANSFORM aggfunction
selectstatement
PIVOT kimutatásmező [IN (érték1[, érték2[, ...]])]

A TRANSFORM utasítás az alábbi részekből áll:

Rész	Leírás
összesítőfüggvény	A kijelölt adatokat feldolgozó összesítő SQL-függvény.
selectutasítás	Egy SELECT utasítás.
kimutatásmező	Az a mező vagy kifejezés, amelyet a lekérdezés eredményhalmazában oszlopfejlécek létrehozásához szeretne használni.
érték1, érték2	Rögzített értékek az oszlopfejlécek létrehozásához.

Az SQL nézet nem korlátozza a kereszttáblás lekérdezésekhez rekordforrásként használható táblák vagy lekérdezések számát. Egyszerű maradhat a lekérdezésterv, ha először létrehoz egy választó lekérdezést, amely visszaadja a kereszttáblás lekérdezésben használni kívánt összes adatot, majd azt a választó lekérdezést használja rekordforrásként. A választó lekérdezés létrehozásáról a Lásd még című szakasz hivatkozásait követve talál részletesebb információkat.

1. A Létrehozás lap Egyéb csoportjában kattintson a Lekérdezéstervező gombra.

2. Zárja be a Tábla megjelenítése párbeszédpanelt.

3. A Tervezés lap Nézetek csoportjában kattintson a Nézet gombra, majd válassza az SQL nézet lehetőséget.

4. Az SQL-objektumlapon írja be a következő SQL-utasítást:

   TRANSFORM 
   SELECT 
   FROM 
   GROUP BY 
   PIVOT 
   ;
   

5. Az első sorban a TRANSFORM utasítás után írja be az összegző értékek kiszámításához használandó kifejezést; például: Szum([Összeg]).

   Ha rekordforrásként több táblát vagy lekérdezést használ; az egyes mezőnevek részeként vegye fel a tábla- vagy lekérdezésnevet, például: Szum([Költség].[Összeg]).

6. A második sorban a SELECT záradék után írja be a sorfejlécekként használni kívánt mezők vagy mezőkifejezések listáját. A listaelemeket vesszővel válassza el egymástól, például: [Költségvetés].[Részlegazonosító], [Költség].[Típus].

7. A harmadik sorban a FROM záradék után írja be a rekordforrásokként használt táblák vagy lekérdezések listáját, például: Költségvetés, Költség.

8. A negyedik sorban a GROUP BY záradék után írja be ugyanazt a mezőlistát, amelyet a 6. lépésben a SELECT záradékban használt.

9. Az ötödik sorban a PIVOT záradék után írja be az oszlopfejlécekhez használni kívánt mezőnevet vagy kifejezést, például: PIVOT [Költségvetés].[Év].

Bizonyos esetekben ahelyett, hogy egy mező minden értékét használná a sor- vagy oszlopfejlécekhez, célszerű a mező értékeit tartományokba csoportosítani, majd ezeket használni oszlopfejlécként. Tegyük fel például, hogy a „Kor” mezőt használja oszlopfejlécként. Ebben az esetben célszerűbb korcsoportokat képviselő oszlopokat használnia ahelyett, hogy minden egyes életkorhoz külön oszlopot használna.

A sor- vagy oszlopfejlécekben használandó tartományok létrehozásához használja az IIf függvényt a kifejezésben.

1. Nyissa meg a kereszttáblás lekérdezést Tervező nézetben.

2. A lekérdezés tervezőrácsának Mező sorában kattintson a jobb gombbal egy üres oszlopra, majd válassza a Nagyítás parancsot.

3. A Nagyítás mezőbe írjon be egy mezőaliast, majd egy kettőspontot (:).

4. Írja be az IIf() függvénynevet.

5. Az IIf kulcsszót követő zárójelek közé írja be az első mezőérték-tartományt definiáló összehasonlító kifejezést.

   Tegyük fel például, hogy a Kor mezőhöz kíván tartományokat létrehozni, mégpedig húsz éveseket. Az első tartományhoz tartozó összehasonlító kifejezés: [Kor]<21.

6. Az összehasonlító kifejezés után vesszővel elválasztva írja be a tartomány nevét idézőjelbe téve. A megadott név lesz a tartományba eső értékek kereszttáblás fejléce.

   A [Kor]<21 kifejezés után például írjon egy vesszőt, majd a "0-20 év" szöveget.

7. A tartománynév után írjon egy vesszőt (a záró idézőjel után), majd válasszon a következő lehetőségek közül:

   • Újabb tartomány létrehozásához írja be az IIf() függvénynevet, majd ismételje meg az 5, a 6 és a 7. lépést.

   • Az utolsó tartomány esetén csak a tartomány nevét írja be.

     Egy olyan beágyazott IIf kifejezés például, amely húsz éves tartományokra osztja fel a Kor mezőt, a következőképpen nézhet ki (az áttekinthetőség érdekében sortörést alkalmazva):

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

     Megjegyzés: A kifejezés kiértékelésekor az Access program az első igaz eredményt visszaadó IIf utasítás után megáll. A tartomány alsó határát nem kell megadnia, mivel minden, az adott tartomány alsó határa alatti érték eleve igaz értékű.

8. A lekérdezés tervezőrácsának Összesítés sorában válassza a Csoportosítás lehetőséget.

9. A Kereszttábla sorban adja meg, hogy sor- vagy oszlopfejlécként kívánja-e használni a tartományokat. Ne feledje, hogy legfeljebb három sorfejlécet és egy oszlopfejlécet adhat meg.

Előfordulhat, hogy szeretné, hogy a kereszttáblás lekérdezés futtatásakor bekérjen egy értéket. Tegyük fel például, hogy több sorfejlécet használ, amelyek egyike az Ország/régió. Ahelyett, hogy mindig megjelenítené az összes ország vagy régió adatait, a lekérdezés bekérhet egy nevet, majd a felhasználó által megadott érték szerint jeleníti meg az eredményt.

Bármely sorfejlécmezőhöz hozzáadhat paraméterkérdést.

1. Nyissa meg a kereszttáblás lekérdezést Tervező nézetben.

2. A megfelelő sorfejlécmező Feltétel sorába írja be a kérdést szögletes zárójelben. Ez a szöveg fog kérdésként megjelenni a lekérdezés futtatásakor.

   Ha például az [Ország/régió?] szöveget írja be a Feltétel sorba, a lekérdezés futtatásakor megjelenik egy párbeszédpanel az „Ország/régió” kérdéssel, egy beviteli mezővel és egy OK gombbal.

   Tipp: Ha rugalmas paramétert kíván használni, használjon összefűzött kifejezést helyettesítő karakterekkel, a Like operátort használva. Így például az [Ország/régió?] feltétel helyett használhatja a Like [Ország/régió?]&"*" kifejezést, hogy a paraméter szélesebb tartományból fogadjon bemeneti értékeket. A Like operátor használata nem módosítja a paraméterkérdés megjelenését.

3. A Tervezés lap Megjelenítés/elrejtés csoportjában kattintson a Paraméterek gombra.

4. A Lekérdezésparaméterek párbeszédpanel Paraméterek oszlopába írja be ugyanazt a paraméterkérdést, amelyet a Feltétel sorban használt. Használjon szögletes zárójeleket, de összefűzött helyettesítő karaktereket vagy a Like operátort ne.

5. Az Adattípus oszlopban válassza ki a paraméter adattípusát. Az adattípusnak egyeznie kell a sorfejlécmező adattípusával.

Ha a kereszttáblás lekérdezés összesítési értékeinek kiszámításához használt mező null értékeket tartalmaz, a rendszer ezeket az értékeket figyelmen kívül hagyja bármely összesítő függvény által. Egyes összesítő függvények esetében az eredmények hatással lehetnek. Egy átlag kiszámításához például össze kell adnia az összes értéket, és el kell osztania az eredményt az értékek számával. Ha azonban a mező null értékeket tartalmaz, a null értékek nem számítanak bele az értékek számában.

Bizonyos esetekben érdemes lehet a null értékeket nullákkal helyettesíteni, hogy ezeket is figyelembe vegyék az összesítő számítások. A null értékeknek nullákkal való helyettesítésére az Nz függvényt használhatja.

Az Nz függvény szintaxisa

Nz ( változó [, értékhanull ] )

Az Nz függvény argumentumai a következők:

Argumentum	Leírás
változó	Megadása kötelező. Egy Variantadattípus változó.
értékhanull	Nem kötelező (kivéve, ha lekérdezésben használja). Egy Variant, amely megad egy eredményül adandó értéket, ha a változó argumentum null értékű. Ezzel az argumentummal nullától illetve nulla hosszúságú karakterlánctól különböző értéket adhat eredményül. Megjegyzés: Ha az Nz függvényt egy lekérdezésben használja, és nem adja meg az értékhanull argumentum értékét, akkor a null értéket tartalmazó mezőkben nulla hosszúságú karakterláncot kap.

1. Nyissa meg a lekérdezést Tervező nézetben, és lekérdezés tervezőrácsában kattintson a jobb gombbal az Érték mezőre.

2. A helyi menüben kattintson a Nagyítás parancsra.

3. A Nagyítás mezőben tegye zárójelbe a mezőnevet vagy a kifejezést, majd írja elé az Nz karaktereket.

4. Írja be a , 0 karaktereket a záró zárójel elé.

   Ha például az „Elveszett idő” mezővel használja az Nz függvényt, a null értékek nullákkal való helyettesítéséhez használt végleges kifejezés így néz ki:

Nz([Hours Lost], 0)

• Legyen egyszerű:    A kereszttáblás lekérdezések a sorkombinációk számának növekedésével áttekinthetetlenné válhatnak. Ne használjon a szükségesnél több sorfejlécet.

• Érdemes lépésenként felépíteni a kereszttáblát:    Ne csak táblákat használjon. Gyakran érdemes előbb egy összegző lekérdezés készíteni, majd ezt használni a kereszttáblás lekérdezés rekordforrásaként.

• Gondosan válassza meg az oszlopfejlécmezőt:    A kereszttáblás adatlapok könnyebben áttekinthetők, ha viszonylag kevés oszlopfejlécet tartalmaznak. Miután meghatározta a fejlécként használni kívánt mezőket, célszerű a legkevesebb különböző értékkel rendelkező mezőt használni az oszlopfejlécek létrehozására. Ha például a lekérdezés életkor és nem alapján számít ki egy értéket, érdemes a nemet használni oszlopfejlécekként, mivel ez rendszerint kevesebb különböző értéket jelent.

• Segédlekérdezés használata WHERE záradékban:    A WHERE záradék részeként használhat segédlekérdezést a kereszttáblás lekérdezésekben.

Vissza a lap tetejére