Så här skriver du Microsoft Access SQL -frågor från början

Så här skriver du Microsoft Access SQL -frågor från början

Microsoft Access är utan tvekan det mest kraftfulla verktyget i hela Microsoft Office -paketet, men det mystifierar (och ibland skrämmer) Office -strömanvändare. Med en brantare inlärningskurva än Word eller Excel, hur ska någon kunna linda huvudet kring användningen av det här verktyget? Den här veckan kommer Bruce Epper att titta på några av de frågor som orsakas 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 kan hittas i tabell B. Jag vill lägga till en kolumn med namnet Results som innehåller produktnamnet från tabell A om det finns, och produktnamnet från tabell B när det inte finns i tabell A. Har du några råd?



Bruce svar:

Microsoft Access är ett databashanteringssystem (DBMS) utformat för användning på både Windows- och Mac -maskiner. Den 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 kommandospråket som används för att lägga till, ta bort, uppdatera och returnera information som lagras i databasen samt ändra kärndatabaskomponenter som att lägga till, ta bort eller ändra tabeller eller index.

Startpunkt

Om du inte redan känner till Access eller annat RDBMS föreslår jag att du börjar med dessa resurser innan du fortsätter:



Att ha en grundläggande förståelse för begreppen i dessa artiklar kommer att göra 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å 1250 och säljer i genomsnitt en 10000 widgets till dessa kunder under en månad. Du använder för närvarande ett enda kalkylblad för att spåra alla dessa försäljningar - faktiskt en enda databastabell. Och varje år lägger tusentals rader till ditt kalkylblad.



Ovanstående bilder är en del av det kalkylblad för beställningsspårning du använder. Säg nu att båda dessa kunder köper widgets från dig flera gånger om året så att du har mycket 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 förvärras om du råkar ha två olika klienter med namnet 'Joan Smith'. Det har precis blivit mycket svårare att hålla dina säljdata konsekventa på grund av en ganska vanlig händelse.

Genom att använda en databas och normalisera data kan vi dela ut objekt i flera tabeller som lager, klienter och order.

Bara genom att titta på klientdelen av vårt exempel skulle vi ta bort kolumnerna för klientnamn och klientadress och lägga dem i en ny tabell. På bilden ovan har jag också brutit ut saker bättre för mer detaljerad åtkomst 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 tabellen.

I den ursprungliga tabellen där vi tog bort dessa data skulle vi lägga till en kolumn för en främmande nyckel (ClientID), vilket är vad som länkar till rätt rad som innehåller informationen för just den här klienten.

När Joan Smith byter namn till Joan Baines behöver ändringen bara göras en gång i klientbordet. Varannan referens från sammanfogade tabeller drar rätt klientnamn och en rapport som tittar på vad Joan har köpt de senaste 5 åren kommer att få alla beställningar under både hennes flicknamn och gifta namn utan att behöva ändra hur rapporten genereras .

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

Gå med i typer

SQL definierar fem olika typer av kopplingar: INNER, VÄNSTER YTTER, HÖGER UTAN, FULL YTTRE och CROSS. Nyckelordet YTTRE är valfritt i SQL -satsen.

Microsoft Access tillåter användning av INNER (standard), VÄNSTER YTTER, HÖGER UTAN och KORS. FULL YUTER stöds inte som sådan, men genom att använda VÄNSTER YTTER, UNION ALL och RIGHT YUTER kan det förfalskas på bekostnad av fler CPU -cykler och I/O -operationer.

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

Låt oss ta en titt på hur de grundläggande fogarna fungerar, sedan 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 långsamt heltal som tilldelas poster när de läggs till i tabellen. Alternativet Text ändrades inte, så det accepterar en textsträng på upp till 255 tecken.

Nu fyller du i dem med lite data.

För att visa skillnaderna i hur de tre kopplingstyperna fungerar har jag tagit bort poster 1, 5 och 8 från ProdA.

Nästa, skapa en ny fråga genom att gå till Skapa> Frågedesign . Välj båda tabellerna i dialogrutan Visa tabell och klicka på Lägg till , då Stänga .

Klicka på ProductID i tabell ProdA, dra det till ProductID i tabell ProdB och släpp musknappen för att skapa relationen mellan tabellerna.

Högerklicka på raden mellan tabellerna som representerar förhållandet mellan objekten och välj Gå med egenskaper .

Som standard är anslutningstyp 1 (INNER) vald. Alternativ 2 är en VÄNSTER YTTRE koppling och 3 är en HÖGER YTTRE anslutning.

Vi kommer att titta på INNER -anslutningen först, så klicka på OK för att stänga dialogrutan.

I frågeutformaren väljer du de fält vi vill se från listrutorna.

När vi kör frågan (den röda utropstecknet i menyfliksområdet) visas fältet ProductName 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. Även om det finns en post för ProductID = 1 i tabell ProdB, visas den inte i resultaten eftersom ProductID = 1 inte finns i tabell ProdA. Detsamma gäller ProductID = 11. Det finns i tabellen ProdA men inte i tabellen ProdB.

Genom att använda knappen Visa på menyfliksområdet och växla till SQL View kan du se SQL -frågan som skapats av designern som används för att få dessa resultat.

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

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

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

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

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

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

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

SELECT ProdA.ProductName, ProdB.ProductName FROM ProdA RIGHT 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 ProductName i tabell ProdA om ProdA har ett värde (det är inte NULL), annars bör det tas från tabell 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ärderas till ett sant eller falskt värde. Den andra parametern är värdet som ska returneras om villkoret är True, och den tredje parametern är värdet som ska returneras om villkoret är Falskt.

Hela funktionskonstruktionen för vår situation ser ut så här:

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

Observera att villkorsparametern inte kontrollerar om det är lika. Ett Null -värde i en databas har inte ett värde som kan jämföras med något annat värde, inklusive ett annat Null. Med andra ord är Null inte lika med Null. Någonsin. För att komma förbi detta kontrollerar vi istället värdet med hjälp av sökordet 'Is'.

Vi kunde också ha använt 'Is Not Null' och ändrat 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 in hela funktionen i fältet: posten. För att få den att skapa kolumnen 'Resultat' måste du använda ett alias. För att göra detta, förorda funktionen med 'Resultat:' som visas i följande skärmdump.

Motsvarande SQL -kod för att göra detta skulle vara:

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;

Nu, när vi kör den här frågan, kommer den att ge dessa resultat.

hur man öppnar terminalen på chromebook

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

För mer resurser för att lära sig Microsoft Access, kolla in Joel Lees How to Learn Microsoft Access: 5 gratis onlineresurser.

Dela med sig Dela med sig Tweet E-post Är det värt att uppgradera till Windows 11?

Windows har gjorts om. Men är det tillräckligt för att övertyga dig om att byta från Windows 10 till Windows 11?

Läs Nästa Relaterade ämnen
  • Produktivitet
  • Fråga experterna
Om författaren Bruce Epper(13 artiklar publicerade)

Bruce har lekt med elektronik sedan 70 -talet, datorer sedan början av 80 -talet och svarat exakt på frågor om teknik som han inte har använt eller sett hela tiden. Han irriterar sig också genom att försöka spela gitarr.

Mer från Bruce Epper

Prenumerera på vårt nyhetsbrev

Gå med i vårt nyhetsbrev för tekniska tips, recensioner, gratis e -böcker och exklusiva erbjudanden!

Klicka här för att prenumerera