Az adatok összegzése lekérdezés használatával

Ez a cikk ismerteti, hogy miként lehet az Access alkalmazásban összesítő függvényt használni adatok összesítésére egy lekérdezés eredményhalmazában. Emellett röviden elmagyarázza, hogyan használhatók más aggregátumfüggvények, például az COUNT és AVG, az értékek összesítésére vagy átlagolására egy eredményhalmazban. Emellett elmagyarázza, hogy az Összeg sor segítségével hogyan összegezhetők az adatok anélkül, hogy a lekérdezések kialakítása megváltozna.

Kapcsolódó műveletek

• Az adatok összegzési módszereinek áttekintése
• Mintaadatok készítése
• Adatok összegzése Összeg sor használatával
• Végösszegek kiszámítása lekérdezéssel
• Csoport összesítéseinek kiszámítása összegző lekérdezéssel
• Több csoport adatainak összegzése kereszttáblás lekérdezéssel
• Az aggregátumfüggvények ismertetése

Az adatok összegzési módszereinek áttekintése

A lekérdezések egy oszlopában szereplő számok összegzésére egy bizonyos típusú, úgynevezett aggregátumfüggvény használható. Az aggregátumfüggvények egy adatoszlopban lévő értékekkel végeznek számítást, és eredményül egyetlen számot adnak. Az Access alkalmazásban többféle aggregátumfüggvény is használható, például , , (átlagok számítására), Min, és Max. AvgCountSum Az adatok összegzéséhez adja hozzá a függvényt Sum a lekérdezéshez. Az adatokat Count a függvénnyel számolhatja meg és így tovább.

Az Access ezenkívül több módszert is kínál arra, hogy további aggregátumfüggvényeket vegyen Sum fel a lekérdezésekbe. Az alábbiakra van lehetősége:

• A lekérdezés megnyitása Adatlap nézetben, és egy Összeg sor hozzáadása. Az Összeg sor az Access funkciója, amely lehetővé teszi, hogy egy lekérdezés eredményhalmazának egy vagy több oszlopában aggregátumfüggvényt használjon anélkül, hogy a lekérdezés megjelenése megváltozna.
• Összegző lekérdezés létrehozása. Az összegző lekérdezéssel részösszegeket lehet kiszámolni rekordok csoportjain; az Összeg sorral pedig végösszegeket lehet kiszámítani adatok egy vagy több oszlopában (mezőjében). Ha például egy adott város vagy negyedév értékesítéseit szeretné összesíteni, a rekordok csoportosításához összegző lekérdezést kell használni, majd összesíteni kell az eladási értékeket.
• Kereszttáblás lekérdezés létrehozása. A kereszttáblás lekérdezés egy speciális típusú lekérdezés, amely az eredményeket Excel-munkalapokhoz hasonló rácselrendezésben jeleníti meg. A kereszttáblás lekérdezésekkel összesíthetők, majd két szempont szerint csoportosíthatók az értékek – az egyik szempont az adatlap oldalán függőlegesen, a másik pedig az adatlap tetején vízszintesen jelenik meg. A kereszttáblás lekérdezés lehetőséget nyújt például az egyes városok értékesítési számainak megjelenítésére az elmúlt három évben, ahogy az alábbi táblázatban látható:

Város	2003	2004	2005
Párizs	254 556	372 455	467 892
Sydney	478 021	372 987	276 399
Dzsakarta	572 997	684 374	792 571
⁠...⁠	...	...	...

A többi aggregátumfüggvény használatának módjáról az Oszlopösszesítők megjelenítése adatlapon című témakör nyújt további információt.

A következő szakaszok bemutatják az Összeg sor hozzáadásának módját, az összegző lekérdezés használatát több csoport adatainak összegzésére és a csoportok, illetve időszakok adatainak részösszegeit kiszámító kereszttáblás lekérdezés használatát. Menet közben ne feledje, hogy sok aggregátumfüggvény csak bizonyos adattípusú mezőkkel működik. A függvény például SUM csak a Szám, a Decimális és a Pénznem adattípusú mezőkkel működik. Az egyes függvényekhez szükséges adattípusokról a jelen cikk későbbi, Az aggregátumfüggvények ismertetése című részében olvashat bővebben.

Az adattípusokról a Mezők adattípusának módosítása című témakörben talál általános tájékoztatást.

Vissza a tetejére

Mintaadatok készítése

A jelen cikkben található útmutatók mintaadattáblákkal is szolgálnak. Az útmutatók a mintatáblákon mutatják be az aggregátumfüggvények használatát. A mintatáblákat tetszés szerint beviheti egy új vagy meglévő adatbázisba.

Az Access több módszert kínál a mintatáblák adatbázisba történő bevitelére. Beviheti az adatokat manuálisan, vagy az egyes táblákat bemásolhatja egy táblázatkezelő alkalmazásba (például az Excelbe), majd a munkalapokat importálhatja az Accessbe, illetve a kimásolt adatokat beillesztheti egy szövegszerkesztőbe (például a Jegyzettömbbe), és a létrejövő szövegfájlokból importálhatja az adatokat.

E szakasz lépései bemutatják, hogy miként vihet be adatokat manuálisan egy üres adatlapba, illetve hogyan másolhatja át a mintatáblákat egy táblázatkezelő programba, majd hogyan importálhatja a táblákat az Accessbe. A szöveges adatok létrehozásáról és importálásáról a Szövegfájlban tárolt adatok importálása vagy csatolása című témakörben olvashat bővebben.

A jelen cikk útmutatóiban az alábbi táblázatok használhatók. A mintaadatok létrehozásához használja az alábbi táblázatokat:

A Kategóriák tábla:

Kategória
Babák
Játékok és logikai játékok
Képzőművészet
Videojátékok
DVD-k és filmek
Modellezés és hobbi
Sportszerek

A Termékek tábla:

Terméknév	Ár	Kategória
Programozó akcióhős-figura	1295 Ft	Babák
Mulatságos C# (társasjáték az egész család számára)	1585 Ft	Játékok és logikai játékok
Relációs adatbázis diagram	2250 Ft	Képzőművészet
A csodálatos számítógéplapka (500 darabos)	3265 Ft	Játékok és logikai játékok
Hozzáférés! A játék!	2295 Ft	Játékok és logikai játékok
Számítógépguruk és mitikus lények	7850 Ft	Videojátékok
Számítógépguruk gyakorlata A DVD!	1488 Ft	DVD-k és filmek
Valódi repülő pizza	3675 Ft	Sportszerek
Külső 5,25 hüvelykes hajlékonylemezes meghajtó (1/4-es léptékű)	6500 Ft	Modellezés és hobbi
Bürokrata antiakcióhős-figura	7888 Ft	Babák
Félhomály	5333 Ft	Videojátékok
Csináld magad billentyűzet	7795 Ft	Modellezés és hobbi

A Rendelések tábla:

Rendelés dátuma	Szállítási dátum	Város	Szállítási díj
2005.11.14.	2005.11.15.	Dzsakarta	5500 Ft
2005.11.14.	2005.11.15.	Sydney	7600 Ft
2005.11.16.	2005.11.17.	Sydney	8700 Ft
2005.11.17.	2005.11.18.	Dzsakarta	4300 Ft
2005.11.17.	2005.11.18.	Párizs	10 500 Ft
2005.11.17.	2005.11.18.	Stuttgart	11 200 Ft
2005.11.18.	2005.11.19.	Bécs	21 500 Ft
2005.11.19.	2005.11.20.	Miami	52 500 Ft
2005.11.20.	2005.11.21.	Bécs	19 800 Ft
2005.11.20.	2005.11.21.	Párizs	18 700 Ft
2005.11.21.	2005.11.22.	Sydney	8100 Ft
2005.11.23.	2005.11.24.	Dzsakarta	9200 Ft

A Rendelés részletei tábla:

Rendelés azonosítója	Terméknév	Termékazonosító	Egységár	Mennyiség	Árengedmény
1	Csináld magad billentyűzet	12	7795 Ft	9	5%
1	Bürokrata antiakcióhős-figura	2	7888 Ft	4	7,5%
2	Számítógépguruk gyakorlata A DVD!	7	1488 Ft	6	4%
2	A csodálatos számítógéplapka	4	3265 Ft	8	0
2	Számítógépguruk és mitikus lények	6	7850 Ft	4	0
3	Hozzáférés! A játék!	5	2295 Ft	5	15%
4	Programozó akcióhős-figura	1	1295 Ft	2	6%
4	Valódi repülő pizza	8	3675 Ft	8	4%
5	Külső 5,25 hüvelykes hajlékonylemezes meghajtó (1/4-es léptékű)	9	6500 Ft	4	10%
6	Relációs adatbázis diagram	3	2250 Ft	12	6,5%
7	Félhomály	11	5333 Ft	6	8%
7	Relációs adatbázis diagram	3	2250 Ft	4	9%

A mintaadatok manuális bevitele

1. A Létrehozás lap Táblák csoportjában kattintson a Tábla gombra. Az Access felvesz egy új, üres táblát az adatbázisba.

   Megjegyzés

   Új, üres adatbázis megnyitásakor erre a lépésre nincs szükség, amikor azonban új táblát vesz fel az adatbázisba, mindig el kell végeznie.

2. Kattintson duplán a fejlécsor első cellájába, és adja meg a mintatábla első mezőjének nevét. Az Access alapértelmezés szerint az üres mezőket a fejlécsorban az Új mező hozzáadása szöveggel jelöli meg, a következőképpen:

3. A nyílbillentyűkkel lépjen át a következő üres fejléccellába, és írja be a második mező nevét. Az új cellát le is nyomhatja TAB vagy duplán kattinthatja. Ismételje ezt a lépést mindaddig, amíg be nem vitte az összes mező nevét.

4. Vigye be a mintatábla adatait. Amint beviszi az adatokat, az Access beállítja az egyes mezők adattípusát. Ha nem járatos a relációs adatbázisok használatában, a táblák minden mezőjében be kell állítani egy meghatározott adattípust (például Szám, Szöveg vagy Dátum/Idő). Az adattípus megadása segít a pontos adatbevitelben és a hibák megelőzésében (megakadályozza például, hogy telefonszámot próbáljon meg használni a számításokban). Ezekben a mintatáblákban hagyhatja, hogy az Access állítsa be az adattípust.

5. Ha befejezte az adatok bevitelét, kattintson a Mentés gombra. Billentyűparancs: CTRL+S. Megjelenik a Mentés másként párbeszédpanel.

6. A Táblanév mezőben adja meg a mintatábla nevét, majd kattintson az OK gombra. Érdemes az egyes mintatáblák nevét használni, mivel a leírásokban létrehozott lekérdezések ezekre a nevekre hivatkoznak.

7. Ismételje meg ezeket a lépéseket addig, amíg a szakasz elején felsorolt összes mintatáblát létre nem hozta.

Ha nem szeretné manuálisan megadni az adatokat, az alábbi lépéseket követve másolja az adatokat egy táblázatfájlba, majd importálja őket a táblázatfájlból az Accessbe.

A mintamunkalapok létrehozása

1. Indítsa el a táblázatkezelő alkalmazást, és hozzon létre egy új, üres fájlt. Excel használata esetén alapértelmezés szerint létrejön egy új, üres munkafüzet.

2. Másolja ki a fenti első mintatáblát, és illessze be az első munkalapra, az első cellától kezdődően.

3. A táblázatkezelő alkalmazás által biztosított módszerrel nevezze át a munkalapot. Adja ugyanazt a nevet a munkalapnak, mint a mintatáblának. Ha például a mintatábla neve Kategóriák, a munkalapnak is adja ugyanezt a nevet.

4. Ismételje meg a 2. és a 3. lépést, másolja át az összes táblát egy üres munkalapra, és nevezze át a munkalapokat.

   Megjegyzés

   Lehetséges, hogy új munkalapokat kell hozzáadni a számolótáblafájlhoz. Erről a műveletről a táblázatkezelő alkalmazás súgójában tájékozódhat.

5. Mentse a munkafüzetet egy megfelelő helyre a számítógépén vagy a hálózaton, majd folytassa a következő lépéssorral.

Adatbázistáblák létrehozása a munkafüzetekből

1. A Külső adatok lap Importálás & csatolás csoportjában kattintson az Új adatforrás>Excel-fájlból> gombra. Megjelenik a Külső adatok átvétele – Excel-munkafüzet párbeszédpanel.
2. Kattintson a Tallózás gombra, nyissa meg a fentebb létrehozott számolótáblafájlt, majd kattintson az OK gombra. Ekkor elindul a Táblázat importálása varázsló.
3. A varázsló alapértelmezés szerint a munkafüzet első munkalapját jelöli ki (ha követte az előző szakasz útmutatását, akkor ez a Vevők nevű munkalap lesz), és a munkalap adatai megjelennek a képernyő alsó részén. Kattintson a Tovább gombra.
4. A varázsló következő lapján jelölje be Az első sor oszlopfejléceket tartalmaz jelölőnégyzetet, majd kattintson a Tovább gombra.
5. A következő lap Mezőbeállítások csoportjában található beviteli mezők és listák segítségével tetszés szerint módosíthatja a mezőneveket és az adattípusokat, vagy akár ki is hagyhat mezőket az importálásból. Egyéb esetben kattintson a Tovább gombra.
6. Hagyja bejelölve Az Access adjon elsődleges kulcsot a táblához választógombot, majd kattintson a Tovább gombra.
7. Az Access alapértelmezés szerint a munkalap nevét használja az új tábla neveként. Fogadja el ezt a nevet, vagy adjon meg egy másikat, majd kattintson a Befejezés gombra.
8. Ismételje meg az 1–7. lépéseket, amíg a munkafüzet minden munkalapjából létre nem hozott egy-egy táblát.

Az elsődleges kulcs mezőinek átnevezése

1. A navigációs ablakban kattintson a jobb gombbal az egyes táblákra, amelyeket az előző lépésekben készített, majd kattintson a Tervező nézet parancsra.
2. Az egyes táblákban keresse meg az elsődleges kulcs mezőjét. Alapértelmezés szerint az Access minden ilyen mezőt az Azonosító névvel jelöl.
3. Az elsődleges kulcs mezőinek Mezőnév oszlopához adja hozzá a tábla nevét. A Kategóriák tábla Azonosító mezőjének például a „Kategóriaazonosító” nevet, a Rendelések tábla azonosító mezőjének pedig a „Rendelésazonosító” nevet adja. A Rendelés részletei táblában a mező neve „Részletazonosító” lesz. A Termékek táblában a mezőt „Termékazonosító” névre nevezi át.
4. Mentse a módosításokat.

Az útmutatóban előforduló mintatáblák mindegyike tartalmazza az elsődleges kulcs mezőjét. A mezőt az előző lépéseket követve nevezheti át.

Vissza a tetejére

Adatok összegzése Összeg sor használatával

Ha fel szeretne venni egy Összeg sort a lekérdezésbe, nyissa meg a lekérdezést Adatlap nézetben, vegye fel a sort, majd válassza ki a használni kívánt aggregátumfüggvényt (például Sum, , MinMaxvagy Avg). A jelen szakaszban bemutatott lépésekkel lehetőség nyílik egy egyszerű választó lekérdezés létrehozására és egy Összeg sor hozzáadására. Ebben a szakaszban nem lesz szükség az előzőleg bemutatott mintatáblákra.

Egyszerű választó lekérdezés létrehozása

1. Kattintson a Létrehozás lap Lekérdezések csoportjának Lekérdezéstervező gombjára.
2. Kattintson duplán a lekérdezésben használni kívánt táblára vagy táblákra. A kijelölt táblák ablakokként jelennek meg a lekérdezéstervező felső részében.
3. Kattintson duplán a lekérdezésben használni kívánt mezőkre. A táblában szerepelhetnek szöveges adatot tartalmazó mezők, például nevek és leírások, azonban mindenképpen szerepelnie kell szám vagy pénznem adatot tartalmazó mezőnek is. Az egyes mezők cellaként jelennek meg a tervezőrácsban.
4. A lekérdezés futtatásához kattintson a Futtatás gombra. A lekérdezés eredményhalmaza megjelenik Adatlap nézetben.
5. Ha szükséges, átválthat Tervező nézetre, és módosíthatja a lekérdezést. Ehhez kattintson a jobb gombbal a lekérdezés dokumentumfülére, és válassza a Tervező nézet parancsot. Ha szükséges, ezután táblamezők hozzáadásával és eltávolításával módosíthatja a lekérdezést. Mező eltávolításához jelölje ki a tervezőrács megfelelő oszlopát, és nyomja le a DELETE billentyűt.
6. Mentse a lekérdezést.

Összeg sor hozzáadása

1. Ellenőrizze, hogy a lekérdezés Adatlap nézetben van-e megnyitva. Ehhez kattintson a jobb gombbal a lekérdezés dokumentumfülére, és válassza az Adatlap nézet parancsot. A navigációs ablakban kattintson duplán a lekérdezésre. Ez elindítja a lekérdezést, és az eredményeket megjeleníti egy adatlapon.
2. A Kezdőlap lap Rekordok csoportjában kattintson az Összesítés gombra. Az adatlapon megjelenik egy új Összeg sor.
3. Az Összeg sorban válassza a mező azon celláját, amelyet összegezni szeretné, majd a listából válassza az Összeg elemet.

Összeg sor elrejtése

• A Kezdőlap lap Rekordok csoportjában kattintson az Összesítés gombra.

Az Összeg sor használatáról a Oszlopösszesítők megjelenítése adatlapon című témakörben olvashat bővebben.

Vissza a tetejére

Végösszegek kiszámítása lekérdezéssel

A végösszeg az egy oszlopban található összes érték összege. Többféle végösszeget is ki lehet számolni, beleértve az alábbiakat:

• Egyszerű végösszeg, amellyel egy adott oszlop összes értékét lehet összegezni. Ki lehet számolni például a teljes szállítási költséget.
• Számított végösszeg, amely egynél több oszlopban lévő értékeket összegez. Ki lehet számolni például a teljes értékesítést a cikkek értékének és megrendelt cikkek számának összeszorzásával, majd a kapott értékek összegzésével.
• Végösszeg, amelybe nem számítanak bele bizonyos rekordok. Ki lehet számolni például a legutóbbi péntek teljes értékesítését.

A következő szakasz lépései bemutatják, hogy miként lehet a különböző típusú végösszegeket kiszámolni. A lépésekben a Rendelések és a Rendelés részletei táblát használjuk.

A Rendelések tábla

Rendelés azonosítója	Rendelés dátuma	Szállítási dátum	Város	Szállítási díj
1	2005.11.14.	2005.11.15.	Dzsakarta	5500 Ft
2	2005.11.14.	2005.11.15.	Sydney	7600 Ft
3	2005.11.16.	2005.11.17.	Sydney	8700 Ft
4	2005.11.17.	2005.11.18.	Dzsakarta	4300 Ft
5	2005.11.17.	2005.11.18.	Párizs	10 500 Ft
6	2005.11.17.	2005.11.18.	Stuttgart	11 200 Ft
7	2005.11.18.	2005.11.19.	Bécs	21 500 Ft
8	2005.11.19.	2005.11.20.	Miami	52 500 Ft
9	2005.11.20.	2005.11.21.	Bécs	19 800 Ft
10	2005.11.20.	2005.11.21.	Párizs	18 700 Ft
11	2005.11.21.	2005.11.22.	Sydney	8100 Ft
12	2005.11.23.	2005.11.24.	Dzsakarta	9200 Ft

A Rendelés részletei tábla

Részlet azonosítója	Rendelés azonosítója	Terméknév	Termékazonosító	Egységár	Mennyiség	Árengedmény
1	1	Csináld magad billentyűzet	12	7795 Ft	9	0,05
2	1	Bürokrata antiakcióhős-figura	2	7888 Ft	4	0,075
3	2	Számítógépguruk gyakorlata A DVD!	7	1488 Ft	6	0,04
4	2	A csodálatos számítógéplapka	4	3265 Ft	8	0,00
5	2	Számítógépguruk és mitikus lények	6	7850 Ft	4	0,00
6	3	Hozzáférés! A játék!	5	2295 Ft	5	0,15
7	4	Programozó akcióhős-figura	1	1295 Ft	2	0,06
8	4	Valódi repülő pizza	8	3675 Ft	8	0,04
9	5	Külső 5,25 hüvelykes hajlékonylemezes meghajtó (1/4-es léptékű)	9	6500 Ft	4	0,10
10	6	Relációs adatbázis diagram	3	2250 Ft	12	0,065
11	7	Félhomály	11	5333 Ft	6	0,08
12	7	Relációs adatbázis diagram	3	2250 Ft	4	0,09

Egyszerű végösszeg kiszámítása

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

2. Kattintson duplán a lekérdezésben használni kívánt táblára. Ha a mintaadatokat használja, akkor kattintson duplán a Rendelések táblára. A tábla ablakként jelenik meg a lekérdezéstervező felső részében.

3. Kattintson duplán az összegezni kívánt mezőre. Ellenőrizze, hogy a mező beállítása Szám vagy Pénznem-e. Ha nem numerikus mezők értékeit szeretné összegezni, például szöveges mezőéit, az Access a Típuseltérési hiba a feltételkifejezésben hibaüzenetet jeleníti meg a lekérdezés elindításakor. A mintaadatok használata esetén kattintson duplán a Szállítási díj oszlopra. A rácshoz további szám adattípusú mezőket is hozzá lehet adni, ha az ezekre a mezőkre vonatkozó végösszeget is ki szeretné számolni. Az összegző lekérdezésben egynél több oszlop végösszegét is ki lehet számolni.

4. A Lekérdezéstervező lap Megjelenítés/elrejtés csoportjában kattintson az Összegek gombra. A tervezőrácsban megjelenik az Összesítés sor, a Szállítási díj oszlop cellájában pedig a Group By (Csoportosítás) elem.

5. Változtassa meg a cella értékét az Összesítés sorban Összeg függvényre.

6. A lekérdezés futtatásához és az eredmények Adatlap nézetben történő megjelenítéséhez kattintson a Futtatás gombra.

   Tipp:

   Az Access az összegezni kívánt mező nevének elejéhez fűzi az SumOf alkalmazást. Ha az oszlop fejlécét érthetőbbre szeretné cserélni, például a Teljes szállítási költség szövegre, váltson vissza Tervező nézetre, és kattintson a tervezőrácsban a Szállítási díj oszlop Mező sorára. Helyezze a kurzort a Szállítási díj mellé, és írja be.Total Shipping: Shipping Fee

7. Ha szükséges, mentse a lekérdezést, és zárja be.

Végösszeg kiszámítása bizonyos rekordok kizárásával

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

2. Kattintson duplán a Rendelések táblára és a Rendelés részletei táblára.

3. Adja a Rendelés dátuma mezőt a Rendelések táblából a lekérdezés tervezőrácsának első oszlopához.

4. Az első oszlop Feltétel sorába írja be a következőt Date() -1: . Ezzel a kifejezéssel ki lehet zárni az aktuális nap rekordjait a számított összegből.

5. Ezután hozza létre az oszlopot az egyes tranzakciók értékesítési összegének kiszámításához. Írja be az alábbi kifejezést a rács második oszlopának Mező sorába: Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity]) Ellenőrizze, hogy a kifejezésben szereplő mezők adattípusának beállítása Szám vagy Pénznem-e. Ha a kifejezésben szerepel olyan mező, amelynek más az adattípusa, a lekérdezés futtatásakor az Access a Típuseltérési hiba a feltételkifejezésben hibaüzenetet jeleníti meg a lekérdezés elindításakor.

6. A Lekérdezéstervező lap Megjelenítés/elrejtés csoportjában kattintson az Összegek gombra. A tervezőrácson megjelenik az Összesítés sor, az első és a második oszlopban pedig a Group By elem.

7. A második oszlopban változtassa meg az Összesítés sorban lévő cella értékét Összeg függvényre. Az Összeg (Sum) függvénnyel lehet összegezni az egyes értékesítési számokat.

8. A lekérdezés futtatásához és az eredmények Adatlap nézetben történő megjelenítéséhez kattintson a Futtatás gombra.

9. Mentse a lekérdezést Napi értékesítés néven.

   Megjegyzés

   Amikor legközelebb megnyitja a lekérdezést Tervező nézetben, eltérést tapasztalhat a Teljes értékesítési összeg oszlop Mező és Összesítés sorának értékei között. A kifejezés a Sum függvénybe beágyazva jelenik meg, és az Összeg sorban a Sum helyett a Expression jelenik meg.

   Ha például a mintaadatok alapján hoz létre egy lekérdezést (az előbb bemutatott lépésekkel), az alábbiak jelennek meg: Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity))

Vissza a tetejére

Csoport összesítéseinek kiszámítása összegző lekérdezéssel

A jelen szakasz lépéseiben bemutatjuk egy olyan összegző lekérdezés létrehozását, amely több csoport adataiból álló részösszegeket számít ki. Tartsa szem előtt, hogy alapértelmezés szerint az összegző lekérdezésben csak olyan mező vagy mezők használhatók, amelyek a csoport adatait tartalmazzák (például a „kategóriák”), illetve amelynek az értékét szerepeltetni szeretné az összegben (például az „értékesítés”). Az összegző lekérdezésben nem szerepelhet más, a kategória elemeit leíró mező. Ha meg szeretné tekinteni a leíró adatokat, létrehozhat egy második választó lekérdezést, amelyben az összegző lekérdezés mezői további adatmezőkkel is kombinálhatók.

A jelen szakasz lépései bemutatják, hogyan lehet létrehozni az egyes termékek teljes értékesítési összegének meghatározására szolgáló összegző és választó lekérdezéseket. A lépésekben ezeket a mintatáblákat használjuk:

A Termékek tábla

Termékazonosító	Terméknév	Ár	Kategória
1	Programozó akcióhős-figura	1295 Ft	Babák
2	Mulatságos C# (társasjáték az egész család számára)	1585 Ft	Játékok és logikai játékok
3	Relációs adatbázis diagram	2250 Ft	Képzőművészet
4	A csodálatos számítógéplapka (500 darabos)	3265 Ft	Képzőművészet
5	Hozzáférés! A játék!	2295 Ft	Játékok és logikai játékok
6	Számítógépguruk és mitikus lények	7850 Ft	Videojátékok
7	Számítógépguruk gyakorlata A DVD!	1488 Ft	DVD-k és filmek
8	Valódi repülő pizza	3675 Ft	Sportszerek
9	Külső 5,25 hüvelykes hajlékonylemezes meghajtó (1/4-es léptékű)	6500 Ft	Modellezés és hobbi
10	Bürokrata antiakcióhős-figura	7888 Ft	Babák
11	Félhomály	5333 Ft	Videojátékok
12	Csináld magad billentyűzet	7795 Ft	Modellezés és hobbi

A Rendelés részletei tábla

Részlet azonosítója	Rendelés azonosítója	Terméknév	Termékazonosító	Egységár	Mennyiség	Árengedmény
1	1	Csináld magad billentyűzet	12	7795 Ft	9	5%
2	1	Bürokrata antiakcióhős-figura	2	7888 Ft	4	7,5%
3	2	Számítógépguruk gyakorlata A DVD!	7	1488 Ft	6	4%
4	2	A csodálatos számítógéplapka	4	3265 Ft	8	0
5	2	Számítógépguruk és mitikus lények	6	7850 Ft	4	0
6	3	Hozzáférés! A játék!	5	2295 Ft	5	15%
7	4	Programozó akcióhős-figura	1	1295 Ft	2	6%
8	4	Valódi repülő pizza	8	3675 Ft	8	4%
9	5	Külső 5,25 hüvelykes hajlékonylemezes meghajtó (1/4-es léptékű)	9	6500 Ft	4	10%
10	6	Relációs adatbázis diagram	3	2250 Ft	12	6,5%
11	7	Félhomály	11	5333 Ft	6	8%
12	7	Relációs adatbázis diagram	3	2250 Ft	4	9%

Az alábbi lépések azt feltételezik, hogy a Rendelések és a Rendelés részletei tábla Termékazonosító mezői egy-a-többhöz kapcsolatban állnak, ahol a Rendelések tábla áll a kapcsolat „egy” oldalán.

Az összegző lekérdezés létrehozása

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

2. Jelölje ki a használni kívánt táblákat, majd kattintson a Hozzáadás gombra. Az egyes táblák ablakokként jelennek meg a lekérdezéstervező felső részében. Az előzőleg bemutatott mintatáblák használata esetén a Termékek és a Rendelés részletei táblát kell hozzáadni.

3. Kattintson duplán a lekérdezésben használni kívánt mezőkre. Általában csak a csoport és az érték mezőt adja hozzá a lekérdezéshez. Számítást is használhat azonban értékmező helyett – a következő lépésekben ezt mutatjuk be.

   1. Vegye fel a Termékek tábla Kategória mezőjét a tervezőrácsba.

   2. A következő kifejezés beírásával a rács második oszlopában hozza létre azt az oszlopot, amely az egyes tranzakciók értékesítési összegét számolja ki: Total Sales Value: (1-[Order Details].[Discount]/100)*([Order Details].[Unit Price]*[Order Details].[Quantity]) Ügyeljen arra, hogy a kifejezésben használt mezők adattípusa Szám vagy Pénznem legyen. Ha a hivatkozott mezők más adattípusúak, az Access a Típuseltérési hiba a feltételkifejezésben hibaüzenetet jeleníti meg az Adatlap nézetre való váltáskor.

   3. A Lekérdezéstervező lap Megjelenítés/elrejtés csoportjában kattintson az Összegek gombra. A tervezőrácson megjelenik az Összesítés sor, és abban a sorban az első és a második oszlopban pedig a Group By elem.

   4. A második oszlopban változtassa meg az Összesítés sor értékét Összeg függvényre. Az Összeg (Sum) függvénnyel lehet összegezni az egyes értékesítési számokat.

   5. A lekérdezés futtatásához és az eredmények Adatlap nézetben történő megjelenítéséhez kattintson a Futtatás gombra.

   6. A lekérdezést hagyja nyitva, mert a következő szakaszban is szüksége lesz rá. Feltételek használata összegző lekérdezésben Az előző szakaszban létrehozott lekérdezés az összes rekordot tartalmazza az alapul szolgáló táblákból. Az összesítések kiszámításakor egyetlen rendelést sem hagy ki, és az összes kategória összesítését megjeleníti. Ha bizonyos rekordokat ki szeretne hagyni, feltételeket adhat a lekérdezéshez. Kihagyhatja például az olyan tranzakciókat, amelyeknek az értéke kisebb, mint 10 000 Ft, vagy kiszámolhatja a termékkategóriák egy részének összesítéseit. A jelen szakasz lépéseiben háromféle feltételtípus használatát mutatjuk be:

   7. Az összegek kiszámítása során bizonyos csoportokat figyelmen kívül hagyó feltételek: ha például csak a Videojátékok, a Képzőművészet és a Sportszerek kategória összesítésére kíváncsi.

   8. Bizonyos összegeket kiszámításuk után elrejtő feltételek: megjelenítheti például csak azokat az összegeket, amelyek meghaladják a 15 000 000 forintot.

   9. Feltételek, amelyek segítségével kizárhat bizonyos rekordokat az összeg kiszámításából: kizárhatja például azokat az egyedi tranzakciókat, amelyeknél az () értékUnit Price * Quantity 10 000 forintnál kisebb. A következő lépésekből megtudhatja, hogy miként vehet fel egyenként a feltételeket, és hogy ez milyen hatással van a lekérdezés eredményére. Feltétel hozzáadása a lekérdezéshez

   10. Nyissa meg az előző részben szereplő lekérdezést Tervező nézetben. Ehhez kattintson a jobb gombbal a lekérdezés dokumentumfülére, és válassza a Tervező nézet parancsot. -vagy- A navigációs ablakban kattintson a jobb gombbal a lekérdezésre, és válassza a Tervező nézet parancsot.

   11. A Kategóriaazonosító oszlop Feltétel sorába írja be a következőt =Dolls Or Sports or Art and Framing: .

   12. A lekérdezés futtatásához és az eredmények Adatlap nézetben történő megjelenítéséhez kattintson a Futtatás gombra.

   13. Váltson vissza Tervező nézetre, és írja be a Teljes értékesítési összeg oszlop Feltétel sorába a >100következőt: .

   14. Futtassa a lekérdezést az eredmények megjelenítéséhez, majd váltson vissza Tervező nézetre.

   15. Ezek után adja hozzá a 10 000 forint alatti egyedi tranzakciókat kizáró feltételeket. Ehhez szükség lesz még egy oszlopra.

       Megjegyzés

       A harmadik feltételt nem adhatja meg a Teljes értékesítési összeg oszlopban. Az oszlopban megadott feltétel nem az egyedi értékekre, hanem a teljes értékre vonatkozik.

   16. Másolja a második oszlopban szereplő kifejezést a harmadik oszlopba.

   17. Az új oszlop Összeg sorában válassza a Hol lehetőséget, és írja be a következőt >20a Feltétel sorba: .

   18. Futtassa a lekérdezést az eredmények megjelenítéséhez, majd mentse a lekérdezést.

       Megjegyzés

       A lekérdezésnek a Tervező nézetben való legközelebbi megnyitásakor észrevehet apró változásokat a tervezőrácsban. A második oszlopban a Mező sorban szereplő kifejezés az Összeg függvénybe ágyazva jelenik meg, és az Összesítés sorban szereplő érték az Expression értéket jeleníti meg az Összeg helyett.

       Total Sales Value: Sum((1-[Order Details].Discount/100)*([Order Details].Unitprice*[Order Details].Quantity)) Egy negyedik oszlopot is látni fog. Ez az oszlop a második oszlop másolata, de a második oszlopban megadott feltétel az új oszlop részeként jelenik meg.

Vissza a tetejére

Több csoport adatainak összegzése kereszttáblás lekérdezéssel

A kereszttáblás lekérdezés egy speciális típusú lekérdezés, amely az eredményeket egy Excel-munkalaphoz hasonló rácselrendezésben jeleníti meg. A kereszttáblás lekérdezésekkel összesíthetők, majd két tényhalmaz szerint csoportosíthatók az értékek – az egyik halmaz (a sorfejlécek halmaza) a függőleges oldal mentén, a másik (az oszlopfejlécek halmaza) pedig a tábla tetején helyezkedik el. Ez az ábra a mintaként megadott kereszttáblás lekérdezés eredményhalmazának egy részét szemlélteti:

Menet közben ügyeljen arra, hogy a kereszttáblás lekérdezés nem mindig tölti fel az eredményhalmazban szereplő összes mezőt, mert a lekérdezésben használt táblák nem mindig tartalmaznak értékeket minden egyes adatponthoz.

Amikor kereszttáblás lekérdezést hoz létre, általában több táblából gyűjti be az adatokat, és a következő három adattípussal dolgozik: a sorfejléc adatai, az oszlopfejléc adatai és az összegezni vagy más módon kiszámítani kívánt adatok.

A szakaszban leírt lépésekben az alábbi táblákat használjuk:

A Rendelések tábla

Rendelés dátuma	Szállítási dátum	Város	Szállítási díj
2005.11.14.	2005.11.15.	Dzsakarta	5500 Ft
2005.11.14.	2005.11.15.	Sydney	7600 Ft
2005.11.16.	2005.11.17.	Sydney	8700 Ft
2005.11.17.	2005.11.18.	Dzsakarta	4300 Ft
2005.11.17.	2005.11.18.	Párizs	10 500 Ft
2005.11.17.	2005.11.18.	Stuttgart	11 200 Ft
2005.11.18.	2005.11.19.	Bécs	21 500 Ft
2005.11.19.	2005.11.20.	Miami	52 500 Ft
2005.11.20.	2005.11.21.	Bécs	19 800 Ft
2005.11.20.	2005.11.21.	Párizs	18 700 Ft
2005.11.21.	2005.11.22.	Sydney	8100 Ft
2005.11.23.	2005.11.24.	Dzsakarta	9200 Ft

A Rendelés részletei tábla

Rendelés azonosítója	Terméknév	Termékazonosító	Egységár	Mennyiség	Árengedmény
1	Csináld magad billentyűzet	12	7795 Ft	9	5%
1	Bürokrata antiakcióhős-figura	2	7888 Ft	4	7,5%
2	Számítógépguruk gyakorlata A DVD!	7	1488 Ft	6	4%
2	A csodálatos számítógéplapka	4	3265 Ft	8	0
2	Számítógépguruk és mitikus lények	6	7850 Ft	4	0
3	Hozzáférés! A játék!	5	2295 Ft	5	15%
4	Programozó akcióhős-figura	1	1295 Ft	2	6%
4	Valódi repülő pizza	8	3675 Ft	8	4%
5	Külső 5,25 hüvelykes hajlékonylemezes meghajtó (1/4-es léptékű)	9	6500 Ft	4	10%
6	Relációs adatbázis diagram	3	2250 Ft	12	6,5%
7	Félhomály	11	5333 Ft	6	8%
7	Relációs adatbázis diagram	3	2250 Ft	4	9%

A következő lépésekből megtudhatja, hogy miként hozhat létre olyan kereszttáblás lekérdezést, amely a teljes értékesítési összeget város szerint csoportosítja. A lekérdezés két kifejezést használ a formázott adat és a teljes értékesítési összeg visszaadására.

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

1. Kattintson a Létrehozás lap Lekérdezések csoportjának Lekérdezéstervező gombjára.
2. Kattintson duplán a lekérdezésben használni kívánt táblákra. Az egyes táblák ablakokként jelennek meg a lekérdezéstervező felső részében. Ha a mintatáblákat használja, kattintson duplán a Rendelések és a Rendelés részletei táblára.
3. Kattintson duplán a lekérdezésben használni kívánt mezőkre. Az egyes mezőnevek a tervezőrács Mező sorának üres celláiban jelennek meg. Ha a mintatáblákat használja, adja hozzá a Város és a Szállítási dátum mezőt a Rendelések táblából.
4. A Mező sor következő üres cellájába másolja és illessze be vagy írja be a következő kifejezést: Total Sales: Sum(CCur([Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100)
5. A Lekérdezéstervező lap Lekérdezés típusa csoportjában kattintson a Kereszttáblás gombra. Az Összesítés és a Kereszttábla sor megjelenik a tervezőrácsban.
6. Kattintson a Város mező Összesítés sorában lévő cellára, majd válassza a Group By elemet. Tegye ugyanezt a Szállítási dátum mezővel is. A Teljes értékesítési összeg Összesítés cellájának értékét módosítsa a Expression értékre.
7. A Kereszttábla sorban állítsa a Város mező celláját Sorfejlécre, a Szállítási dátum mezőt Oszlopfejléc értékre, a Teljes értékesítési mezőt pedig Érték értékre.
8. A Lekérdezéstervező lap Eredmények csoportjában kattintson a Futtatás gombra. A lekérdezés eredménye megjelenik Adatlap nézetben.

Vissza a tetejére

Az aggregátumfüggvények ismertetése

A táblázat felsorolja és ismerteti az Accessben található, az Összeg sorban és az összegző lekérdezésekben használható aggregátumfüggvényeket. Ne feledje, hogy az Access több aggregátumfüggvényt tartalmaz a lekérdezésekhez, mint az Összeg sorhoz.

Funkció	Leírás:	Adattípusok, amelyeken használható
Átlag (Avg)	Kiszámolja egy oszlop adatainak átlagértékét. Az oszlopnak numerikus, pénznem vagy dátum/idő adatokat kell tárolnia. A függvény figyelmen kívül hagyja a null értékeket.	Szám, pénznem, dátum/idő
Szám (Count)	Egy oszlopban található elemek számát adja eredményül.	Bármely adattípus a komplex, ismétlődő skaláris adatok (például többértékű listákból álló oszlopok) kivételével. A többértékű listákról a Többértékű mező létrehozása és törlése című cikkben olvashat bővebben.
Maximum (Max)	A legmagasabb értékű elemet adja vissza. Szöveges adat esetében a legmagasabb értékű elem a betűrend szerint legutolsó – az Access a nagy- és kisbetűket egyformának tekinti. A függvény figyelmen kívül hagyja a null értékeket.	Szám, pénznem, dátum/idő
Minimum (Min)	A legalacsonyabb értékű elemet adja vissza. Szöveges adat esetében a legalacsonyabb értékű elem a betűrend szerint legelső – az Access a nagy- és kisbetűket egyformának tekinti. A függvény figyelmen kívül hagyja a null értékeket.	Szám, pénznem, dátum/idő
Szórás (StDev)	Azt adja meg, hogy mennyire széles sávban térnek el az értékek az átlagtól (középérték). A függvény használatáról az Oszlopösszesítők megjelenítése adatlapon című témakörben olvashat bővebben.	Szám, pénznem
Összeg (Sum)	Összeadja egy oszlop értékeit. Csak numerikus és Pénznem adatokon használható.	Szám, pénznem
Variancia (Var)	Az oszlop értékeinek statisztikai varianciáját számítja ki. Ez a függvény csak numerikus és pénznem típusú adatokon használható. Ha a tábla kettőnél kevesebb sort tartalmaz, az Access null értéket ad vissza. A Variancia függvény használatáról az Oszlopösszesítők megjelenítése adatlapon című témakörben olvashat bővebben.	Szám, pénznem

Vissza a tetejére