Beskrivning av användning av kopplingar i Microsoft Query

Vad är en koppling?

En koppling är en koppling mellan två tabeller där de två tabellerna slås samman enligt ett fält som de har gemensamt och skapar en ny virtuell tabell (som kan sparas som en verklig tabell). Med följande två tabeller: Color_Table: Join_Field Color_Field 1 Red 2 Blue 3 Green Pattern_Table: Join_Field Pattern_Field 2 Striped 3 Checkered 4 Polka-Dot en enkel koppling ser ut ungefär så här: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered Tabellen result innehåller bara posterna 2 och 3 i fältet Join_Field eftersom de är de enda poster som finns i både Color_Table och Pattern_Table. Ett praktiskt exempel på en koppling är en åter försäljares lista över produkter och tillverkare; tabellen produkter och tabellen leverantörer kan vara med i fältet produkt-ID.

Den inre kopplingen

Kopplingen i föregående exempel, kallas inre koppling, är den enklaste typen av koppling. Vanligt vis vill du bara använda en bråkdel av fälten i tabellerna. I en verklig inre koppling, till exempel den som visas ovan, kanske du vill utesluta Join_Field så att kopplingen ser ut så här: Color_Field Pattern_Field Blue Striped Green Checkered I Microsoft Query är inre kopplingar standard kopplings typ (mer information finns i sidan 105 i "Microsoft Query User ' s guide", version 1,0).

SQL-satsen

Ett SQL-uttryck (Structured Query Language) är en typ av makro som du kan använda när du skapar en koppling. Observera att SQL skiljer sig väldigt mycket från Microsoft Excels andra makro språk (VBA (Visual Basic for Applications) och Excel 4,0-makro språk). Det är inte nödvändigt att förstå SQL för att enkelt skapa kopplingar i Microsoft Query. Varje koppling har ett MARKERAT-uttryck som är associerat med det. Du kan visa SELECT-uttrycket för en koppling i Microsoft Query genom att klicka på "SQL" i verktygsfältet. Precis som med Microsoft Excel-makro inspelaren kan du använda Query för att spela in SELECT-satsen. Till skillnad från Microsoft Excel-makro inspelaren är SELECT Statement alltid aktiverat och kan inte inaktive ras. Så här kan SELECT-uttrycket se ut i Microsoft Query för ovanstående inre koppling: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table WHERE Color_Table.Join_Field = Pattern_Table.Join_Field Observera att vi använder databas namnet ' C:\database ' vars. mdb är fil namns tillägget för en Microsoft Access-databas, som kan ha flera tabeller i en fil. I vissa andra databaser, till exempel dBASE, Paradox och FoxPro måste varje tabell ha sin egen fil. I de här fallen kan SQL-syntaxen bli överflödig eftersom tabellens namn alltid är samma som fil namnet utan fil namns tillägget. SQL-syntaxen varierar mellan olika typer av frågor. i Microsoft Access visas exempelvis frågan från exemplet ovan: SELECT Color_Table.[Color_Field], Pattern_Table.Pattern_Field FROM Pattern_Table INNER JOIN Color_Table ON Pattern_Table.[Join_Field] = Color_Table.[Join_Field]; Sökvägen till tabellen används inte i Microsoft Access eftersom tabellen finns i en Microsoft Access. mdb-fil. Även om en extern tabell är bifogad och används i frågan visas inte sökvägen till den externa tabellen i Microsoft Access SQL-instruktionen.

Den yttre kopplingen

En annan typ av koppling kallas en yttre koppling. Med en yttre koppling får du alla poster från en tabell och bara de poster från den andra tabellen som har matchande värden från den första tabellen. Detta kan leda till att vissa fält poster är tomma eller "null". För att två tabeller ska vara kopplade finns det två möjliga yttre kopplingar, "vänster yttre koppling" och "höger yttre koppling" (så kallade, eftersom du vanligt vis visar tabellerna sida vid sida). Med de föregående två tabellerna i ett exempel är följande två möjliga yttre kopplingar: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered Den andra möjliga kopplingen är följande: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot Obs! i en koppling ser du inte ordet "NULL" när du visar data; Använd nyckelordet "NULL" när du arbetar med kopplingar. I Microsoft Query kan båda typerna av yttre koppling enkelt skapas med hjälp av musen (mer information om den här proceduren finns i sidan 112 i "Microsoft Query User ' s guide", version 1,0). Så här kan SQL-uttrycket se ut så här: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} Om du vill skapa ett användbart exempel på en yttre koppling kan du skapa en lista över företagets produkter med försäljnings siffror för de produkter som har sålts, men inte exklusive produkter som inte hade sålts. För att göra det, Använd fältet produkt-ID för att gå med i en Products-tabell och en försäljnings tabell.

Kommandot subtrahera koppling

En tredje typ av koppling är subtrahera-kopplingen. En subtraherad koppling är motsatsen till en yttre koppling; den innehåller bara posterna i en tabell som inte matchar någon post i den andra tabellen. Precis som yttre kopplingar finns det två möjliga subtrahera kopplingar för de två tabeller som du vill ansluta till; Däremot kallas de vanligt vis inte "left subtrahera koppling" eller "Högerjustera koppling". En subtrahera koppling visas normalt från en av tabellerna, eftersom den andra tabellens fält endast returnerar NULL-värden. Här är ett möjligt subtrahera-koppling: join_Field Color_Field 1 Red och här är det andra: Join_Field Pattern_Field 4 Polka-Dot I Microsoft Query skapas en subtrahera koppling genom att först skapa en yttre koppling och sedan använda villkoret "är NULL" på ett lämpligt fält (Pattern_Field i det första exemplet ovan; Color_Field i det andra exemplet) för att utesluta poster som matchar tabellerna. Så här kan SQL-uttrycket leta efter den första subtrahera-kopplingen ovan: SELECT Color_Table.Join_Field, Color_Table.Color_Field FROM {oj `c:\database`.Color_Table LEFT OUTER JOIN `c:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Pattern_Table.Pattern_Field Is Null) För att skapa ett användbart exempel på en subtrahera koppling, en lista över kunder som inte har beställts nyligen. Det gör du genom att gå till tabellen kunder och tabellen Order med fältet order nummer.

Den fullständiga yttre kopplingen

En fjärde sorts koppling är den fullständiga yttre kopplingen. En hel yttre koppling är en kombination av en yttre koppling med motsvarande subtrahera-koppling. En hel yttre koppling inkluderar alla poster från båda tabellerna och sammanfogar de poster som är gemensamma för de två tabellerna. Följande är en hel yttre koppling: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot I Microsoft Query skapas en hel yttre koppling genom att en UNION-operator infogas på en egen linje mellan det yttre kopplings SELECT-uttrycket och SELECT-uttrycket subtrahera koppling (se ovan). Om du vill skapa en hel yttre koppling i Microsoft Query följer du stegen nedan för att skapa motsvarande SQL-mening:

1. Skapa en yttre koppling och klicka sedan på ny på Arkiv-menyn och skapa en subtrahera koppling.

2. Kopiera subtrahera-kopplingens SQL.

3. Växla till den yttre kopplingen, Skriv ordet UNION på en egen rad nedanför det yttre kopplingens SQL-uttryck, klistra in subtrahera-kopplingens SQL nedanför ord föreningen och stäng sedan fönstret subtrahera kopplingens fönster.

Obs! Resultatet av följande tabell saknar Join_Field svärdet "4" eftersom posten som Join_Field är lika med 4 i Pattern_Table tabellen. Under SQL-meningen dones't väljer du fältet Pattern_Table. John_Field. Join_Field Color_Field Pattern_Field (NULL) Polka-Dot 1 Red (NULL) 2 Blue Striped 3 Green Checkered Så här kan SQL leta efter ovanstående fullständiga yttre koppling (kommandon under UNION-operatorn har klistrats in): SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Pattern_Table LEFT OUTER JOIN `C:\database`.Color_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} UNION SELECT Color_Table.Join_Field, Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM {oj `C:\database`.Color_Table LEFT OUTER JOIN `C:\database`.Pattern_Table ON Color_Table.Join_Field = Pattern_Table.Join_Field} WHERE (Color_Table.Color_Field Is Null) or(Pattern_Table.Pattern_Field Is Null) Om du vill skapa ett praktiskt exempel på en hel yttre koppling kan du slå samman överlappande kund listor som används av olika avdelningar, till exempel Fax nummer (som bara fanns på den första listan) och Internet-e-postnamn (som bara finns i den andra listan). Alla avdelningar kan fortsätta att använda sin ofullständiga lista när de har den fullständiga anslutna listan. De kan vara kopplade till ett kund-ID-fält.

Själv koppling

En femte typ av koppling är själv kopplingen. En själv koppling är en koppling där ett fält i en tabell matchas med ett annat fält i en kopia av samma tabell. Med följande exempel tabell: Table_Three Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 och en kopia av den enligt följande: Table_Three_01 Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 En själv koppling kan användas för att skapa en lista över namn på anställda med personens namn. Employee_ID i Table_Three kopplas till Reports_To i Table_Three_01. Så här kan det se ut först: Employee_Name Employee_Name Bob Jim Sue (NULL) Jim Sue Jane Jim Men eftersom det är förvirrande att ha samma fält namn för båda fälten ändrar du ett av fält namnen enligt följande: Employee_Name Supervisor Bob Jim Sue (NULL) Jim Sue Jane Jim Så här kan SQL se ut så här: SELECT table_three.Employee_Name, table_three_01.Employee_Name 'Supervisor' FROM `c:\database`.table_three, `c:\database`.table_three_01 WHERE table_three.Employee_ID = table_three_01.Reports_To När du returnerar data till Microsoft Excel är det inte heller möjligt att byta namn på fältet i Microsoft Query. Det här är sant eftersom Microsoft Excel använder det ursprungliga fält namnet. Mer information om det här problemet finns i följande artikel i Microsoft Knowledge Base: 121551 : xl5: Field i stället för kolumn namn i MSQUERY som returneras till Excel ett Microsoft Excel-makro måste ändra kolumn namnet varje gång de data som returneras uppdateras (såvida du inte returnerar data i en pivottabell).

Equi och naturlig koppling

Nästan alla kopplingar, inklusive alla exempel som ges hittills, är Equi och naturliga kopplingar. Innebörden av dessa termer är av mindre betydelse för medelvärdet av användare av Microsoft Query, men följande två stycken försöker förklara villkoren för dem som kan vara nyfiken. Equi-Join är en koppling där posterna hämtas baserat på om kopplings fälten har matchande värden i båda tabellerna. Det kan verka som bara en definition av en koppling, men det är inte det. Ett exempel på en icke-lika koppling är en koppling där posterna i den första tabellen är kopplade till posterna i den andra tabellen där det kopplade fältet i den första tabellen är större än (i stället för lika med) det kopplade fältet i den andra tabellen (eller mindre än eller något annat än lika med). Naturligt, returnerar detta fler poster än en Equi-Join. En naturlig koppling är en i vilken endast ett av de två tabellernas kopplade fält returneras. Eftersom de här två fälten följer definitionen i en Equi-Join är det överflödigt att inkludera båda. För en icke-lika koppling är det viktigt att lägga till båda dessa fält. Så Equi-kopplingar och naturliga kopplingar går ihop. Du vill att en Equi (som beskriver de flesta kopplingar) ska vara naturlig koppling genom att bara returnera ett av de kopplade fälten; men om du någon gång använder en icke-likvärdig koppling kanske du vill göra den till en icke naturlig koppling genom att returnera båda kopplade fälten. Det finns andra typer av kopplingar. Hela spektrumet av kopplingar definierades senast i 1992 och den här standarden kallas SQL-92. Vissa kopplingar är inte viktiga för Microsoft Excel-användare eftersom dessa kopplingar gör saker som är enklare att göra i Microsoft Excel.

Produkten cartesian-produkten

Om du försöker returnera data från två eller fler tabeller utan kopplingar skapas vad som kallas "produkten cartesian Product". En produkten cartesian-produkt definieras som alla möjliga kombinationer av rader i alla tabeller. Se till att du har kopplingar innan du försöker att returnera data, eftersom en produkten cartesian produkt i tabeller med många poster och/eller många tabeller kan ta flera timmar att slutföra. Följande är en produkten cartesian-produkt som används i de två exempel tabellerna; Observera att den här tabellen endast är tre poster som ger en total summa på 9 poster. Tänk dig att om du i stället har tabellen, var 100 postarna 1 000 poster gånger 10 000 poster; då innehåller tabellen 1 000 000 000-poster. Join_Field Join_Field Color_Field Pattern_Field 1 2 Red Striped 1 3 Red Checkered 1 4 Red Polka-Dot 2 2 Blue Striped 2 3 Blue Checkered 2 4 Blue Polka-Dot 3 2 Green Striped 3 3 Green Checkered 3 4 Green Polka-Dot Ibland vill vissa användare använda en produkten cartesian produkt; de flesta användare som får dem gör det oavsiktligt, och kan ofta förvirra av dem. Eftersom de flesta användare utesluter de flesta fälten i en koppling kan en verklig produkten cartesian-produkt enkelt se ut som perplexing: Color_Field Red Red Red Blue Blue Blue Green Green Green Om 100-poster läggs till i Pattern_Table kan den här frågan ha 309-poster (103 innehåller var och en av rött, blått och grönt). Produkten cartesian-produkter har enkla SELECT-uttryck. Så här kan SQL se upp för ovanstående produkten cartesian-produkt: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table Ett praktiskt exempel på en produkten cartesian produkt är att skapa en lista över alla möjliga kombinationer av alternativ på en produkt, med total pris för varje kombination.