Slik skriver du Microsoft Access SQL-spørringer fra scratch

Microsoft Access er uten tvil det kraftigste verktøyet i hele Microsoft Office-pakken, men det mystifies (og noen ganger skremmer) Office Power-brukere. Med en brattere læringskurve enn Word eller Excel, hvordan skal noen vri på hodet rundt bruken av dette verktøyet? Denne uken vil Bruce Epper se på noen av problemene som er spurt av dette spørsmålet fra en av våre lesere.

En leser spør:

Jeg har problemer med å skrive et spørsmål i Microsoft Access.

Jeg har en database med to produkttabeller som inneholder en felles kolonne med en numerisk produktkode og et tilhørende produktnavn.

Jeg vil finne ut hvilke produkter fra tabell A som finnes i tabell B. Jeg vil legge til en kolonne som heter Resultater som inneholder produktnavnet fra tabell A hvis det eksisterer, og produktnavnet fra tabell B når det ikke eksisterer i tabell A.

Har du noen råd?

Bruces svar:

Microsoft Access er et databasebehandlingssystem (DBMS) designet for bruk på både Windows og Mac-maskiner. Det benytter Microsofts Jet databasemotor for databehandling og lagring. Det gir også et grafisk grensesnitt for brukere som nesten eliminerer behovet for å forstå Structured Query Language (SQL).

SQL er kommandospråket som brukes til å legge til, slette, oppdatere og returnere informasjon som er lagret i databasen, samt endre kjernedatabasekomponenter som å legge til, slette eller endre tabeller eller indekser.

Utgangspunktet

Hvis du ikke allerede har noen kjennskap til Access eller en annen RDBMS, foreslår jeg at du starter med disse ressursene før du fortsetter:

• Så Hva er en database? Så hva er en database, uansett? [MakeUseOf Forklarer] Så Hva er en database, uansett? [MakeUseOf Forklarer] For en programmerer eller en teknologientusiast er begrepet en database noe som virkelig kan tas for gitt. Men for mange mennesker er begrepet en database selv litt utenlandsk ... Les mer hvor Ryan Dube bruker Excel for å vise grunnleggende om relasjonsdatabaser.
• En hurtigveiledning for å komme i gang med Microsoft Access 2007 En rask guide for å komme i gang med Microsoft Access 2007 En rask guide for å komme i gang med Microsoft Access 2007 Les mer som er en oversikt over tilgang på høyt nivå og komponentene som omfatter en Access-database.
• En rask opplæring til tabeller i Microsoft Access 2007 En rask opplæring til tabeller i Microsoft Access 2007 En rask opplæring til tabeller i Microsoft Access 2007 Read More tar en titt på å lage din første database og tabeller for å lagre dine strukturerte data.
• En rask opplæring i spørringer i Microsoft Access 2007 En rask opplæring i spørringer i Microsoft Access 2007 En rask opplæring i spørringer i Microsoft Access 2007 Read More ser på midler for å returnere bestemte deler av dataene som er lagret i databasetabellene.

Å ha en grunnleggende forståelse av konseptene som er gitt i disse artiklene, gjør følgende litt lettere å fordøye.

Database relasjoner og normalisering

Tenk deg at du driver et selskap som selger 50 forskjellige typer widgets over hele verden. Du har en kundebase på 1.250 og i en gjennomsnittlig måned selger 10.000 widgets til disse klientene. Du bruker for øyeblikket et enkelt regneark for å spore alle disse salgene - effektivt en enkelt database tabell. Og hvert år legger du til tusenvis av rader i regnearket ditt.

Ovennevnte bilder er en del av ordrenes sporing av regnearket du bruker. Nå sier begge disse klientene kjøpe widgets fra deg flere ganger i året, så du har langt flere rader for begge.

Hvis Joan Smith gifter seg med Ted Baines og tar etternavnet, må hver eneste rad som inneholder hennes navn, endres. Problemet er sammensatt hvis du har to forskjellige klienter med navnet 'Joan Smith'. Det har nettopp blitt mye vanskeligere å holde salgsdata konsistente på grunn av en ganske vanlig begivenhet.

Ved å bruke en database og normalisere dataene, kan vi skille ut elementer i flere tabeller som lager, klienter og ordrer.

Bare se på klientdelen av vårt eksempel, vi fjerner kolonnene for Klientnavn og Klientadresse og legger dem inn i et nytt bord. I bildet ovenfor har jeg også ødelagt ting bedre for mer granulær tilgang til dataene. Den nye tabellen inneholder også en kolonne for en primærnøkkel (ClientID) - et nummer som vil bli brukt til å få tilgang til hver rad i denne tabellen.

I den opprinnelige tabellen der vi fjernet disse dataene, ville vi legge til en kolonne for en Foreign Key (ClientID) som er det som lenker til den riktige raden som inneholder informasjonen for denne bestemte klienten.

Nå, når Joan Smith endrer navnet til Joan Baines, må endringen bare gjøres en gang i klientbordet. Hvert annet referanse fra sammenkomne tabeller vil trekke riktig klientnavn og en rapport som ser på hva Joan har kjøpt de siste 5 årene vil få alle bestillingene under både hennes jomfru og giftede navn uten å måtte endre hvordan rapporten genereres.

Som en ekstra fordel reduserer dette også den totale mengden lagring som forbrukes.

Bli med i typer

SQL definerer fem forskjellige typer sammenføyninger: INNER, VENSTRE YTTER, RIGHT OUTER, FULL OUTER og CROSS. Det ytre søkeordet er valgfritt i SQL-setningen.

Microsoft Access tillater bruk av INNER (standard), LEFT OUTER, RIGHT OUTER og CROSS. FULL YTTER er ikke støttet som sådan, men ved å bruke VENSTRE YTRE, UNION ALL og RIGHT OUTER, kan det være faked på bekostning av flere CPU-sykluser og I / O-operasjoner.

Utgangen av en CROSS-sammenføyning inneholder hver rad i venstre bord sammen med hver rad i det høyre bordet. Den eneste gangen jeg noensinne har sett en CROSS bli med er brukt under belastningstesting av databaseservere.

La oss se på hvordan de grunnleggende sammenhenger fungerer, og vi vil endre dem slik at de passer til våre behov.

La oss starte med å lage to tabeller, ProdA og ProdB, med følgende designegenskaper.

AutoNumber er et automatisk stigende langt heltall tilordnet oppføringer som de legges til i tabellen. Tekstalternativet ble ikke endret, så det vil akseptere en tekststreng på opptil 255 tegn.

Nå, fyll dem med noen data.

For å vise forskjellene i hvordan de 3 sammenkoblede typene fungerer, har jeg slettet oppføringer 1, 5 og 8 fra ProdA.

Deretter opprett en ny spørring ved å gå til Opprett> Query Design. Velg begge tabellene i dialogboksen Vis tabell og klikk på Legg til, deretter Lukk.

Klikk på ProductID i tabellen ProdA, dra den til ProductID i tabellen ProdB og slipp museknappen for å opprette forholdet mellom tabellene.

Høyreklikk på linjen mellom tabellene som representerer forholdet mellom elementene og velg Bli med Egenskaper.

Som standard er valgt type 1 (INNER) valgt. Alternativ 2 er en LEFT OUTER-tilkobling og 3 er en RIGHT OUTER-tilkobling.

Vi vil se på INNER bli med først, så klikk OK for å avvise dialogboksen.

I søkedesigneren velger du feltene vi vil se fra rullegardinlistene.

Når vi kjører spørringen (det røde utropstegnet i båndet), vil det vise feltet ProductName fra begge tabellene med verdien fra tabellen ProdA i den første kolonnen og ProdB i den andre.

Legg merke til at resultatene bare viser verdier der ProductID er lik i begge tabellene. Selv om det er en oppføring for ProductID = 1 i tabell ProdB, vises den ikke i resultatene, siden ProductID = 1 ikke finnes i tabellen ProdA. Det samme gjelder ProductID = 11. Det finnes i tabellen ProdA, men ikke i tabellen ProdB.

Ved å bruke View-knappen på båndet og bytte til SQL View, kan du se SQL-spørringen generert av designeren som brukes til å få disse resultatene.

VELG ProdA.Produktnavn, ProdB.Produktnavn FRA ProdA INNER KJØP ProdB ON ProdA.ProductID = ProdB.ProductID;

Gå tilbake til Designvisning, endre tilkoblingstypen til 2 (VENSTRE UTRE). Kjør spørringen for å se resultatene.

Som du kan se, er hver oppføring i tabellen ProdA representert i resultatene, mens bare de i ProdB som har en tilsvarende ProductID-oppføring i tabellen ProdB, vises i resultatene.

Det tomme mellomrom i kolonnen ProdB.ProductName er en spesiell verdi (NULL) siden det ikke er en tilsvarende verdi i tabellen ProdB. Dette vil bli viktig senere.

VELG ProdA.Produktnavn, ProdB.Produktnavn FRA ProdA VENSTRE BLI MED PRODB PÅ ProdA.ProductID = ProdB.ProductID;

Prøv det samme med den tredje typen delta (RIGHT OUTER).

Resultatene viser alt fra tabell ProdB mens det vises tomme (kjent som NULL) verdier der ProdA-tabellen ikke har en samsvarende verdi. Så langt bringer dette oss nærmest de ønskede resultatene i leserens spørsmål.

VELG ProdA.Produktnavn, ProdB.Produktnavn FRA ProdA RIGHT JOIN ProdB PÅ ProdA.ProductID = ProdB.ProductID;

Bruke funksjoner i en spørring

Resultatene av en funksjon kan også returneres som en del av en spørring. Vi ønsker at en ny kolonne som heter "Resultater" skal vises i vårt resultatsett. Verdien vil være innholdet i ProductName-kolonnen i tabell ProdA hvis ProdA har en verdi (det er ikke null), ellers bør det tas fra tabell ProdB.

Umiddelbar IF (IIF) -funksjonen kan brukes til å generere dette resultatet. Funksjonen tar tre parametere. Den første er en betingelse som må evalueres til en sann eller falsk verdi. Den andre parameteren er verdien som skal returneres dersom tilstanden er True, og den tredje parameteren er verdien som skal returneres dersom tilstanden er falsk.

Den fulle funksjonskonstruksjonen for vår situasjon ser slik ut:

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

Legg merke til at betingelsesparameteren ikke ser etter likestilling. En null-verdi i en database har ikke en verdi som kan sammenlignes med annen verdi, inkludert en annen null. Med andre ord, Null er ikke lik null. Noensinne. For å komme forbi dette, kontrollerer vi i stedet verdien med "Is" -ordet.

Vi kunne også ha brukt 'Er ikke null' og endret rekkefølgen av de sanne og falske parametrene for å få det samme resultatet.

Når du legger dette inn i spørringsdesigneren, må du skrive hele funksjonen i feltet: For å få det til å opprette kolonnen "Resultater", må du bruke et alias. For å gjøre dette, forord funksjonen med 'Resultater:' som vist i følgende skjermbilde.

Den tilsvarende SQL-koden for å gjøre dette ville være:

SELECT ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID er Null, ProdB.ProductName, ProdA.ProductName) AS RESULTATER FRA PRODA HØYRE STOPP ProdB ON ProdA.ProductID = ProdB.ProductID;

Nå, når vi kjører dette spørsmålet, vil det produsere disse resultatene.

Her ser vi for hver oppføring hvor tabell ProdA har en verdi, den verdien reflekteres i kolonnen Results. Hvis det ikke er en oppføring i ProdA-tabellen, vises oppføringen fra ProdB i Resultater som er akkurat det som leseren spurte.

For mer ressurser for å lære Microsoft Access, sjekk ut Joel Lee. Slik lærer du Microsoft Access: 5 gratis nettbaserte ressurser. Slik lærer du Microsoft Access: 5 gratis nettbaserte ressurser. Slik lærer du Microsoft Access: 5 gratis Internettressurser. Er du nødt til å administrere en stor mengde av data? Du bør se på Microsoft Access. Våre gratis studie ressurser kan hjelpe deg med å komme i gang og lære ferdighetene til mer komplekse prosjekter. Les mer .