Sådan skriver du Microsoft Access SQL -forespørgsler fra bunden

Sådan skriver du Microsoft Access SQL -forespørgsler fra bunden

Microsoft Access er uden tvivl det mest kraftfulde værktøj i hele Microsoft Office -pakken, men alligevel mystificerer (og undertiden skræmmer) Office -strømbrugere. Med en stejlere indlæringskurve end Word eller Excel, hvordan skal nogen vikle deres hoved omkring brugen af ​​dette værktøj? I denne uge vil Bruce Epper se på nogle af de spørgsmål, der er ansporet af dette spørgsmål fra en af ​​vores læsere.





En læser spørger:

Jeg har problemer med at skrive en forespørgsel i Microsoft Access. Jeg har en database med to produkttabeller, der indeholder en fælles kolonne med en numerisk produktkode og et tilhørende produktnavn. Jeg vil finde ud af, hvilke produkter fra tabel A der kan findes i tabel B. Jeg vil tilføje en kolonne med navnet Results, der indeholder produktnavnet fra tabel A, hvis det findes, og produktnavnet fra tabel B, når det ikke findes i tabel A. Har du nogle råd?



Bruce's svar:

Microsoft Access er et Database Management System (DBMS) designet til brug på både Windows- og Mac -maskiner. Det anvender Microsofts Jet -databasemotor til databehandling og opbevaring. Det giver også en grafisk grænseflade til brugere, der næsten eliminerer behovet for at forstå Structured Query Language (SQL).





SQL er kommandosproget, der bruges til at tilføje, slette, opdatere og returnere oplysninger, der er gemt i databasen, samt ændre kernedatabasekomponenter, såsom tilføjelse, sletning eller ændring af tabeller eller indeks.

Udgangspunktet

Hvis du ikke allerede har kendskab til Access eller et andet RDBMS, vil jeg foreslå, at du starter med disse ressourcer, før du fortsætter:



At have en grundlæggende forståelse af begreberne i disse artikler vil gøre følgende lidt lettere at fordøje.

Databaseforhold og normalisering

Forestil dig, at du driver et firma, der sælger 50 forskellige typer widgets over hele verden. Du har en kundebase på 1.250 og sælger i en gennemsnitlig måned 10.000 widgets til disse klienter. Du bruger i øjeblikket et enkelt regneark til at spore alle disse salg - effektivt en enkelt databasetabel. Og hvert år tilføjer tusindvis af rækker til dit regneark.



Ovenstående billeder er en del af det ordresporings regneark, du bruger. Sig nu, at begge disse kunder køber widgets fra dig flere gange om året, så du har langt flere rækker til dem begge.



Hvis Joan Smith gifter sig med Ted Baines og tager sit efternavn, skal hver eneste række, der indeholder hendes navn, nu ændres. Problemet forstærkes, hvis du tilfældigvis har to forskellige klienter med navnet 'Joan Smith'. Det er bare blevet meget sværere at holde dine salgsdata konsistente på grund af en temmelig almindelig begivenhed.

Ved at bruge en database og normalisere dataene kan vi adskille varer i flere tabeller såsom beholdning, klienter og ordrer.

Bare ved at se på klientdelen af ​​vores eksempel, ville vi fjerne kolonnerne for klientnavn og klientadresse og sætte dem i en ny tabel. På billedet ovenfor har jeg også brudt tingene bedre ud for mere detaljeret adgang til dataene. Den nye tabel indeholder også en kolonne for en primær nøgle (ClientID) - et tal, der vil blive brugt til at få adgang til hver række i denne tabel.

I den originale tabel, hvor vi fjernede disse data, tilføjede vi en kolonne til en fremmed nøgle (ClientID), som er det, der linker til den rigtige række, der indeholder oplysningerne for denne bestemte klient.

Når Joan Smith nu skifter navn til Joan Baines, skal ændringen kun foretages én gang i klienttabellen. Hver anden reference fra sammenføjede tabeller trækker det korrekte klientnavn, og en rapport, der ser på, hvad Joan har købt i de sidste 5 år, får alle ordrerne under både hendes pigenavn og gifte navne uden at skulle ændre, hvordan rapporten genereres .

Som en ekstra fordel reducerer dette også den samlede mængde lagret forbrugt.

Deltag i typer

SQL definerer fem forskellige typer joins: INDRE, VENSTRE YDRE, HØJRE YDRE, FULL YDRE og CROSS. Søgeordet OUTER er valgfrit i SQL -sætningen.

Microsoft Access tillader brug af INDVENDIG (standard), VENSTRE YDRE, HØJRE YDRE og KORS. FULL OUTER understøttes ikke som sådan, men ved at bruge VENSTRE YDRE, UNION ALL og HØJRE YDRE kan det forfalskes på bekostning af flere CPU -cyklusser og I/O -operationer.

Outputtet af et CROSS -join indeholder hver række i den venstre tabel parret med hver række i den højre tabel. Den eneste gang jeg nogensinde har set et CROSS -join blive brugt, er under belastningstest af databaseservere.

Lad os se på, hvordan de grundlæggende joins fungerer, så vil vi ændre dem, så de passer til vores behov.

Lad os starte med at oprette to tabeller, ProdA og ProdB, med følgende designegenskaber.

Autonummeret er et automatisk langvarigt heltal, der tildeles poster, når de tilføjes til tabellen. Indstillingen Tekst blev ikke ændret, så den accepterer en tekststreng på op til 255 tegn.

Udfyld dem nu med nogle data.

For at vise forskellene i hvordan de 3 join -typer fungerer, har jeg slettet poster 1, 5 og 8 fra ProdA.

Næste, oprette en ny forespørgsel ved at gå til Opret> Forespørgselsdesign . Vælg begge tabeller i dialogboksen Vis tabel og klik på Tilføj , derefter Tæt .

Klik på ProductID i tabel ProdA, træk det til ProductID i tabel ProdB og slip museknappen for at oprette forholdet mellem tabellerne.

Højreklik på linjen mellem tabellerne, der repræsenterer forholdet mellem elementerne og vælg Deltag egenskaber .

Som standard er join type 1 (INNER) valgt. Mulighed 2 er en VENSTRE YDRE joinforbindelse og 3 er en HØJRE YDRE joinforbindelse.

Vi vil først se på INNER join, så klik på OK for at afvise dialogen.

I forespørgselsdesigneren skal du vælge de felter, vi vil se fra rullelisterne.

Når vi kører forespørgslen (det røde udråbstegn i båndet), viser det feltet ProductName fra begge tabeller med værdien fra tabel ProdA i den første kolonne og ProdB i den anden.

Bemærk, at resultaterne kun viser værdier, hvor ProductID er lig i begge tabeller. Selvom der er en post for ProductID = 1 i tabel ProdB, vises den ikke i resultaterne, da ProductID = 1 ikke findes i tabel ProdA. Det samme gælder for ProductID = 11. Den findes i tabel ProdA, men ikke i tabel ProdB.

Ved at bruge knappen Vis på båndet og skifte til SQL View kan du se SQL -forespørgslen genereret af designeren, der bruges til at få disse resultater.

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

Når du går tilbage til designvisning, ændres sammenføjningstypen til 2 (LEFT OUTER). Kør forespørgslen for at se resultaterne.

Som du kan se, er hver post i tabel ProdA repræsenteret i resultaterne, mens kun dem i ProdB, der har en matchende ProductID -post i tabel ProdB, vises i resultaterne.

Det tomme mellemrum i kolonnen ProdB.ProductName er en særlig værdi (NULL), da der ikke er en matchende værdi i tabel ProdB. Dette vil vise sig vigtigt senere.

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

Prøv det samme med den tredje type joinforbindelse (HØJRE YDRE).

Resultaterne viser alt fra tabel ProdB, mens det viser tomme (kendt som NULL) værdier, hvor ProdA -tabellen ikke har en matchende værdi. Indtil videre bringer dette os tættest på de resultater, der ønskes i vores læsers spørgsmål.

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

Brug af funktioner i en forespørgsel

Resultaterne af en funktion kan også returneres som en del af en forespørgsel. Vi ønsker, at en ny kolonne med navnet 'Resultater' skal vises i vores resultatsæt. Dens værdi vil være indholdet i ProductName -kolonnen i tabel ProdA, hvis ProdA har en værdi (den er ikke NULL), ellers bør den hentes fra tabel ProdB.

Funktionen Umiddelbar HVIS (IIF) kan bruges til at generere dette resultat. Funktionen tager tre parametre. Den første er en betingelse, der skal evalueres til en sand eller falsk værdi. Den anden parameter er den værdi, der skal returneres, hvis betingelsen er sand, og den tredje parameter er den værdi, der skal returneres, hvis betingelsen er Falsk.

Den fulde funktionskonstruktion til vores situation ser sådan ud:

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

Bemærk, at tilstandsparameteren ikke kontrollerer lighed. En Null -værdi i en database har ikke en værdi, der kan sammenlignes med en anden værdi, herunder en anden Null. Med andre ord er Null ikke lig med Null. Nogensinde. For at komme forbi dette kontrollerer vi i stedet værdien ved hjælp af søgeordet 'Is'.

Vi kunne også have brugt 'Er ikke nul' og ændret rækkefølgen af ​​de sande og falske parametre for at få det samme resultat.

Når du indsætter dette i Query Designer, skal du skrive hele funktionen i feltet:. For at få det til at oprette kolonnen 'Resultater', skal du bruge et alias. For at gøre dette skal du indlede funktionen med 'Resultater:' som vist på følgende skærmbillede.

Den tilsvarende SQL -kode til at gøre dette ville være:

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;

Når vi nu kører denne forespørgsel, vil den producere disse resultater.

gratis sms -apps til Android -tablets

Her ser vi for hver post, hvor tabel ProdA har en værdi, den værdi afspejles i kolonnen Resultater. Hvis der ikke er en post i ProdA -tabellen, vises posten fra ProdB i Resultater, hvilket er præcis, hvad vores læser spurgte.

For flere ressourcer til at lære Microsoft Access, tjek Joel Lee's Sådan lærer du Microsoft Access: 5 gratis online ressourcer.

Del Del Tweet E -mail Er det værd at opgradere til Windows 11?

Windows er blevet redesignet. Men er det nok til at overbevise dig om at skifte fra Windows 10 til Windows 11?

Læs Næste Relaterede emner
  • Produktivitet
  • Spørg eksperterne
Om forfatteren Bruce Epper(13 artikler udgivet)

Bruce har leget med elektronik siden 70'erne, computere siden begyndelsen af ​​80'erne og besvaret præcist spørgsmål om teknologi, han ikke har brugt eller set hele tiden. Han irriterer sig også ved at forsøge at spille guitar.

Mere fra Bruce Epper

Abonner på vores nyhedsbrev

Tilmeld dig vores nyhedsbrev for at få tekniske tips, anmeldelser, gratis e -bøger og eksklusive tilbud!

Klik her for at abonnere