Kereszttáblás Lekérdezések Létrehozása és Használata az Accessben

Ha meg szeretné könnyíteni az Accessbeli adatok áttekintését és értelmezését, érdemes kereszttáblás lekérdezést használnia. Kereszttáblás lekérdezéseket olyan esetekben szoktunk készíteni, amikor az adatokat két szempont szerint szeretnénk csoportosítani.

A kereszttáblás lekérdezés egy típusú választó lekérdezés. Segítségével olyan összesítést készíthetünk, amely kettő vagy több kiválasztott mező adatai alapján összesíti egy harmadik mező adatait. A kereszttáblás lekérdezés kiszámít egy összeget, egy átlagot vagy más összesítő függvényt, majd két értékkészlet szerint csoportosítja az eredményeket - az egyik az adatlap oldalán (sorfejlécek), a másik pedig felül található (oszlopfejlécek). A végeredmény egy a táblázatkezelőkhöz hasonló táblázat, amely lényegében megfeleltethető az Excel Kimutatás eszközének.

A kereszttáblás lekérdezés alapvető komponensei

Kereszttáblás lekérdezés létrehozásakor meg kell adnia, hogy mely mezők tartalmaznak sorfejléceket, melyik mező tartalmaz oszlopfejléceket, és melyik mező tartalmaz összegzendő értékeket. A sor- és oszlopfejléceket, illetve az összesítendő értékeket kifejezések használatával is létrehozhatja.

• Az Oszlopfejléc választása esetén az adatok mezőnevekként jelennek meg az oszlopok felett. Az oszlopfejlécek általában kevés különböző értéket tartalmazó mezőkből jönnek létre, hogy a táblázat könnyen áttekinthető maradjon.
• A Sorfejléc választása esetén az adatok a sorok elején jelennek meg. Sorfejlécforrásként legfeljebb három mezőt jelölhet ki, de minél kevesebb sorfejlécet használ, annál könnyebben olvasható lesz a kereszttáblás adatlap.
• Az Érték az Oszlop- és Sorfejlécek metszéspontjainál, az általunk kiválasztott függvénnyel összegezve jelennek meg. Ha oszlopfejléceket és értékeket ad meg, csak egy mezőt használhat az értékek forrásaként.

Kereszttáblás lekérdezés létrehozása

Kereszttáblás lekérdezéseket többféle módon is létrehozhat az Accessben: a Lekérdezés varázslóval, Tervező nézetben vagy SQL nézetben.

A Lekérdezés varázsló használata

A Kereszttáblás lekérdezés varázsló használata általában a kereszttáblás lekérdezés létrehozásának leggyorsabb és legegyszerűbb módja. Egyszerűen használható, és különösen ajánlott, ha még nem ismeri a kereszttáblás lekérdezések szerkezetét. A varázsló kiindulópontként is használható, mivel az utolsó lépésében választhatja a lekérdezés módosítását Tervező nézetben.

Lépésről lépésre a varázslóval:

1. Kattintsunk a menüszalag Létrehozás fülére, majd a Lekérdezések csoportban a Lekérdezés varázsló menügombra.
2. Válasszuk a Kereszttáblás lekérdezés varázsló lehetőséget, majd kattintsunk az OK gombra.
3. A megjelenő panelen válasszuk ki a listából azt a táblát vagy lekérdezést, melyet a kereszttáblás lekérdezésben használni szeretnénk. Fontos, hogy a Kereszttáblás lekérdezés varázsló használata esetén csak egyetlen táblát vagy lekérdezést használhat a rekordforrásaként. Ha a lekérdezésben szerepeltetni kívánt adatok több táblában találhatók, először hozzon létre egy, a kívánt adatokat visszaadó választó lekérdezést.
4. A következő lapon válassza ki azt a mezőt vagy mezőket (legfeljebb hármat), melyek a sorfejléceket alkotják, majd kattintson a Tovább gombra. Minél kevesebb sorfejlécet használ, annál könnyebben olvasható lesz a kereszttáblás adatlap.
5. A következő lapon válassza ki azt a mezőt, melynek értékeit oszlopfejlécként szeretné használni. Általában olyan mezőt kell választania, amely kevés különböző értéket tartalmaz, hogy az eredmények könnyen olvashatók legyenek. Ha Dátum/Idő típusú mezőt választ az oszlopfejlécekhez, a varázsló a következő oldalon rákérdez a dátumok csoportosítására szolgáló intervallumra. A választható lehetőségek: Év, Negyedév, Hónap, Dátum és Dátum/Idő. A varázsló automatikusan intervallumokba csoportosítja a dátumokat.
6. A következő lapon válassza ki az összegzett értékek kiszámítására szolgáló mezőt és függvényt. Ha szerepelteti a sorösszegeket, a kereszttáblás lekérdezés egy további oszlopot tartalmaz majd, amely összegzi a többi oszlopot. Jelölje be az erre vonatkozó jelölőnégyzetet, ha szeretné, hogy az Access létrehozzon egy ilyen oszlopot.
7. Az utolsó lépésben nevezzük el a lekérdezést, majd kattintsunk a Befejezés gombra.

Ha az utolsó párbeszédpanelen a Lekérdezés megtekintése rádiógombot választjuk, akkor a lekérdezés eredménye azonnal megjelenik. Ha a Terv módosítása rádiógombot választjuk, az elkészült lekérdezés Tervező nézetben jelenik meg, így lehetőségünk nyílik tetszőleges módosítások elvégzésére, például különféle szűrőfeltételek beírására. Az eredmények megtekintéséhez váltsunk át Adatlap nézetbe vagy használjuk a Futtatás parancsot.

Munka Tervező nézetben

A Tervező nézetben jobban szabályozhatja a lekérdezéstervet, és nagyobb felügyeletet szeretne a folyamat felett. Ez a módszer különösen hasznos, ha több adatforrást szeretne felhasználni.

A Tervező nézet előnyei és használata:

• 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álhat, amennyit csak szeretne. 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.
• 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 lekérdezéstervező ablakban kattintson duplán minden, a sorfejlécek, az oszlopfejlécek, és az összegzett értékek forrásaként használni kívánt mezőre.
• A használni kívánt mezőket a szokott módon vehetjük fel a tervezőrácsba, majd az összesítő lekérdezésekhez hasonlóan az Összesítés sorban adjuk meg a csoportosítási szempontot vagy a használni kívánt összesítőfüggvényt. Ez után a Kereszttábla sorban adhatjuk meg, hogy az egyes mezők az összesítés mely elemeként jelenjenek meg.
• A Feltétel sorba beírt kifejezéssel korlátozhatja az adott mezőhöz tartozó eredményeket. 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, hanem azt, hogy mely oszlopok tartalmaznak 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.

Lekérdezés írása SQL nézetben

Tetszés szerint SQL nézetben is írhat kereszttáblás lekérdezést, ha nagyobb kontrollra vágyik, vagy összetettebb lekérdezéseket szeretne létrehozni. A kereszttáblás lekérdezés az SQL-ben TRANSFORM utasításként van kifejezve.

Az SQL nézet sajátosságai:

• 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.
• Az SQL lekérdezés második sorában, 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 GROUP BY záradék után írja be ugyanazt a mezőlistát, amelyet a sorfejlécként használt. Az ORDER BY záradék növekvő sorrendbe rendezi az értékeket. Ha további mező vagy kifejezés alapján szeretne rendezni, írjon be egy vesszőt, majd a további mezőnevet vagy kifejezést.
• A kereszttáblás lekérdezés SQL nézetében a PIVOT záradék végén írja be az IN záradékot, majd az oszlopfejlécekként használni kívánt (zárójelek közé foglalt) értékek vesszővel elválasztott listáját.
• SQL nézetben azonban nem adhatja meg a paraméterek adattípusait.

Haladó technikák és beállítások

Adatok csoportosítása tartományokba

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ékként. 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 Accessben ezt az IIf() függvénnyel teheti meg. Az összehasonlító kifejezés után vesszővel elválasztva írja be a tartomány nevét idézőjelbe téve. Újabb tartomány létrehozásához írja be az IIf() függvénynevet, majd ismételje meg a tartományok definiálásának lépéseit. 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 Kereszttábla sorban adja meg, hogy sor- vagy oszlopfejlécként kívánja-e használni a tartományokat.

Tipp: Ha Dátum/idő mezővel szeretne intervallumokat létrehozni, fontolja meg a Kereszttáblás lekérdezés varázsló használatát. A varázsló lehetővé teszi a dátumok év, negyedév, hónap, dátum vagy dátum/idő intervallumba való csoportosítását. Amennyiben ennél specifikusabb vagy egyedi tartományokra van szüksége, a Tervező nézetben való munka kínál nagyobb rugalmasságot.

Paraméterek használata kereszttáblás lekérdezésekben

Előfordulhat, hogy szeretné, hogy a kereszttáblás lekérdezés futtatásakor bekérjen egy értéket, például egy országot vagy régiót, ha több sorfejlécet használ, amelyek egyike az Ország/régió.

Ehhez a megfelelő sorfejlécmező Feltétel sorába írja be a kérdést szögletes zárójelben (pl. [Ország/régió?]). Ezután 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, majd az Adattípus oszlopban válassza ki a paraméter adattípusát. Ne feledje, hogy SQL nézetben nem adhatja meg a paraméter adattípusait.

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.

Null értékek kezelése az összesítésben

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, mint például az átlag, esetében az eredmények jelentősen eltérhetnek emiatt.

Például egy átlag kiszámításához össze kell adnia az összes értéket, és el kell osztania az eredményt az értékek számával. Amennyiben a null értékek kimaradnak, az átlag magasabbnak tűnhet a valóságnál. Bizonyos esetekben érdemes lehet a null értékeket nullákkal helyettesíteni (például az Nz() függvénnyel), hogy ezeket is figyelembe vegyék az összesítő számítások.

Gyakorlati tippek és bevált módszerek

• Legyen egyszerű: A kereszttáblás lekérdezések a sorkombinációk számának növekedésével áttekinthetetlenné válhatnak. Érdemes lépésenként felépíteni a kereszttáblát, és elkerülni a feleslegesen sok sorfejlécet.
• 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.
• Ne csak táblákat használjon: Bonyolultabb lekérdezések esetén érdemes először egy választó lekérdezést létrehozni, amely előkészíti az adatokat, és azt használni a kereszttáblás lekérdezés forrásaként.

Példa kereszttáblás lekérdezés eredményére

Tegyük fel, hogy bolygóészleléseket rögzítünk, és szeretnénk látni, hogy az egyes években bolygótípusonként hány új bolygót fedeztek fel. Adjuk hozzá a lekérdezéshez a Felfedezés éve, a Bolygó típusa és a Bolygó neve mezőket. A lekérdezés célja, hogy az egyes években bolygótípusonként összeszámolja a felfedezéseket (Count összesítő függvényt használva).

Eredmény táblázat:

tags: #keteszt #tabella #keszitese

Népszerű bejegyzések:

• Vizsgálat a Kóka KSK női csapatát érintő ügyben
• Linux telepítése UEFI rendszeren
• Az Arsenal FC múltja és eredményei
• A Napoli mezek stílusa és története
• Tállai András szerepe a Mezőkövesd kiesésében