1. Laboratoria

1. Utwórz kwerendę, która posłuży do stworzenia formularza Faktury

Kwerenda powinna zawierać tylko pola potrzebne na fakturze. Ponieważ występują one w kilku tabelach, to wszystkie niezbędne tabele (lub kwerendy) należy dołączyć do tworzonej kwerendy. Można użyć kreatora prostych kwerend (wybierających) lub widoku projekt kwerendy.

Aby uzyskać prawidłowe zestawienia należy wcześniej zdefiniować wszystkie relacje między tabelami - relacje te zostaną pokazane w projekcie kwerendy i są niezbędne do jej prawidłowego działania.

Na bazie utworzonej kwerendy zbuduj formularz Faktury, umożliwiający wprowadzenie danych do wszystkich wykorzystanych tabel. Skorzystaj z dołączonej kopii faktury w celu określenia potrzebnych na fakturze pól i odpowiedniego ich rozmieszczenia.

Wykorzystaj wszelkie poznane wcześniej elementy pozwalające na usprawnienie działania formularza Faktury, np. pola kombi wyszukującego klienta, dodaj własne przyciski nawigacyjne, przyciski dodające i usuwające rekordy czy wyszukujące itp.

2. Wyrażenia w kwerendach modyfikujących (funkcjonalnych)

Kwerendy modyfikujące to procedury automatycznego przetwarzania danych źródłowych. Uruchomienie kwerend modyfikujących powoduje dokonanie zmian w danych źródłowych, w przeciwieństwie do kwerend wybierających, które takich zmian nie dokonują.

Wynikiem kwerend modyfikujących nie są zestawienia w postaci arkusza danych. Uruchomienie takiej kwerendy dokonuje, najczęściej nieodwracalne zmiany w bazie danych.

Praktyczne zastosowanie kwerend funkcjonalnych można prześledzić na przykładzie rozliczania przychodzących kwot z tytułu dokonanej sprzedaży. Ponieważ wystawienie faktury przy sprzedaży towaru nie jest jednoznaczne z otrzymaniem należności za ten towar to równolegle z ewidencją sprzedaży (zamówień) trzeba prowadzić ewidencję wierzytelności oraz ewidencję wpływających należności (płatności).

Przed dokonaniem jakichkolwiek rozliczeń w tabeli Finanse płatności wartość pola DoRozliczenia jest równa wartości pola Kwota. Po całkowitym rozliczeniu danej płatności zmniejszy się ona do zera. Ponieważ jednak pojedyncza płatność może pokrywać należności z kilku faktur a rozliczenie odbywa się osobno dla każdej faktury, wartość z tego pola odpowiada tej części nadesłanej kwoty, która pozostała jeszcze do rozliczenia. W związku z powyższym potrzebne będzie zapamiętanie w bazie danych, która płatność jakich faktur dotyczy (tabela Szczegóły płatności).

Algorytm rozliczania płatności za pomocą kwerend modyfikujących wyglądać może tak:

1. Kwerenda pierwsza: Wybierz z rejestru płatności pierwszą płatność, dla której wartość w polu DoRozliczenia jest większa od zera (taką, która nie została jeszcze do końca rozliczona) i skojarz ją z najstarszą, nie zapłaconą fakturą (zamówieniem) wystawioną nadawcy wybranej płatności, figurującą w rejestrze wierzytelności

2. Kwerenda druga: Dla wybranej faktury pomniejsz wartość DoZapłaty o wartość DoRozliczenia odpowiadającej płatności. Jednocześnie pomniejsz wartość DoRozliczenia o kwotę rozliczoną dla wybranej faktury. Do wykonania tych dwóch działań w jednym kroku można wykorzystać zapamiętaną osobno wartość z pola DoZapłaty.

3. Kwerenda trzecia: Zapisz numer płatności i identyfikator wybranej faktury w tabeli szczegółowego rejestru płatności.

Konieczne również będzie zabezpieczenie kolejności wykonywania kwerend po sobie. Przed wykonaniem każdej kwerendy funkcjonalnej pojawiają się komunikaty ostrzegające przed zmianami w tabelach i podające ilu wierszy te zmiany będą dotyczyć. Można je wyłączyć w Menu Narzędzia\ Opcje\ grupa opcji Potwierdzaj\ kategoria Edytuj/Znajdź\ wyłączając opcję Kwerendy funkcjonalne.

1. Utwórz tabele i relacje:

1. Finanse wierzytelności

Pola

Nazwa Typ Rozmiar

IDzamówienia Autonumer

WartośćBrutto Liczba (całkowita długa)

DoZapłaty Liczba (całkowita długa)

2. Finanse płatności

Pola

Nazwa Typ Rozmiar

LpPłatności Autonumer

DataOtrzymania Data/Godzina

IDklienta (lub kontrahenta) Liczba (całkowita długa)

Kwota Walutowy

DoRozliczenia Walutowy

3. Szczegóły płatności

Pola

Nazwa Typ Rozmiar

LpPłatności Liczba

IDzamówienia Liczba (całkowita długa)

1. Kwerendy tworzące tabele

Różnią się one od zwykłych kwerend wybierających tym, że zamiast chwilowego zestawienia najbardziej aktualnych danych dają w rezultacie nową tabelę, w której przechowują zarchiwizowane dane z określonej chwili.

Szczególnym przypadkiem zastosowania zapytań tworzących tabele jest zapamiętywanie danych zebranych z różnych tabel źródłowych w postaci pojedynczej tabeli o charakterze pomocniczej i chwilowym. Jej główną zaletą jest uproszczenie złożonych procedur przetwarzania danych. Czasami stworzenie takiej tymczasowej tabeli staje się wręcz konieczne, gdyż Access bardzo spowalnia działanie podczas wykonywania zapytań zbudowanych na zapytaniach, których obiektami źródłowym są zapytania :-).

Jedyne dziedzi­czone po polach tabel źródłowych atrybuty pól nowo stworzonej tabeli to Typ danych i Rozmiar pola. Pozostałe atrybuty przyjmują swoje standardowe ustawienia.

• Otwórz okno projektu nowego zapytania\ Menu Kwerenda\ Kwerenda tworząca tabele

• W polu dialogowym Utwórz tabelę wpisz nazwę tworzonej tabeli (później będzie ją można zmienić na liście atrybutów zapytania)

Musimy skojarzyć pierwszą nie rozliczoną do końca płatności z najstarszą, nie zapłaconą fakturą, przy czym informacją, według której zapytanie ma kojarzyć płatność z fakturą, będzie oczy­wiście identyfikator odbiorcy.

Zauważmy, że chcemy zapisać w docelowej tabeli tylko jeden wiersz, bowiem jak już wspomniano, pojedyncza operacja rozliczenia dotyczy tylko jednej faktury i tylko jednej płatności. Ponieważ rejestr może za­wierać kilka nie rozliczonych płatności, a także w wierzytelnościach może figuro­wać kilka nie zapłaconych faktur dla tego samego odbiorcy, konieczne będzie użycie funkcji agregujących. Nie uprzedzajmy jednak faktów.

• Wybierz tabele źródłowe, którymi będą: Finanse-Platności, Finanse-Wierzytelności i Zamówienia. Ta ostatnia to swego rodzaju łącznik pomiędzy dwoma pierwszymi.

• Utwórz sprzężenia, jeżeli wcze­śniej nie zostały zadeklarowane powiązania zgodnie z przedstawioną wcześniej ilustracją.

• Wypełnij szablon QBE. Jako pierwszą kolumnę wybierzmy pole LpPłatności z tabeli Finanse-Platności. Ponieważ jednak nie cho­dzi nam o wszystkie nie rozliczone płatności, należy włączyć opcję Podsumowa­nie i wybrać dla pola LpPłatności funkcję Pierwszy.

• Następna kolumna to warunek Gdzie dla pola Do rozliczenia z tej samej tabeli. Będzie miał on postać <>0, co wpiszemy w pole Kryteria. Kolejna kolumna to pole ID Faktury z tabeli Finanse—Wierzytelności, także z funkcją Pierwszy. Zakładamy tutaj, że kolejność wierszy w tabeli Finanse-Wierzytelności odpowiada kolejności, w jakiej faktury były wystawiane. (Gdyby tak nie było, należałoby dodać do szablo­nu pole DataZamówienia z tabeli Zamówienia i użyć go jako kryterium sortowania faktur.)

• Do wykonania czynności w drugim etapie procedury przydatna będzie wartość z pola Do zapłaty, które to pole przemianujemy w nowej tabeli na Należ­ność. I w tej kolumnie jako funkcję agregującą wybierzmy Pierwszy. Musimy tu wziąć pod uwagę, że w tabeli Finanse-Wierzytelności mogą figurować pozycje już nieaktualne. Aby nie rozliczać ponownie rozliczonej już faktury, dodajmy w następ­nej kolumnie warunek Gdzie, w którym wykluczymy wybór faktur, mających w polu Do zapłaty wartość 0.

• Ostatnie, czwarte pole nowej tabeli będzie miało charakter pomocniczy, zabezpieczający przed skutkami pomylenia kolejności w uruchamianiu zapytań realizujących rozliczenia. Deklaracja tego pola wygląda tak: Status: „Nie rozliczona", a jej skutkiem będzie stworzenie w nowej tabeli pola o nazwie Status, w które wpisana zostanie wartość „Me rozliczona". Stwierdzenie owo odnosi się do kombinacji płatność - faktura, rozliczanej przez budowaną procedurę.

• Zachowaj zapytanie pod nazwą Rozliczenia-krok 1

• Włącz opcję Kwerenda tworząca tabele z menu Kwerenda, podać nazwę tabeli docelowej, np. Pomocnicza do rozliczeń, sprawdzić działanie kwerendy przełączając w tryb arkusza danych i - jeśli wszystko gra, uruchomić je tworząc nową tabelę

1. Kwerendy aktualizujące

Aktualizacja przechowywanych w tabelach danych poprzez bezpośrednią edycję to zajęcie żmudne i potencjalnie niebezpieczne, gdyż może być źródłem wielu błędów, zwłaszcza przy zmianie wartości liczbowych, kwot itp. Trzeba najpierw odszukać wiersz, a następnie zmienić wartość odpowiedniego pola. Często przy tym konieczne jest skorzystanie z danych znajdujących się w innych tabelach. Częściowym ułatwie­niem w tego rodzaju pracy może być przygotowanie zapytania wybierającego tylko te wiersze, które mają być aktualizowane, oraz potrzebne dane pomocnicze. Wtedy można poddać edycji dane w zestawieniu, unikając niepotrzebnego przeglądania dużych tabel i przechodzenia od tabeli aktualizowanej do tabel pomocniczych.

Najlepiej jednak zadanie to powierzyć zapytaniom aktualizują­cym które są efektywnym i elastycznym narzędziem automatycznego przetwarzania danych.

• Aby zwykłe zapytanie wybierające przekształcić w zapytanie usuwające, Wybierz z menu Kwerenda polecenie Kwerenda aktualizująca.

Różnica pomiędzy szablonami QBE dla zapytań wybierających i aktualizują­cych polega na zastąpieniu wiersza Pokaż wierszem Zamiana na. W rubryki tego wiersza wpisuje się wyrażenia, których wyniki zastępują war­tości pól wybranych wierszy tabeli źródłowej. Niemożliwe jest oczywiście użycie w zapytaniu aktualizującym funkcji agregujących a także zmian w polach klucza głównego, w wyniku których zaburzona zostałaby integralność danych (puste pola lub powtarzające się wartości).

Aby rozliczyć nadchodzących płatno­ści, wykorzystamy zapytania aktualizujące w kolejnym etapie tej procedury. Na razie mamy ewidencję wierzytelności, nadchodzących płatności oraz wybraną „pierwszą z brzegu" parę płatność - faktura. To rozliczenie to nic innego jak aktualizacja obydwu ewidencji.

• Jako obiekty źródłowe do zapytania Rozliczenia-krok 2 wybierz tabele: Finanse-Płatności, Finanse-Wierzytelności i Pomocnicza do rozliczeń. Tym razem ta ostatnia pełnić będzie rolę łącznika przy tworzeniu sprzężeń. Co dalej?

• Trzeba zastąpić kwotę z pola Do zapłaty dla wybranej faktury różnicą Do zapłaty - Do rozliczenia.

Jeżeli kwota płatności jest dokładnie równa należności za wybraną fakturę, wynikiem będzie 0. A skoro Do zapłaty pozostało O, to znaczy, że faktura została zapłacona. Z drugiej strony, skoro cała płatność została zaliczona na poczet wybranej faktury, to i ta płatność została rozliczona, więc należy w pole Do rozliczenia wpisać wartość 0.

Rozważyliśmy jed­nak przypadek szczególny, podczas gdy w rzeczywistości klient najczęściej przysyła tylko tyle pieniędzy, ile uważa za niezbędne minimum. Oznacza to, że dla wybranej pary płatność - faktura zachodzi relacja [Do rozliczenia]< [Do zapłaty] lub [Do rozliczenia] > [Do zapłaty]. Ten drugi przypadek może mieć miejsce tylko wtedy, gdy nasz kontrahent zalega nam za kilka faktur i uznał, że nasza cierpliwość się kończy.

Sytuacja się trochę komplikuje, ale można ją rozwiązać, korzystając z funk­cji Ilf. Aktualizacja pola Do zapłaty polegać będzie na zastąpieniu bieżącej wartości różnicą [Do zaplaty]-[Do rozliczenia], z tym że gdyby wynik miał być ujemny, w pole wpisane zostanie zero.

• Zadanie to zrealizuje następujące wyraże­nie wpisane w rubrykę Zamiana na kolumny:

Do zapłaty: Ilf ([Do zapłaty]-[Do rozliczenia] <0;0; [Do zapłaty]-[Do rozliczenia])

Aktualizacja pola Do rozliczenia w rejestrze płatności polegać będzie z kolei na zastąpieniu wartości bieżącej różnicą [Do rozliczenia]-[Do zapłaty] lub, gdyby okazała się ujemna, zerem. Problem w tym, że wyrażenie to wymaga wartości pola Do zaplaty sprzed aktualizacji, w przeciwnym razie wynik będzie błędny. Z tego względu, w poprzednim etapie, w tabeli Pomocnicza do rozliczeń zapamięta­no wartość Do zaplaty jako Należność.

• Kolejna kolumna szablonu QBE zawierać zatem będzie w rubryce Pole nazwę Do rozliczenia, a w rubryce Zamiana na wyrażenie:

llf([Dorozliczenia]-[Należność]<0;0;[Dorozliczenia]-[Należność])

Po dokonaniu obydwu zmian rozliczenie można uznać za dokonane, aby to uwzględnić, zmieńmy jeszcze wartość pola Status w tabeli Pomocnicza do rozliczeń na „Rozliczona". Cel tej operacji okaże się oczywisty w trzecim, ostatnim już etapie tworzonej procedury.

1. Kwerendy dołączające

Kwerenda dołączająca to taka, która uzupełnia istniejącą tabelę o dane wybrane z innych tabel lub zapytań, dołączając na jej końcu nowe wiersze. Musi być oczywiście zachowana zgodność typu dopisywanych danych z typem pól tabeli docelowej oraz zachowana jednoznaczność wartości klucza głównego tej tabeli.

• Włącz opcję Kwerenda dołączająca z menu Kwerenda do szablonu QBE i wybierz z obiektów źródłowych pola, których war­tości chcesz przenieść do tabeli docelowej, a także te, których wartości posłużą nam za kryteria.

Access nie wymaga, by w budowanym zapytaniu liczba pól była zgodna z liczbą pól tabeli docelowej. Można dopisywać dane tylko do wybranych pól, nie troszcząc się o pozostałe. Ważne jest, aby w zapytaniu było pole odpowiadające polu klucza głównego tabeli docelowej, chyba że kluczem głównym jest pole typu licznik. W takiej sytuacji Access sam poradzi sobie z uzupełnieniem jego wartości.

• Kiedy po przełączeniu w tryb arkusza danych oka­że się, że zapytanie wybiera dokładnie te dane, o jakie nam chodzi, naciskając przycisk z wykrzyknikiem uruchom zapytanie.

• Podobnie jak w przypadku zapytania tworzącego tabelę, tu także podstawo­wym atrybutem zapytania jest nazwa tabeli docelowej, z tą jednak różnicą, że dopisywać wiersze można tylko do tabeli istniejącej. Przy wyborze opcji Kweren­da dołączająca pojawia się pole dialogowe, w które trzeba wpisać tę nazwę lub wybrać ją z listy rozwijanej. Jako że Access umożliwia nam dopisanie danych nie tylko do tabeli bazy danych aktualnie otworzonej, ale do dowolnej innej i to nie tylko Accessowej, do wyboru mamy dwie opcje: Bieżąca baza danych i Inna baza danych.

Szablon QBE dla zapytania dołączającego zamiast wiersza Pokaż ma wiersz Dołączanie do. Rubryki tego wiersza wypełniamy nazwami pól wybranymi z rozwijanych list.

• Zbuduj zapytanie dołączające, które zrealizuje trzeci i ostatni krok roz­liczania płatności. W trzecim etapie należy zapisać identyfikatory rozliczonej dopiero co pary płat­ność - faktura w tabeli Szczegóły płatności, która pełnić tu będzie rolę tabeli docelowej. Dane te zapisane są w tabeli Pomocnicza do rozliczeń.

• Tabela Pomocnicza do rozliczeń będzie obiek­tem źródłowym zapytania dołączającego. Do szablonu QBE wybierzmy z tabeli źród­łowej pola Lp i Pierwszyz ID Faktury, polami docelowymi zaś dla nich niech będą: Lp i ID Faktury.

• Aby zabezpieczyć się przed zapisaniem tych danych, zanim dokonamy rozliczenia (czyli gdybyśmy w naszej procedurze ominęli krok drugi), dodajmy jesz­cze pole Status, dla którego w rubrykę Kryteria wpiszemy wartość „Rozliczona".

• Zapisz Kwerendę pod nazwą Rozliczenia-krok 3

Może się zdarzyć, że spróbujemy dwa razy dopisać tę samą informację do tabeli docelowej. Jednak wykonanie tej czynności pogwałciłoby jednoznaczność klucza głównego, w związku z tym Access zarzuca realizację zapytania, wy­świetla natomiast charakterystyczny komunikat o błędach. Informuje w nim o następujących przyczynach niepowodzeń:

1. w związku z błędną konwersją typów danych, np. przy próbie dopisania wartości typu tek­stowego do pól typu liczbowego, wiersze były puste i nie zostały dołączone;

2. w związku z pogwałceniem jednoznaczności klucza głównego wiersze nie zostały dołączo­ne;

3. w związku z blokadą wierszy w środowisku wielodostępnym, nie zostały one dołączone.

W sytuacji, kiedy w tabeli docelowej kluczem głównym jest pole typu Autonumer, zapytanie dołączające może nie mieć żadnego odpowiednika tego pola, gdyż Access automatycznie nada kolejne numery dopisanym wierszom.

• Można również utworzyć kwerendę dołączającą do okresowego uzupełniania tabeli Finanse-Wierzytelności o dane dotyczące nowych, nie zapłaconych faktur.

1. Kwerendy usuwające

Nie ma chyba nic bardziej spowalniającego nasz system i budzącego większą irytację niż nieaktualne dane, zaśmiecające używane na bieżąco tabele. Oczywi­ście, niektóre z nich dobrze jest zachowywać jeszcze przez jakiś czas „na wszelki wypadek", jednak najlepiej przeznaczyć na nie osobne tabele czy nawet osobną bazę danych jako wydzielone archiwum. Po archiwizacji trzeba usunąć stare wier­sze z bieżących tabel. Najszybciej zadanie to wykona kwerenda usuwająca, należąca do ostatniego z opisywanych typów kwerend modyfikujących. Pozwala ona usunąć z tabel źródłowych całe wiersze wybrane na pod­stawie dowolnie ustalonych kryteriów.

Jedyne ograniczenia w usuwaniu wierszy związane są z naruszeniem integral­ności danych. Na przykład dla pojedynczej tabeli ze strony jeden zadeklarowanego związku jeden-do-wiele nie można usuwać wierszy posiadających swoje odpowied­niki w tabeli ze strony wiele (chyba że przy deklaracji powiązania włączona zosta­ła opcja kaskadowego usuwania).

• Żeby stworzyć zapytanie usuwające, trzeba po wyborze obiektów źródłowych włączyć opcję Kwerenda usuwająca z menu Kwerenda. Szablon QBE zapytania usuwającego wyposażony jest w specjalny wiersz nazwany Usuwanie, w którego rubrykach pojawiać się mogą jedynie dwie pozycje:

1. Skąd, oznacza tabelę, z której wiersze będą usuwane;

2. Gdzie, wybieramy dla pól używanych do ustalenia kryteriów.

W tym przypadku szczególnie ważne jest, aby przed uruchomieniem zapyta­nia przełączyć je w tryb arkusza danych i sprawdzić, czy wybiera ono jedynie te wiersze, które mają być usunięte. Usuwanie wierszy zapytaniem jest bowiem nieodwracalne.

• Prostym przykładem zapytania usuwającego, uzupełniającym zestaw zapytań rozliczających płatności, będzie zapytanie usuwające z tabeli Finanse-Wierzytelności wiersze, dla których wartość pola Do zapłaty wynosi 0.

1. Tworzenie raportów

Raporty opieramy na kwerendach. Należy to przyjęć jako zasadę, nawet jeśli nie będziemy określać żadnych kryteriów wyszukiwania. Umożliwi nam to w każdej chwili łatwą modyfikację zakresu wyświetlanych rekordów lub wyświetlenie tych rekordów, które spełniają kryteria kwerendy.

Raporty pozwalają nam utrwalić w postaci wydruku stan danych w określonym momencie czasu. Nadają się dzięki temu do wszelkiego rodzaju ze­stawień i sprawozdań. Nie można ich natomiast wyko­rzystać do zmiany stanu bazy danych tak, jak to było w przypadku formularzy. Wyższość raportów nad formu­larzami, gdy chodzi o tworzenie sprawozdań (wszak for­mularze też można drukować), wynika z dodatkowych możliwości grupowania danych, obliczania sum częścio­wych i globalnych oraz innych cech, których formularze nie posiadają.

Poza tym raport operuje zbiorczo na wszyst­kich wierszach obiektu źródłowego, a nie na pojedynczych tak jak formularz.

• Utwórz raport - sprawozdanie ze sprzedaży produktów w ramach poszczególnych kategorii - korzystając z kreatora raportów

Raport powinien zawierać numery faktur wraz z datami i wartościami dla produktów z posz­czególnych kategorii, pogrupowane według tychże kategorii. Każda grupa ma być podsumowana. Cały zaś raport niech wieńczy wartość sprzedaży ogółem. Potrzebne dane to opis kategorii, numer faktury, data sprzedaży i wartość każdej pozycji na fakturze. Aby je zebrać, konstruujemy kwerendę opartą na podstawowych tabelach Zamówienia, Opisy zamówień, Produkty i Kategorie.

• Zapisz Kwerendę pod dowolną znaczącą nazwą

• Menu Wstaw/ Raport. Z roz­wijanej listy w polu dialogowym Nowy raport wybierz nazwę właściwe­j kwerendy. Z drugiej listy w tym polu wybierz pozycję Kreator raportów.

• Wybierz odpowiednie pola z obiektu źródłowego, które będą umieszczone na raporcie. Kreator pozwala wybrać pola z kilku obiektów (tabel i/lub zapytań).

• Wybierz poziomy grupowania. Jeśli chcemy pogrupować sprzedaż według kategorii trzeba wskazać na liście pól nazwę Kategoria i kliknąć przycisk >>.

Można ustalić kilka poziomów grupowania, np. możliwe jest pogrupowanie sprzedaży według kategorii, a następnie, w ramach każdej z nich, na sprzedających.

Dostępny w tym polu przycisk Opcje grupowania po­zwala ustalić przedziały grupowania, które zależą od typu danych w polu grupują­cym. Opisy kategorii towarów są tekstami. W tym przypadku standardowo grupo­wanie odbywa się po całej nazwie. Czasem jednak wygodnie jest pogrupować wartości tekstowe biorąc jedynie kilka pierwszych znaków, a nawet tylko pierwszy z nich. W ten sposób uzyskujemy grupowanie według alfabetu. Dla danych licz­bowych grupowanie może odbywać się w przedziałach o określonej wartości, np. co sto, zaś dla dat w różnych interwałach czasowych np. miesięcznych.

• Ustal, jak mają być sortowane wiersze w grupach. Wybierz sortowanie pól w porządku rosnącym lub malejącym.

Dostępny w polu przycisk Opcje sumowania umożliwia ustalenie sposobu sumowania wartości pól licz­bowych w grupach. Po kliknięciu przycisku pojawia się pole dialogowe, w którym dla każdego pola liczbowego wybieramy funkcję agregującą, a za pomocą dodat­kowych opcji decydujemy, czy wartości liczbowe będą wyświetlane w każdym wierszu i dodatkowo w podsumowaniu, czy tylko w podsumowaniu, oraz czy ma zostać obliczony procentowy udział sumy każdej grupy w sumie całkowitej.

W na­szym przypadku dla pola Wartość sprzedaży wybieramy funkcję Suma i włącza­my opcję liczenia procentowych udziałów sprzedaży w ramach każdej kategorii w globalnej sprzedaży.

• Wybierz układ danych na raporcie, tzn. krój czcionki i spo­sób przedstawienia danych.

• Zapisz raport pod wybraną nazwą

Raport oglądamy w trybie podglądu wydruku, natomiast w trybie projektowania możemy doko­nać zmian zarówno w wyglądzie raportu, jak i w sposobie grupowania i agregowania wyświetlanych danych. Przełączeniu w tryb projektowania, podobnie jak dla formu­larzy, służy polecenie Widok Projekt z menu Widok.

Struktura raportu jest dość rozbudowana i zawiera wiele sekcji. Nagłówek i Stopkę raportu, Nagłówek i Stopkę strony a ponadto nagłówek i stopkę grupy danych Kategoria - nagłówek/stopka oraz zasadniczą sekcję raportu, czyli Szczegóły.

Wartości sumarycz­ne dla grupy obliczane są poprzez funkcję Suma w polu tekstowym znaj­dującym się w stopce grupy danych. Globalna suma obliczana jest w polu tekstowym w stopce raportu.

Może się zdarzyć, że tekst danych w polach tekstowych zostanie obcięty (po prostu nie zmieści się w ramce). Problem ten rozwiązujemy w jeden z dwóch możliwych sposobów. Pierwszy to zwiększenie szerokości pól teksto­wych jak się da najbardziej, tak by zmieściły się w nim jak najdłuższe teksty.

Rozwiązanie alternatywne to włączenie atrybutu Można powiększać. Ustawienie to powoduje na wydruku dynamiczne dostosowanie wielkości pola tekstowego do jego zawartości - tekst zostanie złamany i wyświetlony w kilku wierszach.

1. Sortowanie i grupowanie danych

Aby otrzymać przejrzyste wydruki danych przechowywanych w bazie, konieczne jest ich uporządkowanie według określonego klucza. Struktura takiego uporządkowania może być wielo­poziomowa. Przykładem może być pogrupowanie wartości sprzedaży według kategorii pro­duktów, według daty sprzedaży lub według zakresu dat, np. wartości sprzedaży z danego tygodnia. Każdą grupę danych można teraz scharakteryzować pewną wartością zbiorczą, np. sumą.

Innym przypadkiem uporządkowania danych jest ich sortowanie, które jest niczym innym jak ustawieniem danych w określonej kolejności. Samo jednak posortowanie danych nie prowadzi do wyodrębnienia z nich jakichkolwiek grup. Raporty, jako obiekty przeznaczone do tworzenia sprawozdań i wszelkiego rodzaju zestawień, zostały wyposażone w specjalne możli­wości sortowania i grupowania danych.

Grupowanie danych w raportach wygląda trochę podobnie jak w kwerendach podsumowujących, gdzie aby pogrupować wiersze według wartości jednego lub kilku pól, używaliśmy funkcji Grupuj według. Ustalenie sposobu grupowania oraz sortowania danych w raporcie odbywa się w polu dialogowym Sortowanie i grupowanie, otwieranym przyciskiem na pasku narzędzi lub poleceniem o tej samej nazwie z menu Widok.

• Otwórz w widoku Projekt raport stworzony w po­przednim przykładzie/ wybierz Menu Widok/ Sortowanie i grupowanie

Wywołane pole dialogowe składa się z dwóch części, górnej, zawierającej nazwy pól (lub wyrażenia) w kolumnie Pole/Wy­rażenie oraz sposób sortowania w kolumnie Porządek sor­towania i dolnej, nazwa­nej Właściwości grupy. Wpisanie nazwy pola i wybór sposobu sortowania powodują odpowiednie uporządkowanie wartości tego pola w raporcie. Aby podzielić dane na grupy, trze­ba użyć nagłówka zawierającego wartości pola grupującego lub stopki zawierają­cej podsumowanie wartości w danej grupie.

W pierwszym utworzonym raporcie dane zgrupowane są według kategorii sprzeda­wanych produktów, czego wynikiem są dwie dodatkowe sekcje widoczne w projekcie raportu: Kategoria — nagłówek i Kategoria — stopka. Jak widać, nazwa grupy to nazwa pola, według którego grupowane są dane. W nagłówku grupy znajduje się pole tekstowe zawiera­jące wartości pola Kategoria, w stopce zaś pole z wyrażeniem sumującym wartości pola Wartość sprzedaży i drugie, z odwołującym się do wartości pierwszego, wyra­żeniem obliczającym procentowy udział wartości sprzedaży w danej kategorii w kwocie całkowitej.

Wartość pola zawierającego podsumowanie sprzedaży ze stopki raportu dostępna jest na poziomie dowolnej grupy. W rezulta­cie dostajemy w raporcie listę grup oznaczonych opisami kategorii produktów z podsumowaniem każdej grupy.

Atrybuty Grupuj według i Trzymaj razem pozwalają ustalić sposób grupowania. Pierw­szy z nich, określa sposób grupowania w zależności od typu danych pola grupującego. Dla większości typów danych dostępne jest ustawienie Każda wartość, powodujące grupowanie według tych samych wartości danych z pola grupującego. (Ustawienie to zostało wykorzystane przez kreatora w naszym przykładzie, gdzie grupy tworzone są przez wiersze o iden­tycznych wartościach pola Opis kategońi.)

Dla danych typu tekstowego możliwe jest także grupowanie według Pierwszych znaków. Liczbę znaków, które mają być brane pod uwagę przy grupowaniu, należy wpisać w ru­brykę atrybutu Przedział grupy. Może się to przydać przy gru­powaniu danych według alfabetu (dane na literę A, B...), wtedy Przedział grupy wynosi 1, lub przy grupowaniu danych według identyfikatorów składających się z ciągu znaków, z których kilka pierwszych jest identycznych.

Dane typu liczbowego można pogrupować według przedziału, któ­rego długość określona jest wartością atrybutu Przedział grupy. Gdy wynosi ona np. 100, dane grupowane są w przedziałach od O do 99, od 100 do 199 itd. Dodat­kowe możliwości grupowania pojawiają się dla danych typu Data/godzina. Na liście wartości atrybutu Grupuj według znajdujemy wówczas pozycje Rok, Kwartał, Miesiąc, Tydzień, Dzień, Godzina i Minuta.

Ostatni atrybut grupy to Trzymaj razem, a jego wartości to: Nie, Całą grupę i Z pierwszym szczegółem. Atrybut ten dotyczy drukowania grupy z uwzględnieniem podziału na strony. Normalnie jest on wyłączony, więc koniec strony może pojawić się w dowolnym miejscu grupy. Wartość Całą grupę wymusza przechodzenie do nowej strony zawsze, gdy na poprzedniej grupa nie mieści się w całości, to jest razem z nagłów­kiem i stopką. Kiedy grupa jest zbyt duża, by zmieścić się na jednej stronie, usta­wienie to jest ignorowane. Trzecia wartość pozwala zabezpieczyć się przed pozo­stawieniem na kończącej się stronie nagłówka grupy bez jednego choćby wiersza danych należącego do tej grupy.

Access pozwala grupować dane według wartości wielu pól lub wyrażeń jedno­cześnie. W ten sposób możemy wewnątrz każdej grupy określonej przez kategorię produktów wyodrębnić podgrupy, np. ze względu na datę sprzeda­ży, a te z kolei podzielić według wartości sprzedaży.

Włączenie atrybutu Ukryj powtórzenia powoduje nie wy­świetlanie formantów przyjmujących te same wartości w kolejnych wierszach. Dzięki temu znacznie poprawia się czytelność raportu i tym nie trzeba wtedy używać grupowania wierszy.

1. Obliczenia w raportach

Podobnie jak formularze, raporty również mogą zawierać pola wyliczeniowe. Źró­dłem danych w takim przypadku jest wyrażenie, a nie pole tabeli lub zapytania źródłowego. Zasady przypisywania formantom wyrażeń są identyczne jak w for­mularzach. Wpisujemy je jako wartość atrybutu Źródło formantu, zaczynając zawsze od znaku równości.

Najistotniejszą rolę w przy­padku raportów odgrywają obliczenia z użyciem funkcji agregujących, najczęściej funkcji sumowania.

Dzięki opisanym powyżej możliwościom grupowania danych raporty pozwa­lają na obliczanie sum częściowych dla każdej grupy danych. Aby obliczyć taką sumę częściową, należy uaktywnić stopkę grupy danych (włączyć atrybut Stopka grupy w polu Sortowanie i grupowanie) i umieścić w niej pole tekstowe z wyra­żeniem sumującym, np. =Suma([Kwota]) (w angielskim Accessie należy użyć funkcji Sum).

Pole wyliczeniowe z wyrażeniem sumującym umieszczone w stopce raportu obliczy sumę globalną. Oprócz sumowania globalnego i w grupach możemy także obliczać sumy bieżące, których wartości uwzględniają sumy częściowe kolejnych grup. Jako przykład zastosowania weźmy ewidencję sprzedaży.

Oprócz podsumowania wartości sprzedaży za każdy dzień chcielibyśmy także znać wartość sprzedaży, powiedzmy od początku bieżącego miesiąca. Ta ostatnia liczba to właśnie suma bieżąca (krocząca), zmieniająca się każdego dnia. Możli­wości kumulatywnego sumowania danych w polu tekstowym zależą od ustawie­nia, dostępnego tylko dla pól tekstowych w raportach, atrybutu Suma bieżąca. Atrybut ten jest standardowo wyłączony, ale może też przyjmo­wać wartość W grupie lub Wszędzie.

• Otwórz raport zestawiający sprzedaż według kategorii.

• Umieść w stopce każdej grupy duplikat pola z su­mą częściową i zmień mu ustawienie atrybutu Suma bieżąca na Wszędzie

(Oczywiście dla pierwszej grupy wartości obydwu pól w stopce będą równe, dla ostatniej wartość w polu z sumą kroczącą powinna być równa wartości globalnej w stopce raportu.)

• Ponumeruj automatyczne wiersze w raporcie wykorzystując atrybut Suma bieżąca:

• Umieść na blankiecie raportu w sekcji Szczegóły pole tekstowe zawierające wyrażenie =1. Normal­nie, dla każdego wiersza danych ra­portu w polu takim wyświetlona zo­stałaby jedynka. Jednak włączenie atrybutu Suma bieżąca spowoduje, że w kolejnych wierszach raportu poja­wią się kolejne liczby 1, 2, 3... Gdy sumowanie przeprowadzane jest po grupie, wówczas numeracja przepro­wadzana jest oddzielnie dla każdej grupy.

• Na każdym raporcie można umieścić gotowe pola wyliczane wykorzystując polecenia Numery stron i Data i Godzina z menu Wstaw.

1. Utwórz raporty będące wyjściami bazy danych - przykłady

Rozbuduj bazę danych o elementy niezbędne do utworzenia zaplanowanych raportów. Pamiętaj, że aby otrzymać raport zawierający określone dane, trzeba te dane wcześniej zebrać w odpowiedniej tabeli, wprowadzić za pomocą formularzy (interface użytkownika) i dopiero można utworzyć raport oparty na tabeli lub najlepiej na kwerendzie.

Na podstawie przechowywanych w bazie danych dotyczących sprzedaży i wy­datków dokumenty te są automatycznie wypełniane i mogą być wydrukowane.

Raporty najlepiej uruchamiać za pomocą przycisków umieszczonych na poszczególnych formularzach lub z osobnego formularza - Panelu Głównego, lub pomocniczego.

• Utwórz kwerendy wybierające, których źródłem będą dodane w punkcie III.2.3 tabele i tabele utworzone za pomocą kwerend.

• Skonstruuj oparte na nich raporty pokazujące wierzytelności i dokonane płatności

• Utwórz raport faktury VAT

Raport oprócz nagłówka z nazwą, numerem dokumentu i datą sprzedaży zawiera dane sprzedawcy (stała część), dane nabywcy, dane o sprzedaży i część końcową z wyszczególnieniem kwoty do zapłaty oraz miejscem na podpisy kontrahentów.

Raport jest dwustronicowy, pierwsza strona to oryginał, druga to kopia, oczywiście każda strona zawiera identyczne dane. Dzięki temu zarówno kopia, jak i oryginał są drukowane jednocześnie. Inną konsekwencją tego faktu jest umieszczenie wszyst­kich elementów raportu w sekcji szczegółów, bez korzystania z nagłówków i stopek. W polu z datą wystawienia dokumentu oraz z adresem nabywcy wykorzystano wyrażenie łączące stały tekst z wartością pola źródłowego. Zabieg ten sprawia, że tekst pozbawiony jest dużych przerw, jak to ma miejsce przy użyciu pola teksto­wego z etykietą. Operatorem łączącym łańcuchy tekstowe jest znak &, a przykła­dowe wyrażenie wygląda tak:

= [Miejscowość] &", ul." & [Adres]

Najważniejszą częścią dokumentu jest wyliczenie towarów będących przed­miotem sprzedaży oraz szczegółowe rozliczenie wartości netto, podatku i warto­ści brutto według różnych stawek podatku. Wszystkie te elementy faktury repre­zentowane są w naszym raporcie przez podraporty, czyli stworzone osobno raporty umieszczone następnie wewnątrz raportu głównego. Procedura umieszczania pod-raportu w raporcie głównym i ich wzajemnego powiązania jest identyczna jak dla formularzy.

W tym przypadku polem łączącym jest pole z numerem faktury. Źródłem da­nych dla podraportów są specjalnie zdefiniowane zapytania. W podraporcie Faktura sprzedaży-szczególy wykorzystano nagłówek raportu, umieszczając w nim opisy po­szczególnych rubryk, w części przeznaczonej na dane można umieścić po lewej pole w rubryce oznaczonej Lp. z użyciem tu opisanego wcześniej sposobu dynamicznej numeracji wierszy w raporcie, możliwej dzięki atrybutowi Suma bieżąca. W ten sposób wszystkie pozycje na fakturze będą zawsze ponumerowane.

Wróćmy jeszcze na chwilę do raportu głównego. Zawiera on wyodrębnione Pole tekstowe oznaczone etykietą Do zaplaty. W polu tym powtórzona zostaje globalna wartość faktury. Wartość ta występuje także w podraporcie, który jako obiekt raportu głównego nazywa się Brutto, podczas gdy w rzeczywistości jest to raport Faktura sprzedaży-brutto. Aby móc odwołać się z poziomu raportu główne­go do wartości pola należącego do podraportu, należy użyć wyrażenia, którego ogólna składnia wygląda następująco:

= [Nazwa podraportu]. Raport! [Nazwa formantu],

gdzie Nazwa podraportu to wartość atrybutu Nazwa dla podraportu, Na­zwa formantu zaś to wartość tegoż atrybutu dla formantu (np. pola tekstowego) wewnątrz raportu używanego jako podraport. Gdy odwołujemy się do formantu na formularzu, zamiast słowa Raport należy użyć Formularz. W rozważanym przykładzie użyto wyrażenia:

= [Brutto].Raport![ PoleSumOfWartość brutto]

Po identyfikatorach Raport i Formularz nie trzeba wstawiać nazwy raportu (formularza). Access szuka wymienionego formantu na raporcie (formularzu) aktywnym, w przeciwnym razie wystąpi błąd, a zamiast wyniku wyrażenia, na wydruku lub w trybie podglądu, pojawi się napis #Nazwa?.

Do formantów użytych w raportach i formularzach można odwoływać się tak­że z poziomu zapytań. Przykładem jest zapytanie źródłowe raportu głównego Dane dofaktury-naglówek. Zawiera ono jako parametr wyrażenie:

Formularze! [Ewidencja sprzedaży]! [Element pierwszy]

Tu jednak użyto identyfikatora klasy obiektów systemowych Formularze, po którym musi wystąpić nazwa obiektu. Odwołując się do raportu, używamy identyfikatora Raporty. Użycie identyfikatorów Formularze lub Raporty wraz z nazwami obiektów pozwala odwołać się do dowolnego z otwartych formularzy lub raportów. Identyfikatory Formularz i Raport pozwala­ją odwołać się tylko do aktywnego formularza lub raportu.

Dla wyjaśnienia należy dodać, że pole tekstowe o nazwie Element pierwszy w for­mularzu Ewidencja sprzedaży zawiera numer faktury. Dzięki odwołaniu się do warto­ści tego pola w zapytaniu źródłowym dla raportu z fakturą sprzedaży możemy dru­kować fakturę VAT zgodną z danymi w formularzu Ewidencja sprzedaży.

• ^* Jako zadanie specjalne pozostaje opracowanie algorytmu zamiany wartości liczbowych na postać słowną, aby automatycznie wypełnić rubrykę Slownie. Naj­prościej, choć może niezbyt elegancko, będzie posłużyć się metodą spotykana gdzieniegdzie, a polegającą na zamianie poszczególnych cyfr na ich słowne odpo­wiedniki. W ten sposób zawrotna kwota 123 500 zł staje się w postaci słownej ciągiem: „jeden-dwa-trzy-pięć-zero-zero złotych zero-zero groszy".

• ^* Do samodzielnego opracowania, opcjonalnie można utworzyć raport Książka przychodów i rozchodów. W przypadku braku danych źródłowych rozbuduj bazę o potrzebne tabele i kwerendy)

Książka przychodów i rozchodów to podstawowy dokument firm o niewielkich obrotach. Winien on zawierać ewidencję wszelkiej sprzedaży i wydatków rozpisa­nych na szereg kategorii. W przypadku prowadzenia takiej ewidencji za pomocą komputera wymagany jest codzienny wydruk zestawienia przychodów i rozcho­dów. W przedstawianym przykładzie systemu bazy danych książka przychodów i rozchodów jest raportem zawierającym wszystkie potrzebne dane. Informacje te są wprowadzane do bazy poprzez formularze dotyczące sprzedaży oraz wydatków i przechowywane w różnych tabelach. Przed sporządzeniem dziennego raportu o przychodach i rozchodach uruchamiane są cztery zapytania gromadzące dane w jednej tabeli nazwanej Pomocnicza-Książka p/r. Są to zapytania dołączające.

Jak już wspomniano, zarówno przychody, jak i rozchody są podzielone na szereg kategorii, w sumie jest ich siedem. Każdej z nich odpowiada jedna kolumna książki przychodów i rozchodów. Aby uzyskać zestawienie odpowiadające temu podzia­łowi, konieczne jest użycie zapytania krzyżowego, dla którego wartości pola Numer kolumny tabeli Pomocnicza-Książka p/r stanowić będą na­główki kolumn. Zapytanie to jest bezpośrednim źródłem danych naszego raportu i nazywa się Książka p/r—Do wydruku.

W przypadku takim, jak omawiany obecnie, to znaczy gdy źródłem danych dla raportu jest zapytanie krzyżowe, bardzo ważne jest ustalenie kolejności ko­lumn w zapytaniu poprzez wpisanie ich w rubrykę atrybutu Nagłówki kolumn. Bez tego raport nie będzie w stanie współpracować z zapytaniem, W przypadku formularzy opar­tych na zapytaniach krzyżowych nie musimy ustalać kolejności kolumn.

Pozostaje jeszcze problem podsumowania przychodów i rozchodów z każdej kolumny na końcu strony. Gdy stron jest więcej, do podsumowania bieżącej stro­ny (folio) należy dodać podsumowanie z poprzedniej strony (przeniesienie). W tym celu trzeba pogrupować dane w sposób nieco nietypowy, bo nie ze wzglę­du na wartość którejś z danych, ale ze względu na liczbę wierszy, jaka zmieści się na jednej stronie wydruku. Przy przyjętych rozmiarach rubryk na stronie mieści się 37 wierszy danych.

Gdy podzielimy wszystkie dane przeznaczone do raportu na takie grupy, to w stopce każdej z nich bez problemu otrzymamy podsumowanie każdej kolumny w ramach grupy, czyli na jednej stronie, a ustawiając atrybut Suma bieżąca na Wszędzie, także podsumowanie od początku zestawie­nia. Jednak utworzenie grup ze względu na liczbę wierszy wymaga użycia wartości jednego z pól zapytania źródłowego do ponumerowania wierszy. Pole to bierze się z tabeli Pomocnicza-Książka p/r i jest typu Licznik. Stamtąd jego wartości przekazywane są zapytaniem źródłowym do raportu i choć nie są w nim wyświetlane, mogą być użyte jako kryterium grupowania.

Ostatnią, czwartą część instrukcji można będzie pobrać przed rozpoczęciem ostatniego cyklu zajęć ze strony Katedry: http://wr.atr.bydgoszcz.pl/kiz wybierając przycisk menu Accessmaster.

Osobne zapytanie aktualizujące powinno zmieniać wartość pola Status w ta­beli Zamówienia dla faktur już zapłaconych, czyli takich, które w polu Do zapłaty w tabeli Finanse-Wierzytelności mają wartość 0. Należałoby to pole uwzględnić w tabeli Zamówienia.

^* Element opcjonalny

^* Element opcjonalny

---