Alleen records die 1 keer voorkomen in onderliggende tabel selecteren in query

[Jors]

Hallo,

ik probeer een query te maken in Access met twee tabellen met een één-op-veel relatie. Hierbij wil ik echter alleen records selecteren die in de onderliggende tabel maar 1 keer voorkomen.

Dus stel ik heb de volgende twee tabellen met KlantID als één-op-veel relatie tussen de twee:

Tabel 1                               Tabel 2
KlantID Klantnaam              Ordernr   KlantID   Bedrag
1           A                      1          3      500
2           B                      2          2     1000
3           C                      3          2      800
                                   4          1     1200

Hieruit wil ik een query maken waarbij alleen de records uit tabel 2 geselecteerd worden met maar 1 order (dus waarbij klantID maar 1 keer voorkomt). In dit geval zouden dus alleen Ordernr 1 en 4 geselecteerd worden.

Ik dacht dit op te kunnen lossen door bij Totalen "Aantal" aan te zetten van het KlantID en deze dan als criteria "1" mee te geven, maar gek genoeg zijn de getallen bij "Aantal" niet het aantal keer dat de klantID voorkomt in de query.

De SQL code die hierbij hoort zou de volgende zijn:

[SQL]
SELECT Tabel2.Ordernr, Tabel2.KlantID, Tabel1.klantnaam, Count(Tabel2.KlantID) AS AantalVanKlantID, Tabel2.Bedrag
FROM Tabel1 INNER JOIN Tabel2 ON Tabel1.klantID = Tabel2.KlantID
GROUP BY Tabel2.Ordernr, Tabel2.KlantID, Tabel1.klantnaam, Tabel2.Bedrag;
[/SQL]

Maar dit werkt dus niet, omdat de aantallen die worden weergegeven niet de aantallen zijn die overeenkomen met wat ik zie in de query.

Wie kan mij helpen?

 

[OctaFish]

Het werkt wel, als je de onderliggende gedachte van de Totalenquery begrijpt. En dat doe je vermoed ik nog niet helemaal. Groeperen doe je op een specifiek veld, of op een combinatie van velden. Dus groeperen op Land geeft bijvoorbeeld 3 records (Nederland, België en Duitsland) en bij AantalvanID zie je dan een paar grote getallen staan. Zet je daar het groepeerveld Stad bij, dan krijg je meer records (elke combinatie van Land+Stad) en de daarbij horende getallen. De totaalsom is uiteraard gelijk aan het resultaat van de eerste query. Zet je er nog een groepeer veld bij (klant) dan wordt het resultaat nog verder gespecificeerd; nu zie je de aantallen per klant. Kortom: het kleinste gespecificeerde veld bepaalt de groepering.
Wat je in dit soort situaties nooit moet doen, is het sleutelveld uit de tabel groeperen. Elk ordernummer komt namelijk maar één keer voor, dus als je dan de records gaat tellen, is het resultaat altijd 1.
Als je alleen wilt weten hoeveel klanten één order hebben, dan moet je dus ook niet groeperen op Bedrag, want daarvoor geldt hetzelfde (zij het in mindere mate als Ordernr). Je query ziet er dan zo uit:

SELECT Tabel2.KlantID, Tabel1.klantnaam, Sum(Tabel2.Bedrag) AS Totaalsom
FROM Tabel1 INNER JOIN Tabel2 ON Tabel1.klantID = Tabel2.KlantID
GROUP BY Tabel2.KlantID, Tabel1.klantnaam
HAVING Count(Tabel2.[Ordernr])=1;

 

[Jors]

Dank voor je antwoord OctaFish. Het klopt in ieder geval dat ik de de Totalenquery nog niet helemaal begrijp. Ik kan je uitleg grotendeels volgen. Begrijp ik goed dat COUNT in mijn voorbeeld niet de aantallen in klantID telt, maar dat het kijkt naar het totaal van de kolommen die gegroepeerd worden en daarvan bepaalt of deze combinatie uniek is?

In jouw voorbeeld geeft HAVING Count(Tabel2.[Ordernr])=1; dus niet het aantal keer dat Ordernr voorkomt weer, maar de combi KlantID en klantnaam.

Moet de "HAVING COUNT" altijd op een sleutelveld zijn?

// Edit:

Aangezien bovenstaande tabellen slechts een vereenvoudigd voorbeeld zijn van de tabellen waar ik de query op wil uitvoeren, liep ik tegen het probleem aan dat ik meerdere queries moest maken om de data boven tafel te krijgen die ik echt wilde. Ik kan namelijk niet alle velden uitvoeren in de query, omdat de rij dan niet meer uniek is en er dus meerdere "klantID's" per query worden gevonden.

Ik heb een beetje zitten spelen met de query wizard in Access en de query die automatisch gegenereerd wordt bij het zoeken naar "duplicates" aangepast. Het resultaat is de volgende SQL code:

[SQL]
SELECT Tabel2.KlantID, Tabel1.klantnaam, Tabel2.Bedrag
FROM Tabel1 INNER JOIN Tabel2 ON Tabel1.klantID = Tabel2.KlantID
WHERE (((Tabel2.KlantID) In (SELECT [KlantID] FROM [Tabel2] As Tmp GROUP BY [KlantID] HAVING Count([KlantID])=1 )));
[/SQL]

Dit geeft hetzelfde resultaat, maar nu kan ik bijv. Tabel2.Bedrag gewoon direct weergeven. Is bovenstaande methode handig om te gebruiken of zitten er haken en ogen aan? Ik merkte bijvoorbeeld al dat als ik deze code gebruik op mijn echte database, het wat langer duurt voordat deze is uitgevoerd.

 

[OctaFish]

Je gebruikt nu een subquery die refereert aan een tabel die ook al in de hoofdquery wordt gebruikt. Dat kan prima, maar dat kost meer tijd, omdat Access voor elk record in de hoofdquery elk record in de subquery naloopt.

Begrijp ik goed dat COUNT in mijn voorbeeld niet de aantallen in klantID telt, maar dat het kijkt naar het totaal van de kolommen die gegroepeerd worden en daarvan bepaalt of deze combinatie uniek is?

Dat klopt; je kijkt in een Totalen query altijd naar de combinatie van velden waarop je groepeert. En die combinatie komt één keer voor, en de berekende velden maken de berekening op basis van de selectie.

Moet de "HAVING COUNT" altijd op een sleutelveld zijn?

Dat is altijd goed om te doen, maar hoeft niet. De enige eis die je stelt aan een berekend veld, is dat het veld dat je gebruikt altijd gevuld is. Een COUNT op het veld [Tussenvoegsel] is dus erg riskant, omdat lang niet alle personen een tussenvoegsel in de naam hebben. Een sleutelveld mag per definitie nooit leeg zijn, en is dus zeer geschikt. Als het om tellen van records gaat mag je ook zelf wat invullen. dus Count(1) As AantalKlanten werkt net zo goed als Count([KlantID]) As AantalKlanten.

Wat je ook nog kunt proberen is om een extra veld te maken met de functie DCOUNT. Die geef je dan als criterium het KlantID mee. Dat heeft dan deze constructie:

Expr1: DCount("[Ordernr]";"Tabel2";"[KlantID] = " & [KlantID] )

Voordeel hiervan is dat de functie sneller werkt als de subquery. En je hebt geen ingewikkelde constructie nodig.

 

[Jors]

Bedankt voor de uitleg! Ik geloof dat ik begrijp nu.

Wat je ook nog kunt proberen is om een extra veld te maken met de functie DCOUNT. Die geef je dan als criterium het KlantID mee. Dat heeft dan deze constructie:

Expr1: DCount("[Ordernr]";"Tabel2";"[KlantID] = " & [KlantID] )

Voordeel hiervan is dat de functie sneller werkt als de subquery. En je hebt geen ingewikkelde constructie nodig.

Deze functie werkt inderdaad ook, en is inderdaad sneller. Dus nogmaals dank.

MS Access gaf in eerste instantie aan dat [KlantID] naar meerdere velden kon verwijzen, dus ik heb de code iets aangepast:

Expr1: DCount("[Ordernr]";"Tabel2";"[tabel2.KlantID] = " & [tabel2.KlantID])

Het resultaat is goed, dus het zal wel kloppen, alleen voelt het gek om aan te geven dat klantID "zichzelf" is.

// Edit:

Als ik de expressie ombouw naar mijn "echte" tabel dan worden de cellen gevuld met #Fout. Als ik daarop klik krijg ik een msgbox met "Gegevenstypen komen niet overeen in criteriumexpressie." Werkt DCOUNT alleen op numerieke velden ofzo?

 

[OctaFish]

Er is onderscheid tussen vergelijkingen tussen numerieke velden, tekstvelden en datumvelden. Blijkbaar gebruik je nu een ander type veld. Wat overigens vreemd is, want een ID zou altijd numeriek moeten zijn in mijn ogen . Dat je de tabelnaam erbij moest zetten kan kloppen; op het moment dat je twee tabellen hebt met hetzelfde veld(naam), dan moet Access weten naar welke tabel moet worden gekeken. Dat kun je ondervangen door de velnamen een iets andere naam te geven: KlantID en Klant_ID bijvoorbeeld. Dan speelt het niet meer.
De DCount functie is een database functie, en die doet dus eigenlijk een query in een tabel. Vandaar dat je een veldnaam en een tabelnaam moet opgeven. Heb je geen criterium, dan telt hij gewoon alle records. Maar door er een filter in te zetten, krijg je dus het gewenste resultaat. Dat filter pakt hij op basis van een tabelveld (uit Tabel2, dus het veld KlantID) en dat wordt vergeleken met een waarde uit de Recordset van de query, en daar heb je het veld KlantID ook als zodanig instaan. Dus de DCOUNT zegt eigenlijk voor het record van KlantID=1:

DCount("[Ordernr]";"Tabel2";"[tabel2.KlantID] = 1")

en voor KlantID=2:

DCount("[Ordernr]";"Tabel2";"[tabel2.KlantID] = 2")

etc.
Als je op tekst filtert, moet de code er zo uit zien:

DCount("[Ordernr]";"Tabel2";"[tabel2.KlantID] = '" & [KlantID] & "'")

Laatste 'geintje': DCount retourneert (net als alle database functies) een tekst, geen getal. Dus je moet er wellicht een getal van maken als je filtert.

CInt(DCount("[Ordernr]";"Tabel2";"[tabel2.KlantID] = " & [KlantID]))

 

[Jors]

Blijkbaar gebruik je nu een ander type veld. Wat overigens vreemd is, want een ID zou altijd numeriek moeten zijn in mijn ogen .

Het wordt me steeds duidelijker. Dat ik op een tekstveld moet filteren heeft overigens twee oorzaken:

1. Het gaat om een geïmporteerde lijst met meer dan 10.000 records. Het ID is wel uniek, maar bevat soms een letter.
2. Het IDnr is in 99% van de gevallen een 6-cijferig getal, met soms een "voorloop-nul". Als ik de tekst omzet naar een numeriek getal valt deze 0 weg.

Het is dus niet mijn gebruik om met een string als IDnr te werken, maar in dit geval de meest pragmatische oplossing. Ik zal de functie in ieder geval eens aanpassen volgens jouw voorstel en dan kijken wat er gebeurt.

 

[Jors]

De query werkt nu prima! Dank daarvoor.

Maar natuurlijk ben ik nog niet tevreden Stel ik breid tabel 2 als volgt uit:

Tabel 2
Ordernr KlantID Bedrag Product
  1        3      500   AAA.3812
  2        2     1000   AAA.9802
  3        2      800   AAA.0983 
  4        1     1200   BBB.7823
  5        3      300   BBB.0811

Nou wil ik de klantID's selecteren die eenmaal voorkomen per eerste drie letters van het product.

In dit geval zou klant 1 dus twee keer weergeven moet worden (voor product AAA en BBB) klant 2 niet (immers tweemaal AAA) en klant 3 eenmaal (product BBB). Is dit mogelijk

 

[OctaFish]

Dan moet je met LEFT de 3 letters uit de product naam filteren, en daar ook op groeperen.

 

[Jors]

Dus iets als:

DCount("[Ordernr], left([Product];3)";"Tabel2";"[tabel2.KlantID] = '" & [KlantID] & "'")

?

 

[OctaFish]

Je telt op één veld, dus de veldnaam en tabelnaam moet je vanaf blijven . Wel kun je het criterium uitbreiden. Iets als:

DCount("[Ordernr],";"Tabel2";"[KlantID] = '" & [KlantID] & "' AND Left([Product];3) = '" & Left([Product];3) & "'")

 

[Jors]

Ik krijg een syntaxfout:

Syntaxisfout. In query-expressie [klantID] = 'A' AND Left([product];3) = 'AAA'.