Descrierea utilizării asocierilor în Microsoft Query

Ce este o asociere?

O asociere este o conexiune între două tabele în care cele două tabele sunt îmbinate în funcție de un câmp pe care îl au în comun, creând un nou tabel virtual (care poate fi salvat ca tabel real). De exemplu, cu următoarele două tabele: 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 o asociere simplă seamănă cu următorul: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered Tabelul rezultat conține doar înregistrările 2 și 3 în câmpul Join_Field, deoarece acestea sunt singurele înregistrări care există atât în Color_Table, cât și în Pattern_Table. Un exemplu practic de asociere este lista de produse și producători pentru comercianți; un tabel de produse și un tabel de furnizori se pot uni într-un câmp ID produs.

Asocierea internă

Asocierea la exemplul anterior, denumită asociere internă, este cel mai simplu tip de asociere. De obicei, doriți să utilizați doar o fracțiune din câmpurile din tabele. De exemplu, într-o asociere interioară reală, cum ar fi cea de mai sus, este posibil să doriți să excludeți Join_Field, astfel încât asocierea să arate astfel: Color_Field Pattern_Field Blue Striped Green Checkered În Microsoft Query, asocierile interioare sunt tipul de asociere implicit (pentru mai multe informații, consultați pagina 105 din "Ghidul utilizatorului Microsoft Query", versiunea 1,0).

Instrucțiunea SQL

Instrucțiunea SELECT (Language Query) structurat (SQL) este un tip de macrocomandă pe care o puteți utiliza atunci când creați o asociere. Rețineți că SQL este foarte diferit de celelalte limbi de macrocomenzi din Microsoft Excel (Visual Basic for Applications și limba de macrocomenzi Excel 4,0). Nu este necesar să înțelegeți SQL pentru a crea cu ușurință asocieri în Microsoft Query. Fiecare asociere are o instrucțiune SELECT asociată cu aceasta. Puteți vizualiza instrucțiunea SELECT pentru orice asociere în Microsoft Query făcând clic pe "SQL" pe bara de instrumente. Ca și în cazul înregistrării de macrocomenzi Microsoft Excel, puteți utiliza Query pentru a înregistra instrucțiunea SELECT. Spre deosebire de înregistrarea macrocomenzilor din Microsoft Excel, instrumentul de selectare a extrasului de cont este întotdeauna activat și nu poate fi dezactivat. Iată cum poate arăta instrucțiunea SELECT în Microsoft Query pentru asocierea internă de mai sus: 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 Rețineți că utilizăm numele de bază de date ' C:\database ' al cărui. mdb este extensia de nume de fișier pentru o bază de date Microsoft Access, care poate avea mai multe tabele într-un fișier. În unele alte baze de date, cum ar fi dBASE, Paradox și FoxPro, fiecare tabel trebuie să aibă propriul său fișier. În aceste cazuri, sintaxa SQL poate să apară redundantă, deoarece numele tabelului este întotdeauna identic cu numele fișierului fără extensie. Sintaxa SQL variază între motoarele de interogare; de exemplu, în Microsoft Access interogarea din exemplul de mai sus seamănă cu următorul: 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]; Calea către tabel nu este utilizată în Microsoft Access, deoarece tabelul este într-un fișier Microsoft Access. mdb. Chiar dacă un tabel extern este atașat și utilizat în interogare, instrucțiunea SQL Microsoft Access nu afișează calea către tabelul extern.

Asocierea exterioară

Un alt tip de asociere se numește asociere externă. Cu o asociere externă, obțineți toate înregistrările dintr-un tabel și doar acele înregistrări din celălalt tabel care au valori corespondente din primul tabel. Acest lucru poate lăsa unele dintre intrările de câmp necompletate sau "null". Pentru oricare două tabele care urmează să fie Unite, există două asocieri exterioare posibile, "asociere externă la stânga" și "asociere exterioară la dreapta" (denumită în mod normal, deoarece Vizualizați tabelele alăturate). Utilizând cele două tabele anterioare într-un exemplu, următoarea este una dintre cele două asocieri exterioare posibile: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered Cealaltă asociere posibilă este următoarea: Join_Field Color_Field Pattern_Field 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot Notă: într-o asociere, nu vedeți cuvântul "NULL" atunci când vizualizați datele; Utilizați cuvântul cheie "NULL" atunci când lucrați cu asocieri. În Microsoft Query, ambele tipuri de asociere externă se pot crea cu ușurință utilizând mouse-ul (pentru mai multe informații despre această procedură, consultați pagina 112 din "Ghidul utilizatorului Microsoft Query", versiunea 1,0). Iată cum poate arăta instrucțiunea SQL pentru al doilea exemplu de asociere externă: 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} Pentru a crea un exemplu practic pentru o asociere externă, faceți o listă cu produsele unei firme cu cifrele de vânzări pentru produsele care au fost vândute, dar nu excluzând produsele care nu au fost vândute. Pentru a face acest lucru, utilizați un câmp ID produs pentru a vă asocia la un tabel produse și la un tabel de vânzări.

Asocierea scădere

Un al treilea tip de asociere este scăderea asocierii. O asociere scădere este opusul unei asocieri exterioare; include doar acele înregistrări într-un tabel care nu se potrivește cu nicio înregistrare din celălalt tabel. La fel ca asocierile exterioare, există două asocieri posibile de scădere pentru toate cele două tabele la care doriți să vă asociați; cu toate acestea, acestea nu sunt, de obicei, denumite "scădere la stânga asociere" sau "scădere la dreapta asociere". O scădere asociere în mod normal, se returnează câmpuri dintr-un singur tabel, deoarece, prin definiție, câmpurile celuilalt tabel returnează numai valori nule. Mai jos puteți să vă alăturați cu o singură scădere: join_Field Color_Field 1 Red și iată celălalt: Join_Field Pattern_Field 4 Polka-Dot În Microsoft Query, se creează o asociere scădere, creând mai întâi o asociere externă, apoi utilizând criteriile "este NULL" într-un câmp corespunzător (Pattern_Field în primul exemplu de mai sus; Color_Field în al doilea exemplu) pentru a exclude înregistrările care se potrivește între tabele. Iată cum poate să arate instrucțiunea SQL pentru prima scădere a asocierii de mai sus: 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) Pentru a crea un exemplu concret al unei asocieri scădere, o listă de clienți care nu au comandat recent. Pentru a face acest lucru, utilizați un câmp ID comandă pentru a vă asocia la un tabel clienți și la un tabel de comenzi.

Asocierea exterioară completă

Un al patrulea tip de asociere este asocierea exterioară completă. O asociere externă completă este o combinație a unei asocieri exterioare cu asocierea sa de scădere gratuită. O asociere externă completă include toate înregistrările din ambele tabele și îmbină acele înregistrări care sunt comune între cele două tabele. Iată o asociere externă completă: Join_Field Color_Field Pattern_Field 1 Red (NULL) 2 Blue Striped 3 Green Checkered 4 (NULL) Polka-Dot În Microsoft Query, se creează o asociere externă completă prin inserarea operatorului UNION pe propria linie între instrucțiunea de selectare a asocierii exterioare și instrucțiunea SELECT (Vedeți mai sus). Pentru a crea o asociere externă completă în Microsoft Query, utilizați următorii pași pentru a crea propoziția SQL corespunzătoare:

1. Creați o asociere externă, apoi faceți clic pe nou în meniul fișier și creați o asociere scădere.

2. Copiați SQL asociere la scădere.

3. Comutați la asocierea exterioară, tastați cuvântul UNION pe propria linie, sub instrucțiunea SQL asociere externă, lipiți SQL-ul de asociere scade sub cuvântul UNION și închideți fereastra scădere asociere.

Notă Rezultatele tabelului următor pierd valoarea Join_Field "4", deoarece înregistrarea pe care valoarea Join_Field este egală cu 4 se află în tabelul Pattern_Table. Dones't de mai jos SQL propoziție selectează câmpul Pattern_Table. John_Field. Join_Field Color_Field Pattern_Field (NULL) Polka-Dot 1 Red (NULL) 2 Blue Striped 3 Green Checkered Iată cum ar putea arăta SQL asocierea exterioară completă de mai sus (comenzile de sub operatorul Uniunii este lipită): 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) Pentru a crea un exemplu practic pentru o asociere externă completă, îmbinați listele de clienți suprapuse utilizate de diverse departamente, inclusiv numerele de fax (care au fost doar pe prima listă) și numele de e-mail Internet (care se află doar în a doua listă). Fiecare departament poate continua să utilizeze lista sa parțială în timp ce are disponibilă lista completă, conexată. Acestea pot fi asociate unui câmp ID client.

Asocierea automată

Un al cincilea tip de asociere este auto-asocierea. O asociere automată este o conexiune în care un câmp dintr-un tabel este asociat cu un alt câmp într-o copie a aceluiași tabel. Utilizând acest tabel de exemple: Table_Three Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 și o copie a acestuia, după cum urmează: Table_Three_01 Employee_ID Employee_Name Reports_To 1 Bob 3 2 Sue (NULL) 3 Jim 2 4 Jane 3 Se poate utiliza o asociere automată pentru a crea o listă de nume de angajați cu numele supraveghetorului. Employee_ID din Table_Three va fi asociată cu Reports_To în Table_Three_01. Iată cum ar putea arăta la început: Employee_Name Employee_Name Bob Jim Sue (NULL) Jim Sue Jane Jim Cu toate acestea, deoarece este derutant să aveți același nume de câmp pentru ambele câmpuri, modificați unul dintre numele de câmp, după cum urmează: Employee_Name Supervisor Bob Jim Sue (NULL) Jim Sue Jane Jim Iată cum poate arăta SQL pentru asocierea de mai sus: 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 Atunci când returnați date în Microsoft Excel, nu este de niciun folos să redenumiți câmpul în Microsoft Query. Acest lucru este adevărat, deoarece Microsoft Excel utilizează numele de câmp original. Pentru mai multe informații despre această problemă, consultați următorul articol din baza de cunoștințe Microsoft: 121551 : xl5: Field în loc de nume de coloană din MSQUERY a revenit la Excel o macrocomandă Microsoft Excel trebuie să modifice numele coloanei de fiecare dată când se reîmprospătează datele returnate (dacă nu returnați datele dintr-un raport PivotTable, caz în care pivotul însuși poate crea și păstra un

Equi-asocierea și asocierea naturală

Aproape toate asocierile, inclusiv toate exemplele date până acum, sunt equi și asocierile naturale. Semnificația acestor termeni nu are o importanță prea mică pentru utilizatorul mediu al Microsoft Query, dar următoarele două paragrafe încearcă să explice termenii pentru cei care pot fi curioși. Equi-Join este o asociere în care sunt regăsite înregistrările pe baza dacă câmpurile asociere au valori corespondente în ambele tabele. Poate părea o definiție a unei asocieri, dar nu este așa. Un exemplu de asociere non-egal este o asociere în care înregistrările din primul tabel sunt asociate cu acele înregistrări din al doilea tabel în care câmpul asociat din primul tabel este mai mare decât (în loc de egal cu) câmpul asociat în al doilea tabel (sau mai mic decât sau altceva în afară de egal cu). Bineînțeles, aceasta returnează mai multe înregistrări decât un equi-asociere. O asociere naturală este una în care s-a returnat doar unul dintre câmpurile din cele două tabele. Deoarece aceste două câmpuri sunt prin definiție identică într-o equi-asociere, este redundant să le includeți pe ambele. Pentru o asociere non-egală, este important să includeți ambele câmpuri. Prin urmare, equi-asocierile și asocierile naturale merg împreună. Doriți o asociere equi (care descrie majoritatea asocierilor) pentru a fi asociat natural, returnând doar unul dintre câmpurile asociate; Dar, dacă utilizați vreodată o asociere non-egală, este posibil să doriți să o asociați nefiresc, returnând ambele câmpuri asociate. Există și alte tipuri de asocieri. Întregul spectru de asocieri a fost definit cel mai recent în 1992 și acest standard este cunoscut sub numele de SQL-92. Unele asocieri nu sunt importante pentru utilizatorii Microsoft Excel, deoarece aceste asocieri fac lucruri mai ușor de făcut în Microsoft Excel.

Produsul pe care este

Dacă încercați să returnați date din două sau mai multe tabele, fără asocieri, se creează ceea ce se numește "produs". Un produs este definit ca toate combinațiile posibile de rânduri din toate tabelele. Asigurați-vă că v-ați asociat înainte de a încerca să returnați date, deoarece un produs de pe un tabel cu mai multe înregistrări și/sau pe mai multe tabele ar putea dura câteva ore pentru a fi finalizat. Mai jos se află un produs pe care l-ați utilizat în cele două tabele exemplu; Rețineți că acest tabel este de doar 3 înregistrări Times 3 Records, care generează un total de 9 înregistrări. Cu toate acestea, imaginați-vă dacă în schimb tabelul a fost 100 Records Times 1.000 Records Times 10.000 Records; Apoi, tabelul va conține înregistrările 1.000.000.000! 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 Din când în când, unii utilizatori doresc să utilizeze un produs; cu toate acestea, majoritatea utilizatorilor care îi fac să facă acest lucru în mod accidental și sunt deseori confuzi de aceștia. Deoarece majoritatea utilizatorilor exclud majoritatea câmpurilor dintr-o asociere, un produs cu un real număr poate arăta cu ușurință ca fiind nedumerit: Color_Field Red Red Red Blue Blue Blue Green Green Green Dacă sunt adăugate 100 înregistrări la Pattern_Table, această interogare ar avea 309 de înregistrări (103 înregistrează fiecare roșu, albastru și verde). Produsele de la cosma au declarații de selectare relativ simple. Iată cum poate arăta SQL produsul de mai sus: SELECT Color_Table.Color_Field, Pattern_Table.Pattern_Field FROM `c:\database`.Color_Table, `c:\database`.Pattern_Table Un exemplu practic pentru un produs de pe un tabel de pe un număr de pe-o parte este să creați o listă cu toate combinațiile posibile de opțiuni pentru un produs de marfă, cu totaluri de preț pentru fiecare combinație.