[Access] Vraag over query

[Verwijderd]

Ik vraag jullie aandacht voor het volgende.

Ik heb een tabel bestaande uit twee velden. Het eerste veld is InstellingID en het tweede veld is softwarepakketID. 1 Instelling kan meerdere softwarepaketten hebben. Nu wil ik een query maken waarin op een regel een instelling komt te staan met daarachter 1 veld waarin de verschillende softwarepaketten samengevoegd worden tot 1 veld. Zodat dus in de query elke instelling 1 maal voorkomt met daarachter een veld waarin alle softwarepaketten staan die ze gebruiken. Dus de softwarepaketten dienen samengevoegd te worden. Ter verduidelijking. 1 instelling heeft 3 softwarepakket. Normaliter zijn dit 3 records dan. Mijn vraag is of je dus in een query ervoor kunt zorgen dat er dus in 1 record een instelling staat met daarachter dan in een veld de id's van de 3 softwarepaketten.

Is dit mogelijk en hoe moet ik deze query opbouwen. Zelf kom ik er niet uit.

Jullie hulp graag! Alvast bedankt!

[whoami]

Dat kan je niet zomaar doen.
Je zou kunnen een User Defined Function maken, die alle software paketten ophaalt, en deze dan in een comma-separated string gooit, maar dat is niet mogelijk in Access.
Je zult het dus client-side moeten doen denk ik. (In je applicatie dus vanwaar je die query aanroept).

[Verwijderd]

Dit kan wel toch? via een join....of ik snap je vraag niet helemaal...

[Boss]

Ik denk dat hij het volgende wil:

code:

+-----+-----+
|veld1|veld2|
+-----+-----+
|    1|    1|
|    1|    2|
|    1|    3|
|    2|    2|
|    2|    4|
+-----+-----+

en dan als resultaat:

code:

+-----+-----+
|veld1|veld2|
+-----+-----+
|    1|1,2,3|
|    2|  2,4|
+-----+-----+

En dat kan niet in Access. Moet je ff zelf een funcite maken die aan de hand van veld1 alle waarden van veld2 achter elkaar plakt. Ik hoop dat je niet heel veel records hebt, want dat kan nogal traag gaan worden.

[Verwijderd]

Boss schreef op 25 maart 2004 @ 10:21:

En dat kan niet in Access. Moet je ff zelf een funcite maken die aan de hand van veld1 alle waarden van veld2 achter elkaar plakt. Ik hoop dat je niet heel veel records hebt, want dat kan nogal traag gaan worden.

Dit kan je wel regelen met SQL...moet alleen even uitzoeken hoe, want ik doe precies hetzelfde, voeg voornaam en achternaam ook samen in 1 veld...

[whoami]

Je kan verschillende velden idd zomaar aan elkaar plakken in 1 veld dmv string concatenation, maar je kan niet zomaar verschillende records in 1 veld zetten.
Daar heb je een UDF voor nodig, en met Access is dat niet mogelijk.

[Lustucru]

whoami schreef op 25 maart 2004 @ 10:47:
Je kan verschillende velden idd zomaar aan elkaar plakken in 1 veld dmv string concatenation, maar je kan niet zomaar verschillende records in 1 veld zetten.
Daar heb je een UDF voor nodig, en met Access is dat niet mogelijk.

Niet?
Even het volgende uitgeprobeerd:

code:

Public Function TxtCon(id As Long, vValue) As String
    Static buf as string, oId as long
    If oId <> id Then
        buf = vValue
        oId = id
    Else
        buf = buf & ", " & vValue
    End If
    TxtCon = buf
End Function

en dat kun je gewoon in een query aanroepen met

alleIds: Txtcon(tabel1.id;tabel2.id)

Als dan de queryresultaten groepeert en voor de aggregatie voor alleIDs op last zet heb je precies wat ts wil hebben.

Je kunt natuurlijk ook de functie al het werk laten doen en een rs laten openen maar dat is denk ik wat langzamer.

[whoami]

Niesje schreef op 25 maart 2004 @ 11:32:
[...]


Niet?
Even het volgende uitgeprobeerd:

Dat is geen SQL functie. Ik bedoelde dat je in Access geen UDF kunt maken die je rechtstreeks in je SQL query kunt oproepen, zoals je in MSDE/SQL Server/Oracle, etc... wel kunt doen.

Maar kan jij nu die functie ook gebruiken in een SQL statement dat je buiten je access omgeving aanroept?
Stel dat ik bv een Delphi applicatie heb, die een connectie maakt met die Access DB.
Kan ik dan dit doen in m'n delphi app:

code:

myQuery.SQL.Text := 'SELECT jouwfunctie(tabel1.id, tabel2.id) FROM tabel1, .......';
myQuery.Close();
myQuery.Open();

[Lustucru]

ach so, nu snap ik wat je bedoelt en nee, dat zal niet werken. Ik had alleen [access] gelezen en dacht dus helemaal en alleen binnen access.

[Boss]

Volgens mij zou dat weer wel kunnen werken als je via een DAO koppeling direct in Access de sql van die querie aanpast en dan in Delphi weer alleen verwijzen naar die query ipv de SQL op Access loslaten...

Weet niet zeker, maar ik denk dat dat zou kunnen

[Verwijderd]

Iedereen hartstikke bedankt voor het meedenken! Boss dank je voor het in een figuur zetten van hetgeen ik inderdaad wilde en Niesje hartstikke bedankt hetgeen jij neergezet had was precies hetgeen ik nodig heb en het werkt als een trein.

[Verwijderd]

Het werkt helaas toch niet helemaal goed. Op het moment dat de softwarepakket ID's zich niet direct onder elkaar bevinden maar dat er bijvoorbeeld nog een andere instelling met een ander softwarepakket tussen staat dan worden alleen de laatste meegenomen. Hieronder hetgeen wat ik bedoel

Instelling_ID Software_ID
1                    2
2                    4
1                    3

In dit geval zou alleen software_ID 3 zichtbaar worden voor instelling 1 met de functie zoals deze door Niesje aangedragen is. Weet iemand hier toevallig ook nog een oplossing voor? Alvast bedankt voor het meedenken!

[ Voor 20% gewijzigd door Verwijderd op 26-03-2004 19:57 ]

[Lustucru]

Yep, that's the catch . of te wel de prijs die je betaald voor een snelle functie en het feit dat db's nu eenmaal per definitie ongesorteerd zijn. Oplossing: maak eerst een query waarin je sorteert op instellingId,softwareId, en haal daar de aggregatiequery overheen.

Andere oplossing: laat de functie al het werk doen:

code:

Public Function TxtConII(id As Long) As String
    Dim rs As Recordset, sql As String, buf As String
    sql = "Select [veldnaamprogrammaid] from [programmatabel] where {veldnaaminstelling] =" & id
    Set rs = CurrentDb.OpenRecordset(sql, dbOpenSnapshot)
    With rs
        While Not .EOF
            buf = buf & !veldnaam & ", "
            .MoveNext
        Wend
    End With
    TxtConII = buf
End Function

en die roep je weer aan in een query die alleen op de instellingentabel is gebaseerd.

[ Voor 6% gewijzigd door Lustucru op 27-03-2004 13:14 ]

[Verwijderd]

Niesje wederom bedankt voor je antwoord. Ik heb het inderdaad geprobeerd met nog een query aan te maken en dan eerst sorteren en daarna werkt het m.b.t. het softwareverhaal vlekkeloos. Ik krijg netjes een opsomming van de softwarepakketten die een bepaalde instelling gebruikt. Echter als ik dezelfde functie daarna wil toepassen op een ander veld namelijk de regio's waarin een bepaalde instelling actief is dan werkt de functie op de één of andere manier niet. Ik heb eerst ook een query opgebouwd en die gesorteerd. Vervolgens zie ik ook in het lijstje de regio's mooi samengevat in de laatste record van elke instelling. Als ik daarna echter in de query definieer dat de laatste gekozen moet worden zie ik daarna echter steeds maar 1 regio staan. Ool als ik probeer verschillende rollen samen te voegen bij 1 medewerker werkt het niet. Het lijkt erop of het alleen bij softwarepakket + instelling werkt. Ik snap er echt helemaal niets van. Heb jij hier toevallig ook nog een oplossing of suggestie voor. Alvast bedankt!

[ Voor 9% gewijzigd door Verwijderd op 28-03-2004 11:45 ]

[Lustucru]

Flabbergeistert Als je niet groepeert staat in het laatste record wel alles goed en na groeperen niet meer? Daar snap ik zo ook niets van. Stuur dan het ding maar eens op en dan kijk ik erna.
Overigens blijkt variant twee toch sneller, vooral als er veel subrecords bestaan. De herhaalde oproep van de functie neemt dan meer tijd in beslag dan het openen van de recordset.

edit:
nee, dit is nietwaar; de query opent sneller, maar de gegevens owrden langzamer opgehaald

Als je de tweede variant wilt gebruiken en je wilt hetzelfde doen voor meer tabellen dan zul je de functie universeler moeten maken, bv door een extra parameter mee te geven die aangeeft in welke tabel gezocht moet worden en dan de sql string op te bouwen. Kun je bovendien alles in één query doen. Snel zal het allemaal niet worden

[ Voor 8% gewijzigd door Lustucru op 28-03-2004 14:19 ]

[Verwijderd]

Niesje ik heb de DB geemaild naar dit is leuk voor harvesters enzo

[ Voor 42% gewijzigd door whoami op 30-03-2004 08:57 ]

[Lustucru]

Aaargh Het werkt inderdaad niet zoals je zou verwachten. Als ik kijk wanneer de functie wordt aangeroepen vermoed ik dat het probleem wordt veroorzaakt door de optimalisatie die Access toepast om query's uit te voeren. Kortom: de functie is leuk bedacht (vind ik zelf ) maarwerkt voor geen meter.
Variant twee werkt wel goed.

offtopic:
even comprimeren voor je een db verzendt zou fijn zijn: scheelt 6,6 MB van de 7

[bigtree]

offtopic:
Hoera! MySQL kan iets dat Access niet kan: group_concat

[Verwijderd]

Niesje,
Hartstikke bedankt voor de gemailde functie. Het duurde even voordat ik hem kon uitproberen, maar ik heb hem nu getest en de functie doet precies wat ik wil. Ideaal!

[HansMij]

Verwijderd schreef op 25 maart 2004 @ 10:45:
[...]


Dit kan je wel regelen met SQL...moet alleen even uitzoeken hoe, want ik doe precies hetzelfde, voeg voornaam en achternaam ook samen in 1 veld...

Dat is natuurlijk onzin. Het probleem wat omschreven is, houdt in dat er data uit verschillende records in 1 value gezet moet worden. Wat jij doet is verschillende values uit dezelfde record samenvoegen. Dat stelt idd niets voor. Het punt wat hier beschreven wordt kan denk ik niet. De enige mogelijkheid die ik evt. nog zie is met behulp van een sub-query. Hiermee kun je best leuke dingetjes doen, en misschien dat je met inventief programmeren hier wat mee voor elkaar kunt krijgen.