Fråga experterna

Hur man skriver Microsoft Access SQL-frågor från början

Hur man skriver Microsoft Access SQL-frågor från början / Fråga experterna

Microsoft Access är utan tvekan det kraftfullaste verktyget i hela Microsoft Office-paketet, men det mystifierar (och ibland skrämmer) Office Power-användare. Med en brantare inlärningskurva än Word eller Excel, hur ska man vika huvudet kring användningen av det här verktyget? Bruce Epper kommer i veckan att titta på några av frågorna som ställts av denna fråga från en av våra läsare.

En läsare frågar:

Jag har problem med att skriva en fråga i Microsoft Access.

Jag har en databas med två produkttabeller som innehåller en gemensam kolumn med en numerisk produktkod och ett tillhörande produktnamn.

Jag vill ta reda på vilka produkter från tabell A som finns i tabell B. Jag vill lägga till en kolumn med namnet Resultat som innehåller produktnamnet från tabell A om det existerar och produktnamnet från tabell B när det inte existerar i tabell A.

Har du några råd?

Bruces svar:

Microsoft Access är ett databashanteringssystem (DBMS) som är utformat för användning på både Windows- och Mac-maskiner. Det använder Microsofts Jet databasmotor för databehandling och lagring. Det ger också ett grafiskt gränssnitt för användare som nästan eliminerar behovet av att förstå Structured Query Language (SQL).

SQL är det kommandospråk som används för att lägga till, ta bort, uppdatera och returnera information som lagras i databasen, samt ändra kärndatabasskomponenter som att lägga till, radera eller ändra tabeller eller index.

Utgångspunkt

Om du inte redan har någon förtrogenhet med Access eller en annan RDBMS, föreslår jag att du börjar med dessa resurser innan du fortsätter:

  • Så Vad är en databas? Så vad är en databas, hur som helst? [MakeUseOf Explains] Så vad är en databas, hur som helst? [MakeUseOf Förklarar] För en programmerare eller en teknikentusiast är begreppet en databas något som verkligen kan tas för givet. Men för många människor är begreppet en databas i sig lite utländsk ... Läs mer där Ryan Dube använder Excel för att visa grunderna i relationella databaser.
  • En snabbguide för att komma igång med Microsoft Access 2007 En snabbguide för att komma igång med Microsoft Access 2007 En snabbguide för att komma igång med Microsoft Access 2007 Läs mer vilket är en överblick över Access och de komponenter som utgör en Access-databas.
  • En snabb handledning till tabeller i Microsoft Access 2007 En snabb handledning till tabeller i Microsoft Access 2007 En snabb handledning till tabeller i Microsoft Access 2007 Read More tar en titt på att skapa din första databas och tabeller för att lagra din strukturerade data.
  • En snabb handledning om frågor i Microsoft Access 2007 En snabb handledning om frågor i Microsoft Access 2007 En snabb handledning om frågor i Microsoft Access 2007 Read More tittar på sättet att returnera specifika delar av data som lagras i databasborden.

Att ha en grundläggande förståelse för de begrepp som ges i dessa artiklar gör följande lite lättare att smälta.

Databasrelationer och normalisering

Tänk dig att du driver ett företag som säljer 50 olika typer av widgets över hela världen. Du har en kundbas på 1 250 och i en genomsnittlig månad säljer 10 000 widgets till dessa kunder. Du använder för närvarande ett enda kalkylblad för att spåra alla dessa försäljningar - effektivt en enda databastabell. Och varje år läggs tusentals rader till ditt kalkylblad.

Ovanstående bilder är en del av det ordningsspårarkalkylblad du använder. Nu säg att båda dessa kunder köper widgets från dig flera gånger om året så att du har långt fler rader för dem båda.

Om Joan Smith gifter sig med Ted Baines och tar sitt efternamn måste varje rad som innehåller hennes namn nu ändras. Problemet är sammansatt om du råkar ha två olika kunder med namnet "Joan Smith". Det har just blivit mycket svårare att hålla dina försäljningsdata konsekvent på grund av en ganska vanlig händelse.

Genom att använda en databas och normalisera data kan vi separera ut objekt i flera tabeller som inventering, kunder och order.

Om vi ​​bara tittar på klientdelen av vårt exempel skulle vi ta bort kolumnerna för kundnamn och klientadress och lägga dem in i ett nytt bord. I bilden ovan har jag också brutit ut saker bättre för mer granulär tillgång till data. Den nya tabellen innehåller också en kolumn för en primär nyckel (ClientID) - ett nummer som kommer att användas för att komma åt varje rad i denna tabell.

I den ursprungliga tabellen där vi tog bort den här informationen skulle vi lägga till en kolumn för en Foreign Key (ClientID) som är det som länkar till den rätta raden som innehåller informationen för den här kunden.

När Joan Smith ändrar sitt namn till Joan Baines, behöver ändringen bara göras en gång i klientbordet. Varje annan referens från sammanställda tabeller kommer att dra rätt kundnamn och en rapport som tittar på vad Joan har köpt under de senaste 5 åren kommer att få alla order under både hennes jungfru och gifta namn utan att behöva ändra hur rapporten skapas.

Som en extra fördel minskar detta också den totala mängden lagring som förbrukas.

Gå med i typerna

SQL definierar fem olika typer av samlingar: INNER, VÄNSTER UTFÖR, RÄTT UTAN, FULL YTTER OCH KROSS. Det yttre sökordet är valfritt i SQL-satsen.

Microsoft Access tillåter användningen av INNER (standard), VÄNSTER UTFÖR, RÄDRE UTRE och KROSS. FULL OUTER stöds inte som sådan, men genom att använda VÄNSTER UTFÖR, UNION ALL, och RIGHT OUTER, kan den vara faked till kostnaden för fler CPU-cykler och I / O-operationer.

Utgången från ett CROSS-medlemskap innehåller varje rad i vänstra bordet parat med varje rad i den högra tabellen. Den enda gången jag någonsin sett en CROSS-anslutning används är under belastningstestning av databasservrar.

Låt oss ta en titt på hur de grundläggande anslagen fungerar, så kommer vi att ändra dem för att passa våra behov.

Låt oss börja med att skapa två tabeller, ProdA och ProdB, med följande designegenskaper.

AutoNumber är ett automatiskt inkrementellt långt heltal som tilldelas poster som de läggs till i tabellen. Textalternativet ändrades inte, så det kommer att acceptera en textsträng upp till 255 tecken långt.

Nu fyller du dem med viss data.

För att visa skillnaderna i hur de 3 sammanslagna typerna fungerar har jag raderat inlägg 1, 5 och 8 från ProdA.

Skapa sedan en ny fråga genom att gå till Skapa> Query Design. Välj båda tabellerna i dialogrutan Visa tabell och klicka på Lägg till, sedan Stänga.

Klicka på ProductID i tabellen ProdA, dra den till ProductID i tabellen ProdB och släpp musknappen för att skapa förhållandet mellan tabellerna.

Högerklicka på raden mellan tabellerna som representerar förhållandet mellan objekten och välj Bli medlem Egenskaper.

Som standard väljs anslutningstyp 1 (INNER). Alternativ 2 är en VÄNSTER OUTER-anslutning och 3 är en RIGHT OUTER-anslutning.

Vi kommer att titta på INNER-medlemmen först, så klicka på OK för att avvisa dialogrutan.

Markera de fält som vi vill se i listrutorna i frågeformuläret.

När vi kör sökningen (den röda utropstiden i bandet), kommer den att visa fältet Produktnamn från båda tabellerna med värdet från tabellen ProdA i den första kolumnen och ProdB i den andra.

Observera att resultaten endast visar värden där ProductID är lika i båda tabellerna. Trots att det finns en post för ProductID = 1 i tabellen ProdB visas den inte i resultaten eftersom ProductID = 1 inte finns i tabellen ProdA. Detsamma gäller för ProductID = 11. Det finns i tabellen ProdA men inte i tabellen ProdB.

Genom att använda Visa-knappen på bandet och byta till SQL View kan du se SQL-frågan genererad av formgivaren som används för att få dessa resultat.

SELECT ProdA.ProductName, ProdB.ProductName FRÅN ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Gå tillbaka till Design View, ändra anslutningstyp till 2 (VÄNSTER UTFÖR). Kör frågan för att se resultaten.

Som du kan se är varje post i tabellen ProdA representerad i resultaten medan endast de i ProdB som har en matchande ProductID-post i tabellen ProdB visas i resultaten.

Blankutrymmet i kolumnen ProdB.ProductName är ett specialvärde (NULL) eftersom det inte finns ett matchande värde i tabellen ProdB. Detta kommer att bli viktigt senare.

VÄLJ ProdA.Produktnamn, ProdB.Produktnamn FRÅN ProdA VÄNSTER GÅNG ProdB ON ProdA.ProductID = ProdB.ProductID;

Prova samma sak med den tredje typen av anslutning (RIGHT OUTER).

Resultaten visar allt från tabellen ProdB medan den visar tomma (kända som NULL) värden där ProdA-tabellen inte har ett matchande värde. Hittills kommer detta oss närmast de önskade resultaten i vår läsares fråga.

SELECT ProdA.ProductName, ProdB.ProductName FRÅN PRODA HÖGRE JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

Använda funktioner i en fråga

Resultaten av en funktion kan också returneras som en del av en fråga. Vi vill att en ny kolumn med namnet "Resultat" ska visas i vår resultatuppsättning. Dess värde kommer att vara innehållet i kolumnen Produktnamn i tabell ProdA om ProdA har ett värde (det är inte NULL), annars borde det tas från tabellen ProdB.

Funktionen omedelbar IF (IIF) kan användas för att generera detta resultat. Funktionen tar tre parametrar. Det första är ett villkor som måste utvärdera till ett sant eller falskt värde. Den andra parametern är det värde som ska returneras om villkoret är sant och den tredje parametern är det värde som ska returneras om villkoret är falskt.

Den fullständiga funktionskonstruktionen för vår situation ser ut så här:

IIF (ProdA.ProductID är Null, ProdB.ProductName, ProdA.ProductName)

Observera att tillståndsparametern inte söker efter jämlikhet. Ett Null-värde i en databas har inte ett värde som kan jämföras med något annat värde, inklusive en annan Null. Null är med andra ord inte lika med Null. Någonsin. För att komma över detta, kontrollerar vi istället värdet med hjälp av sökordet "Is".

Vi kunde också ha använt "Är inte noll" och ändrade ordningen för de sanna och falska parametrarna för att få samma resultat.

När du lägger in detta i Query Designer måste du skriva hela funktionen i fältet: post. För att få det att skapa kolumnen "Resultat" måste du använda ett alias. För att göra detta, förord ​​funktionen med "Resultat:" enligt följande skärmdump.

Den motsvarande SQL-koden för att göra detta skulle vara:

SELECT ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID är Null, ProdB.ProductName, ProdA.ProductName) som resultat FRÅN ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;

När vi kör denna fråga kommer det att producera dessa resultat.

Här ser vi för varje post där tabell ProdA har ett värde, det värdet återspeglas i kolumnen Resultat. Om det inte finns en post i ProdA-tabellen visas inmatningen från ProdB i Resultat vilket är exakt vad vår läsare frågade.

För mer resurser för att lära dig Microsoft Access, kolla in Joel Lee: Hur läser du Microsoft Access: 5 gratis online-resurser Hur man lär sig Microsoft Access: 5 gratis online-resurser Hur man lär sig Microsoft Access: 5 gratis online-resurser Behöver du hantera en stor mängd av data? Du bör titta på Microsoft Access. Våra gratis studieresurser kan hjälpa dig att komma igång och lära dig färdigheterna för mer komplexa projekt. Läs mer .