W przypadku uwzględnienia wielu źródeł danych w zapytaniu programu Access sprzężenia pozwalają ograniczyć liczbę rekordów, które mają zostać wyświetlone, na podstawie relacji między źródłami danych. Sprzężenia służą również do łączenia rekordów z obu źródeł danych, dzięki czemu każda para rekordów z tych źródeł staje się jednym rekordem w wynikach zapytania.
W tym artykule omówiono różne typy sprzężeń i pokazano, jak używać ich w zapytaniu. Domyślnie sprzężenie jest tworzone automatycznie, jeśli istnieje już relacja między dwoma źródłami danych używanymi w zapytaniu. Sprzężenie jest tworzone również w przypadku, gdy istnieją pola, które wyraźnie ze sobą odpowiadają. Sprzężenia utworzone automatycznie można usunąć. Ten artykuł zawiera podstawowe informacje o relacjach między tabelami, w tym o sposobie ich tworzenia.
Uwaga
Zapytania możesz łączyć w taki sam sposób, jak sprzęgasz tabele, a także sprzężenia obu tych elementów.
W tym artykule
- Omówienie
- Typy sprzężeń
- Pokazanie wierszy, w których istnieje wspólna wartość w obu sprzężonych tabelach
- Pokazywanie wszystkich wierszy z jednej tabeli i odpowiadających im wierszy z drugiej tabeli
- Pokazanie wszystkich wierszy z obu tabel i połączenie wierszy tam, gdzie istnieje wspólna wartość
- Sprzężenia krzyżowe
- Sprzęganie tabel na podstawie nierówności wartości pól
- Usuwanie sprzężenia
Omówienie
Baza danych jest kolekcją tabel danych łączących logiczne relacje między sobą. Relacje służą do łączenia tabel za pomocą wspólnych pól. Tabela może być częścią dowolnej liczby relacji, ale każda relacja zawsze ma dokładnie dwie tabele. W zapytaniu relacja jest reprezentowana przez sprzężenie.
Po dodaniu tabel do zapytania program Access tworzy sprzężenia na podstawie relacji zdefiniowanych między tabelami. Sprzężenia w zapytaniach można tworzyć ręcznie, nawet jeśli nie reprezentują one zdefiniowanych już relacji. Jeśli jako źródła danych dla kwerendy są używane inne zapytania (zamiast tabel lub oprócz nich), można tworzyć sprzężenia między zapytaniami źródłowymi, a także między tymi zapytaniami a dowolnymi tabelami używanymi jako źródła danych.
Sprzężenia działają podobnie do kryteriów zapytania, ponieważ ustanawiają reguły, z którymi muszą być zgodne dane, aby zostały uwzględnione w operacjach zapytania. W przeciwieństwie do kryteriów, sprzężenia określają również, że każda para wierszy, które spełniają warunki sprzężenia, zostanie połączona w zestawie rekordów w celu utworzenia pojedynczego wiersza.
Istnieją cztery podstawowe typy sprzężeń: sprzężenia wewnętrzne, sprzężenia zewnętrzne, sprzężenia krzyżowe i sprzężenia nierówne. W tym artykule opisano poszczególne typy sprzężeń, których można używać, powody ich używania oraz sposoby tworzenia sprzężeń.
Sprzężenia są dla kwerend tym, czym są relacje z tabelami: wskazują możliwości łączenia danych z dwóch źródeł na podstawie wspólnych wartości danych. Poniższa ilustracja przedstawia sprzężenie w widoku projektu kwerendy, którego właściwości są otwarte w oknie dialogowym:
Ta linia między tabelami reprezentuje sprzężenie. Kliknij dwukrotnie sprzężenie, aby otworzyć okno dialogowe Właściwości sprzężenia (przedstawione) i przejrzeć lub zmienić sprzężenie.
Sprzężenia są czasami kierunkowe. W tym obszarze okna dialogowego można wskazać, która tabela jest podstawą sprzężenia oraz które pola służą do sprzęgania tabel.
Ten obszar określa typ sprzężenia: opcja 1 to sprzężenie wewnętrzne, 2 to lewe sprzężenie zewnętrzne, a 3 to prawe sprzężenie zewnętrzne.
Można używać pól z obu tabel i z każdego z nich są wyświetlane dane dotyczące danego zadania. W sprzężeniu wewnętrznym nie są uwzględniane żadne inne dane. W przypadku sprzężenia zewnętrznego niepowiązane rekordy z jednej tabeli są również uwzględniane w wynikach zapytania.
Typy sprzężeń
Istnieją cztery podstawowe typy sprzężeń: sprzężenia wewnętrzne, sprzężenia zewnętrzne, sprzężenia krzyżowe i sprzężenia nierówne. Sprzężenia krzyżowe i sprzężenia nierówne to zaawansowane typy sprzężeń rzadko używane, ale warto o nich wiedzieć, aby w pełni zrozumieć, jak działają sprzężenia.
Sprzężenia wewnętrzne: łącznie tylko powiązane dane z obu tabel
Sprzężenie wewnętrzne to sprzężenie, w którym program Access uwzględnia dane z tabeli tylko wtedy, gdy w powiązanej tabeli istnieją odpowiadające jej dane, i na odwrót. W większości przypadków będziesz używać sprzężeń wewnętrznych. Jeśli utworzysz sprzężenie bez określenia jego rodzaju, program Access założy, że chcesz mieć sprzężenie wewnętrzne. Sprzężenia wewnętrzne są przydatne, ponieważ umożliwiają łączenie danych z dwóch źródeł na podstawie wspólnych wartości, dzięki czemu widać dane tylko wtedy, gdy ogólny obraz jest ich pełny obraz.
Sprzężenia zewnętrzne: wszystkie powiązane dane zostaną prawidłowo połączone oraz wszystkie pozostałe rekordy z jednej tabeli
Sprzężenie zewnętrzne jest podobne do sprzężenia wewnętrznego, ale dodaje pozostałe wiersze z jednej z tabel. Sprzężenia zewnętrzne są kierunkowe: lewe sprzężenie zewnętrzne obejmuje wszystkie rekordy z lewej tabeli (pierwszej tabeli w sprzężeniu), a prawe sprzężenie zewnętrzne obejmuje wszystkie rekordy z prawej tabeli — drugiej tabeli w sprzężeniu.
Pełne sprzężenia zewnętrzne: wszystkie dane połączone tam, gdzie jest to możliwe
W niektórych systemach sprzężenie zewnętrzne może obejmować wszystkie wiersze z obu tabel, przy czym wiersze te są połączone, jeśli są zgodne. Jest to nazywane pełnym sprzężeniem zewnętrznym i program Access nie obsługuje go jawnie. Ten sam efekt można jednak osiągnąć za pomocą sprzężenia krzyżowego i kryteriów.
Sprzężenia krzyżowe: wszystkie dane połączone w każdy możliwy sposób
W większości przypadków sprzężenie krzyżowe jest skutkiem ubocznym dodania dwóch tabel do zapytania, a następnie zapomnienia o ich połączeniu. Program Access interpretuje to w ten sposób, że każdy rekord z jednej tabeli ma być połączony ze wszystkimi rekordami z drugiej tabeli — każdą możliwą kombinacją rekordów. Ponieważ nie można łączyć żadnych danych, tego rodzaju sprzężenie rzadko daje użyteczne wyniki. Istnieje jednak kilka przypadków, w których sprzężenie krzyżowe jest właśnie tym, czego potrzebujesz.
Sprzężenia nierówne: podobne do zwykłego sprzężenia, ale z użyciem innego porównania w celu połączenia wierszy
W sprzężeniach nierównych jest używany operator inny niż znak równości (=) w celu porównania wartości oraz określenia, czy i w jaki sposób dane mają zostać połączone. Sprzężenia nierówne nie są jawnie obsługiwane, ale taki sam efekt można osiągnąć za pomocą sprzężenia krzyżowego i kryteriów.
Pokazanie wierszy, w których istnieje wspólna wartość w obu sprzężonych tabelach
Aby wyświetlić w sprzężonym polu tylko te wiersze, które mają pasujące wartości, użyj sprzężenia wewnętrznego. Program Access automatycznie tworzy sprzężenia wewnętrzne.
Sprzężenia wewnętrzne to najpopularniejszy typ sprzężenia. Na podstawie danych w połączonych polach do kwerendy zostają przekazane zapytanie, że wiersze jednej ze sprzężonych tabel odpowiadają wierszom drugiej tabeli. Podczas uruchamiania zapytania ze sprzężeniem wewnętrznym operacje zapytania będą uwzględniane tylko w wierszach, w których istnieje wspólna wartość w obu sprzężonych tabelach.
Jak mogę używać sprzężenia wewnętrznego?
W większości przypadków nie musisz nic robić, aby użyć sprzężenia wewnętrznego. Jeśli tabele dodawane do zapytania mają już relacje, to podczas dodawania tabel program Access automatycznie tworzy sprzężenie wewnętrzne między każdą parą tabel pokrewnych. Jeśli więzy integralności są wymuszane, program Access wyświetla również cyfrę "1" nad linią sprzężenia, aby wskazać, która tabela znajduje się po stronie "jeden" relacji jeden-do-wielu, oraz symbol nieskończoności (∞), aby wskazać, która tabela znajduje się po stronie "wiele".
Nawet jeśli nie utworzono relacji, program Access automatycznie tworzy sprzężenia wewnętrzne, jeśli do zapytania zostaną dodane dwie tabele, z których każda zawiera pole o takim samym lub zgodnym typie danych, a jedno z pól sprzężenia jest kluczem podstawowym. Symbole "jeden" i "wiele" nie są w tym przypadku wyświetlane, ponieważ więzy integralności nie są wymuszane.
Jeśli dodano zapytania, a nie utworzono między nimi relacji, program Access nie tworzy automatycznie sprzężeń wewnętrznych między tymi zapytaniami ani między zapytaniami i tabelami. Ogólnie rzecz biorąc, należy je utworzyć samodzielnie. Aby utworzyć sprzężenie wewnętrzne, należy przeciągnąć pole z jednego źródła danych do pola w innym źródle danych. Program Access wyświetli linię między tymi dwoma polami, co oznacza, że sprzężenie zostało utworzone.
Składnia SQL dla sprzężenia wewnętrznego
Sprzężenia wewnętrzne są określone w języku SQL w klauzuli FROM, jak pokazano poniżej:
FROM tabela1 INNER JOIN tabela2 ON tabela1. pole1 porównaj tabelę2. pole2
Operacja INNER JOIN zawiera następujące segmenty:
| Segment | Opis |
|---|---|
| tabela1, tabela2 | Nazwy tabel, z których pochodzą łączone rekordy. |
| pole1, pole2 | Nazwy sprzęganych pól. Jeśli nie są to pola numeryczne, muszą mieć ten sam typ danych i zawierać ten sam rodzaj danych, ale mogą mieć inne nazwy. |
| porównaj | Dowolny relacyjny operator porównania: "="," "<," ">," "<=>"," lub "<>." |
Aby uzyskać więcej informacji na temat składni sprzężenia wewnętrznego, zobacz temat Operacja INNER JOIN.
Pokazywanie wszystkich wierszy z jednej tabeli i odpowiadających im wierszy z drugiej tabeli
Sprzężenia zewnętrzne informują zapytanie, że chociaż niektóre wiersze po obu stronach sprzężenia dokładnie odpowiadają, zapytanie powinno uwzględnić wszystkie wiersze z jednej tabeli, a także wiersze z drugiej tabeli, które mają wspólną wartość po obu stronach sprzężenia.
Sprzężenia zewnętrzne mogą być lewymi sprzężeniami zewnętrznymi lub prawymi sprzężeniami zewnętrznymi. W przypadku lewego sprzężenia zewnętrznego zapytanie uwzględnia wszystkie wiersze z pierwszej tabeli w klauzuli FROM instrukcji SQL i tylko te wiersze z drugiej tabeli, w których pole sprzężenia zawiera wartości wspólne dla obu tabel. W przypadku prawego sprzężenia zewnętrznego zapytanie uwzględnia wszystkie wiersze z drugiej tabeli w klauzuli FROM instrukcji SQL i tylko te wiersze z drugiej tabeli, w których pole sprzężenia zawiera wartości wspólne dla obu tabel.
Uwaga
Można łatwo ustalić, która tabela jest lewą lub prawą tabelą w danym sprzężeniu, klikając dwukrotnie sprzężenie, a następnie sprawdzając to w oknie dialogowym Właściwości sprzężenia . Można również przełączyć się do widoku SQL, a następnie zbadać klauzulę FROM.
Ponieważ niektórym wierszom po jednej stronie sprzężenia zewnętrznego nie odpowiadają wiersze z drugiej tabeli, niektóre pola zwrócone w wynikach zapytania z tej tabeli będą puste, gdy wiersze nie będą zgodne.
Jak mogę używać sprzężenia zewnętrznego?
Sprzężenie zewnętrzne tworzy się, modyfikując istniejące sprzężenie wewnętrzne. Jeśli nie istnieje żadne sprzężenie wewnętrzne, należy je utworzyć, a następnie zmienić na sprzężenie zewnętrzne.
Zmienianie sprzężenia wewnętrznego na sprzężenie zewnętrzne
- W widoku projektu kwerendy kliknij dwukrotnie sprzężenie, które chcesz zmienić.
Zostanie wyświetlone okno dialogowe Właściwości sprzężenia. - W oknie dialogowym Właściwości sprzężenia zwróć uwagę na opcje wymienione obok opcji 2 i opcji 3.
- Kliknij opcję, której chcesz użyć, a następnie kliknij przycisk OK.
- Program Access wyświetli sprzężenie i strzałkę biegnącą od źródła danych, gdzie wszystkie wiersze zostaną uwzględnione, do źródła danych, gdzie zostaną uwzględnione tylko te wiersze, które spełniają warunek sprzężenia.
Niejednoznaczne sprzężenia zewnętrzne
Jeśli utworzono zapytanie zawierające wartości LEFT JOIN i INNER JOIN, program Access może nie być w stanie określić, którą operację sprzężenia należy wykonać jako pierwszą. Ponieważ wyniki różnią się w zależności od tego, czy najpierw jest wykonywane sprzężenie lewe, czy sprzężenie wewnętrzne, program Access wyświetli komunikat o błędzie:
Aby naprawić ten błąd, należy zmodyfikować zapytanie w celu ustalenia, które sprzężenie należy wykonać najpierw.
Składnia SQL dla sprzężenia zewnętrznego
Sprzężenia zewnętrzne są określone w języku SQL w klauzuli FROM, jak pokazano poniżej:
FROM tabela1 [ LEWY | RIGHT ] JOIN table2
ON tabela1.pole1 porównaj tabela2.pole2
Operacje LEFT JOIN i RIGHT JOIN zawierają następujące segmenty:
| Segment | Opis |
|---|---|
| tabela1, tabela2 | Nazwy tabel, z których pochodzą łączone rekordy. |
| pole1, pole2 | Nazwy sprzęganych pól. Pola muszą mieć ten sam typ danych i zawierać ten sam rodzaj danych, ale mogą mieć inne nazwy. |
| porównaj | Dowolny relacyjny operator porównania: "="," "<," ">," "<=>"," lub "<>." |
Aby uzyskać więcej informacji na temat składni sprzężenia zewnętrznego, zobacz temat Operacje LEFT JOIN i RIGHT JOIN.
Pokazanie wszystkich wierszy z obu tabel i połączenie wierszy tam, gdzie istnieje wspólna wartość
Aby wyświetlić wszystkie wiersze z dwóch tabel i połączyć je na podstawie wspólnych wartości, należy użyć pełnego sprzężenia zewnętrznego. Program Access nie obsługuje od razu pełnych sprzężeń zewnętrznych, ale taki efekt można osiągnąć za pomocą zapytania składającego. W poniższej procedurze wyjaśniono, jak to zrobić, ale jeśli chcesz uzyskać więcej informacji o zapytaniach składających, zobacz sekcję Zobacz też .
Aby wykonać pełne sprzężenie zewnętrzne za pomocą zapytania składającego:
Utwórz zapytanie zawierające lewe sprzężenie zewnętrzne w polu, którego chcesz użyć do pełnego sprzężenia zewnętrznego.
Na karcie Narzędzia główne w grupie Widoki kliknij pozycję Widok i kliknij pozycję Widok SQL.
Naciśnij klawisze CTRL+C, aby skopiować kod SQL.
Usuń średnik znajdujący się na końcu klauzuli FROM, a następnie naciśnij klawisz ENTER.
Wpisz ciąg UNION i naciśnij klawisz ENTER.
Uwaga
Nie należy używać słowa kluczowego ALL w przypadku użycia zapytania składającego w celu wykonania pełnego sprzężenia zewnętrznego.
Naciśnij klawisze CTRL+V, aby wkleić kod SQL skopiowany w kroku 3.
We wklejonym kodzie zmień wartość LEFT JOIN na RIGHT JOIN.
Usuń średnik na końcu drugiej klauzuli FROM, a następnie naciśnij klawisz ENTER.
Dodaj klauzulę WHERE określającą, że wartość pola sprzężenia ma wartość NULL w pierwszej tabeli wymienionej w klauzuli FROM (w tabeli po lewej).
Jeśli na przykład klauzula FROM to:FROM Products RIGHT JOIN [Order Details] ON Products.ID = [Order Details].[Product ID]Należy dodać następującą klauzulę WHERE:
WHERE Products.ID IS NULLWpisz średnik (;) na końcu klauzuli WHERE, aby wskazać koniec zapytania składającego.
Na karcie Projektowanie w grupie Wyniki kliknij przycisk Uruchom.
Sprzężenia krzyżowe
Sprzężenia krzyżowe różnią się od sprzężeń wewnętrznych i zewnętrznych tym, że nie są jawnie reprezentowane w programie Access. W przypadku sprzężenia krzyżowego każdy wiersz z jednej tabeli jest łączony z każdym wierszem z innej tabeli, w wyniku czego powstaje tak zwany iloczyn wektorowy lub iloczyn kartezjański. Za każdym razem, gdy uruchamiasz zapytanie zawierające tabele, które nie są jawnie sprzężone, wynikiem jest iloczyn wektorowy. Sprzężenia krzyżowe są zwykle niezamierzone, ale w niektórych przypadkach mogą być przydatne.
Dlaczego warto używać sprzężenia krzyżowego?
Jeśli chcesz zbadać każdą możliwą kombinację wierszy między dwiema tabelami lub zapytaniami, użyj sprzężenia krzyżowego. Załóżmy na przykład, że Twoja firma ma za sobą spektakularny rok i rozważasz przyznanie rabatów klientom. Możesz utworzyć zapytanie sumujące zakupy każdego klienta, utworzyć małą tabelę z kilkoma możliwymi wartościami procentowymi rabatów, a następnie połączyć te informacje w innym zapytaniu, wykonującym sprzężenie krzyżowe. Zostanie wyświetlone zapytanie wyświetlające zestaw hipotetycznych rabatów dla każdego klienta.
Jak mogę używać sprzężenia krzyżowego?
Sprzężenie krzyżowe jest tworzone za każdym razem, gdy dołączasz tabele lub zapytania do zapytania i nie tworzysz co najmniej jednego sprzężenia jawnego dla każdej tabeli lub zapytania. Program Access łączy wszystkie wiersze z każdej tabeli lub zapytania, które nie są jawnie sprzężone z żadną inną tabelą lub zapytaniem, z co drugim wierszem w wynikach. Rozważmy scenariusz rabatu z poprzedniego akapitu. Załóżmy, że masz 91 klientów i chcesz sprawdzić pięć możliwych wartości procentowych rabatów. Sprzężenie krzyżowe tworzy 455 wierszy (iloczyn liczb 91 i 5).
Jak można sobie wyobrazić, niezamierzone sprzężenia krzyżowe mogą spowodować powstanie ogromnej liczby wierszy w wynikach zapytania. Ponadto te wyniki są na ogół pozbawione znaczenia, ponieważ jeśli nie zamierzasz łączyć wszystkich wierszy z co drugimi, większość połączonych wierszy wyświetlanych w wynikach nie będzie miała sensu. Ponadto uruchamianie zapytań używających niezamierzonych sprzężeń krzyżowych może trwać bardzo długo.
1. Pola otoczone kółkiem powinny być połączone ze sobą.
1. Warto zwrócić uwagę na bardzo dużą liczbę rekordów.
1. Należy zauważyć, że liczba rekordów jest znacznie mniejsza.
Sprzęganie tabel na podstawie nierówności wartości pól
Sprzężenia nie muszą być oparte na równoważności sprzężonych pól. Sprzężenie może być oparte na dowolnym operatorze porównania, takim jak większe niż (>), mniejsze niż (<) lub nie równe (<>). Sprzężenia, które nie są oparte na równoważności, są nazywane sprzężeniami nierównymi.
Aby połączyć wiersze dwóch źródeł danych na podstawie różnych wartości pól, należy użyć sprzężenia nierównego. Zazwyczaj sprzężenia nierówne są oparte na operatorach porównania większe niż (>), mniejsze niż (), większe niż lub< równe (>=) albo mniejsze niż lub równe (<=). Sprzężenia nierówne, których źródłem jest operator nie równa się(<>), mogą zwrócić niemal tyle samo wierszy co sprzężenia krzyżowe, a ich wyniki mogą być trudne do zinterpretowania.
Jak mogę używać sprzężenia nierównego?
Sprzężenia nierówne nie są obsługiwane w widoku projektu. Jeśli chcesz ich użyć, musisz to zrobić za pomocą widoku SQL. Jednak sprzężenie można utworzyć w widoku projektu, przełączyć się do widoku SQL, znaleźć operator porównania równa się (=) i zmienić go na operator, którego chcesz użyć. Po wykonaniu tej czynności można ponownie otworzyć zapytanie w widoku projektu tylko wtedy, gdy w widoku SQL operator porównania zostanie zmieniony z powrotem na znak równości (=).
Usuwanie sprzężenia
Jeśli program Access automatycznie utworzył sprzężenie, którego nie chcesz, lub jeśli zostało utworzone przez pomyłkę — na przykład przez sprzężenie między dwoma polami o różnych typach danych — możesz usunąć sprzężenie.
- W siatce projektu kwerendy kliknij sprzężenie, które chcesz usunąć.
- Naciśnij klawisz DELETE.
— lub —
- W siatce projektu zapytania kliknij prawym przyciskiem myszy sprzężenie, które chcesz usunąć, a następnie kliknij polecenie Usuń.
Dodawanie rekordów do tabeli za pomocą kwerendy dołączającejŁączenie wyników kilku kwerend wybierających za pomocą kwerendy składającejTworzenie zapytania opartego na wielu tabelachTworzenie, edytowanie lub usuwanie relacji