Kruistabel Query

Status
Niet open voor verdere reacties.
J

jorrit126

Gebruiker
Lid geworden
9 feb 2007
Berichten
48
Beste Helpmij'ers,

Ik wil graag een kruistabel query maken, maar weet niet of mijn idee mogelijk is.

Ik heb een tabel ArtikelNr_TXT met de volgende velden:
- ComboId
- ArtikelNr
- TekstId

Op dit moment staat het op deze manier in de tabel:
ArtikelNr TekstId
26000 26000a
26000 26000b
26000 28000c
26123 26123A
26123 26123B
26123 28123ABC

Zo wil ik het eindresultaat graag hebben:
ArtikelNr TekstId1 TekstId2 TekstId3
26000 26000a 26000b 28000c
26123 26123A 26123B 28123ABC

Ik heb al een query die dit doet, alleen omdat ik van 1 veld (TekstId) meerdere velden wil maken geeft dit een probleem. Zodra ik de kolomkoppen verander, laat de query opeens geen records meer zien?

Query:
TRANSFORM ArtikelNr_TXT.TekstId
SELECT ArtikelNr_TXT.ArtikelNr
FROM ArtikelNr_TXT
GROUP BY ArtikelNr_TXT.ArtikelNr
PIVOT ArtikelNr_TXT.TekstId In ("Tekst1","tekst2","tekst3","tekst4","tekst5","tekst6");

Wat doe ik hier fout, of is wat ik wil niet eens mogelijk?
 
Laatst bewerkt:
Dat eindresultaat gaat zo natuurlijk niet lukken; een kolomnaam bevat alle waarden die in die kolom staan, en maakt daar kolomnamen van. Je krijgt dus kolomnamen als: 26000a en 26000b. In het Waardeveld gebruik je een rekenkundige functie, zoals Aantal of Som. Dus met een kruistabel kun je per tekstID zien hoevaak hij voorkomt per ArtikelID (als je Aantal gebruikt).
Jouw truc om zelf kolomnamen te definiëren werkt niet, omdat je in de kolom TekstID die waarden niet hebt. Wat je ook voor functie gebruikt, de velden in de kruistabel zullen altijd leeg blijven...
Dit zou nog wel een resultaat kunnen opleveren met iets wat enigszins lijkt op wat je per artikelregel wilt zien:
[SQL]TRANSFORM First(TekstId) AS EersteTekstId
SELECT ArtikelNr
FROM ArtikelNr_TXT
GROUP BY ArtikelNr
PIVOT TekstId;[/SQL]
 
Ha Michel bedankt voor je reactie. Hier was ik inderdaad al bang voor, maar ik dacht dat het misschien wel mogelijk was de kolomnamen te veranderen. De Sql code geeft inderdaad hetzelfde eindresultaat als wanneer ik de opgegeven kolomkop namen weghaal.
Het probleem is alleen hoe meer combinaties van ArtikelNr en TekstId er zijn, hoe meer kolommen ik krijg (en er zijn een stuk of 10.000 combinaties).

Je vraagt je misschien af waarom ik dit wil: Ik wil de data exporteren om te gebruiken in een ander programma, en deze kan niet overweg met mijn koppeltabel (ArtikelNr_TXT).

Ik denk dat ik de uitkomst die wordt opgeslagen in de koppeltabel, ipv in kolommen in rijen zal moeten opslaan.
 
Als je de data nodig hebt voor een export, en je verwacht uiteindelijk zo'n 10000 kolommen, dan heb je überhaupt al een probleem, want volgens mij houdt een query of tabel op bij 255 velden (of kolomkoppen). Ik snap ook de dataconstructie niet helemaal, behalve dan dat ik wel zie dat je de artikelnamen verlengt met letters. En daar kan ik geen enkele zinnige reden voor bedenken :D
Maar bij een export heb je niet altijd kolomkoppen nodig; een beetje programma kan verschillende bestanden importeren, een een kommagescheiden bestand zonder kolomnamen zou daar toch bij moeten zitten. En dan maakt het uiteraard niet uit hoe een kolom heet: bij de import wordt simpel het aantal velden geteld, en op de juiste positie geïmporteerd.
 
Sorry, met de 10000 kolommen doelde ik op de uitkomst van de query als ik hem zo laat.
De artikelnummers heb ik niet verlengd met een letter, maar dit zijn de bestandsnamen van de tekstbestanden (is inderdaad een beetje verwarrend, maar juist ook weer makkelijker om op te zoeken bij het artikelnummer)
Helaas kan ik met de software waar ik mijn database aan wil koppelen alleen importeren per kolom. Met deze query kan ik dus alleen het veld TekstId importeren, maar wat ik nodig heb zijn alle (maximaal 10) tekstbestanden die aan 1 Artikelnummer gekoppeld zijn. Het makkelijkst zou dus zijn als ik als kolomkop Tekst1 t/m 10 zou krijgen (ook al hoort dit eigenlijk niet in een genormaliseerde database).

De tekstbestanden worden dus gekoppeld aan een artikelnummer en worden geplaatst in een koppeltabel. Misschien is het handiger om de vba code die dit opslaat aan te passen, zodat ik het volgende effect krijg:

ArtikelNr| TekstId1| TekstId2| TekstId3| TekstId4 |TekstId5
26000| 26000a| 26000b| 26000c| 26000d| 26000e
801| 801ANW| 801ABC| 801-2| 801-3| 801BB

Dit is de code van de knop (waar jij me nog mee geholpen hebt;))
Code: 
Private Sub cmdOpslaan_Click()

Dim ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim i As Integer
Dim tmp

    Set ctl = Me.lstText
    If ctl.ItemsSelected.Count > 0 And Me.cboArtikel <> "" Then
        For Each varItm In ctl.ItemsSelected
            strSQL = ""
            strSQL = "SELECT DISTINCT TekstId, ArtikelNr FROM ArtikelNr_TXT WHERE " & vbCrLf
            strSQL = strSQL & "TekstId='" & ctl.ItemData(varItm) & "' AND ArtikelNr =" & Me.cboArtikel
            ''tmp = InputBox("", "", strSQL)
            With CurrentDb.OpenRecordset(strSQL)
                If .RecordCount = 0 Then
                    strSQL = "INSERT INTO ArtikelNr_TXT ( TekstId, ArtikelNr )" & vbCrLf
                    strSQL = strSQL & "VALUES('" & ctl.ItemData(varItm) & "', " & Me.cboArtikel & ")"
                    ''tmp = InputBox("", "", strSQL)
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings True
                    Me!SubArtTxt.Form.Requery
                Else
                    MsgBox "De combinatie '" & ctl.ItemData(varItm) & "' + Artikelnr " & Me.cboArtikel & " is al toegevoegd."
                End If
            End With
        Next varItm
    Else
        MsgBox "Eerst een artikel en een (of meer) tekstdocument(en) selecteren."
    End If
End Sub
 
Laatst bewerkt:
Dus als ik het goed begrijp, wil je een exporttabel hebben, en vanwege de importbeperkingen van het andere pakket ben je genoodzaakt om vaste veldnamen aan te leveren. Terwijl de gegevens die je genereert met de kruistabel query dynamische veldnamen genereert.
Er is wel een kleine wordaround te verzinnen: maak een tijdelijke tabel (op basis van een kruistabel o.i.d. met daarin alle veldnamen die je moet exporteren. Zoals je hierboven zelf al had neergepend dus. Deze tabel kun je vullen vanuit de kruistabelquery. Ik zou dat dan wel doen via een VBA procedure, omdat je de veldnamen nooit zeker kunt weten. De procedure moet dus de kruistabel openen, en via een Toevoegquery loop je door alle records uit de kruistabel en voeg je ze toe aan de tijdelijke tabel. Door de veldnamen te nummeren (rs.Fields(1),rs.Fields(2) etc.) maakt het niet uit hoe het veld heet; de procedure zet het juiste veld in het juiste veld.
De tijdelijke tabel kun je dan exporteren.
 
Dat begrijp je goed:) Alleen kan ik nu geen kruistabel maken omdat ik maar 1 veld heb voor de tekstbestanden. Is het dan niet handiger om de code van mijn knop aan te passen zodat hij de combinaties opslaat in de koppeltabel EN in een "exporttabel" waar die de tekstbestanden achter elkaar in kolom 1 t/m 10 invoert? Ik denk dat dit wel mogelijk is, weet alleen niet hoe ik dit voor mekaar moet zien te krijgen. Of is het gewoon te doen met nog een INSERT INTO statement? en daarna de velden te specificieeren?

Heb mn database ook even bijgevoegd
 

Bijlagen

Ik zie niet in waarom je geen kruistabel kunt gebruiken; ik zou zeggen: juist wel! Zolang je maar met een procedure de juiste (aantallen) velden uitleest en koppelt aan de juiste velden in je tijdelijke tabel, kan het bijna niet fout gaan. De methode die je voorstelt gaat ofwel niet werken, of is dermate lastig te maken dat ik er niet aan begin :) Ik kijk wel even of ik in je voorbeeldje iets kan maken...
 
Ik zit echt vast en kom er dankzij dit probleem niet uit:(
Volgens mij ben jij ook nog de enige helper op dit forum Michel!

Wat is nou de makkelijkste manier om dit probleem aan te pakken? (desnoods exporteer ik de tabel naar excel en zet ze daar om in een kruistabel met de kolomkoppen die ik wil)
 
Kijk eens of deze procedure werkt bij jou...

Code: 
Private Sub cmdExport_Click()
Dim dbs As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field
Dim strSQL As String, strSQL2 As String, sVeld As Variant
Dim iVelden As Integer, i As Integer
Dim Matrix() As Variant

    Set dbs = CurrentDb
    On Error Resume Next
    dbs.TableDefs.Delete "Export"
    
    strSQL = "SELECT TOP 1 ArtikelNr, Count(TekstId) AS Aantal FROM ArtikelNr_TXT GROUP BY ArtikelNr ORDER BY Count(TekstId) DESC"
    With dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        If .RecordCount = 1 Then iVelden = .Fields(1).Value
        .Close
    End With

    Set tbl = dbs.CreateTableDef("Export")
    With tbl
        .Fields.Append .CreateField("ArtikelNr", dbText)
        For i = 1 To iVelden
            .Fields.Append .CreateField("TekstID" & i, dbText)
        Next i
      dbs.TableDefs.Append tbl
    End With
    dbs.TableDefs.Refresh

    strSQL = "SELECT DISTINCT ArtikelNr FROM ArtikelNr_TXT ORDER BY ArtikelNr"
    With dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        If .RecordCount > 0 Then
            .MoveFirst
            Do While Not .EOF
                i = 1
                sVeld = .Fields(0).Value
                strSQL2 = "SELECT DISTINCT TekstId FROM ArtikelNr_TXT WHERE (ArtikelNr = " & sVeld & ") ORDER BY TekstId;"
                With dbs.OpenRecordset(strSQL2, dbOpenSnapshot)
                    ReDim Matrix(.RecordCount)
                    .MoveFirst
                    Do While Not .EOF
                        Matrix(i) = .Fields(0).Value
                        i = i + 1
                        .MoveNext
                    Loop
                    .Close
                End With
                With dbs.OpenRecordset("Export")
                    .AddNew
                    .Fields(0) = sVeld
                    For i = 1 To UBound(Matrix)
                        .Fields(i) = Matrix(i)
                    Next i
                    .Update
                End With
                .MoveNext
            Loop
        End If
    End With

End Sub

Hij maakt, als het goed is, een tabel Export aan op basis van het aantal mogelijke velden dat je nodig hebt. Dat wordt bepaald in de eerste recordset. Vervolgens wordt een recordset geopend met de Artikelnummers, waarna een nieuwe recordset wordt gemaakt die per artikelnummer de bijbehorende tekstlabels uitleest. Deze labels worden vervolgens in een matrix gezet, en als laatste wordt de matrix in een nieuw record in de tabel Export geplaatst. Het resultaat ziet er dan zo uit:

ArtikelNr TekstID1 TekstID2 TekstID3 TekstID4 TekstID5 TekstID6
26001 26001a 26001b 26001c 26001e 26002abc 26002bbb
26002 26002abc 26002bbb
 
DIT IS HELEMAAL SUPER!
Hij wilde eerst niet meewerken, was de DAO verwijzing vergeten.:rolleyes:
Zonder jou hulp was ik nog weken aan het prutsen geweest, dus heel erg bedankt voor je hulp Michel!

Ik heb de status verandert in "opgelost".
 
Michel nog 1 laatste vraag. Is het mogelijk om 2 functies onder 1 OnClick event te plaatsen?
Het betreft de volgende 2 functies:

Code: 
Private Sub cmdexport_Click()
Dim dbs As DAO.Database, tbl As DAO.TableDef, fld As DAO.Field
Dim strSQL As String, strSQL2 As String, sVeld As Variant
Dim iVelden As Integer, i As Integer
Dim Matrix() As Variant

    Set dbs = CurrentDb
    On Error Resume Next
    dbs.TableDefs.Delete "Export"
    
    strSQL = "SELECT TOP 1 ArtikelNr, Count(TekstId) AS Aantal FROM ArtikelNr_TXT GROUP BY ArtikelNr ORDER BY Count(TekstId) DESC"
    With dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        If .RecordCount = 1 Then iVelden = .Fields(1).Value
        .Close
    End With

    Set tbl = dbs.CreateTableDef("Export")
    With tbl
        .Fields.Append .CreateField("ArtikelNr", dbText)
        For i = 1 To iVelden
            .Fields.Append .CreateField("TekstID" & i, dbText)
        Next i
      dbs.TableDefs.Append tbl
    End With
    dbs.TableDefs.Refresh

    strSQL = "SELECT DISTINCT ArtikelNr FROM ArtikelNr_TXT ORDER BY ArtikelNr"
    With dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        If .RecordCount > 0 Then
            .MoveFirst
            Do While Not .EOF
                i = 1
                sVeld = .Fields(0).Value
                strSQL2 = "SELECT DISTINCT TekstId FROM ArtikelNr_TXT WHERE (ArtikelNr = " & sVeld & ") ORDER BY TekstId;"
                With dbs.OpenRecordset(strSQL2, dbOpenSnapshot)
                    ReDim Matrix(.RecordCount)
                    .MoveFirst
                    Do While Not .EOF
                        Matrix(i) = .Fields(0).Value
                        i = i + 1
                        .MoveNext
                    Loop
                    .Close
                End With
                With dbs.OpenRecordset("Export")
                    .AddNew
                    .Fields(0) = sVeld
                    For i = 1 To UBound(Matrix)
                        .Fields(i) = Matrix(i)
                    Next i
                    .Update
                End With
                .MoveNext
            Loop
        End If
    End With

End Sub

Code: 
Private Sub cmdOpslaan_Click()

Dim ctl As Control
Dim varItm As Variant
Dim strSQL As String
Dim i As Integer
Dim tmp

    Set ctl = Me.lstText
    If ctl.ItemsSelected.Count > 0 And Me.cboArtikel <> "" Then
        For Each varItm In ctl.ItemsSelected
            strSQL = ""
            strSQL = "SELECT DISTINCT TekstId, ArtikelNr FROM ArtikelNr_TXT WHERE " & vbCrLf
            strSQL = strSQL & "TekstId='" & ctl.ItemData(varItm) & "' AND ArtikelNr =" & Me.cboArtikel
            ''tmp = InputBox("", "", strSQL)
            With CurrentDb.OpenRecordset(strSQL)
                If .RecordCount = 0 Then
                    strSQL = "INSERT INTO ArtikelNr_TXT ( TekstId, ArtikelNr )" & vbCrLf
                    strSQL = strSQL & "VALUES('" & ctl.ItemData(varItm) & "', " & Me.cboArtikel & ")"
                    ''tmp = InputBox("", "", strSQL)
                    DoCmd.SetWarnings False
                    DoCmd.RunSQL strSQL
                    DoCmd.SetWarnings True
                    Me!SubArtTxt.Form.Requery
                Else
                    MsgBox "De combinatie '" & ctl.ItemData(varItm) & "' + Artikelnr " & Me.cboArtikel & " is al toegevoegd."
                End If
            End With
        Next varItm
    Else
        MsgBox "Eerst een artikel en een (of meer) tekstdocument(en) selecteren."
    End If
    
End Sub

Bvd!
 
Je kan elke functie of procedure (we hebben het hier over procedures) aanroepen vanuit een andere:

Code: 
Private Sub cmdexport_Click()
     ...
     ...
     Call cmdOpslaan_Click
End Sub
 
Ah ik kon dit nergens op internet vinden! Ik ben heel erg maar ik heb nog 1 vraag voor je Michel,

Ik wil graag op de export procedure ook een check of er dubbele records inzitten, ben daar vandaag mee bezig geweest maar krijg het niet voor elkaar. Moet ik de check na de eerste recordset maken of pas na het maken van de matrix?
 
Het makkelijkst is om van de velden die niet dubbel mogen een sleutel- of index combinatie te maken. Dat gezegd hebbende: in dat geval moet je met een vaste tabel werken die je eerst leeg maakt, en dan gaat vullen. Dus de routine die nu de tabel maakt zou moeten worden veranderd in een ALTER TABLE query die de bestaande tabel uitbreidt als dat nodig mocht zijn. Maar (veel beter) is natuurlijk om te voorkomen dat in je brontabel dubbele gegevens voorkomen; dan hoef je helemaal niks aan te passen.
 
Michel ik kom je weer lastig vallen:) Bijna alles werkt naar behoren in mijn database, op 1 ding na:

Ik kom er vandaag achter dat wanneer ik een textbestand ben vergeten toe te wijzen aan een Artikelnummer op mijn formulier, en dit later alsnog probeer te doen, dat de "export" procedure dit niet invoegt in hetzelfde record en de "opslaan" procedure in de koppeltabel juist weer wel?

Weet jij waar dit aan ligt? (Zoals je ziet op de afbeelding mist het textbestand 26000 DU 3 in de export tabel).
Bij voorbaat dank:)

284437oeps.jpg
 
Laatst bewerkt:
Ik zit met nog een probleem. Net even zitten testen met de export naar het labelprogramma, maar de export werkt nog steeds niet zoals ik het zou willen. Op dit moment geeft de export procedure het Artikelnummer met de bijbehorende bestandsnaam van de text die eraan gekoppeld zit. Echter pakt de software deze relatie niet dus krijg ik de inhoud van het .txt bestand niet te zien maar alleen de naam zoals die in de tabel staat (bv: "26000abc). Is er een makkelijke manier om i.p.v. de bestandsnaam in veld "tekstID", de inhoud van TekstID te weergeven?

Hierbij ook opnieuw de database
 

Bijlagen

Ik snap het probleem niet; wel zag ik in he formulier [ArtikelNr_TXT] een subformulier dat je hebt gekoppeld aan de tabel Export, waardoor je de export tabel niet kunt maken als je op <Opslaan> klikt. Dat komt omdat het subformulier de tabel gelocked heeft. Als je het subformulier verwijdert, kun je wel normaal opslaan. En je laatste vraag snap ik ook niet helemaal, want wat bedoel je met:
jorrit126 zei:
Is er een makkelijke manier om i.p.v. de bestandsnaam in veld "tekstID", de inhoud van TekstID te weergeven?
Klik om te vergroten...
Het ging er toch juist om dat je universele veldnamen hebt? Anders kun je net zo goed een kruistabel gebruiken per record; die maakt wel veldnamen aan op basis van de inhoud.
 
Bedankt voor je reactie Michel!
Zodra de tabel export gemaakt is, en ik maak een combinatie van TekstId's en ArtikelNr slaat hij deze wel op, ook met het subform. Wanneer ik de tabel export verwijder en ik klik op opslaan dan maakt hij hem inderdaad niet aan. (maar dit is makkelijk te verhelpen door ff een ' voor de code te zetten, de export tabel hoeft toch maar 1x aangemaakt te worden)

Het eerste probleem wat ik aangaf, is dat wanneer ik op mijn formulier [ArtikelNr_TXT] een TekstId vergeten ben te koppelen aan een ArtikelNr, en deze later nog wil koppelen aan hetzelfde ArtikelNr, dat de combinatie wel opgeslagen wordt in tabel [ArtikelNr_TXT] maar niet in tabel [Export].

Het tweede probleem is dat ik zodra ik mijn etiket ga ontwerpen, en vanuit de software verbinding maak met de access database, ik nu in plaats van de tekstbestanden alleen de naam van het tekstbestand krijg ( zoals deze ook opgeslagen wordt in tabel [ArtikelNr_TXT] en [export]), terwijl ik graag de inhoud van het TekstId wil zien.
Graag zou ik in het resultaat van de tabel export (en eventueel ArtikelNr_TXT) als volgt willen krijgen:
821564test.jpg


Dus de kolomkoppen mogen wel TekstId1 t/m 15 blijven, maar ipv het TeksId wil ik de TekstInhoud weergeven. Dit kan voor sommige TekstId's wel een aardig stuk tekst zijn dus dit moet een "memo" veld worden.
Hopelijk is het nu duidelijk;)
 
Begrijp ik het goed dat je een export tabel hebt gemaakt met de exportprocedure die de bestandsnamen genereert, maar dat je in de import daar niks aan hebt, omdat je de inhoud van die bestanden moet importeren? En waar haal je die inhoud dan weer vandaan?
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan