Harry James
1 
4380
47
Microsoft Access je pravděpodobně nejúčinnějším nástrojem v celé sadě Microsoft Office, přesto však mystifikuje (a někdy děsí) mocenské uživatele Office. Jak má někdo s použitím tohoto nástroje strmější křivku učení než Word nebo Excel? Tento týden se Bruce Epper podívá na některé z otázek vyvolaných touto otázkou od jednoho z našich čtenářů.
Čtenář se ptá:
Mám potíže s napsáním dotazu v aplikaci Microsoft Access.
Mám databázi se dvěma tabulkami produktů obsahujícími společný sloupec s číselným kódem produktu a přidruženým názvem produktu.
Chci zjistit, které produkty z tabulky A lze nalézt v tabulce B. Chci přidat sloupec s názvem Výsledky, který obsahuje název produktu z tabulky A, pokud existuje, a název produktu z tabulky B, pokud neexistuje v tabulce A.
Máte nějakou radu?
Bruceova odpověď:
Microsoft Access je systém správy databází (DBMS) určený pro použití na počítačích Windows i Mac. Využívá databázový stroj Jet společnosti Microsoft pro zpracování a ukládání dat. Poskytuje také grafické rozhraní pro uživatele, které téměř eliminuje potřebu porozumět Structured Query Language (SQL).
SQL je příkazový jazyk používaný k přidávání, mazání, aktualizaci a vracení informací uložených v databázi a také k úpravě komponent základní databáze, jako je přidávání, mazání nebo úpravy tabulek nebo indexů.
Výchozí bod
Pokud ještě nemáte nějaké znalosti o aplikaci Access nebo jiném RDBMS, doporučujeme vám začít s těmito prostředky před pokračováním:
- Co je to databáze? Co je vlastně databáze? [MakeUseOf vysvětluje] Takže co je vlastně databáze? [MakeUseOf vysvětluje] Pro programátora nebo technologického nadšence je koncept databáze něco, co lze skutečně považovat za samozřejmost. Pro mnoho lidí je však koncepce samotné databáze trochu cizí ... kde Ryan Dube používá Excel k ukázání základů relačních databází.
- Stručný průvodce Začínáme s aplikací Microsoft Access 2007 Stručný průvodce Začínáme s aplikací Microsoft Access 2007 Stručný průvodce Začínáme s aplikací Microsoft Access 2007, což je přehled aplikace Access a komponent, které tvoří databázi Access, na vysoké úrovni.
- Stručný návod k tabulkám v aplikaci Microsoft Access 2007 Stručný návod k tabulkám v aplikaci Microsoft Access 2007 Stručný návod k tabulkám v aplikaci Microsoft Access 2007 se podívá na vytvoření první databáze a tabulek pro ukládání strukturovaných dat.
- Stručný návod k dotazům v aplikaci Microsoft Access 2007 Stručný návod k dotazům v aplikaci Microsoft Access 2007 Stručný návod k dotazům v aplikaci Microsoft Access 2007 se dívá na prostředky k vrácení určitých částí dat uložených v databázových tabulkách.
Díky základnímu porozumění pojmům uvedeným v těchto článcích bude trochu snazší strávit následující.
Vztahy a normalizace databáze
Představte si, že provozujete společnost prodávající 50 různých typů widgetů po celém světě. Máte klientskou základnu 1 250 a v průměru za měsíc prodáte těmto klientům 10 000 widgetů. Momentálně používáte ke sledování všech těchto prodejů jednu tabulku - ve skutečnosti jediná databázová tabulka. A každý rok přidává do tabulky tisíce řádků.
Výše uvedené obrázky jsou součástí tabulky pro sledování objednávek, kterou používáte. Nyní řekněte, že oba tito klienti od vás kupují widgety několikrát ročně, takže pro oba z nich máte mnohem více řádků.
Pokud se Joan Smith ožení s Tedem Bainesem a vezme si příjmení, musí být nyní změněn každý řádek, který obsahuje její jméno. Problém je znásoben, pokud náhodou máte dva různé klienty se jménem „Joan Smith“. Je jen mnohem těžší udržet vaše údaje o prodeji konzistentní kvůli poměrně běžné události.
Pomocí databáze a normalizací dat můžeme jednotlivé položky rozdělit do několika tabulek, jako jsou zásoby, klienti a objednávky.
Když se podíváme na část klienta z našeho příkladu, odebereme sloupce pro jméno klienta a adresu klienta a vložíme je do nové tabulky. Na obrázku výše jsem také lépe rozdělil věci, abych získal podrobnější přístup k datům. Nová tabulka také obsahuje sloupec pro primární klíč (ClientID) - číslo, které bude použito pro přístup ke každému řádku v této tabulce.
V původní tabulce, kde jsme tato data odstranili, jsme přidali sloupec pro cizí klíč (ClientID), což je odkazy na správný řádek obsahující informace o tomto konkrétním klientovi.
Nyní, když Joan Smith změní své jméno na Joan Baines, je třeba tuto změnu provést pouze jednou v klientské tabulce. Každý další odkaz ze spojených tabulek vytáhne správné jméno klienta a zpráva, která se dívá na to, co Joan zakoupila za posledních 5 let, získá všechny objednávky pod jejím panským i ženatým jménem, aniž by musela změnit způsob generování zprávy..
Další výhodou je také snížení celkové spotřeby úložiště.
Připojte se k typům
SQL definuje pět různých typů spojení: VNITŘNÍ, LEVÝ VNĚJŠÍ, PRAVÝ VNĚJŠÍ, PLNÝ VNĚJŠÍ a CROSS. Klíčové slovo OUTER je v příkazu SQL volitelné.
Microsoft Access umožňuje použití INNER (výchozí), LEFT OUTER, RIGHT OUTER a CROSS. FULL OUTER není podporován jako takový, ale pomocí LEFT OUTER, UNION ALL a RIGHT OUTER může být falešný za cenu dalších CPU cyklů a I / O operací.
Výstup spojení CROSS obsahuje každý řádek levé tabulky spárovaný s každým řádkem pravé tabulky. Jedinýkrát, co jsem kdy viděl použité spojení CROSS, je během testování zátěže databázových serverů.
Podívejme se, jak fungují základní spojení, pak je upravíme tak, aby vyhovovaly našim potřebám.
Začněme vytvořením dvou tabulek, ProdA a ProdB, s následujícími vlastnostmi návrhu.
AutoNumber je automaticky inkrementující dlouhé celé číslo přiřazené k položkám, jak jsou přidávány do tabulky. Možnost Text nebyla změněna, takže bude akceptovat textový řetězec dlouhý až 255 znaků.
Nyní je vyplňte některými daty.
Abych ukázal rozdíly v tom, jak fungují tři typy spojení, odstranil jsem položky 1, 5 a 8 z ProdA.
Poté vytvořte nový dotaz na Vytvořit> Návrh dotazu. V dialogu Zobrazit tabulku a vyberte obě tabulky klikněte na Přidat, pak Zavřít.
Klikněte na ProductID v tabulce ProdA, přetáhněte jej na ProductID v tabulce ProdB a uvolněte tlačítko myši pro vytvoření vztahu mezi tabulkami.
Klepněte pravým tlačítkem myši na řádek mezi tabulkami představující vztah mezi položkami a vyberte Připojit vlastnosti.
Ve výchozím nastavení je vybrán typ připojení 1 (INNER). Možnost 2 je spojením VNĚJŠÍ VNĚJŠÍ a 3 je spojením Vpravo VNĚJŠÍ.
Nejprve se podíváme na spojení INNER, takže kliknutím na OK dialog zavřete.
V návrháři dotazů vyberte pole, která chceme vidět z rozevíracího seznamu.
Když spustíme dotaz (červený vykřičník v pásu karet), zobrazí pole ProductName z obou tabulek s hodnotou z tabulky ProdA v prvním sloupci a ProdB ve druhém.
Všimněte si, že výsledky ukazují pouze hodnoty, kde je ProductID v obou tabulkách stejný. Přestože v tabulce ProdB existuje položka pro ProductID = 1, nezobrazí se ve výsledcích, protože ProductID = 1 v tabulce ProdA neexistuje. Totéž platí pro ProductID = 11. Existuje v tabulce ProdA, ale nikoli v tabulce ProdB.
Pomocí tlačítka Zobrazit na pásu karet a přepnutím do zobrazení SQL můžete vidět dotaz SQL generovaný návrhářem, který byl použit k získání těchto výsledků..
ZVOLTE ProdA.ProductName, ProdB.ProductName Z ProdA INNER JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;Vraťte se zpět do návrhového zobrazení a změňte typ spojení na 2 (LEFT OUTER). Spusťte dotaz a podívejte se na výsledky.
Jak vidíte, každý záznam v tabulce ProdA je ve výsledcích reprezentován, zatímco ve výsledcích se zobrazí pouze ty v ProdB, které mají odpovídající položku ProductID v tabulce ProdB..
Prázdné místo ve sloupci ProdB.ProductName je speciální hodnota (NULL), protože v tabulce ProdB není odpovídající hodnota. To se ukáže později.
ZVOLTE ProdA.ProductName, ProdB.ProductName Z ProdA LEFT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;Vyzkoušejte totéž s třetím typem spojení (Vpravo VNĚJŠÍ).
Výsledky ukazují vše z tabulky ProdB, zatímco ukazuje prázdné (známé jako NULL) hodnoty, kde tabulka ProdA nemá odpovídající hodnotu. Dosud nás to přibližuje výsledkům požadovaným v otázce čtenáře.
ZVOLTE ProdA.ProductName, ProdB.ProductName Z ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;Použití funkcí v dotazu
Výsledky funkce mohou být také vráceny jako součást dotazu. Chceme, aby se v naší sadě výsledků objevil nový sloupec s názvem „Výsledky“. Jeho hodnota bude obsahem sloupce ProductName tabulky ProdA, pokud má ProdA hodnotu (není NULL), jinak by měla být převzata z tabulky ProdB.
K vygenerování tohoto výsledku lze použít funkci Immediate IF (IIF). Funkce má tři parametry. První je podmínka, která musí vyhodnotit na hodnotu True nebo False. Druhý parametr je hodnota, která má být vrácena, pokud je podmínka true, a třetí parametr je hodnota, která má být vrácena, pokud je podmínka False.
Plně funkční konstrukce naší situace vypadá takto:
IIF (ProdA.ProductID Is Null, ProdB.ProductName, ProdA.ProductName)Všimněte si, že parametr podmínky nekontroluje rovnost. Hodnota Null v databázi nemá hodnotu, kterou lze porovnat s jakoukoli jinou hodnotou, včetně jiné hodnoty Null. Jinými slovy, Null se nerovná Null. Vůbec. Abychom to obešli, namísto toho zkontrolujeme hodnotu pomocí klíčového slova „Is“.
Mohli jsme také použít 'Není Not Null' a změnit pořadí parametrů True a False, abychom získali stejný výsledek.
Při vkládání do produktu Query Designer musíte do položky Field: zadat celou funkci. Chcete-li vytvořit sloupec „Výsledky“, musíte použít alias. Chcete-li to provést, předmluvte funkci „Výsledky:“, jak je vidět na následujícím obrázku.
Ekvivalentním kódem SQL by to bylo:
VYBERTE ProdA.ProductName, ProdB.ProductName, IIF (ProdA.ProductID je Null, ProdB.ProductName, ProdA.ProductName) AS Výsledky OD ProdA RIGHT JOIN ProdB ON ProdA.ProductID = ProdB.ProductID;Nyní, když spustíme tento dotaz, přinese tyto výsledky.
Zde vidíme pro každou položku, kde má tabulka ProdA hodnotu, tato hodnota se projeví ve sloupci Výsledky. Pokud v tabulce ProdA neexistuje záznam, objeví se položka z ProdB ve výsledcích, což je přesně to, co náš čtenář požádal.
Další zdroje pro učení aplikace Microsoft Access naleznete v tématu Jak se naučit Microsoft Access: 5 zdrojů online zdarma Jak se naučit aplikaci Microsoft Access: 5 zdrojů online zdarma Jak se naučit aplikaci Microsoft Access: 5 zdrojů online zdarma Musíte spravovat velké množství dat? Měli byste se podívat do aplikace Microsoft Access. Naše bezplatné studijní zdroje vám mohou pomoci začít a naučit se dovednosti pro složitější projekty. .