Access-o-Mania

Hallo Forum,

bei der Erstellung einer Datenbank und dem damit verbundenen Import einer Excel-Datei incl. der Normalisierung der Daten würde ich mich sehr über Eure Hilfe freuen.

Meine Excel-Datei incl. aller Daten sieht wie folgt aus: Marktgebiet, Filiale, Betreuer, Kundennummer, Name, diverse weiter Felder. Die Excel-Tabelle habe ich zur weiteren Bearbeitung nach Access importiert und in die Tabelle "Daten" gespeichert.

Diese soll in folgende Tabellen überführt werden: Marktgebiete, Filialen, Kundenbetreuer, Kunden

Die Tabellen und Beziehungen stehen soweit, mein Problem ist die vernünftige Programmierung einer Prozedur, die die Tabelle "Daten" durchläuft und die jeweiligen Werte in den Zieltabellen auf Vorhandensein prüft, ansonsten den entsprechenden Datensatz anfügt und hierbei auch die entsprechenden Fremdschlüssel setzt. Ein wenig VBA-Erfahrung habe ich zwar, aber es reicht nicht. Aber ich würde mir zutrauen ein entsprechendes Muster auf meine Bedürfnisse umzuschreiben. Toll wäre es noch, wenn ich in einem Arbeitsschritt alle Tabellen entsprechend befüllen könnte.

Ich danke Euch für Eure Mithilfe

Michael

Hallo Michael,

bei der Erstellung einer Datenbank sind nicht die Daten zu normalisieren sondern das Datenmodell selbst.

ZitatDie Tabellen und Beziehungen stehen soweit

stell doch mal einen Screenshot des Beziehungsfensters oder die leere DB hier rein.

Marktgebiete und Filialen sind vermutlich Nachschlagetabellen, die kannst du per Abfrage relativ einfach in deine neuen Tabellen importieren.

z.B.:

INSERT INTO Filialen (Filialbezeichnung) SELECT DISTINCT Filialbezeichnung FROM Daten

Hallo,

anbei die Beziehungen. Die Tabelle Daten_Alt ist in Wirklichkeit Ellenlang und soll in die entsprechenden Tabellen aufgeteilt werden. Wichtig wären dann die korrekten Fremdschlüssel.

Danke

Abfragen sind übersichtlicher und vor allem schneller als VBA-Prozeduren.
Vergleiche Importtabelle in m:n-Beziehung auflösen (http://www.dbwiki.net/wiki/Datei:AccSampleDivideTable.zip)

Hallo Eberhard,

vielen Dank, das geht schon sehr in die richtige Richtung, jedoch habe ich nur 1:n-Beziehungen, so dass bei meinen doch recht eingeschränkten SQL-Kenntnissen die m:n-Aufteilung ziemlich komplex erscheint und ich Probleme habe diese auf meine Datenbank umzuschreiben. Gibt es das Ganze auch als Beispiel für 1:n? Ich stelle mir das so vor, dass ich über die INSERT INTO-Funktion jeweils eine komplette Tabelle mit den entsprechenden Daten aus der Datensatzherkunft befülle, und hierbei gleichzeitig die Fremdschlüssel der mit der Tabelle in Beziehung stehenden Tabellen eingetragen wird.

Ich weiß, hört sich jetzt verworren an, aber wie soll ich es anders ausdrücken?

Wäre toll, wenn ihr hier nochmal helfen könntet.

Danke!

Michael

Eine m:n-Beziehung ist eigentlich die Zusammensetzung von zwei 1:n-Beziehungen.
Wenn Du also die zweite Primärtabelle und deren Verknüpfung in die Sekundärtabelle entfallen lässt, hast Du die einfachere 1:n-Beziehung.

Zitatjedoch habe ich nur 1:n-Beziehungen

KBS und Kunden sind dann aber doch Verknüpfungstabellen in jeweils einer m:n-Beziehung ...!

ZitatIch stelle mir das so vor, dass ich über die INSERT INTO-Funktion jeweils eine komplette Tabelle mit den entsprechenden Daten aus der Datensatzherkunft befülle, und hierbei gleichzeitig die Fremdschlüssel der mit der Tabelle in Beziehung stehenden Tabellen eingetragen wird.

Genau das erfolgt doch im verlinkten Beispiel. Pro Tabelle gibt es genau eine Anfügeabfrage. Für zu wiederholende Pflege- und Aktualisierungsaufgaben kämen dann pro Tabelle noch Aktualisierungen (und Löschungen?) dazu, vergleiche SQL ist leicht (4) - Aktualisierung einer Tabelle (http://www.ms-office-forum.net/forum/showthread.php?t=304156).

Zitat
Probleme habe diese auf meine Datenbank umzuschreiben

Übersichtlich wäre es, wenn Du eine Demo mit Deinen Tabellen und Beziehungen sowie der Importtabelle (einige wenige datenschutztechnisch unkritische Datensätze genügen) und Deinen Versuchen hier als Anlage einstellst.

Sorry, hat etwas gedauert, hier die Datenbank.

Am Ende sollen alle Daten aus der Tabelle Daten_Alt in die anderen Tabellen übertragen sein, so dass man die Ursprungstabelle wieder löschen kann. Ich verzweifle am Verständnis der SQL-Zeilen, die Unterabfragen bringen mich um den Verstand. Leider habe ich noch keine praktische Lösungsmöglichkeit gefunden wie ich den SQL-Text mal einfach verstehen kann. Immer wenn die nächste Klammer aufgeht verzweifle ich noch mehr.

Danke für die Hilfe

Michael

Hallo,
wenn Du die DB nach Access2003 konvertierst, findest Du mehr die Dir helfen können. Viele haben kein Access2010.

geht leider nicht - keine Ahnung warum bzw. was ich ändern müsste

Hallo,

EDIT: habe überlesen, dass die *.accdb schon im Forum ist.
Konvertieren macht keinerlei Probleme!
Menü 'Datei' --> 'Speichern und Veröffentlichen' --> 'Access 2002 - 2003 - Datenbank' --> 'Speichern unter'

*.mdb ist im Anhang

Den einfachen Teil (3 Tabellen, die keine Fremdschlüssel benötigen) hast Du schon einmal gelöst. Bei der Tabelle Filialen wird es dann etwas umfangreicher (ist bis hier unvollständig).

Als Hinweis: Die Abfragen kannst Du natürlich im Abfrageeditor erzeugen und belassen und im Code dann nur mit dem Namen aufrufen (db.Execute "NameAbfrage", dbFailOnError).
Im Beispiel sind die Abfragen nur der Übersicht wegen als SQL-Strings im VBA-Code hinterlegt, Übersicht aber (nur) dann, wenn man SQL lesen und verstehen kann. Das Erzeugen der Strings an der Stelle wäre aber mehr als anstrengend (würde ich nicht tun).

Zweiter Hinweis: Die genannten Abfragen funktionieren als solches schon (Teststart: F5-Taste). Dabei kannst Du den Testlauf unendlich wiederholen, durch die eingebaute Inkonsistenzprüfung werden keine Einträge verdoppelt.

ZitatIch verzweifle am Verständnis der SQL-Zeilen ...

Wenn Du diese Abfragen fehlerfrei selber erstellen kannst, bist Du in der Fortgeschrittenengruppe schon etwas weiter fortgeschritten. Das ist also ein etwas größerer Schritt, der bewältigt werden will.

Wir widmen uns nun mal der Tabelle Filialen:
Zu füllen sind Filialbezeichnung (aus Daten_Alt) und Marktgebiet (PK aus Marktgebiete als FK). In Daten_Alt ist zudem die Zuordnung Marktgebietsbezeichung-Filiale (in Klarnamen)  enthalten, die nachfolgend benötigt und genutzt wird.

SELECT M.ID, DA.Filialbezeichnung
FROM Daten_ALT AS DA INNER JOIN Marktgebiete AS M
ON DA.Marktgebiet = M.Marktgebietsbezeichnung

Jetzt sollten wir aber dafür sorgen, dass Filialen jeweils nur einmalig eingetragen werden können. Also legen wir in der Tabelle einen eindeutigen Index auf Filialbezeichnung, und die SQL-Anweisung wird nachgerüstet, um keine Schlüsselfehler zu erzeugen: Ergänzung Inkonsistenzprüfung.
SELECT DISTINCT M.ID, DA.Filialbezeichnung
FROM Daten_ALT AS DA INNER JOIN Marktgebiete AS M
ON DA.Marktgebiet = M.Marktgebietsbezeichnung
WHERE NOT EXISTS
(SELECT NULL FROM Filialen AS F
WHERE F.Filialbezeichnung = DA.Filialbezeichnung)
Das DISTINCT dient dazu, in der Quelle für das Anfügen selber Duplikate zu vermeiden.

Nun machen wir noch eine Anfügeabfrage daraus: Voranstellen der INSERT-Zeile.
INSERT INTO Filialen ( Marktgebiet, Filialbezeichnung )
SELECT DISTINCT M.ID, DA.Filialbezeichnung
FROM Daten_ALT AS DA INNER JOIN Marktgebiete AS M ON DA.Marktgebiet = M.Marktgebietsbezeichnung
WHERE NOT EXISTS
(SELECT NULL FROM Filialen AS F
WHERE F.Filialbezeichnung = DA.Filialbezeichnung)

Die nächste(n) Tabelle(n) darfst Du wieder selber probieren. Ich hoffe, die Anleitung hilft ein wenig.

Anhang im Acc2000-Format:

Hallo Eberhard, ich mal wieder.

Nochmal vielen Dank für Deine Unterstützung bis hierher. Der Hinweis mit dem Abfrageeditor war super, hiermit kann ich mir zumindest bildlich etwas besser vorstellen wie die Abfragen funktionieren. Jetzt dachte ich mir "sei mal schlau" und nimm die fertige Abfrage vom Eberhard zur Befüllung der Filial-Tabelle und schreibe sie einfach nur zur Befüllung der Beratertabelle um. Ich habe also den SQL-Text in den Abfrageeditor kopiert und mir den Abfrageentwurf erst mal angesehen. Dann habe ich die beiden Tabellen "Berater" und "Funktionen" geöffnet und einfach nur das wiederholt, was Du vorher mit den Tabellen "Filialen" und "Marktgebiete" gemacht hast (also Beziehungen entsprechend angepasst, siehe Screenshot).

Das Ergebnis ist aber: es passiert gar nichts, und die 3. Spalte im Abfrageeditor schreibt auf einmal ein Ausdr1 vor den SQL-Text. Was mache ich denn immer falsch? Ich hoffe die Screenshots sind verständlich.

Michael

ZitatIch hoffe die Screenshots sind verständlich.

Für mich nicht. Ich lese SQL-Anweisungen. Da steht nämlich alles drin, was ausgeführt wird.

Meine Variante:
INSERT INTO Berater ( Funktion, Beratername )
SELECT DISTINCT F.ID, DA.Kundenbetreuer
FROM Daten_ALT AS DA INNER JOIN Funktionen AS F ON DA.Funktion = F.Funktionsbezeichnung
WHERE NOT EXISTS
(SELECT NULL FROM Berater AS B
WHERE B.Beratername = DA.Kundenbetreuer)

Zusätzlich muss die Abfrage auf Berater aus der 1. Schritt lt. VBA-Code entfernt werden (hatte ich übersehen), und natürlich muss die Tabelle geleert werden, da sonst nichts (gleiches) angefügt wird.

Ergänzung: Wie Du bemerkst, verwende ich Aliase (http://www.ms-office-forum.net/forum/showthread.php?t=298432) für die Tabellenbezeichnungen. Diese sind zwar frei wählbar, sollten aber doch "sprechend" auf den vollständigen Tabellennamen hinweisen, um eine Übersicht zu gewährleisten:
Berater AS B
Marktgebiete AS M

OK OK, so langsam glaube wird's vernünftig.

Für die Tabelle KBS bin ich nun mit folgendem Code zu korrekten Einträgen gekommen:

INSERT INTO KBS ( Berater, Filiale )
SELECT DISTINCT Berater.ID, Filialen.ID
FROM Filialen INNER JOIN (Berater INNER JOIN Daten_ALT ON Berater.Beratername = Daten_ALT.Kundenbetreuer) ON Filialen.Filialbezeichnung = Daten_ALT.Filialbezeichnung;


Wie muss ich den Code denn noch ergänzen, damit bei Vorhandensein eines entsprechenden Eintrags in der Tabelle KBS (bestehen immer aus dem Wertepaar Filiale, Berater (hierbei jeweils die Fremdschlüssel aus den Tabellen Filialen und Berater) nicht weitere Datensätze in der Tabelle KBS hinzugefügt werden?

Michael

A) Werte oder Wertkombinationen, die in der Tabelle eindeutig sein sollen, sichert man über einen eindeutigen (zusammengesetzten) Index (http://www.ardiman.de/datenbanken/grundlagen/tabellen/#SEC1) ab. Ein solcher Index verhindert in jedem Fall das Anlegen eines ungewünschten Duplikats, egal ob man das Eintragen per Hand, per Abfrage, per Recordsetaktion oder über gebundene Formulare versucht.

B) Die Abfrage unterstützt obigen Vorgang:
INSERT INTO KBS ( Berater, Filiale )
SELECT DISTINCT B.ID, F.ID
FROM Filialen AS F INNER JOIN
(Berater AS B INNER JOIN Daten_ALT AS DA ON B.Beratername = DA.Kundenbetreuer)
ON F.Filialbezeichnung = DA.Filialbezeichnung
WHERE NOT EXISTS
(SELECT NULL FROM KBS
WHERE KBS.Berater = B.ID AND KBS.Filiale = F.ID)

In der Abfrage fehlen übrigens noch die Inhalte für das Zielfeld Kundenbetreuerschlüssel.

Als Ergänzung: Der Gesamtimport betrifft ja das Ergänzen von sieben Tabellen. Ein guter Import wäre das fehlerfreie Ausführen von dann sieben Anfügeabfragen. Wenn jetzt aber bei der (z.B.) fünften Abfrage ein Fehler auftreten würde, hätte man hinsichtlich des Gesamtimports einen undefinierten Zustand (Teilergebnis, unbekannter Stand). Da wäre es günstig, den gesamten Import auf den Ursprungszustand zurücksetzen zu können, um eine Fehlerbehebung und einen Neuimport starten zu können. Das ermöglicht eine Transaktion (nachzulesen in der Hilfe).

Also mal ganz laienhaft macht dieser Passus

ZitatWHERE NOT EXISTS
(SELECT NULL FROM KBS
WHERE KBS.Berater = B.ID AND KBS.Filiale = F.ID)

Folgendes: "WHERE NOT EXISTS" ist dann quasi wie ein Schalter zu verstehen, der die ganze voranstellende Abfrage sozusagen ein- und ausschalten kann. Ob der Schalter jetzt eingeschaltet ist, und somit die Anfügeabfrage ausgeführt wird hängt nun vom "WHERE..." ab. In diesem Fall wird das Abfrageergebnis, also das Wertepaar B.ID/F.ID (verbunden durch AND) mit den Datensätzen aus der Tabelle KBS Berater/Filiale verglichen und bei Vorhandensein wird der "Schalter" für die Gesamtabfrage quasi ausgeschaltet. Richtig verstanden?

Was bedeutet aber das SELECT NULL?

Hallo Eberhardt,

dank Deiner Unterstützung habe ich nun fast den kompletten Import fertig. Ich habe auch noch weitere Tabellen eingerichtet und den Import aus Daten_ALT erweitert (in der hochgeladenen Version der Datenbank waren noch nicht alle Felder vorhanden). Nun sieht das Meisterwerk wie folgt aus:

  'Import der Funktionsbezeichnungen aus Daten_Alt nach Tabelle Funktionen
   sSQL = "INSERT INTO Funktionen (Funktionsbezeichnung)" & _
          " SELECT DISTINCT Funktion FROM Daten_ALT D WHERE NOT EXISTS" & _
          " (SELECT Null FROM Funktionen G WHERE G.Funktionsbezeichnung = D.Funktion)"
   db.Execute sSQL, dbFailOnError

   'Import der Marktgebietsbezeichnungen aus Daten_Alt nach Tabelle Marktgebiete
   sSQL = "INSERT INTO Marktgebiete (Marktgebietsbezeichnung)" & _
          " SELECT DISTINCT Marktgebiet FROM Daten_ALT D WHERE NOT EXISTS" & _
          " (SELECT Null FROM Marktgebiete M WHERE M.Marktgebietsbezeichnung = D.Marktgebiet)"
   db.Execute sSQL, dbFailOnError

   'Import der Filialbezeichnungen aus Daten_Alt nach Tabelle Filialen (und Zuordnung des Fremdschlüssels für Marktgebiet)
   sSQL = "INSERT INTO Filialen ( Marktgebiet, Filialbezeichnung )" & _
          " SELECT DISTINCT M.ID, DA.Filialbezeichnung FROM Daten_ALT AS DA" & _
          " INNER JOIN Marktgebiete AS M ON DA.Marktgebiet = M.Marktgebietsbezeichnung" & _
          " WHERE Not EXISTS (SELECT NULL FROM Filialen AS F WHERE F.Filialbezeichnung = DA.Filialbezeichnung)"
   db.Execute sSQL, dbFailOnError
   
    'Import der Partnerklassen aus Daten_Alt nach Tabelle Partnerklassen
   sSQL = "INSERT INTO Partnerklassen (Partnerklasse)" & _
          " SELECT DISTINCT Partnerklasse FROM Daten_ALT D WHERE NOT EXISTS" & _
          " (SELECT Null FROM Partnerklassen M WHERE M.Partnerklasse = D.Partnerklasse)"
   db.Execute sSQL, dbFailOnError
   
       'Import der Branchen aus Daten_Alt nach Tabelle Branchen
   sSQL = "INSERT INTO Branchen (Branchenbezeichnung)" & _
          " SELECT DISTINCT Branche FROM Daten_ALT D WHERE NOT EXISTS" & _
          " (SELECT Null FROM Branchen M WHERE M.Branchenbezeichnung = D.Branche)"
   db.Execute sSQL, dbFailOnError
   
       'Import der Berufsgruppen aus Daten_Alt nach Tabelle Berufsgruppen
   sSQL = "INSERT INTO Berufsgruppen (Berufsgruppenbezeichnung)" & _
          " SELECT DISTINCT Berufsgruppe FROM Daten_ALT D WHERE NOT EXISTS" & _
          " (SELECT Null FROM Berufsgruppen M WHERE M.Berufsgruppenbezeichnung = D.Berufsgruppe)"
   db.Execute sSQL, dbFailOnError

   'Import der Beraternamen aus Daten_Alt nach Tabelle Berater (und Zuordnung des Fremdschlüssels für Funktionsbezeichnung)
   sSQL = "INSERT INTO Berater ( Funktion, Beratername )" & _
          " SELECT DISTINCT F.ID, DA.Kundenbetreuer FROM Daten_ALT AS DA" & _
          " INNER JOIN Funktionen AS F ON DA.Funktion = F.Funktionsbezeichnung" & _
          " WHERE Not EXISTS (SELECT NULL FROM Berater AS B WHERE B.Beratername = DA.Kundenbetreuer)"
   db.Execute sSQL, dbFailOnError

   'Befüllen der Tabelle KBS mit den Fremdschlüsseln zu Berater und Filiale
   sSQL = "INSERT INTO KBS ( Berater, Filiale )" & _
        " SELECT DISTINCT B.ID, F.ID FROM Filialen AS F INNER JOIN" & _
        " (Berater AS B INNER JOIN Daten_ALT AS DA ON B.Beratername = DA.Kundenbetreuer)" & _
        " ON F.Filialbezeichnung = DA.Filialbezeichnung" & _
        " WHERE Not EXISTS (SELECT NULL FROM KBS WHERE KBS.Berater = B.ID AND KBS.Filiale = F.ID)"
   db.Execute sSQL, dbFailOnError

Funktioniert Super, jedoch werden leere Feldinhalte aus Daten_ALT in den einzelnen Tabellen doch immer wieder als neuer Datensatz angeführt. Fehlt zum Beispiel bei einem Kunden die Branche oder die Berufsgruppe, so wird in der Tabelle "Branchen" immer wieder ein Datenstz angefügt, der jedoch keinen Inhalt aufweist. Hier scheint die Bedingung "WHERE NOT EXISTS" nicht zu greifen. Ist das begehbar?

Beste Grüße

Michael

Zitat"WHERE NOT EXISTS" ist dann quasi wie ein Schalter zu verstehen ...

Nein. Eine WHERE-Bedingung ist ein Filter. Wenn man per Datenlage und Filter ein leeres Recordset erzeugt, kann natürlich nichts angefügt werden.

EXISTS erzeugt True oder FALSE, je nach dem, ob es in der Unterabfrage korrespondierende Datensätze zur Hauptabfrage gibt. Dabei ist es in dieser Gestaltung egal, ob oder welche Felder im SELECT-Teil der Unterabfrage aufgeführt sind (da es um Datensätze mit Kriterien geht). So kann man, statt irgendwelche Felder aufzuführen und dabei vielleicht den Eindruck zu erwecken, diese Felder hätten eine Bedeutung, auch eine Konstante zum "Füllen" des SELECT-Teils verwenden. In Anregung von Nouba (http://www.ms-office-forum.net/forum/member.php?u=24432) verwende ich da NULL.
Einerseits "dokumentiert" NULL besonders schön die Bedeutungslosigkeit von Werten an dieser Stelle, andererseits müssen keine Werte in das Abfragerecordset geladen werden: Schlanke Recordsets sind performanter zu verarbeiten.

Eine andere Variante der Inkonsistenzprüfung wäre diese: Datensätze aus A, die nicht in B sind (http://www.donkarl.com?FAQ3.16)
Hier, wo sehr viele Tabellen zu verknüpfen wären, würde es aber ziemlich unübersichtlich bei dann vielen JOIN's bis vermutlich sogar problematisch.

Die Frage "leere Feldinhalte" musst Du selber zusätzlich lösen.
Denkbare Maßnahmen (auch im Kombination):
- Quelltabelle vor Import vervollständigen (oder insgesamt abweisen)
- In der Zieltabelle "Eingabe erforderlich" für das betrachtete Feld einstellen. Damit erzeugt ein leerer Inhalt einen Fehler.
- In der Abfrage für Ersatzeinträge sorgen.
- In der Abfrage Datensätze mit leeren Feldern ausfiltern. Diese Datensätze dann für den Import ignorieren oder aber mit Einzeleingaben und -anfügen nachtragen.

Und natürlich:
   sSQL = "INSERT INTO KBS ( Berater, Filiale, ... )" & _
        " SELECT DISTINCT B.ID, F.ID, ... FROM ..."
Wenn man Felder nicht in die Abfrage aufnimmt, können in der Zieltabelle keine Werte ankommen.