Slik skriver du Microsoft Access SQL -spørringer fra bunnen av

Slik skriver du Microsoft Access SQL -spørringer fra bunnen av

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





En leser spør:

Jeg har problemer med å skrive en spørring i Microsoft Access. Jeg har en database med to produkttabeller som inneholder en felles kolonne med en numerisk produktkode og et tilknyttet produktnavn. Jeg vil finne ut hvilke produkter fra tabell A som kan bli funnet i tabell B. Jeg vil legge til en kolonne med navnet Results som inneholder produktnavnet fra tabell A hvis det finnes, og produktnavnet fra tabell B når det ikke finnes i tabell A. Har du noen råd?



Bruce svar:

Microsoft Access er et databasebehandlingssystem (DBMS) designet for bruk på både Windows- og Mac -maskiner. Den bruker 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 litt kjennskap til Access eller et annet RDBMS, vil jeg foreslå at du starter med disse ressursene før du fortsetter:



Å ha en grunnleggende forståelse av konseptene i disse artiklene vil gjøre følgende litt lettere å fordøye.

Databaseforhold og normalisering

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



Bildene ovenfor er en del av ordresporingsregnearket du bruker. Si nå at begge disse kundene kjøper widgets fra deg flere ganger i året, så du har langt flere rader for dem begge.



Hvis Joan Smith gifter seg med Ted Baines og tar etternavnet sitt, må hver rad som inneholder navnet hennes nå endres. Problemet blir større hvis du tilfeldigvis har to forskjellige klienter med navnet 'Joan Smith'. Det har nettopp blitt mye vanskeligere å holde salgsdataene dine konsistente på grunn av en ganske vanlig hendelse.

Ved å bruke en database og normalisere dataene, kan vi dele ut varer i flere tabeller som beholdning, klienter og bestillinger.

Bare ved å se på klientdelen av vårt eksempel, ville vi fjerne kolonnene for klientnavn og klientadresse og sette dem inn i en ny tabell. På bildet ovenfor har jeg også brutt ting ut bedre for mer detaljert tilgang til dataene. Den nye tabellen inneholder også en kolonne for en primærnøkkel (ClientID) - et tall som skal brukes for å få tilgang til hver rad i denne tabellen.

I den originale tabellen der vi fjernet disse dataene, ville vi legge til en kolonne for en fremmed nøkkel (ClientID), som er det som lenker til den riktige raden som inneholder informasjonen for denne klienten.

Når Joan Smith endrer navn til Joan Baines, trenger endringen bare gjøres én gang i klientbordet. Hver annen referanse fra sammenføyde tabeller vil trekke det riktige klientnavnet, og en rapport som ser på hva Joan har kjøpt de siste 5 årene, vil få alle bestillingene under både jomfru- og gifte navn uten å måtte endre hvordan rapporten genereres .

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

Bli med i typer

SQL definerer fem forskjellige typer sammenføyninger: INNE, VENSTRE YTRE, HØYRE YTRE, FULL YTRE og KRUS. Søkeordet YTRE er valgfritt i SQL -setningen.

Microsoft Access tillater bruk av INNE (standard), VENSTRE YTRE, HØYRE YTRE og KRUS. FULL OUTER støttes ikke som sådan, men ved å bruke VENSTRE YTTER, UNION ALL og HØYRE YTTER kan det forfalskes på bekostning av flere CPU -sykluser og I/O -operasjoner.

Utgangen fra en CROSS -sammenføyning inneholder hver rad i tabellen til venstre sammenkoblet med hver rad i den høyre tabellen. Den eneste gangen jeg noen gang har sett en CROSS -sammenkobling brukt, er under lastetesting av databaseservere.

La oss se på hvordan de grunnleggende sammenføyningene fungerer, så vil vi endre dem for å passe våre behov.

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

Autonummeret er et langt heltall som automatisk tilskrives poster som blir lagt til i tabellen. Alternativet Tekst ble ikke endret, så det vil godta en tekststreng på opptil 255 tegn.

Nå, fyll dem med noen data.

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

Neste, opprette en ny spørring ved å gå til Opprett> Spørredesign . Velg begge tabellene fra dialogboksen Vis tabell og klikk på Legg til , deretter Lukk .

Klikk på ProductID i tabell ProdA, dra den til ProductID i tabell 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 join type 1 (INNER) valgt. Alternativ 2 er en VENSTRE YTRE sammenføyning og 3 er en HØYRE YTRE sammenføyning.

Vi vil se på INNER -sammenføyningen først, så klikk OK for å avvise dialogboksen.

I feltdesigneren velger du feltene vi vil se fra rullegardinlistene.

Når vi kjører spørringen (det røde utropstegnet på båndet), vil det vise ProductName -feltet 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 tabell ProdA. Det samme gjelder ProductID = 11. Den finnes i tabell ProdA, men ikke i tabell ProdB.

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

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

Når du går tilbake til designvisning, endrer du sammenføyningstypen til 2 (VENSTRE YTRE). Kjør spørringen for å se resultatene.

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

Tomrommet i kolonnen ProdB.ProductName er en spesiell verdi (NULL) siden det ikke er en matchende verdi i tabellen ProdB. Dette vil vise seg viktig senere.

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

Prøv det samme med den tredje typen join (RIGHT YUTER).

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

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

Bruke funksjoner i en forespørsel

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

Funksjonen Umiddelbar HVIS (IIF) kan brukes til å generere dette resultatet. Funksjonen tar tre parametere. Den første er en tilstand som må evalueres til en sann eller usann verdi. Den andre parameteren er verdien som skal returneres hvis betingelsen er True, og den tredje parameteren er verdien som skal returneres hvis betingelsen er False.

Hele funksjonskonstruksjonen for vår situasjon ser slik ut:

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

Legg merke til at tilstandsparameteren ikke sjekker likhet. En Null -verdi i en database har ikke en verdi som kan sammenlignes med andre verdier, inkludert en annen Null. Med andre ord, Null er ikke lik Null. Noen gang. For å komme forbi dette, sjekker vi i stedet verdien ved å bruke søkeordet 'Is'.

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

Når du legger dette inn i Query Designer, må du skrive inn hele funksjonen i feltet:. For å få den til å lage kolonnen 'Resultater', må du bruke et alias. For å gjøre dette, forord funksjonen med 'Resultater:' som vist på følgende skjermbilde.

Tilsvarende SQL -kode for å gjøre dette vil 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å, når vi kjører denne spørringen, vil den gi disse resultatene.

hva skal jeg gjøre med en ny bærbar datamaskin

Her ser vi for hver oppføring der tabellen ProdA har en verdi, den verdien gjenspeiles i kolonnen Resultater. Hvis det ikke er en oppføring i ProdA -tabellen, vises oppføringen fra ProdB i Resultater, og det er akkurat det leseren vår spurte.

For flere ressurser for å lære Microsoft Access, sjekk ut Joel Lee's How to Learn Microsoft Access: 5 Free Online Resources.

Dele Dele kvitring E -post Er det verdt å oppgradere til Windows 11?

Windows har blitt redesignet. Men er det nok til å overbevise deg om å bytte fra Windows 10 til Windows 11?

Les neste Relaterte temaer
  • Produktivitet
  • Spør ekspertene
Om forfatteren Bruce Epper(13 artikler publisert)

Bruce har lekt med elektronikk siden 70 -tallet, datamaskiner siden begynnelsen av 80 -tallet og besvart nøyaktig spørsmål om teknologi han ikke har brukt eller sett hele tiden. Han irriterer seg også ved å prøve å spille gitar.

Mer fra Bruce Epper

Abonner på vårt nyhetsbrev

Bli med i vårt nyhetsbrev for tekniske tips, anmeldelser, gratis ebøker og eksklusive tilbud!

Klikk her for å abonnere