Hoe Microsoft Access SQL-query's vanuit Scratch schrijven

Microsoft Access is misschien wel de meest krachtige tool in de hele Microsoft Office-suite, maar het raadselachtige (en soms schrikkende) Office-gebruikers. Hoe kan iemand met een steilere leercurve dan Word of Excel zijn hoofd rond het gebruik van deze tool wikkelen? Deze week kijkt Bruce Epper naar enkele van de problemen die deze vraag van een van onze lezers oproept.

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 u kunt vinden in tabel B. Ik wil een kolom met de naam Resultaten toevoegen die de productnaam uit tabel A bevat, en de productnaam uit tabel B wanneer deze niet bestaat in tabel A.

Heb je enig advies?

Bruce's Antwoord:

Microsoft Access is een databasebeheersysteem (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 behoefte aan het begrijpen van gestructureerde querytaal (SQL) bijna elimineert.

SQL is de opdrachttaal die wordt gebruikt om informatie die is opgeslagen in de database, toe te voegen, te verwijderen, bij te werken en te retourneren, evenals 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:

• Dus wat is een database? Dus wat is een database eigenlijk? [MakeUseOf Explains] Dus wat is een database eigenlijk? [MakeUseOf Explains] Voor een programmeur of een technologie-liefhebber is het concept van een database iets dat echt als vanzelfsprekend kan worden beschouwd. Voor veel mensen is het concept van een database zelf echter een beetje vreemd ... Lees meer waar Ryan Dube Excel gebruikt om de basisprincipes van relationele databases te tonen.
• Een beknopte handleiding Aan de slag met Microsoft Access 2007 Een beknopte handleiding Aan de slag met Microsoft Access 2007 Een beknopte handleiding Aan de slag Met Microsoft Access 2007 Lees meer, een overzicht op hoog niveau van Access en de componenten waaruit een Access-database bestaat.
• Een korte handleiding voor tabellen in Microsoft Access 2007 Een korte handleiding voor tabellen in Microsoft Access 2007 Een korte handleiding voor tabellen in Microsoft Access 2007 Lees Meer neemt een kijkje bij het maken van uw eerste database en tabellen om uw gestructureerde gegevens op te slaan.
• Een snelle zelfstudie over query's in Microsoft Access 2007 Een korte zelfstudie over query's in Microsoft Access 2007 Een korte zelfstudie over query's In Microsoft Access 2007 leest Read More over de manier waarop specifieke delen van de gegevens die in de databasetabellen zijn opgeslagen, worden geretourneerd.

Het hebben van een basisbegrip van de concepten in deze artikelen maakt het volgende een beetje makkelijker te verteren.

Databasebetrekkingen en normalisatie

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

De bovenstaande afbeeldingen maken deel uit van de spreadsheet voor het bijhouden van orders die u gebruikt. Zeg nu dat beide klanten meerdere keren per jaar widgets bij u kopen, dus u hebt veel meer rijen voor beiden.

Als Joan Smith met Ted Baines trouwt en zijn achternaam neemt, moet elke rij met haar naam nu worden gewijzigd. Het probleem wordt nog groter als je toevallig twee verschillende clients hebt met de naam 'Joan Smith'. Het is net 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 orders.

Als we alleen naar het clientgedeelte van ons voorbeeld kijken, verwijderen we de kolommen voor klantnaam en klantadres en plaatsen deze in een nieuwe tabel. In de bovenstaande afbeelding heb ik ook dingen beter uitgebroken voor een meer gedetailleerde toegang tot de gegevens. De nieuwe tabel bevat ook een kolom voor een primaire sleutel (ClientID) - een getal dat wordt gebruikt voor toegang tot elke rij in deze tabel.

In de oorspronkelijke tabel waar we deze gegevens verwijderden, voegden we een kolom toe voor een Foreign Key (ClientID) die verwijst naar de juiste rij met de informatie voor deze specifieke client.

Als Joan Smith haar naam nu wijzigt in Joan Baines, hoeft de wijziging maar één keer in de tabel Client te worden aangebracht. Elke andere referentie van gekoppelde tabellen zal de juiste naam van de klant bevatten en een rapport dat kijkt naar wat Joan de afgelopen 5 jaar heeft gekocht, krijgt alle bestellingen onder zowel haar eerste als gehuwde namen zonder te hoeven veranderen hoe het rapport wordt gegenereerd.

Als een extra voordeel, vermindert dit ook de totale hoeveelheid verbruikte opslag.

Doe mee met typen

SQL definieert vijf verschillende typen joins: INNERLIJK, LINKERBUITENKANT, RECHTS BUITENKANT, VOLLEDIG BUITENKANT en KRUIS. Het OUTER-sleutelwoord is optioneel in de SQL-instructie.

Microsoft Access maakt het gebruik van INNER (standaard), LINKER-BUITENKANT, RECHTS BUITENKANT en KRUIS mogelijk. FULL OUTER wordt niet als zodanig ondersteund, maar door LINKERBUITEN, UNIE ALL en RECHTS BUITEN te gebruiken, kan het worden gefingeerd ten koste van meer CPU-cycli en I / O-bewerkingen.

De uitvoer van een CROSS-join bevat elke rij van de linkertabel die aan elke rij van de rechtertabel is gekoppeld. De enige keer dat ik ooit een CROSS-join heb gebruikt, is tijdens loadtests van databaseservers.

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

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

Het AutoNummering is een automatisch incrementeel lang geheel getal dat is toegewezen aan items wanneer deze aan de tabel worden toegevoegd. De tekstoptie is niet gewijzigd, dus accepteert hij een tekstreeks tot 255 tekens lang.

Vul ze nu in met enkele gegevens.

Om de verschillen in de werking van de 3 join-typen te laten zien, heb ik de items 1, 5 en 8 verwijderd uit ProdA.

Maak vervolgens een nieuwe query door naar Maak> Queryontwerp. Selecteer beide tabellen in het dialoogvenster Tabel tonen 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 en selecteer Join Properties.

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

We zullen eerst de INNER join bekijken, 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 in beide tabellen het veld Productnaam weergegeven met de waarde uit tabel ProdA in de eerste kolom en ProdB in de tweede kolom.

Merk op dat de resultaten alleen waarden weergeven waar ProductID in beide tabellen gelijk is. Hoewel er een invoer is voor ProductID = 1 in tabel ProdB, wordt deze niet weergegeven in de resultaten omdat ProductID = 1 niet voorkomt in tabel ProdA. Hetzelfde is van toepassing op ProductID = 11. Het bestaat in de tabel ProdA maar niet in de tabel ProdB.

Door de knop View op het lint te gebruiken en over te schakelen naar SQL View, 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 jointype in 2 (LINKEROUTER). Voer de query uit om de resultaten te bekijken.

Zoals u kunt zien, wordt elke invoer in de tabel ProdA in de resultaten weergegeven, terwijl alleen die in ProdB met een overeenkomend ProductID-item in tabel ProdB in de resultaten verschijnen.

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 (RIGHT OUTER).

De resultaten laten alles zien uit tabel ProdB terwijl het blanco (bekend als NULL) waarden toont waarbij 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 gebruiken in een query

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

De Onmiddellijke IF (IIF) -functie kan worden gebruikt om dit resultaat te genereren. De functie heeft drie parameters. De eerste is een voorwaarde die moet worden geëvalueerd naar een juiste of valse waarde. De tweede parameter is de waarde die moet worden geretourneerd als de voorwaarde Waar is en de derde parameter de waarde is die moet worden geretourneerd als de voorwaarde False is.

Het volledig functionele construct voor onze situatie ziet er als volgt uit:

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

Merk op dat de conditieparameter niet controleert op gelijkheid. 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 hier achter te komen, controleren we in plaats daarvan de waarde met het sleutelwoord 'Is'.

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

Wanneer u dit in de Query Designer plaatst, moet u de hele functie in het veld: invoeren. Als u wilt dat de kolom 'Resultaten' wordt gemaakt, moet u een alias gebruiken. Om dit te doen, geeft u de functie vooraf op met 'Resultaten:' zoals te zien 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, worden deze resultaten geproduceerd.

Hier zien we voor elke invoer waarin tabel ProdA een waarde heeft, die waarde wordt weergegeven in de kolom Resultaten. Als er geen item in de ProdA-tabel staat, wordt het item van ProdB weergegeven in Results en dat is precies wat onze lezer vroeg.

Voor meer bronnen voor het leren van Microsoft Access, bekijk Joel Lee's Hoe leer ik Microsoft Access: 5 gratis online bronnen Leren van Microsoft Access: 5 gratis online bronnen Leren van Microsoft Access: 5 gratis online bronnen Moet je een groot aantal beheren Van de gegevens? Je zou in Microsoft Access moeten kijken. Onze gratis studiehulpmiddelen kunnen u helpen aan de slag te gaan en de vaardigheden voor meer complexe projecten te leren. Lees verder .