Die Abfragen

Die Abfragen
Das Prinzip der Abfrage.
Mit Abfragen stellt man Fragen über die Daten, die Access in
Tabelle speichert.
Bemerkungen:
• Das Abfrage ist die Regeln zur Auswertung der Daten.
• Die Abfragen speichern niemals Daten.
• Mit Abfragen kann man eine neue Sicht über die Tabellen definieren.
• Ein Abfrage greift immer auf die Daten zu, die in einer oder
mehreren Tabellen/Abfragen (oder beides) gespeichert sind.
• Die Abfragen können wir "durch Beispiel" (englisch: Query By
Example; kutz QBE) oder mit Hilfe der SQL-Sprache formulieren.
• Das Ergebnis einer Abfrage wird als
Dynaset bezeichnet.
• Ein Dynaset hat den Charakter einer
Tabelle. Es besteht aus Datenfeldern
(Spalten) und Datensätzen (Zeilen).
• Das Dynaset selbst wird nicht gespeichert.
• Es gibt die Möglichkeit das Dynaset in
eine Tabelle umzuleiten, so daß es für
weitere Anwendungen verfügbar ist. Man
braucht eine spezilelles Mechanismus:
QBE - Make Table.
Vorteile.
• Der Vorteil liegt in ihrer Aktualität.
• Außerdem helfen Abfragen Speicherplatz
sparen. Wenn man beispielweise ein Feld für
Nettowert un ein weiteres für die Mehrwertsteuer
eines Projektes definiert haben, dann benötigt
man kein weiteres für den Bruttowert.
• Mit Abfragen lassen sich auch solche Tabellen
verknüpfen, für die keine referentielle Integrität
definiert wurde.
• Die meisten Formulare und Berichte besieren
daher auf Abfragen.
• Abfragen bietet die Möglichkeiten wie:
-Tabellen nach mehreren Feldern zu sortieren,
-Tabellen nach mehreren Kriterien zu
gruppieren und
- verschiedene Berechnungen durchzuführen.
• Außerdem lassen sich Datensätze mit
Aktualisierungsabfragen automatisch
ändern, mit Einfügeabfragen automatisch
einfügen und mit Löschabfragen
automatisch löschen.
Das Abfragefenster
• Access zeigt im oberen Bereich (Zone A)
des Abfragefensters alle geöffneten
Tabellen und/oder Abfragen und deren
Verknüpfungen an. Der untere Bereich
(Zone B) des Abfragefensters bildet den
QBE - Entwurfsbereich. Man benötigt der
Zone B um Feldern und Sätze
auszuwählen sowie um Abfragekriterien,
Berechnungen und Gruppierungen zu
definieren.
Zone A
In dieser Zone kann man die Source der Daten
(die Basistebellen und/oder Abfragen)
definieren.
Problem. Man soll die Verkäufer aus
Hermannstadt mit Verkaufstransaktionen nach
dem 08.03.1999 erhalten.
Lösung
Die notwendigen Informationen sind in zwei
Tabellen gespeichert. Also, man braucht als
Basistabellen die Tabellen KUNDEN und
VERKAEUFE und die betreffende Beziehung
Bemerkung. Wenn man braucht mehreren Tabelle/Abfrage
für welche keine Standard-beziehungen gibt, muss man
die Tabellen/ Abfragen manuell verknüpfen und naher die
Verknüpfungseigenschaften definieren(Abb.5.9)
Zone B
Zeile FELDER.
• Wenn man die Spaltenüberschrift ändern will,
benutzt man die Syntax:
Spaltenname: Datenfeld.
Beispiel:
Mehrwertsteuer: MWSt.
• Für die Berechnetefeldern der Syntax ist folgende:
Berechnetefelder: Ausdruck
Beispiel:
Nettowert: [Menge] * [Preis]
• Berechnetefelder enthalten in aller Regel
Ausdrücke, die auf mindestens einer Spalte
des Dynaset basieren.
Die Formel können lang und
komplex sein
Zeile TABELLE.
Diese Positionen werden automatisch geschrieben. Das
System weist vom welche Tabelle ein Datenfeld in der
Zeile Felder ist.
Wenn in der Zone A man gibt mehrere Tabelle, die
Feldauswahlliste zeigt wie folgt:
Tabelle1. *
Tabelle1.Feld1
Tabelle1.Feld2
....
Tabelle2.*
Tabelle2.Feld1
Tabelle2. Feld2
...
wobei * bedeutet "Alle Felder".
Zeile ANZEIGEN
Manchmal benötigt man Felder nur um
Abfrage-kriterien oder eine
Sortierreihenfolge zu definieren.
Die betreffenden Felder sollen in
Dynaset nicht angezeigt werden.
In diesem Fall leehrt das betreffende
Kontrollkästchen.
Zeile SORTIERUNG
• Diese Zeile bietet die Möglichkeit das
Dynaset sortieren.
• Man wählt die Sortierreihenfolge aus
dem betreffende Kombinationsfeld.
Zeile KRITERIEN
• Abfragekriterien schränken die Menge der
Datensätze im Dynaset ein.
• Abfrage-kriterien bestehen aus logischen
Ausdrücke. Die meistens Abfragekriterien
sind Vergleiche.
• Die wichtigsten Vergleichsoperatoren sind:
> ;< ; =; >=; <= ; <>; Zwischen
• Man benutzt auch logische Operatoren:
Aeqv; Exoder; Imp; Nicht; Oder; Und
• und aritmethische Operatoren:
-; *; /; \; ^; +; Mod.
Abfrage
Kunden aus Düsseldorf
Kunden aus Düsseldorf
Die Datensätze mit
einem MWSt von 19 und
22 Prozent
Kunden ohne Telefonnr.
Die Verträge aus März
Die Verträge aus März
Alle Verträge bei
denen das Datum
höchstens 10 Tage
zurückliegt
Feld
Ort
Adresse
MWSt
Kriterium
"Düsseldorf"
Like "*Düsseldorf*"
In (0.19; 0.22)
Tel
Datum
Is Null
Zwischen
#01/03/2003#
#31/03/2003#
=3
Monat:
Month([D
atum])
Datum
Verträge mit dem Wert Wert
zwischen 10 Tousend un
20 Tousend
und
>= Datum( ) – 10
>= 10 und <=20
oder
Zwischen 10 und 20
Parameter in Abfragekriterien.
• Die Definition von Parametern ist
denkbar einfach. Man klick in die
Kriterienzeile einer Spalte und gibt man
in eckigen Klammern einen Text ein.
Problem:
• Die Informationen (NrDok, Datum,
AnzahlAktien und Preis) ueber die
Verkaufstransaktionen welche in einem
bestimmten Periode und von einem
bestimmte Verkaeufer gemacht werden.
Gruppierung von Datensätzen
• Mit Abfragen hat man die Möglichkeit,
den Datenbestand zunächst nach
einem oder mehreren Kriterien zu
gruppieren, um anschließend
Berechnungen durchfüren zu lassen.
• Die Bildung der Gruppen definiert man
in der Funktionenzeile. Die
Umschaltfläche von dieder Zeile bietet
folgende Möglichkeiten:
a. Gruppierung
- implizit und für die Gruppenfelder.
- Access erlaubt die Bildung mehrerer
Gruppen.
- Wie die Sortierkriterien wertet Access
auch die Gruppenkriterien von links
nach rechts aus. Es kann daher
vorkommen, daß man die Spalten des
Dynasets neu anordnen muß.
b. Liste der Aggregatfunktionen
- Man kann mehrere Gruppenkriterien
auch mit mehreren Aggregatfunktionen
kombinieren.
- So lassen sich beispielweise der
durchschnittliche und der maximale
Umsatz je Kundennummer und
Rechnungsjahr ermitteln.
c. Bedingung.
Neben der Definition von Kriterien für
die berechneten Werte kann man auch
Kriterien definieren, die die Datensätze
vor der Berechnung ausschließen.
d. Ausdruck .
Der Eintrag AUSDRUCK aus dem
Kombinationsfeld benutzt man wenn
die betreffende Spalte ein
Berechnetfeld enthält.
Problem: Zahlen der Bestellungen, die Gesammtsumme
(Nettowert und Bruttowert), für ein Monat, wobei das
Mehrwertsteuerprozent ein Parameter ist.
Aktionsabfragen
Aktionsabfragen können Datensätze automatisch
aktualisieren, löschen oder anfügen.
Aktualisierungsabfragen.
Der Befehl ist QUERY/Update Query. Der Zone B hat eine
spezielle Zeile: Update to.
In der FELDERZEILE zieht man nun alle Datenfelder deren
Werte man ändern will.
In der Zeile Update to man schreibt die neuen Werte
(Konstanten, Ausdrucken - mit oder ohne Parameter) für
die Datenfelder.
Man kann gegebenenfalls Abfragekriterien definiren.
Problem:
Man soll eine Abfrage für die Aktualisierung der
Adresse eines bestimmten Kunden definieren
Anfügeabfrage
Mit einer Anfügeabfrage kann man Daten aus
einer oder mehreren Tabellen extrahieren
und diese an bereits existierende Tabellen
anfügen.
Zeile Append to:
Mit Hilfe dieser Zeile bestimmt man für jede Spalte
des Entwurfsbereiches, in welches Datenfeld die
Daten aus den Quelltabellen übertragen werden
sollen. Man kann auch Abfragekriterien
definieren.
Problem: Man soll eine Tabelle mit allen Transaktionen
(Die Verkauftransaktionen und die Einkauftransaktionen)
ermitteln.
Löschabfragen
Mit einer Löschabfrage kann man Datensätze, die
bestimmten Kriterien entsprechen, automatisch
löschen lassen.
• Der erste Schritt zur Löschabfrage sollte stets eine
Auswahlabfrage sein. Auswahlabfrage bietet die
Möglichkeit, zunächst die Abfragekriterien zu
überprüfen, bevor die Datensätze gelöscht werden.
• Weiter man wählt den Befehl Query/Delete Query
aus, um die Auswahl - in eine Löschabfrage
umzuwandeln.
• Zeilen Delete..
where..
Problem 1: (basiert auf einer Tabelle)
Man soll die Datensätze über die Verkauftransaktionen aus
Oktober loeschen.
Problem 2: (basiert auf zwei Tabellen)
Man soll die Datensätze über die Verkauftransaktionen
aus Oktober loeschen welche bei einem bestimmten
Broker gemacht wurden.
Kreuztabellenabfragen
• Man benötigt Kreuztabellenabfragen nur , wenn man den
Datenbestand nach mindestens zwei Kriterien
gruppieren will.
• Die Kreuztabellenabfragen ermöglichen eine bessere
Darstellung von berechneten Ergebnissen, gut für die
Analyse von Daten.
• Mit Kreuztabellenabfragen lassen sich die Ergebnisse in
einer zweidimensionalen Tabelle anordnen.
• Die Kreuztabellenabfrage stellt die Werte des einen
Gruppenkriteriums als Überschriften der Spalten und
die Werte des anderen Gruppenkriteriums als
Überschriften der Zeihlen einer Tabelle dar. Die Zellen
der Tabelle werden dann von berechneten Werten
gebildet.
Problem:
Die Berechnung der kumulierten Werte fuer jeden
Brokername, fuer jedes Verkaufsjahr und jeden
Verkaufsmonat.
Hinweis. Man kann zunäschst eine normale Abfrage
erstellen und diese dann in eine Kreuztabellenabfrage
umwandeln.
• Das erste Gruppenkriterium ist also die Brokername.
• Das zweite und das dritte Gruppenkriterium bedarf der
Anwendung eines kleinen Tricks: in die FELDZEILE zwei
"Berechnetefelder" definieren und zwar:
Verkaufsjahr : Format([Datum];"jjjj") und
Verkaufsmonat: Format([Datum]; "mmm")
• Als vierte Spalte benötigt man das Feld für den Wert
(also die Summe von [Preis]* [AnzahlAktien])
SQL – Abfragen
• Die strukturierte Abfragesprache SQL (englisch:
Structured Query Language) bildet einen Standard zur
Formulierung von Abfragen.
• Der Befehl Ansicht/SQL zeigt das SQL-Fenster einer
Abfrage an. Das SQL und das Abfragefenster bilden
zwei verschiedene Darstellungsformen derselben
Abfrage.
• Wenn man Einstellungen des Abfragefensters ändern,
aktualisiert Access automatisch den korrespondierenden
SQL- Befehl und umgekehrt.
Problem:
Man soll die Kunden welche in der Periode 25.01.99 –
30.08.99 Aktien eingekauft haben ermitteln.
Wiederholung der wichtigsten Operationen:
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
Spalten und Zeilen auswählen.
Layout des Dynaset.
Sortieren nach mehrere Spalten.
Berechnete Felder.
Abfragen mit mehreren Kriterien. Nutzung den Operatoren,
Konstanten und Funktionen.
Parameter in Abfragekriterien.
Datensätze gruppieren.
Aggregatfunktionen.
Abfragekriterien bei Gruppenbildung.
Kreuztabellenabfragen.
Aktionsabfragen:
Bestehende Tabellen aktualisieren
Neue Tabellen erstellen
Datensätze an bestehende Tabelle anfügen
Datensätze löschen