[SQL/Access] Waarde zoeken in hele tabel

[Wok]

Achtergrondinformatie
De afgelopen tijd ben ik bezig geweest met het ontwikkelen van applicatie voor PABO-studenten. Met behulp van deze applicatie kunnen studenten van de PABO lesvoorbereidingen maken in het kader van stages die zij moeten lopen bij een basisschool. De applicatie zelf is volledig functioneel.

Het enige onderdeel wat nog ontbreekt is een zoekfunctie. Deze wil ik nu gaan maken. Het is de bedoeling dat er een willekeurige waarde (tekenreeks) wordt gezocht in een van de aanwezige tabellen. Hiervoor wil ik gebruik maken van SQL.

In een hiervoor gemaakt zoekformulier kan de gebruiker een zoekterm invullen, waarna er in de onderliggende tabel wordt gezocht naar alle records waarin de zoekterm voorkomt.

Opbouw van de applicatie
De applicatie is gemaakt in Access XP. Er wordt veelvuldig gebruik gemaakt van queries en Visual Basic for Applications (VBA).

Er moet gezocht worden in de volgende tabellen:
- Stageschool
- Stageperiode
- Les

Het zoekformulier heb ik onderverdeeld in drie gedeeltjes: een voor elke tabel waarin gegevens gezocht moeten worden. Dit is ook duidelijk voor de gebruiker(s).
Ik wil voor elk van de onderdelen een aparte zoekquery maken. Het is de bedoeling dat elke query een aparte tabel doorzoekt. Met andere woorden: als een gebruiker iets wil zoeken in de tabel Les, dan wordt de query aangeroepen die de tabel Les doorzoekt. De zoekquery moet de waarde opdoeken die in het daarvoor bestemde invoerveld is gegeven.

Probleem
Ik beperk me hier nu even tot de tabel Les. Het zoekformulier heeft een invoerveld met de naam "Txt_les_zoek". Ik wil nu dat de query alle records weergeeft waarin de waarde in het vak "Txt_les_zoek" voorkomt. Om de waarde uit het invoerveld te gebruiken gebruik ik de volgende variabele:

code:

[Forms]![Zoeken]![Txt_les_zoek]

De bedoeling is nu dat de query uit de tabel Les alle records weergeeft waarin deze variabele voorkomt. De query moet daarbij alle velden doorzoeken en daarbij de volledige records weergeven.

Ik probeer daarvoor het volgende:

code:

SELECT l.*
FROM Les l
WHERE [Forms]![Zoeken]![Txt_les_zoek] = 
(SELECT q.*
FROM Les q)

Als ik deze query wil uitvoeren, krijg ik echter de foutmelding dat ik een subquery heb gemaakt die meer dan één veld kan weergeven zonder dat ik gebruik heb gemaakt van het EXISTS commando. Het is juist de bedoeling dat er meerdere velden weergegeven kunnen worden.

Als ik mijn boeken raadpleeg omtrent het EXISTS commando, komt het er op neer dat ik in WHERE-clausule van de subquery moet aangeven in welke kolommen van de tabel Les gezocht moet worden. Ik wil echter de hele tabel doorzoeken (dus alle kolommen). Hoe kan ik dit oplossen? Ik wil dus dat alle kolommen doorzocht moeten worden, maar het is geen doen om alle kolomnamen in te geven. Het is bijvoorbeeld niet mogelijk om het volgende aan te geven:

code:

WHERE EXISTS ( SELECT q.* FROM Les q WHERE q.* = [Forms]![Zoeken]![Txt_les_zoek])

[Ashbjorn]

Bij mijn weten moet je altijd de kolomnamen specificeren waarin je wilt zoeken.

[ATS]

Wat ik in een soortgelijk geval heb gedaan is het volgende: Maak een query over je oorspronkelijke tabel, waarbij je alle velden waarin je wil kunnen zoeken aan elkaar plakt in één groot veld, met een scheidingteken tussen de velden (anders kan je rare resultaten verwachten). Gebruik nu dit veld om te doorzoeken, waarbij je het LIKE commando gebruikt:

WHERE searchField LIKE "*" & zoekterm & "*"

[Wok]

@ATS: Dus als ik het goed begrijp, komt het erop neer dat ik de volgende query maak over de tabel Les:
SELECT * FROM Les;
waarbij het resultaat in een view geplaatst wordt?

Vervolgens moet de zoekquery dus plaatsvinden over de view. Zit ik nu in de goede richting te denken?

[Freee!!]

wokmaster schreef op 18 March 2003 @ 11:22:
@ATS: Dus als ik het goed begrijp, komt het erop neer dat ik de volgende query maak over de tabel Les:
SELECT * FROM Les;
waarbij het resultaat in een view geplaatst wordt?

Vervolgens moet de zoekquery dus plaatsvinden over de view. Zit ik nu in de goede richting te denken?

Dat is inderdaad de goede richting.

[Wok]

Ik was dus toch iets te optimistisch Access ondersteunt op de een of andere manier het CREATE VIEW statement niet.

Nu zou ik dit kunnen ondervangen door CREATE TABLE te gebruiken, maar hoe krijg ik het dan voor elkaar om de uitvoer van

SELECT * FROM Les in één veld te krijgen (met scheidingstekens) in plaats van dat er een kopie van de tabel Les (tig kolommen en records) gemaakt wordt?

[ATS]

Je kan toch dynamisch je query aanmaken? Create View kent Access inderdaad niet, maar die heb je ook helemaal niet nodig. Je kan gewoon in VBA met OpenRecordset een recordset openen van een willekeurige query. Wat je daarin doet is NIET gewoon "SELECT * FROM Les", want dan staat alles nog steeds in verschillende velden. Wat je doet is iets in deze zin:

code:

SELECT ID, MyField1 & "|" & MyField2 & "|" & MyField3 AS searchField FROM Les

waarbij | je scheidingsteken is en je alle te doorzoeken kolommen toevoegt.
Maar... nu wil je gaan zoeken, en niet selecteren, dus verander je je query:

code:

SELECT * FROM Les WHERE MyField1 & "|" & MyField2 & "|" & MyField3 LIKE "*" & Zoekterm & "*"

Je resultaat-set bevat nu alle records waarbij de zoekterm voorkomt in de te doorzoeken velden.

[Verwijderd]

Hoe stuur je de zoekvelden aan?

Voorstel: een combobox met alle mogelijke waarden in de tabel Les (distinct, dus maar een maal voorkomend). Als er geen criteria gegeven is moet er * staan. (dit is te doen met Phantom velden

On event after update keuzebox 1:

code:

if isnull(keuze1) then
     me.phantom1.value = "*"
else
     me.phantom2.value = me.keuze1.value
end if

dan in de query bij het veld waar keuzebox 1 de criteria voor aangeeft de criteria:
like [phantom1] gebruiken


En gebruik je als criteria in je query "=" of LIKE (zoals hierboven al aangegeven)?

En misschien dat overbodig, maar Where * = Like Blabla oid werkt mi niet goed, beter is iedere kolom waar je op wilt zoeken een aparte keuzekox met criteria geven.

[Wok]

@ATS: Thanx! Dat helpt me een heel eind op weg!

Verwijderd schreef op 18 March 2003 @ 14:07:
Hoe stuur je de zoekvelden aan?

Voorstel: een combobox met alle mogelijke waarden in de tabel Les (distinct, dus maar een maal voorkomend). Als er geen criteria gegeven is moet er * staan. (dit is te doen met Phantom velden

On event after update keuzebox 1:

code:

if isnull(keuze1) then
     me.phantom1.value = "*"
else
     me.phantom2.value = me.keuze1.value
end if

dan in de query bij het veld waar keuzebox 1 de criteria voor aangeeft de criteria:
like [phantom1] gebruiken


En gebruik je als criteria in je query "=" of LIKE (zoals hierboven al aangegeven)?

En misschien dat overbodig, maar Where * = Like Blabla oid werkt mi niet goed, beter is iedere kolom waar je op wilt zoeken een aparte keuzekox met criteria geven.

Kun je hier iets meer over zeggen? is 'phantom' dan een gereserveerde naam in VBA?

Verder heb ik een zoekveld ("Txt_les_zoek") waarin de gebruiker een tekenreeks opgeeft die gezocht wordt in de tabel Les.
Wat jij zegt (combobox gebruiken) is zeer aantrekkelijk, zodat de gebruiker kan specificeren waar in de tabel les gezocht moet worden. Vraag hierbij: is het mogelijk in de combobox de namen van de kolommen van de tabel Les op te geven en in de query de waarden van de combobox te gebruiken als kolomnaam? Dus bijvoorbeeld:

SELECT * FROM Les WHERE "[Forms]![Zoeken]![Combo1]" & "|" & "[Forms]![Zoeken]![Combo2]" LIKE "*" & [Forms]![Zoeken]![Txt_les_zoek] & "*";

waarbij Combo1 en Combo2 dus de kolomnamen zijn.

[ATS]

Where * = Like Blabla oid werkt mi niet goed, beter is iedere kolom waar je op wilt zoeken een aparte keuzekox met criteria geven.

Dat ligt er maar net aan hoe je toegang tot de gegevens wil geven. Jouw voorstel zorgt al snel voor een heel geklik, en dat vinden niet alle gebruikers prettig. Bovendien kan je met jouw voorstel niet zoeken op velden waarin je een vrije tekst kan invoeren, omdat je dan steeds de gehele vrije tekst zou moeten opnemen in de drop-down boxes. Er is een tijd voor de ene oplossing, en een voor de andere. Meestal combineerde ik beide systemen in een zoekscherm met meerdere tabjes.

[ATS]

Where * = Like Blabla oid werkt mi niet goed, beter is iedere kolom waar je op wilt zoeken een aparte keuzekox met criteria geven.

Natuurlijk kan dat. Ik zou zelfs kiezen voor een lijst en geen combobox, zodat je net zoveel velden kan selecteren als je wil. Vul gewoon je lijstje met de kolomnamen en een leesbare versie ervan, doe een loopje over je lijst om te kijken welke velden geselecteerd zijn en maak daarmee dynamisch je query.

code:

Dim sSql as String
Dim sWhere as String
Dim i As Integer

sSql="SELECT * FROM Les"
For i=1 to lstVelden.listcount
    if lstVelden.selected[i] then
        if sWhere="" then
            sWhere=" WHERE lstVelden.listItem[i]"
        else
            sWhere= sWhere & "|" & lstVelden.listItem[i]
        end if
    end if
next

if sWhere="" then
    'geen velden opgegeven
else
    sSql=sSql & sWhere & " Like *" & zoekstring & "*"
end if

De string sSql kan je nu gebruiken voor je query (grove versie, beetje finetunen nog...)

BTW: waarom verdwijnt indentation binnen code tags?

[Wok]

ATS schreef op 18 March 2003 @ 15:46:
[...]

Natuurlijk kan dat. Ik zou zelfs kiezen voor een lijst en geen combobox, zodat je net zoveel velden kan selecteren als je wil. Vul gewoon je lijstje met de kolomnamen en een leesbare versie ervan, doe een loopje over je lijst om te kijken welke velden geselecteerd zijn en maak daarmee dynamisch je query.
<knip>

Mag ik vragen hoe je dit dan doet? Ik kan wel een listbox maken met een Value list als Row Source Type en de tabel Les als Row Source, maar dan krijg ik dus alle 'onleesbare' kolomnamen. Bovendien wil ik niet alle kolommen in de tabel Les gebruiken.

[ Voor 3% gewijzigd door Wok op 18-03-2003 19:34 ]

[Wok]

OK, ik heb besloten om het over een andere boeg te gooien. Ik krijg het niet voor elkaar om met list- en comboboxen het zoekgebied te beperken.

Ik ga terug naar de manier ik eerder al in gedachten had: op elke formulier (Stageschool, Stageperiode en Lesvoorbereiding) maak ik een zoekveld en bijbehorende Command Button. Na het klikken op deze knop wordt de opgegeven zoekterm gezocht in de achterliggende tabel. Ik beperk me weer even tot de tabel les.

ATS: je had het over het openen van een Recordset in VBA. Bij mijn weten moet ik dan onderstaande code gebruiken:

code:

sSql =  "SELECT * FROM Les
         WHERE kolom1 & "|" & kolom2 & "|" & kolom 3 LIKE "*" & zoekterm & "*" "
set rs = OpenRecordset(sSql)

Als alles goed gaat is er nu een gegevensset aangemaakt met alle records waarin 'zoekterm' voorkomt.

[denkt hardop modus]
Als ik deze gegevensset op mijn scherm wil hebben, zal ik een nieuw formulier moeten openen met daarin een listbox waarin alle records worden weergegeven. De Row Source Type van deze listbox moet dan een Table/Query zijn en de Row Source de Recordset: met andere woorden rs.
[/denkt hardop modus]

Klopt deze denkwijze?

[ATS]

Dat kan, wat je ook kan doen is het gedeelte na de WHERE gebruiken als filter op je normale formulier.

[Wok]

Ben weer aan het prutsen geweest, maar het wil nog niet echt lukken. Ik heb op het formulier "Lesvoorbereiding_raadplegen" een textbox gemaakt met de naam "Txt_zoekterm" en een Command Button "Knp_zoeken".

Na het ingeven van de zoekterm en het klikken op de zoekknop, wordt er een nieuw formulier geopend met daarin een listbox met de gevonden resultaten. Probleem: de listbox wordt niet gevuld met resultaten.

Bij het openen van het nieuwe formulier geef ik aan dat er een recordset gemaakt moet worden met de resultaten van een query. Daarna wil ik aangegeven dat de listbox zijn gegevens uit de recordset moet halen:

code:

Private Sub Form_Load()
Dim sSql As String
sSql = "SELECT * FROM Les
   WHERE [aandachtspunten] & [dt_leerinhoud] & [opdrachtomschrijving]
   LIKE [Forms]![Lesvoorbereiding_raadplegen]![Txt_zoekterm]"
lstVelden.RowSource = "dbOpenTable.OpenRecordset(sSql)"
End Sub

Waar ga ik de mist in? De Help-functie geeft geen fatsoenlijke uitleg...

[ Voor 5% gewijzigd door Wok op 19-03-2003 17:25 . Reden: even wat minder lay-outverneukend gemaakt... ]

[Crazy D]

Je where klopt weinig van... die moet in het formaat
where veld like 'waarde' or anderveld like 'waarde'
(met eventueel nog wildcards, en/of ipv or een and).
Ik weet niet waar de help je naartoe lootst maar vast niet naar de uitleg over queries