Hoe Microsoft Access SQL-query's vanaf nul te schrijven

Hoe Microsoft Access SQL-query's vanaf nul te schrijven

Microsoft Access is misschien wel de krachtigste tool in de hele Microsoft Office-suite, maar het verbijstert (en soms schrikt) Office-hoofdgebruikers. Met een steilere leercurve dan Word of Excel, hoe moet iemand zijn hoofd rond het gebruik van deze tool wikkelen? Deze week zal Bruce Epper enkele van de problemen bekijken die door deze vraag van een van onze lezers worden veroorzaakt.





Een lezer vraagt:

Ik heb problemen met het schrijven van een query in Microsoft Access. Ik heb een database met twee producttabellen met een gemeenschappelijke kolom met een numerieke productcode en een bijbehorende productnaam. Ik wil weten welke producten uit tabel A te vinden zijn in Tabel B. Ik wil een kolom met de naam Resultaten toevoegen die de productnaam uit Tabel A bevat als deze bestaat, en de productnaam uit Tabel B als deze niet bestaat in Tabel A. Heeft u advies?





Reactie van Bruce:

Microsoft Access is een Database Management System (DBMS) dat is ontworpen voor gebruik op zowel Windows- als Mac-machines. Het maakt gebruik van de Jet-database-engine van Microsoft voor gegevensverwerking en -opslag. Het biedt ook een grafische interface voor gebruikers die de noodzaak om Structured Query Language (SQL) te begrijpen bijna overbodig maakt.





SQL is de opdrachttaal die wordt gebruikt om informatie die in de database is opgeslagen toe te voegen, te verwijderen, bij te werken en te retourneren, en om kerndatabasecomponenten te wijzigen, zoals het toevoegen, verwijderen of wijzigen van tabellen of indexen.

Startpunt

Als u nog niet bekend bent met Access of een ander RDBMS, raad ik u aan met deze bronnen te beginnen voordat u doorgaat:



Als u een basiskennis heeft van de concepten in deze artikelen, wordt het volgende een beetje gemakkelijker te verteren.

Databaserelaties en normalisatie

Stel je voor dat je een bedrijf runt dat 50 verschillende soorten widgets over de hele wereld verkoopt. Je hebt een klantenbestand van 1.250 en verkoopt in een gemiddelde maand 10.000 widgets aan deze klanten. U gebruikt momenteel een enkele spreadsheet om al deze verkopen bij te houden - in feite een enkele databasetabel. En elk jaar voegt u duizenden rijen toe aan uw spreadsheet.





De bovenstaande afbeeldingen maken deel uit van de spreadsheet voor het volgen van bestellingen die u gebruikt. Stel nu dat beide klanten meerdere keren per jaar widgets van u kopen, zodat u voor beide veel meer rijen heeft.





Als Joan Smith met Ted Baines trouwt en zijn achternaam aanneemt, moet elke rij die haar naam bevat nu worden gewijzigd. Het probleem wordt nog groter als je twee verschillende klanten hebt met de naam 'Joan Smith'. Het is gewoon veel moeilijker geworden om uw verkoopgegevens consistent te houden vanwege een vrij veel voorkomende gebeurtenis.

Door een database te gebruiken en de gegevens te normaliseren, kunnen we items scheiden in meerdere tabellen, zoals voorraad, klanten en bestellingen.

Als we alleen naar het klantgedeelte van ons voorbeeld kijken, zouden we de kolommen voor Klantnaam en Klantadres verwijderen en in een nieuwe tabel plaatsen. In de afbeelding hierboven heb ik de zaken ook beter uitgewerkt voor meer gedetailleerde toegang tot de gegevens. De nieuwe tabel bevat ook een kolom voor een primaire sleutel (ClientID) - een nummer dat wordt gebruikt om toegang te krijgen tot elke rij in deze tabel.

In de oorspronkelijke tabel waar we deze gegevens hebben verwijderd, zouden we een kolom toevoegen voor een Foreign Key (ClientID) die linkt naar de juiste rij met de informatie voor deze specifieke klant.

Als Joan Smith nu haar naam verandert in Joan Baines, hoeft de wijziging maar één keer in de tabel Klanten te worden aangebracht. Elke andere referentie van samengevoegde tabellen zal de juiste klantnaam ophalen en een rapport dat kijkt naar wat Joan de afgelopen 5 jaar heeft gekocht, krijgt alle bestellingen onder zowel haar meisjesnaam als getrouwde namen zonder te hoeven veranderen hoe het rapport wordt gegenereerd .

Als bijkomend voordeel vermindert dit ook de totale hoeveelheid verbruikte opslagruimte.

Deelnametypes

SQL definieert vijf verschillende typen joins: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER en CROSS. Het sleutelwoord OUTER is optioneel in de SQL-instructie.

Microsoft Access staat het gebruik van INNER (standaard) , LEFT OUTER, RIGHT OUTER en CROSS toe. FULL OUTER wordt als zodanig niet ondersteund, maar door LEFT OUTER, UNION ALL en RIGHT OUTER te gebruiken, kan het worden vervalst ten koste van meer CPU-cycli en I/O-bewerkingen.

De uitvoer van een CROSS-join bevat elke rij van de linkertabel gekoppeld aan elke rij van de rechtertabel. De enige keer dat ik ooit een CROSS-join heb zien gebruiken, is tijdens het testen van de belasting van databaseservers.

Laten we eens kijken hoe de basis-joins werken, dan zullen we ze aanpassen aan onze behoeften.

Laten we beginnen met het maken van twee tabellen, ProdA en ProdB, met de volgende ontwerpeigenschappen.

De AutoNummering is een automatisch oplopend lang geheel getal dat wordt toegewezen aan vermeldingen wanneer ze aan de tabel worden toegevoegd. De optie Tekst is niet gewijzigd, dus het accepteert een tekenreeks van maximaal 255 tekens.

Vul ze nu met wat gegevens.

Om de verschillen te laten zien in hoe de 3 join-types werken, heb ik de items 1, 5 en 8 uit ProdA verwijderd.

Volgende, een nieuwe zoekopdracht maken door naar te gaan Maken > Query-ontwerp . Selecteer beide tabellen in het dialoogvenster Tabel weergeven en klik op Toevoegen , dan Dichtbij .

Klik op ProductID in tabel ProdA, sleep het naar ProductID in tabel ProdB en laat de muisknop los om de relatie tussen de tabellen te maken.

Klik met de rechtermuisknop op de lijn tussen de tabellen die de relatie tussen de items weergeven en selecteer Deelnemen Eigenschappen .

Standaard is verbindingstype 1 (INNER) geselecteerd. Optie 2 is een LEFT OUTER join en 3 is een RIGHT OUTER join.

We zullen eerst naar de INNER join kijken, dus klik op OK om het dialoogvenster te sluiten.

Selecteer in de queryontwerper de velden die we willen zien in de vervolgkeuzelijsten.

Wanneer we de query uitvoeren (het rode uitroepteken in het lint), wordt het veld ProductName van beide tabellen weergegeven met de waarde uit tabel ProdA in de eerste kolom en ProdB in de tweede.

Merk op dat de resultaten alleen waarden tonen waarbij ProductID in beide tabellen gelijk is. Ook al is er een vermelding voor ProductID = 1 in tabel ProdB, deze verschijnt niet in de resultaten omdat ProductID = 1 niet bestaat in tabel ProdA. Hetzelfde geldt voor ProductID = 11. Het bestaat in tabel ProdA maar niet in tabel ProdB.

Door de knop Weergeven op het lint te gebruiken en over te schakelen naar SQL-weergave, kunt u de SQL-query zien die door de ontwerper is gegenereerd om deze resultaten te krijgen.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Ga terug naar de ontwerpweergave en wijzig het verbindingstype in 2 (LEFT OUTER). Voer de query uit om de resultaten te zien.

Zoals u kunt zien, wordt elk item in tabel ProdA weergegeven in de resultaten, terwijl alleen degenen in ProdB met een overeenkomend ProductID-item in tabel ProdB in de resultaten worden weergegeven.

De lege ruimte in de kolom ProdB.ProductName is een speciale waarde (NULL) omdat er geen overeenkomende waarde is in tabel ProdB. Dit zal later van belang blijken.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Probeer hetzelfde met het derde type join (RECHTS BUITEN).

De resultaten tonen alles van tabel ProdB terwijl het lege (bekend als NULL) waarden toont waar de ProdA-tabel geen overeenkomende waarde heeft. Tot dusverre brengt dit ons het dichtst bij de gewenste resultaten in de vraag van onze lezer.

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Functies in een query gebruiken

De resultaten van een functie kunnen ook worden geretourneerd als onderdeel van een query. We willen dat er een nieuwe kolom met de naam 'Resultaten' in onze resultatenset verschijnt. De waarde ervan is de inhoud van de kolom ProductName van tabel ProdA als ProdA een waarde heeft (het is niet NULL), anders moet het uit tabel ProdB worden gehaald.

De functie Immediate IF (IIF) kan worden gebruikt om dit resultaat te genereren. De functie heeft drie parameters. De eerste is een voorwaarde die moet resulteren in een True of False waarde. De tweede parameter is de waarde die moet worden geretourneerd als de voorwaarde True is, en de derde parameter is de waarde die moet worden geretourneerd als de voorwaarde False is.

De volledige functieconstructie voor onze situatie ziet er als volgt uit:

IIF(ProdA.ProductID Is Null, ProdB.ProductName,ProdA.ProductName)

Merk op dat de parameter condition niet op gelijkheid controleert. Een Null-waarde in een database heeft geen waarde die kan worden vergeleken met een andere waarde, inclusief een andere Null. Met andere woorden, Null is niet gelijk aan Null. Ooit. Om hieraan voorbij te gaan, controleren we de waarde met het trefwoord 'Is'.

We hadden ook 'Is Not Null' kunnen gebruiken en de volgorde van de parameters True en False kunnen wijzigen om hetzelfde resultaat te krijgen.

Wanneer u dit in de Query Designer invoert, moet u de volledige functie in het veld Veld: invoeren. Om het de kolom 'Resultaten' te laten maken, moet je een alias gebruiken. Om dit te doen, laat u de functie voorafgaan met 'Resultaten:' zoals te zien is in de volgende schermafbeelding.

De equivalente SQL-code om dit te doen zou zijn:

SELECT ProdA.ProductName, ProdB.ProductName, IIF(ProdA.ProductID Is Null,ProdB.ProductName,ProdA.ProductName) AS Results FROM ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Wanneer we deze query uitvoeren, zal deze deze resultaten opleveren.

hoe u uw computer sneller kunt maken Windows 10

Hier zien we voor elk item waar tabel ProdA een waarde heeft, die waarde wordt weergegeven in de kolom Resultaten. Als er geen item in de ProdA-tabel is, verschijnt het item van ProdB in Resultaten, wat precies is wat onze lezer vroeg.

Voor meer bronnen voor het leren van Microsoft Access, bekijk Joel Lee's How to Learn Microsoft Access: 5 gratis online bronnen.

Deel Deel Tweeten E-mail Is het de moeite waard om te upgraden naar Windows 11?

Windows is opnieuw ontworpen. Maar is dat genoeg om u te overtuigen om over te stappen van Windows 10 naar Windows 11?

Lees volgende
Gerelateerde onderwerpen
  • productiviteit
  • Vraag het aan de experts
Over de auteur Bruce Epper(13 artikelen gepubliceerd)

Bruce speelt al met elektronica sinds de jaren '70, computers sinds het begin van de jaren '80, en beantwoordt nauwkeurig vragen over technologie die hij de hele tijd niet heeft gebruikt of gezien. Hij ergert zich ook door te proberen gitaar te spelen.

Meer van Bruce Epper

Abonneer op onze nieuwsbrief

Word lid van onze nieuwsbrief voor technische tips, recensies, gratis e-boeken en exclusieve deals!

Klik hier om je te abonneren