plik

Wydawnictwo Helion
ul. Koœciuszki 1c
44-100 Gliwice
tel. 032 230 98 63

e-mail: helion@helion.pl

Access. Analiza danych.
Receptury

Autor: Ken Bluttman, Wayne Freeze
T³umaczenie: Grzegorz Kowalczyk
ISBN: 978-83-246-1285-7
Tytu³ orygina³u:

Access Data Analysis Cookbook

Format: B5, stron: 360

Korzystaj z bazy danych Access jak profesjonalista!

•

Jak stosowaæ wskaŸniki statystyczne do analizy danych biznesowych?

•

Jak rozszerzaæ funkcjonalnoœæ zapytañ SQL, stosuj¹c skrypty VBA?

•

Jak przetwarzaæ dane i przenosiæ je miêdzy bazami Access?

Access to znane ju¿ narzêdzie s³u¿¹ce do wszechstronnego przetwarzania i analizy
danych. Posiada sporo ukrytych mechanizmów, pozwalaj¹cych efektywnie wykonywaæ
zadania, które pocz¹tkowo mog¹ wydawaæ siê skomplikowane. Ksi¹¿ka przedstawia
przyk³ady kwerend, metody przenoszenia danych pomiêdzy bazami Access, obliczania
wielu wskaŸników finansowo-biznesowych i sporo innych zagadnieñ – wszystko pod
k¹tem analizy i przetwarzania danych. Ka¿da zaprezentowana receptura jest opatrzona
kompletnym opisem rozwi¹zania problemu wraz ze szczegó³owym omówieniem metody
postêpowania oraz analiz¹ kodu.

Access. Analiza danych. Receptury to uniwersalny podrêcznik przeznaczony zarówno
dla pocz¹tkuj¹cych u¿ytkowników bazy danych Access, jak i doœwiadczonych.
Dziêki przejrzystemu jêzykowi i mnogoœci poruszonych zagadnieñ ka¿dy, niezale¿nie
od stopnia zaawansowania, mo¿e poszerzyæ swoj¹ wiedzê. Zawiera mnóstwo ciekawych
wskazówek i technik u³atwiaj¹cych codzienn¹ pracê z bazami danych, co czyni j¹
atrakcyjn¹ nawet dla osób doskonale pos³uguj¹cych siê baz¹ Access. Jest to tak¿e
kompendium wiedzy niezbêdnej ka¿demu, kto chce wyci¹gaæ ze zbiorów danych
naprawdê cenne informacje.

•

Tworzenie kwerend ró¿nych typów

•

Wstawianie, aktualizacja i usuwanie danych

•

Przetwarzanie tekstu i liczb zapisanych w formie ³añcucha znaków

•

Zastosowanie tabel, modyfikacja zawartoœci systemu Windows, szyfrowanie danych

•

Wykorzystanie obiektu FileSystemObject, przetwarzanie danych XML oraz XSLT,

komunikacja z bazami SQL

•

Rozwi¹zywanie problemów biznesowych

•

Obliczanie wskaŸników statystycznych

Baza danych to fundament biznesu – zobacz, jak efektywnie ni¹ zarz¹dzaæ!

Spis tre

ļci

Przedmowa ...............................................................................................................................9

1. Tworzenie kwerend .................................................................................................... 15

1.1.

Wyszukiwanie niedopasowanych rekordów

1.2.

Zastosowanie operatorów AND i OR

1.3.

Kryteria wykorzystujñce operator IN

1.4.

Wyäñczanie rekordów z zapytania przy uĔyciu operatora NOT

1.5.

Parametryzacja zapytaþ

1.6.

Zwracanie n rekordów z górnej lub dolnej czöĈci zakresu wyszukiwania

1.7.

Zwracanie unikatowych rekordów

1.8.

Zwracanie losowo wybranych rekordów

1.9.

Dostrajanie wyszukiwania przy uĔyciu podzapytaþ

1.10. ãñczenie danych za pomocñ operacji UNION

1.11. Dynamiczne wstawianie pól w kwerendzie wybierajñcej

1.12. Zastosowanie aliasów do upraszczania wyraĔeþ SQL

1.13. Lewe sprzöĔenie zewnötrzne — tworzenie i zastosowanie

1.14. Prawe sprzöĔenie zewnötrzne — tworzenie i zastosowanie

1.15. Tworzenie peänego sprzöĔenia zewnötrznego

2. Obliczenia w kwerendach ........................................................................................... 57

2.1.

Obliczanie sum i wartoĈci Ĉrednich zbioru danych

2.2.

Obliczanie liczby elementów w danej grupie

2.3.

Zastosowanie wyraĔeþ w kwerendach

2.4.

Zastosowanie funkcji wäasnych w kwerendach

2.5.

Zastosowanie wyraĔeþ regularnych w kwerendach

2.6.

Iloczyn kartezjaþski, czyli jak otrzymaè wszystkie kombinacje danych

2.7.

Tworzenie i zastosowanie kwerend krzyĔowych

Spis tre

ļci

3. Kwerendy funkcjonalne .............................................................................................. 81

3.1.

Kwerendy aktualizujñce

3.2.

Doäñczanie danych

3.3.

Usuwanie danych

3.4.

Kwerendy tworzñce tabele

4. Zarz

édzanie tabelami, polami, indeksami i kwerendami ..........................................99

4.1.

Programowe tworzenie tabel

4.2.

Modyfikacja struktury tabeli

106

4.3.

Tworzenie i zastosowanie indeksów

109

4.4.

Programowe usuwanie tabel

111

4.5.

Programowe tworzenie kwerend

112

5. Przetwarzanie ci

égów znaków .................................................................................115

5.1.

Wyodröbnianie wybranej liczby znaków z lewej lub prawej strony ciñgu
alfanumerycznego

115

5.2.

Wyodröbnianie znaków z äaþcucha znaków, kiedy znana jest pozycja
poczñtkowa i däugoĈè

117

5.3.

OkreĈlanie pozycji poczñtkowej znanego podciñgu znaków

118

5.4.

Obcinanie spacji z koþca äaþcucha znaków

120

5.5.

Usuwanie spacji z wnötrza äaþcucha znaków

123

5.6.

Zamiana jednego äaþcucha znaków na inny

124

5.7.

Konkatenacja äaþcuchów znaków

126

5.8.

Sortowanie liczb zapisanych w postaci tekstowej

129

5.9.

Kategoryzacja znaków na podstawie kodów ASCII

132

6. Programowe przetwarzanie danych ........................................................................ 135

6.1.

Wykorzystywanie funkcji programu Excel z poziomu bazy Access

135

6.2.

Przetwarzanie danych przechowywanych w pamiöci

140

6.3.

Zastosowanie tablic wielowymiarowych

143

6.4.

Sortowanie tablic

146

6.5.

Späaszczanie struktury danych

150

6.6.

Rozszerzanie struktury danych

154

6.7.

Szyfrowanie danych

156

6.8.

Wyszukiwanie wartoĈci zbliĔonych do wzorca

159

6.9.

Przetwarzanie transakcyjne

163

6.10. Odczytywanie i zapisywanie danych z rejestrów systemu Windows

165

6.11. Przetwarzanie kodu HTML stron sieci WWW

168

6.12. Formatowanie raportów definiowanych przez uĔytkownika

171

6.13. Zaokrñglanie wartoĈci

174

6.14. Korespondencja seryjna

177

6.15. Tworzenie formularzy budowania kwerend

180

Spis tre

ļci

7. Import i eksport danych ............................................................................................ 185

7.1.

Tworzenie specyfikacji importu lub eksportu

185

7.2.

Automatyzacja operacji importu i eksportu danych

191

7.3.

Eksportowanie danych przy uĔyciu obiektu FileSystemObject

194

7.4.

Importowanie danych przy uĔyciu obiektu FileSystemObject

196

7.5.

Importowanie i eksportowanie plików przy uĔyciu XML

201

7.6.

Generowanie schematów XML

204

7.7.

Zastosowanie jözyka XSLT w operacjach importu i eksportu danych

206

7.8.

Wykorzystanie XML za poĈrednictwem parsera MSXML

209

7.9.

Odczytywanie i zapisywanie atrybutów XML

213

7.10. Tworzenie Ēródeä danych RSS

215

7.11. Przekazywanie parametrów do bazy danych SQL Server

218

7.12. Obsäuga wartoĈci zwracanych przez procedury osadzone bazy SQL Server 220
7.13. Praca z typami danych bazy SQL Server

221

7.14. Obsäuga osadzonych znaków cudzysäowu

223

7.15. Importowanie kalendarza spotkaþ z programu Outlook

224

7.16. Importowanie wiadomoĈci poczty elektronicznej z programu Outlook

227

7.17. Importowanie listy kontaktów z programu Outlook

229

7.18. Importowanie danych z programu Excel

232

7.19. Eksportowanie danych do programu Excel

235

7.20. Wspóäpraca z programem PowerPoint

237

7.21. Wybieranie danych losowych

240

8. Obliczanie daty i czasu ..............................................................................................243

8.1.

Obliczanie czasu

243

8.2.

Obliczanie czasu z uwzglödnieniem wyjñtków

247

8.3.

Przeliczanie stref czasowych

249

8.4.

Lata przestöpne w obliczeniach

252

8.5.

Rozkäadanie dat na elementy skäadowe

253

8.6.

Rozkäadanie czasu na elementy skäadowe

256

8.7.

Dodawanie wartoĈci reprezentujñcych czas

257

9. Obliczenia biznesowo-finansowe ............................................................................ 261

9.1.

Obliczanie Ĉredniej waĔonej

261

9.2.

Obliczanie Ĉredniej kroczñcej

263

9.3.

Obliczanie okresów zwrotu inwestycji

264

9.4.

Obliczanie stopy zwrotu inwestycji

266

9.5.

Obliczanie amortyzacji liniowej

267

9.6.

Tworzenie harmonogramu späaty kredytu

270

9.7.

Zastosowanie tabel przestawnych i wykresów przestawnych

272

9.8.

Tworzenie tabel przestawnych

274

Spis tre

ļci

9.9.

Prezentacja danych na wykresach

279

9.10. Odszukiwanie trendów danych

281

9.11. Znajdowanie formacji „gäowa i ramiona”

285

9.12. Wyznaczanie wstög Bollingera

295

9.13. Obliczanie odlegäoĈci na podstawie kodów pocztowych

298

10. Obliczenia statystyczne ............................................................................................305

10.1. Tworzenie histogramów

305

10.2. Obliczanie i porównywanie Ĉredniej, mediany oraz dominanty

308

10.3. Obliczanie wariancji zbioru danych

311

10.4. Obliczanie kowariancji dwóch zbiorów danych

314

10.5. Obliczanie korelacji dwóch zbiorów danych

315

10.6. Wyznaczanie wszystkich permutacji elementów zbioru danych

316

10.7. Wyznaczanie wszystkich moĔliwych kombinacji elementów zbioru danych 319
10.8. Obliczanie czöstoĈci wystöpowania wartoĈci w zbiorze danych

321

10.9. Obliczanie rocznej stopy wzrostu

322

10.10. Obliczanie funkcji rozkäadu prawdopodobieþstwa dla zbioru danych

325

10.11. Obliczanie wartoĈci kurtozy

327

10.12. Obliczanie wspóäczynnika asymetrii krzywej rozkäadu zbioru danych

331

10.13. Procentowy podziaä zakresu wartoĈci zbioru danych

333

10.14. OkreĈlanie rangi wartoĈci poszczególnych elementów danych

335

10.15. Obliczanie wspóäczynników regresji liniowej

336

10.16. Wyznaczanie zmiennoĈci danych

338

Skorowidz .............................................................................................................................343

ROZDZIA

Ĥ 3.

Kwerendy funkcjonalne

Kwerendy dzielimy na pasywne i funkcjonalne. Kwerendy pasywne, takie jak standardowe kwe-
rendy wybierajñce, zwracajñ zestawy rekordów speäniajñcych kryteria wyszukiwania, ale w Ĕaden
sposób nie modyfikujñ danych (kwerendy pasywne nie modyfikujñ danych w tabelach Ēródäo-
wych ani nie przechowujñ zwracanych rekordów däuĔej niĔ dana kwerenda jest aktywna).

Kwerendy aktywne mogñ modyfikowaè dane Ēródäowe, a zestawy rekordów zwracanych przez
takie kwerendy mogñ byè dostöpne w nieskoþczonoĈè. Na przykäad kwerenda usuwajñca, jak
sama nazwa wskazuje, usuwa rekordy z tabel Ēródäowych bazy danych — jest to procedura caä-
kowicie niszczñca dane. JeĔeli takie usuniöte informacje nie zostaäy wczeĈniej zapisane w kopii
bezpieczeþstwa, to ich odzyskanie nie bödzie moĔliwe. Kwerendy aktualizujñce równieĔ mogñ
modyfikowaè dane Ēródäowe, zmieniajñc informacje przechowywane w poszczególnych rekor-
dach — podobnie jak w przypadku kwerend usuwajñcych, jeĔeli aktualizowane rekordy nie
zostaäy wczeĈniej zapisane w kopii bezpieczeþstwa, to po ich aktualizacji przywrócenie poprzed-
nich wartoĈci nie bödzie moĔliwe.

Kwerendy doäñczajñce oraz kwerendy tworzñce tabele naleĔñ do grupy kwerend funkcjonal-
nych, które nie modyfikujñ danych Ēródäowych, ale za to powodujñ, Ĕe zwracane rekordy sñ
przechowywane däuĔej, niĔ wynosi czas aktywnoĈci kwerendy. Kwerendy doäñczajñce dodajñ
zwracane rekordy do istniejñcych tabel, a kwerendy tworzñce tabele umieszczajñ zwracane
rekordy w nowych tabelach. W tym rozdziale bödziemy szczegóäowo omawiali wszystkie cztery
rodzaje kwerend funkcjonalnych.

3.1. Kwerendy aktualizuj

éce

Opis problemu

Mamy danñ tabelö, której rekordy wymagajñ modyfikacji. Pole Nazwa stanu przechowuje dwu-
literowe skróty nazw stanów. Naszym zadaniem jest zamiana tych skrótów na peäne nazwy
stanów. Jak tego dokonaè?

Rozdzia

ĥ 3. Kwerendy funkcjonalne

Rozwi

ézanie

Rozwiñzaniem problemu bödzie zastosowanie kwerendy aktualizujñcej, którñ moĔemy w prosty
sposób utworzyè z poziomu widoku projektu kwerendy. Aby tego dokonaè, naleĔy po przeäñ-
czeniu do widoku projektu kwerendy wybraè z menu gäównego polecenie Kwerenda/Kwerenda
aktualizujñca, co spowoduje odpowiednie przygotowanie siatki projektu kwerendy.

W wersji Access 2007 naleĔy najpierw przy uĔyciu WstñĔki utworzyè pusty projekt kwerendy,
a nastöpnie na karcie Projektowanie w grupie Typ kwerendy kliknñè polecenie Aktualizuj.

Po utworzeniu kwerendy aktualizujñcej w siatce projektu kwerendy pojawi siö dodatkowy wiersz
o nazwie Aktualizacja do, natomiast zniknñ wiersze Sortuj i PokaĔ znane z kwerend wybierajñcych.

Na rysunku 3.1 przedstawiono projekt kwerendy aktualizujñcej, której zadaniem jest zastñpienie
w polu Nazwa stanu wszystkich wystñpieþ akronimu

na peänñ nazwö

New York

Rysunek 3.1. Prosta kwerenda aktualizujñca

Zwróèmy uwagö, Ĕe w wierszu Kryteria znajduje siö wartoĈè

— jest to wartoĈè, która bödzie

poszukiwana i zamieniana na nowñ. WartoĈè wpisana w wierszu Aktualizacja do —

New York

—

to nowa wartoĈè, która zastñpi wartoĈè poszukiwanñ. Odpowiednie uĔycie kryteriów ma tutaj
ogromne znaczenie, bo przecieĔ chcemy zmodyfikowaè tylko rekordy, które w polu Nazwa stanu
majñ wartoĈè

Kwerenda aktualizujñca nie musi posiadaè klauzuli

Where

(która odpowiednio filtruje rekordy

i pozwala na aktualizacjö tylko tych, które sñ zgodne z podanym wzorcem wyszukiwania) —
kwerenda aktualizujñca moĔe aktualizowaè wszystkie rekordy w bazie. W pewnych sytuacjach
taki zakres dziaäania kwerendy moĔe byè bardzo poĔñdany, ale w naszym przykäadzie aktuali-
zacja wszystkich rekordów w tabeli klientów byäaby prawdziwñ katastrofñ (jak pamiötamy, jeĔeli
nie mamy kopii zapasowej tabeli, to nie moĔemy anulowaè zmian wprowadzonych przez kwe-
rendö aktualizujñcñ i przywróciè poprzedniej zawartoĈci tabeli).

Po uruchomieniu kwerendy na ekranie pojawi siö okno dialogowe z proĈbñ o potwierdzenie
zamiaru wykonania takiej operacji, przedstawione na rysunku 3.2. Aby kontynuowaè, naleĔy
nacisnñè przycisk Tak.

3.1. Kwerendy aktualizuj

éce

Rysunek 3.2. Okno dialogowe z proĈbñ o potwierdzenie zamiaru wykonania aktualizacji

Przedstawiona powyĔej kwerenda aktualizuje nazwö tylko jednego stanu. Jest to rozwiñzanie do
przyjöcia, aczkolwiek uruchamianie podobnej kwerendy aktualizujñcej 50 razy (po jednej kwe-
rendzie aktualizujñcej dla kaĔdego stanu) moĔe byè dosyè czasochäonne i nuĔñce. Na szczöĈcie
istniejñ równieĔ bardziej efektywne metody.

Jednym z moĔliwych rozwiñzaþ jest wywoäanie z poziomu kwerendy wäasnej funkcji prze-
twarzajñcej dane. W naszym przypadku taka funkcja powinna pobieraè akronim nazwy sta-
nu i zwracaè jego peänñ nazwö. PoniĔej przedstawiamy przykäadowy kod takiej funkcji:

Function new_state_name(current_state_name As String) As String
new_state_name = current_state_name
   If current_state_name = "NY" Then new_state_name = "New York"
   If current_state_name = "CT" Then new_state_name = "Connecticut"
   If current_state_name = "MA" Then new_state_name = "Massachusetts"
   If current_state_name = "CA" Then new_state_name = "California"
End Function

Jak widaè, dla uproszczenia w kodzie naszej przykäadowej funkcji zakodowaliĈmy zaledwie
kilka akronimów nazw stanów, ale oczywiĈcie nic nie stoi na przeszkodzie, aby umieĈciè tam
wszystkie 50 nazw stanów (bñdĒ równie dobrze tylko niektóre, wybrane nazwy stanów). Kodo-
wanie wszystkich 50 stanów moĔe byè nieco Ĕmudnym zajöciem, ale przynajmniej w efekcie
otrzymamy dosyè uniwersalnñ, w peäni uĔytecznñ funkcjö.

Pierwszy wiersz kodu funkcji ustawia wynik dziaäania funkcji na wartoĈè bödñcñ argumentem
funkcji (bieĔñcy akronim nazwy stanu), dziöki czemu jeĔeli peäna nazwa stanu nie zostanie odna-
leziona, funkcja zwróci akronim nazwy stanu i w zasadzie w takim rekordzie nic siö nie zmieni.
JeĔeli jednak akronim bödñcy argumentem funkcji pasuje do któregoĈ z poleceþ

, funkcja jako

wynik swojego dziaäania zwraca peänñ nazwö stanu.

Na rysunku 3.3 przedstawiono wyglñd siatki projektu takiej kwerendy aktualizujñcej. Zwróèmy
uwagö na fakt, Ĕe tym razem nie mamy ustawionych Ĕadnych kryteriów. Brak kryteriów wyszu-
kiwania wynika z prostego faktu, Ĕe chcemy przetwarzaè wszystkie rekordy tabeli. Sama funkcja
wywoäywana jest w wierszu Aktualizacja do, a jako argumentu wywoäania funkcji uĔywamy
wartoĈci pola Nazwa stanu.

Wyniki dziaäania kwerendy przedstawiono na rysunku 3.4. Jak widaè, dla tych stanów, które
zostaäy w funkcji odpowiednio zakodowane przy uĔyciu poleceþ

, akronimy zostaäy zastñpione

peänymi nazwami stanów; dla wszystkich pozostaäych stanów oryginalne akronimy nie zostaäy
zaktualizowane.

Jeszcze innym sposobem dokonania konwersji akronimów na peäne nazwy stanów jest zastoso-
wanie wbudowanej funkcji

Dlookup

. W naszym przykäadzie uĔyjemy tabeli Stany, przechowu-

jñcej nazwy stanów. Tabela posiada dwa pola: Akronim oraz Peäna nazwa stanu. Wyglñd tej tabeli
zostaä przedstawiony na rysunku 3.5.

3.1. Kwerendy aktualizuj

éce

Wyglñd siatki projektu kwerendy zostaä przedstawiony na rysunku 3.6.

Rysunek 3.6. Kwerenda aktualizujñca z funkcjñ DlookUp

Wywoäanie funkcji

DlookUp

nastöpuje w wierszu Aktualizacja do i wyglñda nastöpujñco:

DLookUp("[Pe

Īna nazwa stanu]";"Stany";"[Akronim]='" & [Nazwa stanu] & "'")

Peäna nazwa stanu oraz Akronim to nazwy dwóch pól tabeli Stany, a Nazwa stanu to pole w tabeli
Klienci. W tabeli Stany kaĔdy stan posiada swój rekord przechowujñcy peänñ nazwö stanu oraz
jej akronim. Dziöki takiemu rozwiñzaniu po uruchomieniu nasza kwerenda dokona zamiany
akronimów na peäne nazwy dla wszystkich 50 stanów (pod warunkiem oczywiĈcie, Ĕe w tabeli
Stany nie ma Ĕadnych bäödów).

Omówienie

Wszystkie kwerendy aktualizujñce, o których mówiliĈmy do tej pory, pracowaäy tylko na jednym
polu tabeli. Kod SQL kwerendy przedstawionej na rysunku 3.1 jest krótki i wyglñda nastöpujñco:

UPDATE Klienci SET Klienci.[Nazwa stanu] = "New York"
WHERE (((Klienci.[Nazwa stanu])="NY"));

Kod SQL kwerendy aktualizujñcej zawsze rozpoczyna siö od säowa kluczowego

UPDATE

, po

którym nastöpuje nazwa tabeli i klauzula

SET

wskazujñca pole, które bödzie aktualizowane

(jednoczeĈnie moĔemy aktualizowaè wiöcej niĔ jedno pole, o czym bödziemy mówiè juĔ za
chwilö). Dowolne kryteria wyszukiwania uĔywane do ograniczenia liczby aktualizowanych
rekordów sñ definiowane w klauzuli

WHERE

Kryteria wyszukiwania nie muszñ bazowaè na polu, które jest aktualizowane. W poprzednim
przykäadzie kryteria wyszukiwania operowaäy co prawda na aktualizowanym polu, ale porów-
najmy kod poprzedniej kwerendy z nastöpujñcym zapytaniem SQL:

UPDATE Klienci SET Klienci.[Nazwa stanu] = "New York"
WHERE (((Klienci.Miasto)="New York City"));

W tym przykäadzie pole Nazwa stanu jest aktualizowane tylko dla tych rekordów, w których pole
Miasto ma wartoĈè

New York City

Rozdzia

ĥ 3. Kwerendy funkcjonalne

Jedna kwerenda moĔe aktualizowaè dowolnñ iloĈè pól, aczkolwiek naleĔy pamiötaè o tym, Ĕe
dla wszystkich pól obowiñzujñ te same kryteria wyszukiwania zdefiniowane w kwerendzie. Na
rysunku 3.7 przedstawiono projekt kwerendy, która dla wszystkich klientów majñcych status

Nieaktywny

ustawia wartoĈè trzech pól na

Pusty

Rysunek 3.7. Kwerenda aktualizujñca kilka pól jednoczeĈnie

Kod SQL kwerendy przedstawionej powyĔej wyglñda nastöpujñco:

UPDATE Klienci_Status SET
Klienci_Status.[Nazwa klienta] = "Pusty",
Klienci_Status.Miasto = "Pusty",
Klienci_Status.[Nazwa stanu] = "Pusty"
WHERE (((Klienci_Status.Status)="Nieaktywny"));

Zgodnie z wymogami skäadni jözyka SQL kwerenda rozpoczyna siö od säowa kluczowego

UPDATE

, po którym nastöpuje nazwa tabeli i klauzula

SET

. Nastöpnie wymieniane sñ wszystkie

pola, którym przypisywane sñ nowe wartoĈci. Kod kwerendy koþczy klauzula

WHERE

definiu-

jñca kryteria wyszukiwania (o ile oczywiĈcie takie kryteria zostaäy zdefiniowane).

3.2. Do

ĥéczanie danych

Opis problemu

Chcemy, aby rekordy zwracane przez danñ kwerendö byäy doäñczane do innej tabeli. Jak moĔna
tego dokonaè?

Rozwi

ézanie

Bardzo czösto spotykamy siö z koniecznoĈciñ archiwizacji starszych danych, zakoþczonych trans-
akcji i innych tego typu informacji. Zazwyczaj takie operacje sñ realizowane poprzez przenie-
sienie odpowiednich rekordów do innych tabel przechowujñcych zarchiwizowane czy teĔ histo-
ryczne dane. Takie tabele majñ zazwyczaj identycznñ strukturö jak tablice Ēródäowe, dziöki czemu
przenoszenie rekordów pomiödzy nimi jest bardzo proste i wygodne. Warto jednak pamiötaè
o tym, Ĕe nie jest to Ĕaden twardy wymóg — tabele przechowujñce dane archiwalne mogñ mieè

3.2. Do

ĥéczanie danych

dodatkowe pola, w których umieszczane sñ takie informacje jak data przeniesienia rekordu do
archiwum, kto zatwierdziä archiwizacjö danego rekordu i tak dalej.

Prawdziwym woäem roboczym takich rozwiñzaþ jest jedna z kwerend funkcjonalnych — kwe-
renda doäñczajñca (ang. append query). Jak sama nazwa wskazuje, kwerenda doäñczajñca dodaje
rekordy do istniejñcej tabeli. Bardzo czösto doäñczane rekordy sñ pobierane z innej tabeli, ale
równie dobrze doäñczane rekordy mogñ byè generowane przez jakiĈ proces, wartoĈci poszcze-
gólnych pól mogñ byè wyliczane bñdĒ nawet mogñ byè pobierane z tej samej tabeli.

Doäñczajñc do tabeli rekordy pobierane z tej samej tabeli, naleĔy uwaĔaè, aby nie doszäo
do dublowania wartoĈci unikalnego klucza tabeli.

Na rysunku 3.8 przedstawiono tabelö Transakcje przechowujñcñ informacje o transakcjach doko-
nywanych przez poszczególnych klientów. Rekordy opisujñ transakcje przeprowadzane w róĔ-
nych dniach, opiewajñce na róĔne kwoty i posiadajñce róĔne statusy.

Rysunek 3.8. Tabela przechowujñca informacje o transakcjach

Jednym z zadaþ, które sñ czösto wykonywane w takich sytuacjach, jest wyczyszczenie tabeli ze
starych rekordów opisujñcych zakoþczone transakcje. ZaäóĔmy, Ĕe mamy do dyspozycji drugñ
tabelö, ArchiwumTransakcji, która przechowuje takie rekordy. W prosty sposób moĔemy teraz
utworzyè projekt kwerendy, która odfiltruje zakoþczone transakcje i umoĔliwi przeniesienie ich
do archiwum. Na rysunku 3.9 przedstawiono wyglñd projektu kwerendy doäñczajñcej, wyszu-
kujñcej rekordy majñce datö transakcji wczeĈniejszñ niĔ

2006-04-01

i status

Zrealizowana

Uruchomienie tej kwerendy spowoduje umieszczenie takich rekordów w tabeli ArchiwumTrans-
akcji (na rysunku 3.9 nie widaè tego wprost, ale tabela ArchiwumTransakcji zostaäa wybrana z listy
Nazwa tabeli podczas tworzenia kwerendy doäñczajñcej w oknie dialogowym Doäñczanie).

Rozdzia

ĥ 3. Kwerendy funkcjonalne

Rysunek 3.9. Projekt kwerendy doäñczajñcej

JeĔeli struktura tabeli ArchiwumTransakcji jest identyczna jak tabeli Transakcje, to w wierszu Do-
äñczanie do nazwy odpowiednich pól pojawiñ siö automatycznie. JeĔeli doäñczamy rekordy do
tabeli o innej strukturze, to musimy röcznie wybraè odpowiednie pola Ēródäowe i dopasowaè
do nich pola docelowe.

Kod SQL kwerendy doäñczajñcej przedstawionej na rysunku 3.9 wyglñda nastöpujñco:

INSERT INTO ArchiwumTransakcji
( [ID klienta], [Data transakcji], [Kwota transakcji], Status )
SELECT Transakcje.[ID klienta],
Transakcje.[Data transakcji],
Transakcje.[Kwota transakcji],
Transakcje.Status
FROM Transakcje
WHERE (((Transakcje.[Data transakcji])<#2006-04-01#)
AND ((Transakcje.Status)="Zrealizowana"));

Kod SQL kwerendy doäñczajñcej rozpoczyna siö od säów kluczowych

INSERT INTO

, po których

nastöpuje nazwa tabeli docelowej oraz umieszczona w nawiasach lista pól tabeli docelowej. Na-
stöpnie w skäadni kwerendy pojawia siö wyraĔenie

SELECT

, które pobiera odpowiednie rekordy

z tabeli Ēródäowej (w naszym przypadku jest to tabela Transakcje), zgodnie ze zdefiniowanymi
kryteriami wyszukiwania. Warto zwróciè uwagö na fakt, Ĕe iloĈè pól wybranych z tabeli Ēró-
däowej musi odpowiadaè iloĈè pól w tabeli docelowej. Kwerenda doäñczajñca nie musi opero-
waè na wszystkich polach tabeli Ēródäowej.

Po uruchomieniu naszej kwerendy rekordy sñ dodawane do tabeli ArchiwumTransakcji. Ale to
dopiero poäowa caäej historii — wybrane rekordy zostaäy skopiowane do tabeli archiwalnej, ale
ich oryginaäy nadal istniejñ w tabeli Ēródäowej. Aby je usunñè, musimy uĔyè odpowiedniej kwe-
rendy usuwajñcej. Kwerendy usuwajñce zostanñ szczegóäowo omówione w podrozdziale 3.3.

Omówienie

PowyĔszy przykäad dobrze ilustruje typowy sposób dziaäania kwerend doäñczajñcych: pod-
kwerenda wybiera z tabeli Ēródäowej odpowiednie rekordy, wyszukiwane w zaleĔnoĈci od zde-
finiowanych (bñdĒ nie) kryteriów wyszukiwania i nastöpnie wyszukane rekordy sñ doäñczane do
tabeli docelowej.

3.2. Do

ĥéczanie danych

Teraz przyjrzymy siö innej metodzie wstawiania rekordów do tabeli, w której zamiast pól
z tabeli Ēródäowej do pól tabeli docelowej bödziemy bezpoĈrednio wstawiali odpowiednie
wartoĈci. W przedstawionym poniĔej kodzie SQL kwerendy doäñczajñcej rolö tabeli docelowej
nadal speänia tabela ArchiwumTransakcji, ale tym razem do tabeli docelowej wstawiamy zako-
dowane „na sztywno” wartoĈci zdefiniowane w klauzuli

VALUES

. Warto zauwaĔyè, Ĕe wstawiane

wartoĈci muszñ byè umieszczone w nawiasach.

INSERT INTO ArchiwumTransakcji
VALUES (2000, #2006-04-10#, 35.25, "Zrealizowana");

Tworzñc takie kwerendy doäñczajñce, musimy pamiötaè o nastöpujñcych kluczowych elementach:

INSERT INTO <nazwa tabeli>

to prawidäowy sposób rozpoczynania wyraĔenia SQL.

Za pomocñ klauzuli

VALUES

moĔna „na sztywno” zakodowaè wartoĈci wstawiane do tabeli.

Poszczególne wartoĈci muszñ byè odpowiednio dopasowane do typu poszczególnych pól
w tabeli, do których sñ wstawiane. WartoĈci numeryczne nie potrzebujñ Ĕadnych kwalifi-
katorów i mogñ byè wstawiane bezpoĈrednio w kodzie SQL kwerendy jako liczby caäkowite,
rzeczywiste itd. (na przykäad

2000

czy

35.25

). Daty muszñ byè ujöte w znaki krzyĔyka

(ang. hash), a tekst (ciñgi alfanumeryczne) musi byè ujöty w znaki apostrofu lub cudzysäowu
(oba warianty sñ dopuszczalne).

Cztery wartoĈci uĔyte w kodzie SQL poprzedniego przykäadu odpowiadajñ pod wzglödem typu
i kolejnoĈci czterem polom w tabeli docelowej, stñd nie istnieje tutaj koniecznoĈè definiowania
listy pól. Warto jednak powiedzieè, Ĕe zdefiniowanie w takim przypadku listy pól nie jest Ĕad-
nym bäödem, a co wiöcej, moĔe byè nawet rozwiñzaniem preferowanym ze wzglödu na wiökszñ
przejrzystoĈè kodu. W takim przypadku kod SQL kwerendy doäñczajñcej wyglñdaäby nastöpujñco:

INSERT INTO ArchiwumTransakcji
([ID klienta], [Data transakcji], [Kwota transakcji], Status)
VALUES (2000, #2006-04-10#, 35.25, "Zrealizowana");

Wyniki dziaäania obu przedstawionych kwerend sñ identyczne.

Zdefiniowanie nazw pól tabeli docelowej jest wymagane w sytuacji, kiedy wstawiane wartoĈci
nie sñ uäoĔone w odpowiednim porzñdku bñdĒ jeĔeli niektóre pola tabeli zostajñ pominiöte.
Przykäadowo: moĔemy wstawiè do tabeli nowy rekord, w którym ustawimy tylko wartoĈci pól
ID klienta oraz Kwota transakcji; wartoĈci innych pól mogñ nie byè jeszcze znane i dlatego zostaäy
pominiöte. Kod SQL kwerendy doäñczajñcej moĔe wyglñdaè w takiej sytuacji nastöpujñco:

INSERT INTO ArchiwumTransakcji ([ID klienta], [Kwota transakcji])
VALUES (2000, 35.25);

W tym przypadku ustawiamy jedynie wartoĈci dwóch pól. Taki sposób postöpowania jest naj-
zupeäniej prawidäowy, a wykonanie kwerendy zakoþczy siö powodzeniem, pod warunkiem Ĕe
pola, których wartoĈci nie ustawiamy, mogñ przyjmowaè wartoĈci puste.

ĥéczanie rekordów ze zbioru rekordów

Polecenie

INSERT INTO

jest czösto wykorzystywane w procedurach przetwarzajñcych dane

VBA/ADO, gdzie tabela docelowa jest wypeäniana w miarö przechodzenia pötli przez kolejne
rekordy zbioru rekordów (ang. recordset), przykäadowo:

Sub append_routine()
  Dim conn As ADODB.Connection
  Set conn = CurrentProject.Connection
  Dim rs_transactions As New ADODB.Recordset

Rozdzia

ĥ 3. Kwerendy funkcjonalne

  Dim ssql As String
  ' Pobieramy wszystkie rekordy z tabeli Transakcje
  ssql = "Select * From Transakcje"
  rs_transactions.Open ssql, conn, adOpenKeyset, adLockOptimistic
  Do Until rs_transactions.EOF
    ' Je

Īeli data transakcji to 1 kwietnia,

' wstawiamy rekord do archiwum i ustawiamy kwot

Ċ transakcji na 0

    If rs_transactions.Fields("Data transakcji") = #2006-04-01# Then
      ssql = "Insert Into ArchiwumTransakcji Values ("
      ssql = ssql & rs_transactions.Fields("ID klienta") & ", "
      ssql = ssql & "#" & rs_transactions.Fields("Data transakcji") & "#, "
      ssql = ssql & 0 & ", "
      ssql = ssql & "'April''s Fools Day — wszystko za darmo!')"
      conn.Execute ssql
    End If
  rs_transactions.MoveNext
  Loop
  ' usuwamy wszystkie rekordy z 2006-04-01 z tabeli Transakcje
  ssql = "Delete * From Transakcje Where "
  ssql = ssql & " Transakcje.[Data transakcji]=#2006-04-01#"
  conn.Execute ssql
  rs_transactions.Close
  Set rs_transactions = Nothing
  conn.Close
  MsgBox "Gotowe!"
End Sub

W tym przykäadzie zbiór rekordów (

rs_transactions

) zawiera wszystkie rekordy z tabeli Trans-

akcje. Podczas przetwarzania w pötli kolejnych rekordów ze zbioru procedura sprawdza, czy data
transakcji to

2006-04-01

. JeĔeli tak, tworzone jest odpowiednie polecenie

INSERT INTO

jözyka

SQL. Przykäadowo:

INSERT INTO ArchiwumTransakcji Values
VALUES (106, #2006-04-01#, 0, 'April''s Fools Day — wszystko za darmo!');

UwaĔni Czytelnicy z pewnoĈciñ zwrócili uwagö na uĔycie podwójnego znaku apo-
strofu w ciñgu znaków

April''s

. Taki zapis zapobiega wystñpieniu bäödu, który

mógäby siö w przeciwnym razie pojawiè podczas próby wstawienia ciñgu znaków
zawierajñcego apostrof.

Procedura przedstawiona powyĔej kopiuje wszystkie rekordy z 1 kwietnia 2006 do tabeli archi-
walnej i jako kwotö transakcji ustawia wartoĈè

. Po przejĈciu pötli przez wszystkie rekordy

zbioru wykonywana jest kwerenda usuwajñca wszystkie rekordy z 1 kwietnia 2006 z tabeli
Transakcje. PoniĔej przedstawiamy fragment kodu odpowiedzialny za usuwanie rekordów:

' usuwamy wszystkie rekordy z 2006-04-01 z tabeli Transakcje
ssql = "Delete * From Transakcje Where "
ssql = ssql & " Transakcje.[Data transakcji]=#2006-04-01#"
conn.Execute ssql

UĔycie odpowiedniej procedury VBA do przechodzenia przez kolejne rekordy i podejmowanie
odpowiednich decyzji o doäñczaniu poszczególnych rekordów do innej tabeli jest Ĉwietnym
rozwiñzaniem zwäaszcza w sytuacji, kiedy warunki okreĈlajñce sposób przetwarzania stajñ siö
zäoĔone. Bo jak inaczej znaleĒè na przykäad rekordy, w których musimy zredukowaè kwotö
transakcji do

, jeĔeli moĔemy to zrobiè tylko dla ĈciĈle wybranych klientów, dla transakcji

wykonanych tylko w kilku okreĈlonych dniach i tylko wtedy, gdy caäkowite saldo transakcji
takiego klienta jest mniejsze niĔ

100

, a ostatnie zlecenie zostaäo zäoĔone nie wczeĈniej niĔ

30 dni temu?

3.3. Usuwanie danych

Zdefiniowanie takich warunków w siatce projektu kwerendy moĔe byè niezäym wyzwaniem,
stñd znajomoĈè sposobu poäñczenia w procedurze VBA poleceþ jözyka SQL z instrukcjami warun-
kowymi moĔe byè bezcennñ pozycjñ w zestawie umiejötnoĈci kaĔdego uĔytkownika bazy danych
Microsoft Access.

3.3. Usuwanie danych

Opis problemu

Musimy usunñè z tabeli okreĈlone dane. Rekordy, które majñ byè usuniöte, muszñ speäniaè okre-
Ĉlone kryteria wyszukiwania, a pozostaäe rekordy muszñ pozostaè nienaruszone. W jaki sposób
moĔna bezpiecznie wykonaè takñ operacjö?

Rozwi

ézanie

Aby usunñè z tabeli rekordy speäniajñce okreĈlone kryteria wyszukiwania, naleĔy uĔyè kwerendy
usuwajñcej (ang. delete query). JeĔeli w takiej kwerendzie nie zastosujemy Ĕadnych kryteriów
wyszukiwania, to uĔywajñc jej, musimy zachowaè daleko idñcñ ostroĔnoĈè — kwerenda
usuwajñca uruchomiona bez kryteriów wyszukiwania moĔe caäkowicie wyczyĈciè zawartoĈè
tabeli.

Kwerendy usuwajñce powodujñ usuniöcie danych, ale pozostawiajñ tabele. Tabele
nie sñ usuwane.

Aby usunñè wybrane dane z tabeli, musimy utworzyè odpowiedniñ kwerendö usuwajñcñ, która
wybierze tylko rekordy przeznaczone do skasowania. Na rysunku 3.10 przedstawiono projekt
kwerendy, która usuwa rekordy klientów pochodzñcych ze stanu CA (California). Oznacza to,
Ĕe usuniöte zostanñ tylko takie rekordy, dla których pole Nazwa stanu ma wartoĈè

; inne

rekordy tabeli pozostanñ nienaruszone. Zawsze musimy pamiötaè, Ĕe mimo iĔ w siatce projektu
kwerendy na rysunku 3.10 umieszczone zostaäo tylko jedno pole, uruchomienie kwerendy nie
usuwa danych tylko z tego pola — zamiast tego w caäoĈci zostajñ usuniöte wszystkie rekordy
speäniajñce podane kryterium wyszukiwania. W siatce projektu kwerendy nie musimy umiesz-
czaè wszystkich pól rekordu; wystarczy umieĈciè tam pola, dla których definiujemy kryteria
wyszukiwania. Kiedy uĔywamy kwerendy usuwajñcej wszystkie rekordy z tabeli (czyli kwe-
rendy bez kryteriów wyszukiwania), wystarczy z okna tabeli przeciñgnñè gwiazdkö na siatkö
projektu kwerendy — gwiazdka oznacza po prostu wszystkie pola tabeli.

Aby utworzyè kwerendö usuwajñcñ, naleĔy po przejĈciu na siatkö projektu kwerendy wybraè
z menu gäównego polecenie Kwerendy/Kwerenda usuwajñca. W wersji Access 2007 wystarczy
w tym celu skorzystaè z odpowiedniego przycisku na WstñĔce. Kod SQL kwerendy przedsta-
wionej na rysunku 3.10 wyglñda nastöpujñco:

DELETE [Klienci].[Nazwa stanu]
FROM Klienci
WHERE ((([Klienci].[Nazwa stanu])="CA"));

Rozdzia

ĥ 3. Kwerendy funkcjonalne

Rysunek 3.10. Kwerenda usuwajñca ze zdefiniowanymi kryteriami wyszukiwania

Jak widaè, kod SQL kwerendy jest relatywnie prosty. Jego skäadnia jest nieco zbliĔona do skäadni
kwerendy wybierajñcej

SELECT

, z wyjñtkiem tego, Ĕe kod kwerendy usuwajñcej rozpoczyna siö

od säowa kluczowego

DELETE

. Interesujñcy jest fakt, Ĕe „przesäanie” czy teĔ „wiadomoĈè” wyni-

kajñce ze skäadni powyĔszego kodu SQL mogñ byè nieco mylñce. Jak juĔ wspominaliĈmy wcze-
Ĉniej, wykonanie takiej kwerendy usuwa caäe rekordy, a nie tylko wartoĈci pola Nazwa stanu.
Zdecydowanie lepszym „skäadniowo” zapisem takiej kwerendy bödzie nastöpujñcy kod SQL:

DELETE [Klienci].*
FROM Klienci
WHERE ((([Klienci].[Nazwa stanu])="CA"));

RóĔnica polega na tym, Ĕe zamiast nazwy pojedynczego pola uĔyliĈmy gwiazdki, która symbo-
lizuje wszystkie pola tabeli. Jak widaè, czasami sposób, w jaki Access tworzy kod SQL kwerend,
nie jest najlepszñ reprezentacjñ zamierzonej akcji. OczywiĈcie kod generowany przez Accessa jest
poprawny i dziaäa, ale moĔe byè nieco mylñcy.

Omówienie

Kiedy usuwamy rekordy z tabeli, która jest powiñzana relacjami z innymi tabelami, musimy
wziñè pod uwagö kilka dodatkowych elementów. PoniewaĔ tabela nadrzödna jest poäñczona
z tabelñ podrzödnñ relacjñ jeden do wielu, usuniöcie rekordów z tabeli nadrzödnej spowodo-
waäoby naruszenie wiözów integralnoĈci i pozostawienie „osieroconych” rekordów w tabeli
podrzödnej.

Access posiada mechanizm pozwalajñcy na sprawne rozwiñzanie takiego dylematu, co nie zmie-
nia faktu, Ĕe zrozumienie istoty problemu jest niezmiernie waĔne. Aby zilustrowaè caäe zagad-
nienie, posäuĔymy siö przykäadem. Na rysunku 3.11 przedstawiono relacjö ustanowionñ pomiö-
dzy tabelami Klienci i Transakcje. Zwróèmy uwagö, Ĕe w oknie dialogowym Edytowanie relacji
zaznaczona zostaäa opcja Wymuszaj wiözy integralnoĈci (aby wyĈwietliè to okno dialogowe,
naleĔy dwukrotnie kliknñè lewym przyciskiem myszy liniö äñczñcñ obie tabele bñdĒ z menu
gäównego wybraè polecenie Relacje/Edytuj relacjö). Taka relacja oznacza, Ĕe rekordy w tabeli
Transakcje muszñ byè dopasowane do odpowiednich rekordów z tabeli Klienci, a dokäadniej,
Ĕe kaĔdy rekord w tabeli Transakcje musi posiadaè w polu ID klienta wartoĈè, która odpowiada
wartoĈci pola ID klienta jakiegoĈ rekordu w tabeli Klienci.

3.3. Usuwanie danych

Rysunek 3.11. Przeglñdanie relacji pomiödzy dwiema tabelami

Rekordy w tabeli Klienci muszñ posiadaè unikatowe wartoĈci w polu ID klienta, stñd iloĈè rekor-
dów w tabeli Klienci jest taka sama, jak iloĈè unikatowych identyfikatorów klientów (iloĈè uni-
katowych wartoĈci pola ID klienta). W taki wäaĈnie sposób tabela Klienci speänia rolö tabeli
nadrzödnej w relacji jeden do wielu.

Pole ID klienta w tabeli Transakcje nie musi posiadaè unikatowych wartoĈci. W praktyce sytu-
acja wyglñda tak, Ĕe niemal kaĔdy rekord z tabeli Klienci bödzie posiadaä wiele odpowiadajñcych
mu rekordów w tabeli podrzödnej Transakcje — dobrze prowadzony biznes powoduje, Ĕe stali,
lojalni klienci ciñgle wracajñ i skäadajñ nowe zlecenia.

I znów, jedynym wymaganiem dla rekordów z tabeli Transakcje jest to, Ĕe wartoĈè pola ID klienta
musi odpowiadaè wartoĈci tego pola w jednym z rekordów tabeli Klienci.

Teraz zaäóĔmy, Ĕe chcemy usunñè danego klienta z tabeli Klienci. PoniewaĔ pomiödzy tabelami
istniejñ wiözy integralnoĈci, ale kaskadowe usuwanie rekordów pokrewnych nie jest dozwolone
(opcja Kaskadowo usuþ rekordy pokrewne jest wyäñczona, jak to zostaäo zilustrowane na rysunku
3.11), to jeĔeli dany klient bödzie posiadaä powiñzane rekordy w tabeli podrzödnej, Access nie
pozwoli na proste usuniöcie klienta. Wiözy integralnoĈci pomiödzy tabelami nie pozwolñ na
utworzenie „osieroconych” rekordów. Klienci nie muszñ mieè Ĕadnych rekordów opisujñcych
transakcje, wiöc usuniöcie klientów bez transakcji jest moĔliwe, ale jeĔeli dla danego klienta
istniejñ w tabeli podrzödnej jakiekolwiek rekordy opisujñce jego transakcje, to usuniöcie takiego
klienta nie bödzie moĔliwe.

JeĔeli klient posada jakieĈ powiñzane z nim transakcje, to przed usuniöciem rekordu klienta
musimy usunñè wszystkie rekordy opisujñce jego transakcje. Usuwanie rekordów transakcji nie
podlega Ĕadnym ograniczeniom i w Ĕaden sposób nie moĔemy utworzyè „osieroconego” rekordu
klienta — „osierocone” rekordy mogñ siö teoretycznie pojawiè jedynie w tabeli podrzödnej.

Rozdzia

ĥ 3. Kwerendy funkcjonalne

A zatem w jaki sposób usunñè wszystkie transakcje danego klienta? Kwerenda usuwajñca przed-
stawiona na rysunku 3.12 usuwa wszystkie rekordy transakcji dla klienta

April Kramer

. W re-

kordzie opisujñcym tego klienta znajduje siö odpowiednie pole ID klienta, które jest wykorzy-
stywane przez kwerendö do identyfikacji usuwanych rekordów. Zwróèmy uwagö na fakt, Ĕe
w wierszu Usuwanie w pierwszych dwóch kolumnach umieszczono klauzule

Where

, speäniajñce

rolö kryteriów wyszukiwania. Trzecia kolumna identyfikuje tabelö, z której bödñ usuwane odna-
lezione rekordy (Transakcje); w wierszu Usuwanie tej kolumny umieszczono klauzulö Skñd.

Rysunek 3.12. Usuwanie rekordów z jednej tabeli w oparciu o kryteria z innej tabeli

Kod SQL kwerendy przedstawionej na rysunku 3.12 wyglñda nastöpujñco:

DELETE Klienci.Imi

Ă, Klienci.Nazwisko, Transakcje.*

FROM Klienci INNER JOIN Transakcje ON
Klienci.[ID klienta] = Transakcje.[ID klienta]
WHERE (((Klienci.Imi

Ă)="April") AND

((Klienci.Nazwisko)="Kramer"));

Jest to nieco mylñce, poniewaĔ po säowie kluczowym

DELETE

wystöpujñ nazwy pól z tabeli

Klienci (Imiö i Nazwisko) — moĔna stñd wysnuè mylny wniosek, Ĕe usuwane bödñ rekordy z tabeli
Klienci. OczywiĈcie nie jest to prawdñ — usuwane sñ tylko rekordy transakcji z tabeli podrzödnej,
podczas gdy tabela klientów pozostaje nienaruszona. Rekord opisujñcy klienta zostaje usuniöty
przez kolejnñ kwerendö, przedstawionñ na rysunku 3.13.

Usuniöcie w ten sposób rekordu klienta April Kramer moĔe byè niebezpieczne,
poniewaĔ teoretycznie moĔe istnieè wiöcej niĔ jeden klient o takim imieniu i nazwisku.
Imienia i nazwiska klienta uĔyto w tej kwerendzie tylko na potrzeby lepszego zilustro-
wania zasady usuwania takich rekordów. W praktyce jedynym sposobem gwarantujñ-
cym, Ĕe usuniöty zostanie wäaĈciwy rekord klienta, jest posäuĔenie siö polem ID klienta.

Jak widaè, jeĔeli kaskadowe usuwanie rekordów pokrewnych nie jest dozwolone (na przy-
käad kiedy w oknie Edytowanie relacji opcja Kaskadowo usuþ rekordy pokrewne zostaäa wyäñczona),
przed usuniöciem rekordu klienta musimy usunñè wszystkie odpowiadajñce mu rekordy z tabeli
podrzödnej. JeĔeli jednak ta opcja zostaäa wäñczona, to usuniöcie wybranych rekordów z tabeli
nadrzödnej spowoduje automatyczne usuniöcie wszystkich pokrewnych rekordów z tabeli pod-
rzödnej. W takiej sytuacji usuniöcie

April Kramer

z tabeli Klienci spowodowaäoby automatycz-

nie usuniöcie wszystkich zwiñzanych z niñ rekordów z tabeli Transakcje.

3.4. Kwerendy tworz

éce tabele

Rysunek 3.13. Kwerenda usuwajñca z tabeli nadrzödnej rekord klienta

Takie rozwiñzanie potrafi zaoszczödziè masö czasu, ale nie ma nic za darmo. Kaskadowe usu-
wanie rekordów pokrewnych moĔe w prosty sposób spowodowaè niezamierzone usuniöcie
z tabel cennych informacji. JeĔeli chcemy skorzystaè z tej opcji, powinniĈmy upewniè siö, Ĕe
kopie zapasowe danych sñ tworzone odpowiednio czösto, w regularnych odstöpach czasu. Usu-
niöcie danych jest nieodwoäalne i jeĔeli nie posiadamy odpowiedniej kopii zapasowej, anulo-
wanie takiej operacji i przywrócenie poprzednich danych nie bödzie moĔliwe. JeĔeli nie posia-
damy odpowiedniego mechanizmu tworzenia kopii zapasowych, ryzyko zwiñzane z uĔyciem
moĔliwoĈci kaskadowego usuwania rekordów moĔe przewaĔyè nad wszystkimi zaletami päy-
nñcymi z zastosowania tego mechanizmu. Zanim zdecydujemy siö na jego zastosowanie, musimy
starannie rozwaĔyè wszystkie argumenty za i przeciw.

3.4. Kwerendy tworz

éce tabele

Opis problemu

W jaki sposób moĔna utworzyè tabelö przechowujñcñ rekordy bödñce rezultatem dziaäania
kwerendy?

Rozwi

ézanie

W pewnych sytuacjach bardzo uĔyteczna moĔe byè moĔliwoĈè umieszczenia rekordów zwra-
canych przez kwerendö bezpoĈrednio w nowej tabeli. Aby tego dokonaè, musimy skorzystaè
z kwerendy tworzñcej tabelö (ang. make-table query).

W zasadzie moĔemy sobie teraz zadaè pytanie, po co mamy zadawaè sobie trud tworzenia nowej
tabeli, skoro tabele przechowujñce takie dane juĔ istniejñ? Oto kilka powodów:

Aby poäñczyè w jednej tabeli powiñzane ze sobñ dane nieposiadajñce struktury hierarchicznej.

Aby podzieliè dane z jednej wielkiej tabeli na kilka mniejszych tabel. Takiego podziaäu doko-
nujemy zazwyczaj w oparciu o wartoĈci jednego lub kilku pól kluczowych tabeli Ēródäowej.

Czytaj dalej...

Rozdzia

ĥ 3. Kwerendy funkcjonalne

Rysunek 3.14 dobrze ilustruje pierwszñ sytuacjö. Mamy tutaj dwie tabele, które najwyraĒniej sñ
ze sobñ powiñzane — majñ wspólne pole ID pracownika, aczkolwiek nie istnieje tutaj relacja jeden
do wielu. W kaĔdej z tabel jednemu pracownikowi odpowiada tylko jeden rekord. Utrzymywa-
nie nazwisk pracowników w jednej tabeli, a informacji o dacie zatrudnienia i dziale w innej tabeli
byè moĔe ma jakieĈ uzasadnienie biznesowe, ale nie ma Ĕadnego sensu z punktu widzenia pro-
jektowania bazy danych. Poäñczenie danych z tych dwóch tabel w jednñ wydaje siö byè jak naj-
bardziej sensownym posuniöciem. Tabela bödñca rezultatem takiej operacji bödzie miaäa jedno
pole ID pracownika oraz trzy dodatkowe pola opisujñce dane pracownika.

Rysunek 3.14. Dwie tabele poäñczone relacjñ jeden do jednego

Aby utworzyè takñ tabelö, musimy umieĈciè dwie istniejñce tabele w widoku projektu kwe-
rendy, a w siatce projektu umieĈciè wszystkie pola tych tabel (ale tylko jedno pole ID pracownika).
Nastöpnie musimy poinformowaè Accessa, Ĕe tworzymy kwerendö tworzñcñ tabele. Aby tego
dokonaè, wybieramy z menu gäównego polecenie Kwerendy/Kwerenda tworzñca tabele (w wersji
Access 2007 uĔywamy odpowiedniego przycisku na WstñĔce). Kiedy wybierzemy z menu kwe-
rendö tworzñcñ tabele, na ekranie pojawi siö okno dialogowe Tworzenie tabeli, w którym moĔemy
wpisaè nazwö nowej tabeli lub wybraè z listy rozwijanej nazwö istniejñcej tabeli.

Na rysunku 3.15 przedstawiono projekt kwerendy, która äñczy rekordy z dwóch tabel i zapisuje
je w jednej, nowej tabeli o nazwie DaneOsobowePracowników.

Kod SQL kwerendy przedstawionej na rysunku 3.15 wyglñda nastöpujñco:

SELECT Pracownicy.[ID pracownika], Pracownicy.Pracownik,
DataZatrudnienia_Dzia

Ī.[Data zatrudnienia],

DataZatrudnienia_Dzia

Ī.[Nazwa dziaĪu] INTO DaneOsobowePracowników

FROM Pracownicy INNER JOIN DataZatrudnienia_Dzia

Ī ON

Pracownicy.[ID pracownika] = DataZatrudnienia_Dzia

Ī.[ID pracownika];

Zwróèmy uwagö, Ĕe kluczowym elementem takiej kwerendy jest polecenie

SELECT <lista pól>

INTO <nazwa nowej tabeli>

, po którym nastöpujñ: klauzula

FROM

oraz ewentualne sprzöĔenia

i kryteria wyszukiwania.