Obliczenia w kwerendach - cz. I
Załóżmy, że mamy tabelę:
i naszym zadaniem jest zaprojektowanie kwerendy, która będzie liczyła wynagrodzenie całkowite tzn. sumę poszczególnych jego składników wg. wzoru.
wyplata = wynagrodzenie + wynagrodzenie * wysługa / 100 + premia
O kostrukcji wyrażeń możesz przeczytać tutaj: http://office.microsoft.com/pl-pl/assistance/HP030716271045.aspx
Jeśli chcesz sam wykonać zadanie – klikniej aby pobrać przykład.
Aby to zrobić należy skorzystać z kreatora i utworzyć "najzwyklejszą" kwerendę wybierającą. Następnie należy przejść do widoku projekt i w pierwszej wolnej kolumnie (po wyrażeniu premia) kliknąć prawym przyciskiem w polu znajdującym się na wyskości nazw pól tabeli:
z menu, które się pojawi należy wybrać Konstruuj, pojawi się okno Konstruktora wyrażeń, w którym wpisujemy zadaną formułę:
po zatwierdzeniu formuły i wskazaniu, że jej wynik ma być wyświetlany po uruchomieniu kwerendy powinniśmy otrzymać następujący widok:
Jak widać wypłata została policzona zgodnie z zadanym wzorem – kliknij aby pobrać plik rozwiązania.
Załóżmy jednak, że Jan Nowak, nie dostanie w tym miesiącu premii, a panu Stefanowi Kowalskiemu zmieniono system wynagradzania i wysługa lat nie jest brana pod uwagę. Tabela przyjmuje postać - kliknij aby pobrać przykład:
a kwerenda obliczająca, zaczyna wyświetlać następujący wynik - kliknij jesli chcesz zobaczyć przykład:
czyli jak widać dla osób, które choć jeden składnik wypłaty miały pusty (wartość Null) obliczona wypłata przyjeła też wartość Null. Można sobie z tym poradzić, opis problemu jest w pomocy MS Access lub na stronach Microsoftu: http://office.microsoft.com/pl-pl/assistance/HP051880271045.aspx
Aby móc liczyć wypłatę w takiej sytuacji musimy skorzystać z funkcji NZ, która konwertuje pola Null do 0.
Wzór na wypłatę powinien przyjąć postać:
a kwerenda powinna zacząć obliczać sumę także dla tych rekordów gdzie niektóre pola są puste
Kliknij aby obejrzeć przykład z rozwiązaniem. Problem został rozwiązany.
Kolejne, ostatnie w tej części zadanie obejmuje bardzo szkolny przykład, załóżmy, że mamy oceny semestralne (dla potrzeb zadania wybrano tylko 4 przedmioty).
Kliknij aby pobrać plik zadanie.
Przyjęto, że:
- jeśli uczeń nie miał danego przedmiotu to oceny się mu nie wpisuje – wartość Null
- jeśli uczeń był nieklasyfikowany to wpisujemy mu 0
Średnia liczona jest wg. wzoru:
ocena1 + ocena2 + … + ocenaN
----------------------------------------
liczba ocen
widzimy więc, że mamy dwa problemy:
a) pierwszy konwersję pustych pól (z tym sobie poradzimy za pomocą funkcji NZ),
b) drugi taki, że musimy na bieżąco dla każdego rekordu liczyć liczbę ocen bo:
- uczeń danego przedmiotu mógł w ogóle nie mieć
- uczeń był nieklasyfikowany z danego przedmiotu – wpis 0 – ale do średniej tego brać nie należy
rozwiązaniem tego problemu jest dość skomplikowana formuła:
srednia: (Nz([pol])+Nz([mat])+Nz([che])+Nz([fiz]))/(IIf((([pol]=0) Or (IsNull([pol])));0;1)+IIf((([mat]=0) Or (IsNull([mat])));0;1)+IIf((([che]=0) Or (IsNull([che])));0;1)+IIf((([fiz]=0) Or (IsNull([fiz])));0;1))
przyjrzyjmy się fragmentowi, który się powtarza w mianowniku:
(IIf((([pol]=0) Or (IsNull([pol])));0;1)
oznacza on, że jeśli (funkcja IIf) ocena z polskiego jest równa zero ([pol]=0) lub (Or) pole jest puste IsNull([pol]) to wyrażenie ma zwrócić wartość 0 czyli pole to nie będzie liczone do liczby ocen – jeśli nie to wyrażenie ma zwrócić wartość 1 – ocena będzie liczona do średniej.