Opgelost Query vraag

[janluc]

Zie de bijlage, een tabel met factuurregels. Ik wil graag weten bij welke klanten (clientID) het jaartal 2022 in de omschrijving ontbreekt. Als ik in het query ontwerp als criterium invoer: Not Like "*" & "2022" & "*" kom ik er niet, maar wat mis ik?
Ik vermoed dat het eenvoudig zou moeten zijn, maar ik kom er niet uit... dank alvast!
Groet, Jan-Luc

 

[OctaFish]

Waarom zo lastig opgeschreven? Heb je dit Not Like *2022* al geprobeerd?
Deze variant, en Like *2022* geprobeerd, en ze werken allebei.

 

[janluc]

Dat is inderdaad al een stukje eenvoudiger, dank. Maar de query geeft nog niet het gewenste resultaat. Ik krijg hiermee nog steeds als uitkomst de records waarin 2022 niet in de omschrijving voorkomt. Maar ik wil weten welke klant geen records heeft met 2022 in de omschrijving. Ik hoop dat ik me duidelijk uitdruk.

 

[OctaFish]

Da's inderdaad een iets andere invulling van de vraag . Dat zul je met een subquery moeten doen, vrees ik. Die subquery filtert de klanten die wél 2022 in de omschrijving hebben:

SELECT DISTINCT ClientID FROM tblFactuurregels WHERE Omschrijving Like "*2022*"

Vervolgens maak je een query waarin je de ClientID's filtert die hier niet aan voldoen. Dan krijg je als eindresultaat deze query:

SELECT DISTINCT ClientID FROM tblFactuurregels WHERE ClientID Not In 
(SELECT DISTINCT ClientID FROM tblFactuurregels WHERE Omschrijving Like "*2022*")

 

[janluc]

Ah, een subquery! Super bedankt, dat werkt. Ik merk wel dat de Query tijd kost, ik schat dat hij een halve minuut bezig is om 6000 records te doorlopen. Dat is sneller dan ik natuurlijk, maar kun je dat verwachten bij zo'n soort query? Ik heb een Intel(R) Core(TM) i5, 8 GB RAM

 

[OctaFish]

Ja, dat klopt. Omdat er steeds twee queries worden uitgevoerd op de hele tabel. Je kunt de procedure nog wel verkorten door eerst een tijdelijke tabel te maken met de uitgesloten records (de subquery dus) en die met een Outer Join koppelen aan je tblFactuurregels. Dat is zeker sneller.

 

[OctaFish]

Dan krijg je zoiets:

SELECT DISTINCT ClientID INTO tblTemp FROM tblFactuurregels 
WHERE Omschrijving Like "*2022*"

Query mag je een willekeurige naam geven uiteraard. Ik noem 'm Query1 .
Query2 is dan:

SELECT DISTINCT ClientID
FROM tblFactuurregels LEFT JOIN tblTemp ON tblFactuurregels.ClientID = tblTemp.ClientID
WHERE (tblFactuurregels.ClientID Is Not Null AND tblTemp.ClientID Is Null)

En dit is aanzienlijk sneller.

 

[janluc]

Dat werkt als een trein inderdaad, dank je! Heel veel dank hiervoor.

 

[OctaFish]

Ik weet niet of je deze procedure vaker nodig hebt, anders kun je een functie maken waarin je e.e.a. automatiseert. Dus eerst de tabel tblTemp leeggooien, dan vullen (moet je de query een klein beetje aanpassen) en dan de selectiequery uitvoeren. Die functie hang je dan onder een knop op een formulier, waarbij je bijvoorbeeld het jaar flexibel kan maken. Altijd handig .

 

[AHulpje]

Kan in dit geval ook met COUNT, is zeer snel, zie QueryAH.
Geen hulptabel nodig.

 

[OctaFish]

Nog steeds drie queries nodig. Een béétje vent doet het uiteraard met één query. En net zo snel als die drie van jou natuurlijk.

SELECT ClientID FROM tblFactuurregels
GROUP BY ClientID
HAVING ClientID Is Not Null AND Sum(InStr(1,[Omschrijving],"2022"))=0

 

[janluc]

Dank jullie wel!

 

[NoellaG]

Tip: je hoeft de temp table uit #7 niet aan te maken, maar kan die als CTE gebruiken:

SELECT DISTINCT tblFactuurregels.ClientID
FROM tblFactuurregels LEFT JOIN (SELECT DISTINCT ClientID  FROM tblFactuurregels
WHERE Omschrijving Like "*2022*")  as Temp ON tblFactuurregels.ClientID = Temp.ClientID
WHERE (tblFactuurregels.ClientID Is Not Null AND Temp.ClientID Is Null)

 

[OctaFish]

Maar ook deze oplossing lijkt mij bepaald niet sneller als de mijne (één query), of die van ah (met drie queries).

 

[NoellaG]

Dit is ook één query en ongeveer 2x sneller (gemeten op SQL server) dan eerst de tabel aan te maken en daarna de tabel gebruiken in een andere query, afhankelijk van het beschikbare geheugen natuurlijk.
Wat query uit #11 betreft: ook groeperingen hebben resources nodig en maken intern gebruik van temp datasets, je moet maar eens naar de queryplannen vergelijken. In Access bestaat er ook een tool dacht ik waarmee je de queryplannen kunt bekijken.

 

[OctaFish]

Zoals ik al zei: nog steeds niet sneller als de enkele query uit bericht #11. En daar gaat het toch om? Een snelle en simpele oplossing?

We zitten hier op een Access forum, dus oplossingen die op een SQL server prima werken, zijn voor de groep hier echt niet interessant. Ik snap ook niet zo goed waarom je die maar blijft insturen... Maar goed, als we alle langzame oplossingen gaan opsturen, heb ik er ook nog wel een paar liggen .

 

[NoellaG]

Zoals ik zei: in Access bestaat ook een tool om query plannen te tonen https://stackoverflow.com/questions/12607296/how-to-get-query-plans-showplan-out-from-access-2010

maar ik ben wel echt geïnteresseerd in het bewijs dat jou query uit #11 echt de snelste is.

 

[OctaFish]

Test 'm. In Access, niet in die SQL server van je. En dan natuurlijk gelijk ook aantonen dat die van jou makkelijker te maken is dan die van mij. Als je toch bezig bent...
Wellicht heb je overigens gemist dat de vraag al op <Opgelost> staat, dus dat TS tevreden is met de eerder aangeboden oplossingen .

 

[AHulpje]

Had niet het idee dat onze query's qua snelheid veel uiteen zouden lopen, maar meten is weten.
Dus tien keer de query's van mij, OctaFish en NoellaG uitgevoerd, resultaat gemiddeld in milliseconden:

AHulpje   48,2
OctaFish  52,7
NoellaG   49,5

Een volkomen verwaarloosbaar verschil dus.
Q.E.D.

 

[OctaFish]

En weer een uur van je leven kwijt. . Blijft over: de makkelijkste oplossing. En zie maar dat je mij van de hoogste tree afkrijgt . In ieder geval qua gebruikte letters.

Maar voor wie doe je dit eigenlijk? Ik zie nergens dat TS hierom gevraagd heeft. Snap ik de bedoeling van het forum dan niet meer, dat we mensen met een vraag helpen met een net antwoord, en dat we daarna elkaar vliegen moeten afvangen? Ikke nie snappe....