PDA

Vollständige Version anzeigen : Datenbereinigung Anrede


daha1
23.03.2004, 17:15
HAbe eine grosse AdressDB

Nun will ich anhand der Anrede bestimmen wieviel Männer/Frauen/Paare/Familien Adressen ich besitzte!

Nun musste ich feststellen das ich unzähliche verschiedenartige ANREDEN besitze.

Zum Bsp.

Herr
Herrn
Her (fasche Schreibweise)
Herr + Frau
Herr und Frau
Herr u. Frau
Herr & Frau
Frau & Herr
Frau + Herr
Frau & Frau
Frau u. Herrn

etc....


Ich will nun mittels einer SQL Abfrage diese filtern und in der Tabelle ersetzten!

PSEUDOCODE:

Wenn "Her" oder "Her_" oder "He__" dann write "Herr"
oder
wenn "Fr_" oder "Fr__" oder "Fr___" dann write "Frau"
oder wenn
("%Her%" oder "%Her_%" oder "%He__%" + "%Fr_%" oder "%Fr__%" oder "%Fr___%") dann write "Herr u. Frau"


Wie setze ich das in SQL um?


Danke für Hilfe

Dani

Wenn

Thomas Braun
23.03.2004, 17:29
etwa so:
SELECT Sum(IIf([Anrede] Like "*h*" And [Anrede] Not Like "*f*",1,0)) AS Herren, Sum(IIf([Anrede] Like "*f*" And [Anrede] Not Like "*h*",1,0)) AS Frauen, Sum(IIf([Anrede] Like "*h*" And [Anrede] Like "*f*",1,0)) AS beide
FROM tblAnreden;

daha1
23.03.2004, 17:54
Danke, aber ich möchte die Werte in der Adresstabelle ersetzten!

Wie ersetzte ich diese, anhand der Pseudocode Logik?

Engeli
23.03.2004, 18:25
Hallo

Also nimm den SQL Code und kopier ihn in eine leere Abfrage.
Dann siehst Du in der Auswahlabfrage ob du alle Ausreisser erwischt hast.
Dann das ganze als Aktualisierungsabfrage starten und die Einträge ändern. Brauchst Du weitere infos?

Ach ja ganz vergessen. Wenn die Daten bereinigt sind. Habe in meiner DB eine Abfrage die Herren, Frauen ect. zählt. Zuerst mal schauen das das andere klappt.

Gruss Markus

daha1
23.03.2004, 18:34
Ja, den SQL Code von oben zählr mir die Datensätze aus welche Mann und Frau beinhalten!

Ich aber möchte die Tabelle bereinigen. So das es am Schluss nur noch einige Anredeformen gibt:

Herr
Frau
Herr u. Frau

Ich schaffe es aber nicht, die Tabelle per SQL Abfrage zu bereinigen. Könnte es auch im Suchen und Ersetzten von Hand lösen, erhalte aber regelmössig so schlechte Adressen und möchte das mit einer SQL-Abfrage erledigen!

Benötige genaue Angaben über den Code, da ich keine grosse AC Erfahrung habe!

Besten Dank für Hilfe...

Dani

Engeli
23.03.2004, 18:44
Hallo

Sorry habe falsch gelesen. Also ich würde gar nicht lange suchen. Mach einfach drei Durchgänge. Zuerst Frau, dann Herr (Aktualisierungsabfrage). Wieso Herr u. Frau?

UPDATE tblAdressen SET tblAdressen.Anrede = "Frau"
WHERE (((tblAdressen.Anrede) Like "f*"));


Gruss Markus

daha1
23.03.2004, 18:48
Vielen Dank

Da ich aber auch noch alle Zählern will, bin ich an deinem Tool interessiert!

Will am Schluss wissen wieviele Frauen, Männer, Paare und Fmilien ich in der Adressdatenbank habe!


Danme mal soweit!

Dani

Engeli
23.03.2004, 18:59
Hallo Daha1

Damit meine Anreden auf vier beschränkt sind , habe ich im Formular ein Kombinationsfeld Wertliste Herr, Frau, Paar, Fam. und nur Listeneinträge auf ja. So gibt es keine abweichenden Einträge. Hier die Zählabfrage, wobei du natürlich auch die Abfrage von Thomas Braun verwenden kannst. Musst sie nur anpassen.

SELECT Sum(IIf([Anrede]="Herr",1,0)) AS Herren, Sum(IIf([Anrede]="Frau",1,0)) & _
AS Frauen, Sum(IIf([Anrede]="Fam",1,0)) AS Familien, Sum(IIf([Anrede]="Paar",1,0)) AS Paar

Gruss Markus

Engeli
23.03.2004, 19:12
Hallo

So das ist die angepasste von Thomas Braun.

SELECT Sum(IIf ([Anrede] & _
Like "*h*" And [Anrede] Not Like "*f*",1,0)) & _
AS Herr, Sum(IIf ([Anrede] Like "*f*" And [Anrede] & _
Not Like "*h*",1,0)) AS Frau, Sum(IIf ([Anrede] Like "*h*" & _
And [Anrede] Like "*f*",1,0)) AS HerrUndFrau, & _
Sum(IIf ([Anrede] ,Like "*Fa*",1,0)) AS Familie, & _
Sum(IIf([Anrede] Like "*P*",1,0)) AS Paar

Vorher ganz vergessen. & _ aus SQL entfernen.

Gruss Markus

daha1
23.03.2004, 19:22
Hallo Markus

Kann es sein das dieser SQL Code nicht funktioniert!

Er ändert bei mir keine EInträge:

Dein Code:
UPDATE tblAdressen SET tblAdressen.Anrede = "Frau"
WHERE (((tblAdressen.Anrede) Like "f*"));



Mein Code:

UPDATE test SET test.anrede = "Herr"
WHERE (((test.Anrede) Like "H__"));


Danke für ein Check!!


Dani

Engeli
23.03.2004, 19:32
Hallo

statt __. * verwenden. Aber Achtung , wenn Du denn Stern verwendest. Wird Herr und Frau auch zu Herr

Gruss Markus

jmc
23.03.2004, 19:33
Hi Dani

ich würde einen anderen Ansatz wählen:

- mit einer Gruppierungs-Abfrage ermitteln, welche Anreden es tatsächlich gibt

- diese vorhandenen Anreden in eine Tabelle füllen (Tabellen-Erstellungs-Abfrage) und in dieser Tabelle jeder vorhandenen Anrede einen Anrede_Code geben !

- dann die Ersetzung der Text-Anrede durch den Code vornehmen. (kannst du mit einer Aktualisîerungs-Abfrage leicht erledigen)
In diesem Zusammenhang: verwende doch die grafische Darstellung der Abfrage, damit lässt sich nämlich ziemlich einfach etwas bewerkstelligen, ohne dass man die genaue SQL-Syntax beherrscht !

- die Anrede-Tabelle reduzierst du dann auf die noch gewünschten.

- in Zukunft kann nur noch aus der Anrede-Tabelle gewählt werden.

alles klar ?

daha1
23.03.2004, 19:34
Ich muss aber Herr und Frau behalten!

Funktioniert den _ underline nicht als^Platzhalter?

jmc
23.03.2004, 19:35
Hi Dani

du bist in Access nicht in irgend einem DB-System, das vielleicht den Underline auch akzeptiert ...

schau dir meinen Vorschlag an, damit kommst du IMHO am besten weiter ...

Engeli
23.03.2004, 19:48
Hallo

Wie gross ist den Deine DB. Natürlich hat Jean mit seinem Typ recht. Wenn Du willst, Falls keine Vertraulichen Daten, kannst Du mir die DB an arni@tiscalinet.ch mailen und ich pass die Anrede an. Mit Zählen usw.


Gruss Markus

jmc
23.03.2004, 19:52
@Engeli

bleibt ja in der Schweiz - und die Schweizer sind ja bekannt für Diskretion - siehe Bankgeheimnis .. http://www.das-forum.ch/_various/pics/smilies/cool/cool07.gif

@Dani
hab's nicht so explizit gesagt wie Markus, aber du weisst ja wo ich wohne ...

daha1
23.03.2004, 19:52
Die Tabelle ist ca. 60MB gross hat ca. 150'000 Datensätze!

Das Problem ist ich erhalte ca. 40x pro Jahr eine änhliche LIste von verschiedenen Organisationen. Habe alse keine Einfluss auf die Datenqualität.

Desshalb versuche ich die lästige suchen/ersetzen Uebung per SQL zu lösen!

jmc
23.03.2004, 19:57
Hi Dani

nun, umso mehr würde ich eine Lösung mit einer Tabelle anstreben, denn sonst kannst du jedesmal den SQL-Code anpassen ...

Heisst das somit, dass du gar nichts mehr an den Daten machst ?
Dann kannst du auch direkt in der Anrede-Tabelle statt eines Codes den Ersatzwert einsetzen.

Jedesmal wenn wieder eine neue Tabelle kommt, machst du zuerst die Anfüge-Abfrage der vorhandenen Anreden. Wenn du die Spalte mit dem Original-"Text" als Schlüsselfeld definierst, dann werden nur die noch nicht vorhandenen Varianten angefügt. Dann den Ersatz-Text eingeben und die Aktualisierungs-Abfrage starten ...

Engeli
23.03.2004, 20:01
Hallo

Dann bietet es sich ja an das ganze zu automatisieren, da kann Dir Jean als Profi sicher bessere Tipps geben als ich (Anfänger). Ups und schon wieder schneller.

Gruss Markus

daha1
23.03.2004, 20:11
Danke...

Bin ja auch Anfänger...

Eigentlich liegt das Problem ja nus darin das ich bei folgender Abfrage:


UPDATE test SET test.Anrede = "Herr"
WHERE (((test.Anrede) Like "H*"));

nur "H*" verwenden kann.

Ich müsste aber nicht mit * sondern mit Platzhalter arbeiten, sonst werden "Herr und Frau" Anreden auch gelöscht!

Gibts den das keine Lösung!

Wenn wir das schaffen, kann ich doch eine gemäss meinen Pseudocode, verschachtelte Abfrage bauen?

Engeli
23.03.2004, 20:20
Hallo

Du kannst natürlich in den Kriterien alle versch. Kombinationen angeben. Oder und setzen. Aber wie Jean schon gesagt hat, das ist ja eine Fleissarbeit bei so vielen DS. Hast Du den schon alle versch. Anreden mit einer Abfrage Gruppiert, oder sind das wirklich alle Kombinationen die Du am Anfang des Beitrages geschrieben hast?
Versuch grad was mit Aktualisierungsabfrage.


Fragt Markus

daha1
23.03.2004, 20:26
E sind so 20 bis 30 Filterabfragen!

Das ist eigentlich kein grosser Aufwand!

Das Problem ist wie gesagt das ich den Platzhalter _ underline anscheinend nicht verwenden kann.

"H*" funktioniert aber "H___" nicht!

Gibt es denn keine Alternative?

jmc
23.03.2004, 20:41
Hi Dani

ich weiss nicht, was denn an meiner Vorgehensweise so schlecht sein soll, dass du ums V.... mit den SQL-Bedingungen arbeiten willst ???

Der Underline geht definitiv NICHT !!


E sind so 20 bis 30 Filterabfragen!
Das ist eigentlich kein grosser Aufwand!
Das mag jetzt bei diesem File so sein, aber bist du sicher, dass du damit für die nächsten 39 Files alle Fälle erschlagen hast ...

Engeli
23.03.2004, 20:41
Hallo

UPDATE tblAdressen SET tblAdressen.Anrede = "Herr u. Frau"
WHERE (((tblAdressen.Anrede) Like "Herr & Frau" Or (tblAdressen.Anrede) Like "Herr + Frau" Or (tblAdressen.Anrede) Like "Frau & Frau"));


du musst den Code natürlich um alle Varianten erweitern. Wenn du nicht klar kommst. Ich bleibe noch online.

Oder So.

UPDATE tblAdressen SET tblAdressen.Anrede = & _
"Herr u. Frau"
WHERE (((tblAdressen.Anrede) Like "H* * F*" & _
Or (tblAdressen.Anrede) Like "F* * H*" Or & _
(tblAdressen.Anrede) Like "f* * F*"));


& _ entfernen

Gruss Markus

daha1
23.03.2004, 21:52
DANKE

Denke komme auf diese Weise weiter.... Teste alles Morgen und melde mich wieder.


Guete Nacht!

kama
24.03.2004, 06:15
@Jean

ich weiss nicht, was denn an meiner Vorgehensweise so schlecht sein soll, dass du ums V.... mit den SQL-Bedingungen arbeiten willst ???

Also ich finde deinen Vorschlag angemessen, einfach umzusetzen, flexibel. :10points:


Aber wem nicht zu raten ist dem ist nicht zu helfen.

Hätte er dein Vorschlag sofort umgesetzt wäre er schon längst fertig und brauchte hier nicht weiter zu suchen.


Gruss Klaus

daha1
24.03.2004, 07:52
Danke soweit für die Hilfe


UPDATE test SET test.anrede = "xdyd"
WHERE test.anrede Like "H*F*" Or test.anrede Like "F*H*" Or test.anrede Like "F*F*" Or test.anrede Like "H*H*";

Herr und Frau Anrede hab ich erledigt!

Nun versuche ich die mehrere SET Werte mit WHERE Abfrage in einer ABFRAGE zu integrieren!

Geht das, oder wie lautet der Syntax?

Danke


Nur noch als input, warum ich das gerne in einer SQL, oder VBA Routine lösen will.

Wenn ich die Anredebereinigung hier mit Euch in dieser Form lösen kann, kann kann ich diese Routine für alle anderen Adressdaten, mit welchen ich arbeiten muss, verwenden und erhalte so langsam aber sicher einen Standart.

kama
24.03.2004, 08:23
......kann kann ich diese Routine für alle anderen Adressdaten, mit welchen ich arbeiten muss, verwenden und erhalte so langsam aber sicher einen Standart.

Disen Standard erhälst du ja auch mit mit der von Jean Vorgeschlagenen Lösung!

Viel leichter wartbar und Erweiterbar

daha1
24.03.2004, 08:35
Das kann sein...nur leider verstehe ich in diesem Falle seinen Vorschlag nicht!


Bin AC Anfänger.

Kannst du mir Schritt für Schritt den Lösungsweg von Jean erklären!

kama
24.03.2004, 08:53
Hallo am einfachsten wärw es wenn du die Tabelle(Testdaten) hochladen würdest. Du wirst sehen es ist ganz einfach.

Bin aber jetzt für drei stunden nicht erreichbar, also etwas geduld :)

daha1
24.03.2004, 09:37
Hier meine Test DB

Wäre toll wenn du Jeans Vorschlag intergrieren kannst.

Damit ich verstehen was und wie es gemeint ist!

Danke

Thomas Braun
24.03.2004, 09:51
Ich hab den Thread nicht genau verfolgt, aber vielleicht hilft dir das Beispiel weiter.

kama
24.03.2004, 10:21
Hallo
Hier das Beispiel

daha1
24.03.2004, 11:14
Wauw gleich zei Lösungen....

Super

Die VBA Lösung ist die Antwort auf mein SQL-WUNSCH, besten Dank.... Werde es am Nachmittag testen.

...und die "Jeansche" Lösung finde ich auch genial. Eigentlich so einfach....
nur überschreibt es mir die Werte in der Ursprungstabelle "Test" nicht!

Kann ich mit der Jeanschen Lösung die Tabelle TEST direkt verändern?

DANKE ABER Für die schnelle, kollegiale Hilfe......

Dani

kama
24.03.2004, 11:19
Hallo

nur überschreibt es mir die Werte in der Ursprungstabelle "Test" nicht!

Das ist ja das Geniale

Die Ursprungsanrede bleibt erhalten und kann eventuell weiterverwendet werden.

kommt nun eine neue Anrede hinzu zb

Buana>>>>Herr

Brauchst du sie nur um diesen Datensatz erweitern und schon wird dir die Gewünschte Abrede angezeigt.

Die Abfrage kannst wie eine Tabelle weiterverarbeiten

Wenns denn wirklich sein muus speicher die Abfrage als Tabelleohne das Feld Anrede, dann hast du was du willst

Thomas Braun
24.03.2004, 12:39
@ daha1: Die Funktion kannst du wie jede andere Funktion auch.
Entweder in der Entwurfansicht auf die Funktion klicken und dann Menü Ausführen --> Weiter oder einen Button erstellen und beim Klick-Ereingnis eintragen ...

daha1
24.03.2004, 12:49
Kann ich diese auch innerhalb einer SQL Anweisung starten...und zwar so das es mit die Tabelle TEST verändert?

Thomas Braun
24.03.2004, 12:55
Die kannst die Funktion mit VBA starten.
Naürlich kann man die Update-Parameter auch in einer Aktualisierungabfrage unterbringen und die dann starten. Allerdings ist mir nicht klar, was der Hintergrund deiner Frage ist.

kama
24.03.2004, 12:58
@Thomas Braun

Bin ich zu blöd oder ist deine Function sinnlos

Wo übergibst du die Daten?.

Hast du dir die Anreden mal angesehen

Schau dir Grafik an und sag mir wie das umsetzt?

daha1
24.03.2004, 13:15
Also die Anreden werden perfekt mittels der VBA Funktion bearbeitet!


Ich würde nun nur gerne wissen, ob und wie ich diese mittels einer SQL-Anweisung starten kann!

Wurde dann nämlich auch noch gerne eine Variable für die Tabelle "Test" setzen, damit ich die VBA Anweisung auch anderweitig gebrauchen kann!

Es geht mir jetzt primär nur darum wie man so was macht, aus Lerninteresse. Bin AC Anfänger....

Dani

Thomas Braun
24.03.2004, 13:16
@ kama: Weder noch - als ich mein Beispiel hochgeladen hatte, war deins noch nicht da. Daher konnte ich mir die Anreden nicht ansehen und wurden im Beispiel nicht berücksichtigt. Auch hatte ich geschrieben, dass ich den Thread nicht genau verfolgt hatte.
Die Funktion soll auch nur zeigen, wie man vorgehen könnte. Das mit der Umetzungstabelle ist natürlich etwas eleganter.
Wie daha1 vorgehen will, muss er selber entscheiden. Ist das eine einmalige Aktion halte ich den Weg über Aktualisierungsabfragen für schneller (die alte Anrede kann ja auch in einem neuen Feld gespeichert werden).
Falls die die Anreden auch in Zukunft unstrukturiert eingegeben werden, ist der Weg über die Umsetzungstabelle besser geeignet.

kama
24.03.2004, 13:24
Also die Anreden werden perfekt mittels der VBA Funktion bearbeitet!

also bleibt nur das

:stupid:

fragt sich nur wer :D

@ daha1

Thomas funktion unterscheidet nur zwischen anreden miT f oder h im Text!

Ich bin nach wie vor der Meinung das die Ursprungsdaten erhalten bleiben sollten. wenn nicht bleibt die Tabellenerstellungsabfrage mit der neuen Anrede.

SQL oder VBA würde ich in diesem Fall nicht bemühen.


Gruss

daha1
24.03.2004, 13:31
Ja.... nun versuche ich die Funktion umzugestalten das ich den Tabellennamen test mittels einer Variablen übergebn kann.

Wie muss ich diese im Code deklarieren?

Hier der Code;

Public Function fct_AnredeBereinigen()
Dim strUpdate As String

'Mann und Frau
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'xdyd' WHERE anrede LIKE 'h*f*' or anrede LIKE 'f*h*'")

'Herren
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Herr' WHERE anrede LIKE 'h*' AND anrede NOT LIKE '*f*'")

'Frauen
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Frau' WHERE anrede LIKE 'f*' AND anrede NOT LIKE '*h*'")

'Monsieur und Madame
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'xfyf' WHERE anrede LIKE 'mo*ma*' or anrede LIKE 'ma*mo*'")

'Monsieur
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Monsieur' WHERE anrede LIKE 'Mo*' AND anrede NOT LIKE '*ma*'")

'Madame
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Madame' WHERE anrede LIKE 'ma*' AND anrede NOT LIKE '*mo*'")

'Signore und Signora
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'xiyi' WHERE anrede LIKE 's*e*a' or anrede LIKE 's*a*e'")

'Signore
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Signore' WHERE anrede LIKE 'S*e*' AND anrede NOT LIKE '*a'")

'Signora
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Signora' WHERE anrede LIKE 's*a' AND anrede NOT LIKE '*e*'")

'Familie
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Familie' WHERE anrede LIKE 'Fam*' AND anrede NOT LIKE '*fra*'")

'Set xxyx WERTE
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Herr & Frau' WHERE anrede LIKE 'xdyd'")
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Monsieur & Madame' WHERE anrede LIKE 'xfyf'")
DBEngine(0)(0).Execute ("UPDATE test SET anrede = 'Signore & Signora' WHERE anrede LIKE 'xiyi'")
End Function



Danke für Input!

Gruess

Dani

Thomas Braun
24.03.2004, 13:44
Public Function fct_AnredeBereinigen()
Dim strUpdate As String
Dim strTabelle As String

strTabelle = "test"

'Mann und Frau
strUpdate = "UPDATE " & strTabelle & " SET anrede = 'xdyd' WHERE anrede LIKE 'h*f*' or anrede LIKE 'f*h*'"
DBEngine(0)(0).Execute (strUpdate)
' usw ...

Noch eleganter wird es, wenn du die Tabelle von Kama in einen Recordset lädst, den in einer Schleife durchläufst und den Update-String dynamisch erstellst.

kama
24.03.2004, 13:57
hallo

hier mit VBA
Function N_Anrede(strAnrede As Variant) As String

Select Case strAnrede

Case "Frau", "Madame", "Signora"
N_Anrede = "Frau"

Case "Herr u. Frau", "Herrn", "Monieur", "Signor"
N_Anrede = "Herr"
End Select

End Function

und aufrufen derselben in SQL um die Tabelle zu akuallisieren

UPDATE Test SET Test.anrede = N_Anrede([anrede]);

jmc
24.03.2004, 15:22
Hallo Leute

was doch so alles geht, wenn man mal ein paar Stunden weg vom Forum ist ...

Also um meinen Senf auch noch dazu zu geben, anbei eine mdb, wie ich das sehe. Also gemäss meinem ersten Vorschlag:

- tblAnredeUmsetzung: Resulat aus der Abfrage qryAnredeAktualisieren. Wenn du neue Daten hast, dann lässt du einfach diese laufen und es werden noch nicht vorhandene Anrede-Formen angefügt ..
Dann kannst du kontrollieren, ob es neue gegeben hat und die gewünschte Anrede in die Spalte Anrede_Neu einfügen.

- test: deine Originaltabelle, aber verändert durch qryAnredeUpdate

- Test_Original: eine Originaltabelle, unverändert

- qryKontrolle: beide Tabellen zusammengenommen und vergleich der Anreden

So - und das ganze ohne VBA und ohne dann in den Code eingreifen zu müssen

Vorgehen bei einem nächsten Lauf:
- qryAnredeAktualisieren laufen lassen
- schauen, ob in tblAnredeUmsetzung neue Einträge sind
- gewünschte Anrede einsetzen
- qryAnredeUpdate laufen lassen

FERTIG http://www.das-forum.ch/_various/pics/smilies/ch_smilies/ch_smilie11.gif

PS: wenn ich die zig Updates vom Dani anschaue, dann kriege ich die Krise ... http://www.das-forum.ch/_various/pics/smilies/crazy/crazy01.gif
.

daha1
24.03.2004, 15:41
Hi jmc


Echt genial dein Tool.... funktioniert ausgezeichnet!


Vielen Dank....

STAY TUNED....

GRUESS

Dani