Microsoft Access er uden tvivl det mest kraftfulde værktøj i hele Microsoft Office-pakken, men det mystifies (og sommetider skræmmer) Office Power-brugere. Med en brattere indlæringskurve end Word eller Excel, hvordan skal man vride hovedet omkring brugen af dette værktøj? I denne uge vil Bruce Epper kigge 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 indeholdende en fælles kolonne med en numerisk produktkode og et tilhørende produktnavn.
Jeg vil gerne finde ud af, hvilke produkter fra tabel A der findes i tabel B. Jeg vil gerne tilføje en kolonne med navnet Resultater, der indeholder produktnavnet fra tabel A, hvis det findes, og produktnavnet fra tabel B, når det ikke findes i tabel A.
Har du nogen råd?
Bruces svar:
Microsoft Access er et databasebehandlingssystem (DBMS), der er designet til brug på både Windows- og Mac-maskiner. Det udnytter Microsofts Jet databasemotor til databehandling og lagring. Det giver også en grafisk brugerflade, der næsten eliminerer behovet for at forstå Structured Query Language (SQL).
SQL er det kommandosprog, der bruges til at tilføje, slette, opdatere og returnere oplysninger, der er gemt i databasen, samt ændre kernedatabasekomponenter som f.eks. Tilføjelse, sletning eller ændring af tabeller eller indekser.
Udgangspunktet
Hvis du ikke allerede har kendskab til Access eller en anden RDBMS, foreslår jeg, at du starter med disse ressourcer, inden du fortsætter:
- Så hvad er en database? Så hvad er en database, alligevel? [MakeUseOf Forklarer] Så hvad er en database, alligevel? [MakeUseOf Forklarer] For en programmør eller en teknologi entusiast er begrebet en database noget, der virkelig kan tages for givet. Men for mange mennesker er begrebet en database selv en smule fremmed .... Læs mere, hvor Ryan Dube bruger Excel til at vise det grundlæggende i relationelle databaser.
- En hurtig vejledning til at komme i gang med Microsoft Access 2007 En hurtig guide til at komme i gang med Microsoft Access 2007 En hurtig guide til at komme i gang med Microsoft Access 2007 Læs mere, som er et overblik over Access og de komponenter, der omfatter en Access-database.
- En hurtig vejledning til tabeller i Microsoft Access 2007 En hurtig vejledning til tabeller i Microsoft Access 2007 En hurtig vejledning til tabeller i Microsoft Access 2007 Read More tager et kig på at oprette din første database og tabeller for at gemme dine strukturerede data.
- En hurtig vejledning i forespørgsler i Microsoft Access 2007 En hurtig vejledning i forespørgsler i Microsoft Access 2007 En hurtig vejledning i forespørgsler i Microsoft Access 2007 Read More ser på midlerne til at returnere bestemte dele af de data, der er gemt i databasetabellerne.
At have en grundlæggende forståelse af begreberne i disse artikler vil gøre følgende lidt lettere at fordøje.
Database Relations 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 i en gennemsnitlig måned sælger 10.000 widgets til disse kunder. Du bruger i øjeblikket et enkelt regneark til at spore alle disse salg - effektivt en enkelt database tabel. Og hvert år tilføjer tusindvis af rækker til dit regneark.
Ovennævnte billeder er en del af det ordregivende 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 for 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 er sammensat, hvis du tilfældigvis har to forskellige kunder med navnet 'Joan Smith'. Det er lige blevet meget sværere at holde dine salgsdata ensartede på grund af en temmelig almindelig begivenhed.
Ved at bruge en database og normalisere dataene, kan vi adskille elementer i flere tabeller som lager, kunder og ordrer.
Bare kigger på klientdelen af vores eksempel, vi fjerner kolonnerne for klientnavn og klientadresse og sætter dem i en ny tabel. I billedet ovenfor har jeg også brudt ting bedre ud for mere granuleret adgang til dataene. Den nye tabel indeholder også en kolonne for en primær nøgle (ClientID) - et nummer, som vil blive brugt til at få adgang til hver række i denne tabel.
I den oprindelige tabel, hvor vi fjernede disse data, ville vi tilføje en kolonne for en fremmed nøgle (ClientID), som er hvad der linker til den rigtige række, der indeholder oplysningerne til denne bestemte klient.
Nu, når Joan Smith ændrer sit navn til Joan Baines, skal ændringen kun foretages en gang i Clientbordet. Enhver anden henvisning fra de tilsluttede tabeller vil trække det korrekte klientnavn, og en rapport, der ser på, hvad Joan har købt i de sidste 5 år, vil få alle ordrer under både hendes jomfru og giftede navne uden at skulle ændre hvordan rapporten genereres .
Som en ekstra fordel reducerer dette også den samlede mængde forbrugt forbrug.
Deltag i typer
SQL definerer fem forskellige typer tilslutninger: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER og CROSS. Det eksterne søgeord er valgfrit i SQL-sætningen.
Microsoft Access tillader brug af INNER (standard), VENSTRE YDER, RIGHT OUTER og CROSS. FULL OUTER understøttes ikke som sådan, men ved at bruge LEFT OUTER, UNION ALL, og RIGHT OUTER, kan det falde på bekostning af flere CPU-cyklusser og I / O-operationer.
Udgangen fra en CROSS-tilslutning indeholder hver række i venstre bord parret med hver række i højre tabel. Den eneste gang jeg nogensinde har set en CROSS join, der bruges, er under belastningstest af databaseservere.
Lad os tage et kig på, hvordan de grundlæggende samlinger fungerer, så vil vi ændre dem, så de passer til vores behov.
Lad os begynde med at oprette to tabeller, ProdA og ProdB, med følgende designegenskaber.
AutoNumber er et automatisk stigende lang heltal tildelt poster, som de tilføjes til tabellen. Tekst-indstillingen blev ikke ændret, så det vil acceptere en tekststreng på op til 255 tegn lang.
Fyld dem nu med nogle data.
For at vise forskellene i hvordan de 3 sammenkoblede typer fungerer, har jeg slettet indgange 1, 5 og 8 fra ProdA.
Opret derefter en ny forespørgsel ved at gå til Create> Query Design . Vælg begge tabeller i dialogboksen Vis tabel, og klik på Tilføj, og klik derefter på Luk .
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 Join Properties .
Som standard er deltype 1 (INNER) valgt. Mulighed 2 er en LEFT OUTER-tilslutning og 3 er en RIGHT OUTER-tilslutning.
Vi vil se på INNER joinen først, så klik på OK for at afvise dialogen.
I forespørgselsdesigneren skal du markere de felter, vi ønsker at se fra rullemenuen.
Når vi kører spørgsmålet (det røde udråbstegn i båndet), vil det vise feltet Produktnavn fra begge tabeller med værdien fra tabellen ProdA i den første kolonne og ProdB i den anden.
Bemærk, at resultaterne kun viser værdier, hvor ProductID er ens i begge tabeller. Selv om der er en post for ProductID = 1 i tabel ProdB, vises den ikke i resultaterne, da ProductID = 1 ikke findes i tabellen ProdA. Det samme gælder for ProductID = 11. Det 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; Gå tilbage til Design View, skift tilslut type til 2 (LEFT OUTER). Kør forespørgslen for at se resultaterne.
Som du kan se, er alle poster i tabel ProdA repræsenteret i resultaterne, mens kun de i ProdB, der har en tilsvarende ProductID-indgang i tabel ProdB, vises i resultaterne.
Det tomme mellemrum i kolonnen ProdB.ProductName er en speciel værdi (NULL), da der ikke er en matchende værdi i tabellen 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 af deltagelse (RIGHT OUTER).
Resultaterne viser alt fra tabel ProdB, mens det viser blanke (kendt som NULL) værdier, hvor ProdA-tabellen ikke har en matchende værdi. Hidtil bringer dette os nærmest de ønskede resultater 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 led i en forespørgsel. Vi ønsker, at en ny kolonne med navnet 'Resultater' vises i vores resultatsæt. Dens værdi vil være indholdet af ProductName-kolonnen i tabellen ProdA, hvis ProdA har en værdi (det er ikke NULL), ellers skal det tages fra tabellen ProdB.
Funktionen Immediate IF (IIF) kan bruges til at generere dette resultat. Funktionen tager tre parametre. Den første er en betingelse, der skal vurderes til en sand eller falsk værdi. Den anden parameter er den værdi, der skal returneres, hvis tilstanden er sand, og den tredje parameter er den værdi, der skal returneres, hvis tilstanden er falsk.
Den fulde funktions konstruktion for vores situation ser sådan ud:
IIF(ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName) Bemærk, at betingelsesparameteren ikke kontrollerer for ligestilling. 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 betyder Null ikke Null. Nogensinde. For at komme forbi dette, kontrollerer vi i stedet værdien ved hjælp af 'Is' søgeordet.
Vi kunne også have brugt 'Er ikke Null' og ændret rækkefølgen af de sande og falske parametre for at få det samme resultat.
Når du sætter dette ind i forespørgselsdesigneren, skal du skrive hele funktionen i feltet: indgang. For at få det til at skabe kolonnen 'Resultater', skal du bruge et alias. For at gøre dette, forord funktionen med 'Resultater:' som det ses i følgende skærmbillede.
Den tilsvarende SQL-kode for 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; Nu, når vi kører denne forespørgsel, vil det producere disse resultater.
Her ser vi for hver post, hvor tabellen ProdA har en værdi, den værdi afspejles i kolonnen Resultater. Hvis der ikke er en post i ProdA-tabellen, vises indgangen fra ProdB i Resultater, hvilket er præcis, hvad vores læser spurgte.
For flere ressourcer til at lære Microsoft Access, skal du tjekke Joel Lee's Sådan lærer du Microsoft Access: 5 gratis online-ressourcer Sådan lærer du Microsoft Access: 5 gratis online-ressourcer Sådan lærer du Microsoft Access: 5 gratis online ressourcer Er du nødt til at administrere en stor mængde af data? Du skal undersøge Microsoft Access. Vores gratis studie ressourcer kan hjælpe dig med at komme i gang og lære færdigheder til mere komplekse projekter. Læs mere .