Distribueren van records naar andere tabellen

MrDummy · 30 mrt 2016

Hoi,

Wellicht dat iemand enig idee heeft om deze uitdaging op te lossen, ben al 70% op weg maar moet het laatste stukje nog:

In bijlage heb ik een voorbeeld database gezet en zit met een probleem voor het juist toevoegen van records in tabellen vanuit de basis tabel. de basis tabel wordt gevuld dmv het invoeren van een XML maar heb dit er even uitgelaten om het eenvoudig te houden.

Vanuit tblImEx wil ik de data distribueren naar 3 andere tabellen en dit gaat goed voor de eerste twee tabellen maar zit met de derde tabl. Het gaat goed in de eerste tabel (tblVoyageData). Hier wordt de V-record vanuit tblImEx toegevoegd. Daarna gaan de C-records vanuit tblImEx naar tblContainerData. de twee records zijn gelinked middels de VoyageDataID.
Nu moeten de U-records in de derde tabel (tblUNCompl) gezet worden en moeten gelinked zijn aan de twee C-records van tblContainerdata middels ContainerDataID. Je kunt zien hoe deze gelinked zijn in tblImEx in het veld ImEx1. Dit zijn dan respectievelijk de waarden 1 en 2. de relaties zijn ook aangeven in "Relaties"
Voorbeeld van hoe het moet worden. Vanuit tblImEx:
C-record waarbij ImEx0 = “C” en ImEx1 =1 is gerelateerd met U-record waarbij ImEx0=”U” en ImEx1=1; voor deze is 1 C-record gerelateerd aan 3 U-records.
C-record waarbij ImEx0 = “C” en ImEx1 =2 is gerelateerd met U record waarbij ImEx0=”U” en ImEx1=2; voor deze is 1 C record gerelateerd aan 4 U-records.
Met de huidige code worden alle U-records gelinked aan de laatste C-record van tblContainerData terwijl iedere U record aan zijn eigen C-record gelinked moet worden.

Misschien dat iemand een idee heeft om deze laatste stap te maken.

OctaFish · 31 mrt 2016

Ik snap niet wat je aan het doen bent. Je gebruikt een functie DMax om het hoogste containernummer op te halen, dus dan moet je niet gek opkijken dat alle records in tabel 3 dat container nummer krijgen. Om met een onlangs overleden filosoof te spreken: "Da's logisch". Is je import tabel realistisch? In de zin dat je altijd één reisnummer hebt, en daar meerdere containers in hebt met verschillende detailrecords?

OctaFish · 31 mrt 2016

Wellicht zit ik er naast met mijn oplossing, maar ik vermoed uiteindelijk dat je zoiets bedoelt:

Code:

Dim strSQL1 As String, strSQL2 As String
Dim lngID As Long
Dim rs As DAO.Recordset

    strSQL1 = "INSERT INTO [tblVoyageData] ( VoyageLetter, ShipsDataID, PortOfDeparture, DateOfDeparture, PortOfArrival, VoyageRef, AgentPortOfDep )" _
        & "SELECT ImEx0, ImEx1, ImEx3, ImEx4, ImEx5, ImEx6, ImEx7 FROM [tblImEx] WHERE ImEx0 = ""V"""
    CurrentDb.Execute strSQL1, dbFailOnError 'This going OK
    
    lngID = DMax("VoyageDataID", "tblVoyageData")
    strSQL1 = "INSERT INTO [tblContainerData] ( VoyageDataID, BookingRef, ContainerNumber, VehicleNumber, ContainerSizeID, StowagePosition, ContainerLetter ) " _
        & "SELECT " & lngID & ", ImEx3, ImEx4, ImEx5, ImEx6, ImEx7, ImEx0 FROM [tblImEx] WHERE ImEx0 = ""C"""
    CurrentDb.Execute strSQL1, dbFailOnError 'This going OK
    
    strSQL1 = "SELECT [ContainerDataID] FROM tblContainerData Where [VoyageDataID] = " & lngID
    Set rs = CurrentDb.OpenRecordset(strSQL1)
    With rs
        Do While Not .EOF
            lngID = !ContainerDataID
            strSQL2 = "INSERT INTO [tblUNComplete] (ContainerDataID, VoyageDataID, UNComplLetter, UNVar, TechnicalName, SuppPSNID, AddSegGrp, LTDQTY ) " _
                & "SELECT " & lngID & ", ImEx2, ImEx0, ImEx3, ImEx4, ImEx5, ImEx6, ImEx7 FROM [tblImEx] WHERE ImEx0 = ""U"""
            CurrentDb.Execute strSQL2, dbFailOnError
            .MoveNext
        Loop
        .Close
    End With

MrDummy · 31 mrt 2016

Beste OctaFish,

Dank je voor de terugkoppeling. Ga er vanavond mee aan de slag maar ziet er, heel, goed uit.

MrDummy · 31 mrt 2016

Hoi,
De opmerking of de tabel realistisch is zette me even aan het denken. Ik zie op dit moment geen andere optie. Maar misschien dat er een andere optie is. Het volgende is er aan de hand: de DB wordt gebruikt op kantoor en de mobiele eenheid waartussen geen continue direkte verbinding middels internet of iets dergelijks of een server waar je op inlogt en dan gebruik maakt van dezelfde database. Ik zit dus te denken aan 1 bestand (in dit geval XML) dat via de e-mail verstuurd wordt. Dit bestand wordt opgebouwd uit dezelfde drie tabellen en wordt gemaakt aan een kant en aan de andere kant wordt het ingelezen in de DB. Eventueel kan het bij de mobiele eenheid worden aangepast. Dit bestand bestaat iedere keer uit 1 Voyage met meerdere containers en elke container heeft weer verschillende inhouden. Misschien is er een makkelijkere manier van werken en sta open voor suggesties.

MrDummy · 31 mrt 2016

Hoi,

Dank je voor de code. Dit is precies het probleem waar ik tegenaan loop. Alle U-records worden gelinkt aan beide containers of, zoals ik via diverse wegen heb gedaan aan de laatste Container wat 1 van de opties was om verder te gaan. Dus elke container krijgt nu 7 U-records ipv resp 3 en 4.

Maar ik ben al heel blij met dit want de code is al een stuk eenvoudiger geworden. Ik zat denken aan een soort van dubbele loop waarbij voor elke C-record de bijbehorende U-records gevonden worden en gelinkt worden. Maar kom hier niet uit. Enige suggesties?

OctaFish · 1 apr 2016

Met 'realistisch' bedoelde ik: komt je tabel overeen met een gebruikelijke situatie. Dat lijkt uit je andere berichten wel ongeveer te kloppen. In dat geval zou de aangepaste code redelijk bruikbaar moeten zijn. Je kunt nog overwegen om de nummers zelf te genereren i.p.v. te vertrouwen op Autonummering. Die eigen nummers zijn namelijk makkelijker in te passen in de procedure. Maar in essentie kun je op basis van Recordsets oneindig doorlussen.

MrDummy · 2 apr 2016

Ik heb de loop aangepast met een extra loop. Hierin probeer ik middels de BookingRef de juiste ImEx1 te vinden in tblImEx en deze te gebruiken bij de "Where" statement van de strSQL2 zodat de juiste records ingevoegd worden. Echter ik loop vast op de lngID2. maar ook als ik bijvoorbeeld voor ImEx1 = 1 invul (gewoon even harde waarde) komt het er niet uit.

Code:

Dim strSQL1 As String, strSQL2 As String, strSQL3 As String


Dim lngID As String
Dim lngID1 As String
Dim lngID2 As String

Dim rs As DAO.Recordset
Dim rst As DAO.Recordset

    strSQL1 = "INSERT INTO [tblVoyageData] ( VoyageLetter, ShipsDataID, PortOfDeparture, DateOfDeparture, PortOfArrival, VoyageRef, AgentPortOfDep )" _
        & "SELECT ImEx0, ImEx1, ImEx3, ImEx4, ImEx5, ImEx6, ImEx7 FROM [tblImEx] WHERE ImEx0 = ""V"""
    CurrentDb.Execute strSQL1, dbFailOnError 
    
    lngID = DMax("VoyageDataID", "tblVoyageData")
    
    strSQL1 = "INSERT INTO [tblContainerData] ( VoyageDataID, BookingRef, ContainerNumber, VehicleNumber, ContainerSizeID, StowagePosition, ContainerLetter ) " _
        & "SELECT " & lngID & ", ImEx3, ImEx4, ImEx5, ImEx6, ImEx7, ImEx0 FROM [tblImEx] WHERE ImEx0 = ""C"""
    CurrentDb.Execute strSQL1, dbFailOnError 
      
    strSQL1 = "SELECT [ContainerDataID] FROM tblContainerData Where [VoyageDataID] = " & lngID
    strSQL3 = "SELECT [BookingRef] FROM tblContainerData Where [VoyageDataID] = " & lngID
       
    Set rs = CurrentDb.OpenRecordset(strSQL1)
    Set rst = CurrentDb.OpenRecordset(strSQL3)
        
    With rst
    
        Do While Not .EOF
            
          lngID1 = !BookingRef
                         
          lngID2 = "Select [ImEx1] from tblImEx where [ImEx3]= " & "'" & lngID1 & "'"
                                
                With rs
                       Do While Not .EOF
  
                         lngID = !ContainerDataID
            

                         strSQL2 = "INSERT INTO [tblUNComplete] (ContainerDataID, VoyageDataID, UNComplLetter, UNVar, TechnicalName, SuppPSNID, AddSegGrp, LTDQTY ) " _
                         & "SELECT " & lngID & ", ImEx2, ImEx0, ImEx3, ImEx4, ImEx5, ImEx6, ImEx7 FROM [tblImEx] WHERE ImEx0 = ""U"" and ImEx1 = lngID2"
                         CurrentDb.Execute strSQL2, dbFailOnError
                         .MoveNext
                         
                    Loop
                 .Close
                End With
                
            .MoveNext
            Loop
        .Close
      End With

MrDummy · 3 apr 2016

Hoi,

Ben er van overtuigd dat onderstaande Code potentie heeft om te gaan werken. Alleen loop ik vast bij STRSQL2. Hier komt de foutmelding "Gegevens Type komen niet overeen". Probleem zit, denk ik, in de lngID2. Ik probeer in strSQL2 records te selecteren op basis van twee voorwaarden ImEx0 = "U" en ImEx1 = lngID2

Code:

Dim strSQL1, strSQL2, strSQL3, strSQL4 As String
Dim lngID, lngID1, lngID2 As String
Dim rs, rst, rst1 As DAO.Recordset


    strSQL1 = "INSERT INTO [tblVoyageData] ( VoyageLetter, ShipsDataID, PortOfDeparture, DateOfDeparture, PortOfArrival, VoyageRef, AgentPortOfDep )" _
        & "SELECT ImEx0, ImEx1, ImEx3, ImEx4, ImEx5, ImEx6, ImEx7 FROM [tblImEx] WHERE ImEx0 = ""V"""
    CurrentDb.Execute strSQL1, dbFailOnError 'This going OK
    
    lngID = DMax("VoyageDataID", "tblVoyageData")
    
    strSQL1 = "INSERT INTO [tblContainerData] ( VoyageDataID, BookingRef, ContainerNumber, VehicleNumber, ContainerSizeID, StowagePosition, ContainerLetter ) " _
        & "SELECT " & lngID & ", ImEx3, ImEx4, ImEx5, ImEx6, ImEx7, ImEx0 FROM [tblImEx] WHERE ImEx0 = ""C"""
    CurrentDb.Execute strSQL1, dbFailOnError 'This going OK
      
    strSQL1 = "SELECT [ContainerDataID] FROM tblContainerData Where [VoyageDataID] = " & lngID
    strSQL3 = "SELECT [BookingRef] FROM tblContainerData Where [VoyageDataID] = " & lngID

       
    Set rs = CurrentDb.OpenRecordset(strSQL1)
    Set rst = CurrentDb.OpenRecordset(strSQL3)

        
    With rst
    
        Do While Not .EOF
            
          lngID1 = !BookingRef
            
                    strSQL4 = "Select [ImEx1] from tblImEx where [ImEx3]= " & "'" & lngID1 & "'"
                        Set rst1 = CurrentDb.OpenRecordset(strSQL4)
         With rst1
         
               Do While Not .EOF
               
                     lngID2 = !ImEx1
                     
                 With rs

                       Do While Not .EOF
  
                         lngID = !ContainerDataID
                         
                         strSQL2 = "INSERT INTO [tblUNComplete] (ContainerDataID, VoyageDataID, UNComplLetter, UNVar, TechnicalName, SuppPSNID, AddSegGrp, LTDQTY ) " _
                         & "SELECT " & lngID & ", ImEx2, ImEx0, ImEx3, ImEx4, ImEx5, ImEx6, ImEx7 FROM [tblImEx] WHERE ImEx0 = ""U""" 'and ImEx1 =  lngID2 " ter verduidelijking waar ik de tweede voorwaarde wil zetten !
                         CurrentDb.Execute strSQL2, dbFailOnError
                         .MoveNext
                         
                    Loop
                 .Close
                End With
                
            .MoveNext
            Loop
        .Close
      End With
      
    .MoveNext
   Loop
 .Close
End With

OctaFish · 3 apr 2016

Als de koers saai is, werp ik er wel even een blik op

.

OctaFish · 3 apr 2016

Even los van je probleem (hoewel het wel degelijk de oorzaak kan zijn): de manier waarop je declareert deugt niet. Je kunt niet volstaan met alleen aan het eind aangeven welk type variabele gebruikt; elke declaratie moet correct zijn. Dat betekent ook dat je het juiste type moet gebruiken, dus niet 'lng' (wat je gebruikt voor getallen) als string definiëren en vervolgens als getal gebruiken. Dit:

Code:

Dim strSQL1, strSQL2, strSQL3, strSQL4 As String
Dim lngID, lngID1, lngID2 As String
Dim rs, rst, rst1 As DAO.Recordset

is dus niet correct. Dit wel:

Code:

Dim strSQL1 As String, strSQL2 As String, strSQL3 As String, strSQL4 As String
Dim lngID As Long, lngID1 As Long, lngID2 As Long
Dim rsDAO As DAO.Recordset, rstDAO As DAO.Recordset, rst1 As DAO.Recordset

MrDummy · 3 apr 2016

Dank je voor aanvulling. De wijze van declareren heb ik nu gewijzigd. De haven is in zicht en lijkt nu te werken. lngID2 wordt in de eerste loop correct bepaald op 1 daarna wordt deze gebruikt in strSQL2 (lngID2 is 1) en dat gaat goed maar dan lijkt hij in omgekeerde volgorde te werken. Voor de duidelijkheid heb ik msgBox toegevoegd om te laten zien wanneer de lngID2 gebruikt of berekend wordt.

Volgens mij zouden de msgbox als volgt moeten opkomen: 1e keer, 2e keer dan nieuwe loop en 1e keer, 2e keer maar hij gaat dus alsvolgt: 1e keer, 2e keer dan 2e keer, 1e keer. Beetje vaag misschien maar de db weer even bijgevoegd voor de duidelijkheid.

Maar zoals ik al aangaf de haven is in zicht........

MrDummy · 3 apr 2016

VBA loopt

Ok, OK ik ben eruit en hij doet wat hij moet doen. Zie de bijlage. Wellicht dat er hier en daar nog wat bij geschaafd mag worden. (zodat ik het over een paar jaar ook nog snap als ik het terug lees)

Dank voor de input OctaFish en de koers.

OctaFish · 3 apr 2016

Ik snap je code niet helemaal want je maakt een lus op een recordset met één record. Dat heeft m.i. geen enkele zin. Althans: als ik je voorbeeldje bekijk dan filter je in stap 2 op [BookingRef]. Dat is toch een unieke code? Vervolgens maak je weer een recordset die een getal filtert als tekst (and ImEx1 = " & "'" & lngID2 & "'") en dat moet je ook echt niet doen. Ik raak door al die loops in ieder geval de draad behoorlijk kwijt

.

MrDummy · 4 apr 2016

Dank je. Het hield mij ook al bezig dat het eenvoudiger kan en inderdaad de BookingRef is uniek dus daar moet ik inderdaad nog even naar kijken. Heb het even laten liggen om er later nog eens naar te kijken.

Kijk ik er weer even met een frisse blik naar.

Het was gisteren uiteindelijk toch zondag. :thumb:

desalnietemin ben ik blij dat het kan en loopt het. Nu alleen nog vereenvoudigen.

Ik sluit deze vraag. Many thanks

Distribueren van records naar andere tabellen

MrDummy

Gebruiker

Bijlagen

OctaFish

Verenigingslid

OctaFish

Verenigingslid

MrDummy

Gebruiker

MrDummy

Gebruiker

MrDummy

Gebruiker

Bijlagen

OctaFish

Verenigingslid

MrDummy

Gebruiker

MrDummy

Gebruiker

OctaFish

Verenigingslid

OctaFish

Verenigingslid

MrDummy

Gebruiker

Bijlagen

MrDummy

Gebruiker

Bijlagen

OctaFish

Verenigingslid

MrDummy

Gebruiker

Nieuwste berichten

Wij waarderen jouw privacy