lordxearo
May 22, 2014, updated at May 23, 2014 (UTC)
view2709
view5
0
Goto Top

Zuordnen von indirekt verknüpften Daten

GermansolvedQuestionVB for ApplicationsDevelopment
Hallo zusammen,

ich könnte etwas Hilfe bei der Erstellung eines Skripts ODER einer SQL-Abfrage gebrauchen ( Falls es bereits mit einer SQL-Abfrage lösbar ist)

Aus Excel wird eine MS SQL Datenbank abgefragt. Hier nur die wesentlichen Daten:

| BELEG-ID | Referenz-ID | Belegart | Belegnummer
12 | 23 | Rechnung | XxXxXxXx
33 | 14 | Rechnung | XxXxxXxX
23 | 3 | Lieferschein | XxxXxXxX
3 | -- | Auftragsbestägiung | XxXxXxXx
14 | 5 | Lieferschein | XxXxXxX
5 | -- | Auftragsbestätigung | XxXXxxX

Nun soll es möglich sein von einer bestimmten Rechnung (anhand der Belegnummer), auf die da zugehörige Auftragsbestätigung (Belegnummer) zugelangen. Ich konnte jetzt anhand der Datenbank nur erkennen, das die Belege nur auf diese weise Verknüpft sind (Rechnung <--> Lieferschein <--> Auftragsbestätigung). Es gibt auch Belege mit mehreren Zwischenschritten (Stornorechnung,Angebot,Gutschrift...)

Bisher habe ich ein "dummes" Skript, das immer der Reihe nach die IDs zwishenspeichert und erneut einen Filter über die ganzen Daten setzt. Die Zuordnung dauert dann für einen Beleg bis zu 10 Sekunden. Bei über 10.000 Datensätzen dauert das einfach zu lange.

Ich hoffe das ist einigermaßen verständlich, sonst versuche ich es nochmal klar zu machen.

Edit:
Ich denke ich bekomme das ganze mit mehreren Hilfsspalten über SVERWEIS gelöst, werd ich mich morgen mal dran setzen.


Viele Grüße

Xearo
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 238918

Url: https://administrator.de/contentid/238918

Printed on: April 19, 2024 at 17:04 o'clock

5 Comments
Latest comment
Goto Top
Member: colinardo
colinardo May 22, 2014 updated at 15:23:56 (UTC)
Goto Top
Hallo Xaero,
also ich habe das ganze hier mal mit einer QueryTable und einer Verbindung mit einem SQL-Server getestet.
Ich weiß nicht ob ich dich korrekt verstanden habe, aber eigentlich sollte hier doch folgende SQL-Query reichen:
SELECT * FROM Rechnungen WHERE Belegnummer = 12345 AND Belegart = 'Auftragsbestätigung'
wenn man dann eine QueryTable im Sheet hat lässt sich die SQL-Query so auf die DB absetzen:
Dim sheet As Worksheet
Set sheet = Worksheets(1)
strBelegnummer = "12345"  
sheet.ListObjects("NamedesListObjects").QueryTable.CommandText = "Select * From TestDB.dbo.Rechnungen where Belegnummer = " & strBelegnummer & " and Belegart = 'Auftragsbestätigung'"  
sheet.ListObjects("NamedesListObjects").QueryTable.Refresh
Dann enthält die Tabelle nur noch die gefundene AB zur Belegnummer.

Die Belegnummer lässt sich ja in eine Variable setzen die man dann z.B. in eine Zelle oder eine Textbox eingeben kann und dann die Suche via Button gestartet wird.

Alternativ lässt sich natürlich auch über ein Makro die AB suchen, das wäre auch kein Problem:
Excel suchen per Makro

Eine Hilfsspalte würde natürlich auch reichen.

Grüße Uwe
Member: LordXearo
LordXearo May 22, 2014 at 17:52:05 (UTC)
Goto Top
Hallo Uwe,

ich glaube du hast mich nur so halb verstanden. Gegeben ist die Belegnummer einer Rechnung.

Wie die passende Belegnummer zu der Auftragsbestätigung dieser Rechnung lautet, muss rausgefunden werden. Die Rechnung steht zur Auftragsbestätigung in keinem direkten zusammenhang. Aber von dieser Rechnung, gibt es eine "verlinkung" (RefID) zum Lieferschein, und dieser Lieferschein hat wiederum eine RefID die auf die Auftragsbestätigung zeigt.

Hätte man nun auch noch eine "Stornorechnung", dann geht diese "verfolgung" noch eine Ebene tiefer. (Stornorechnung-->Rechnung-->Lieferschein-->Auftragsbestätigung-->Angebot)

In der Tabelle, wo die Belege abgespeichert sind (Rechnung,Lieferschein etc.), gibt es keine direkte Beziehung zu den einzelnen Belege die zu einem Auftrag gehören.

Gruß
Xearo
Member: colinardo
Solution colinardo May 22, 2014, updated at May 23, 2014 at 06:23:40 (UTC)
Goto Top
Ah OK, nun verstanden face-smile
Dafür habe ich dir mal ein Makro geschrieben, du kannst das Demo-Sheet hier herunterladen

Grüße Uwe
Member: LordXearo
LordXearo May 23, 2014 at 06:23:37 (UTC)
Goto Top
WoW, Klasse =)

Genau so sollte es sein. Jetzt muss ich das nur noch so umbauen, dass das Makro eine ganze Spalte von Belegnummern abarbeitet und das Ergebnis in eine Spalte einträgt.

Hast mir sehr gut geholfen, Danke.

Gruß

Xearo
Member: LordXearo
LordXearo May 23, 2014 updated at 08:22:28 (UTC)
Goto Top
Hier noch meine verschlimmbesserte Version.

Sub FindAB()
    Dim ws As Worksheet, loDaten As ListObject, rngBelege As Range, rngX As Range, rngBelegID As Range, rngID As Range, strRechBeleg As String
    Dim i As Integer
    ' Worksheet setzen  
    Set ws = Worksheets("AB-Nr. Abfrage")  
    'ListObject referenzieren  
    Set loDaten = ws.ListObjects("Tabelle")  
    For i = 2 To 30000
NaechsteZeile:
        'Zelle in der der Suchwert steht  
        strRechBeleg = ws.Range("L" & i).Value  
        If strRechBeleg = "" Then  
            Exit Sub
        End If
        'Spalte in der die Belegnummern stehen  
        Set rngBelege = loDaten.DataBodyRange.Cells(, 10).EntireColumn
        'Spalte in der die BELEG-ID steht  
        Set rngBelegID = loDaten.DataBodyRange.Cells(, 1).EntireColumn
        'Finde Belegnummer der Rechnung  
        Set rngX = rngBelege.Find(what:=strRechBeleg, LookAt:=xlWhole, LookIn:=xlValues)
        'Wenn Rechnung gefunden wurde ...  
        If Not rngX Is Nothing Then
            Set rngX = rngX.Offset(0, -9)
            'Iteriere so lange bis eine Auftragsbestätigung gefunden wurde  
            Do
                refID = rngX.Offset(0, 1).Value
                Set rngID = rngBelegID.Find(what:=refID, LookAt:=xlWhole, LookIn:=xlValues)
                If rngID Is Nothing Then
                    'MsgBox "Auftragsbestätigung konnte nicht gefunden werden", vbExclamation  
                    ws.Range("M" & i) = "Auftragsbestätigung konnte nicht gefunden werden"  
                    i = i + 1
                    GoTo NaechsteZeile
                    'Exit Sub  
                End If
                Set rngX = rngID
            Loop Until rngX.Offset(0, 3).Value = "Auftragsbestätigung"  
            
            If Not rngX Is Nothing Then
                'MsgBox "Die Belegnummer der Auftragsbestätigung lautet: " & rngX.Offset(0, 9).Value  
                ws.Range("M" & i) = rngX.Offset(0, 9).Value  
            End If
        
        Else    'Rechnung wurde nicht gefunden  
            'MsgBox "Rechnungsbelegnummer wurde nicht gefunden!", vbExclamation  
            ws.Range("M" & i) = "Rechnungsbelegnummer wurde nicht gefunden!"  
        End If
    Next i
End Sub

In Spalte L stehen die zu suchenden Rechnungsbelegnummern.
In Spalte M werden die gefundenen AB Nummern eingetragen
GermansolvedQuestionVB for ApplicationsDevelopment
Hotly discussed
gleixnerdCheck of ZFW Firewallgleixnerd - 3 CommentsAlexWishaHow to set up and configure a Linux GRE tunnelAlexWisha - 3 CommentsjstrickerWIREGUARD VPN ON UDM PRO BEHIND FRITZBOX - HANDSHAKE DID NOT COMPLETEjstricker - 3 CommentsDaniEnd of Support dates for Office 2016, 2019 Apps und Productivity ServersDani - 1 Comment