1

Podzapytania – zapytania SELECT umieszczone w innym zapytaniu SELECT

01. podzapytanie z równością – podaj wszystkich pracowników zatrudniony w biurze przy ulicy Małej 63:

SELECT Personel.pracownikNr, Personel.imię, Personel.nazwisko, Personel.stanowisko

FROM Personel

WHERE Personel.biuroNr=(SELECT biuroNr FROM Biuro WHERE ulica = "Mała 63");

 02. podzapytanie z funkcją agregującą – podaj wszystkich pracowników, których pensja jest wyższa od średniej; pokaż różnice między poszczególnymi pensjami a średnią:

SELECT Personel.pracownikNr, Personel.imię, Personel.nazwisko, Personel.stanowisko, [pensja]-(SELECT AVG(pensja) FROM personel) AS różnica

FROM Personel

WHERE (((Personel.pensja)>(SELECT AVG(pensja) FROM Personel)));

2

03. podzapytania zagnieżdżone – powtórzenia właścicieli nieruchomości:

SELECT Nieruchomość.właścicielNr, Nieruchomość.nieruchomośćNr, Nieruchomość.miasto, Nieruchomość.ulica

FROM Nieruchomość

WHERE (((Nieruchomość.właścicielNr) IN (SELECT [właścicielNr] FROM [Nieruchomość] AS Tmp GROUP BY [właścicielNr] HAVING Count(*)>1 )))

ORDER BY Nieruchomość.właścicielNr;

04. zastosowanie ANY / SOME – znajdź wszystkich pracowników, którzy mają pensję wyższą niż przynajmniej jeden pracownik biura o numerze B003

  SELECT Personel.pracownikNr, Personel.imię, Personel.nazwisko, Personel.stanowisko, Personel.pensja

FROM Personel

WHERE pensja > SOME (SELECT pensja FROM Personel WHERE biuroNr="B003"); 

3

05. zastosowanie ALL – znajdź wszystkich pracowników, którzy mają pensję wyższą niż pensja każdego z pracowników biura o numerze B003

SELECT Personel.pracownikNr, Personel.imię, Personel.nazwisko, Personel.stanowisko, Personel.pensja

FROM Personel

WHERE.pensja>ALL (SELECT pensja FROM Personel WHERE biuroNr="B003");

Kwerendy z odnośnikami

06. Wpisywanie danych pracowników z uzupełnieniem danych o Biurze:

SELECT Personel.pracownikNr, Personel.imię, Personel.nazwisko, Personel.stanowisko, Personel.płeć, Personel.dataUr, Personel.pensja, Personel.biuroNr, Biuro.ulica, Biuro.miasto

FROM Biuro INNER JOIN Personel ON Biuro.biuroNr = Personel.biuroNr;

4

Pułapki w Access:

07 – Podaj nazwiska, stanowiska oraz wysokości pensji pracowników z biura o wpisanym numerze

Problem maski: \B000

wówczas zapisywane dane do tabeli 002

003

004 itd.

Powinna być maska w tabeli: \B000;0;_

Aby litera B była zapisywana razem z pozostałymi cyframi.

08 – informacje o nieruchomościach nadzorowanych przez pracownika

Inna sytuacja gdzie kryteria mogą być wpisywane również małymi literami.

5

Właściwości sprzężenia w kwerendzie:

09 – Obiekt Biuro nie pasuje do obiektu Personel (właściwości sprzężenia w kwerendzie nr 2)

SELECT Biuro.biuroNr, Biuro.miasto

FROM Biuro LEFT JOIN Personel ON Biuro.biuroNr = Personel.biuroNr

WHERE (((Personel.biuroNr) Is Null));

6

Funkcje agregujące i do jakich pól można ich użyć:

Wybierz Aby obliczyć Dopuszczalne typy danych

Suma Sumę wartości w polu. Liczba, Data/Godzina, Waluta i Autonumerowanie

Średnia Średnią z wartości w polu. Liczba, Data/Godzina, Waluta i Autonumerowanie

Minimum Najmniejszą wartość w polu. Tekst, Liczba, Data/Godzina, Waluta i Autonumerowanie

Maksimum Największą wartość w polu. Tekst, Liczba, Data/Godzina, Waluta i Autonumerowanie

Zlicz Liczbę wartości pola, z pominięciem wartości Null (pustych).

Tekst, Memo, Liczba, Data/Godzina, Waluta, Autonumerowanie, Tak/Nie i Obiekt OLE

OdchStd Odchylenie standardowe wartości w polu.

Liczba, Data/Godzina, Waluta i Autonumerowanie

Wariancja Wariancję wartości w polu. Liczba, Data/Godzina, Waluta i Autonumerowanie

7

Wybierz Aby

Grupuj według Określić grupy, dla których mają zostać przeprowadzone obliczenia. Aby na przykład obliczyć wartość sprzedaży według kategorii, należy wybrać opcję Grupuj według dla pola "NazwaKategorii".

Wyrażenie Utworzyć pole obliczeniowe zawierające w swoim wyrażeniu funkcję agregującą. Pole obliczeniowe tworzy się zazwyczaj wtedy, gdy w wyrażeniu jest kilka funkcji.

Gdzie Określić kryteria dla pola, które nie jest używane do zdefiniowania grupy. Jeśli opcja ta zostanie wybrana dla pola, Program Microsoft Access ukryje to pole w wynikach kwerendy przez wyczyszczenie pola wyboru Pokaż.

10a – liczba pracowników każdego z biur oraz ich sumaryczna pensja

Uwaga   Funkcje agregujące nie uwzględniają w obliczeniach rekordów zawierających wartości puste (Null).

Również jeśli w wyrażeniu używany jest operator arytmetyczny (+, -, *, /) i jedno z pól w wyrażeniu ma wartość Null, wynik całego wyrażenia będzie Null.

Można przekształcić wartość Null w zero używając np.. funkcji Nz lub IIf.

8

Przykłady pracy z wartościami Null za pomocą pól obliczeniowych:

Wyrażenie:

BieżącyKraj: IIf(IsNull([Kraj]); " "; [Kraj])

Używa funkcji IIf i IsNull do wyświetlania pustego ciągu znaków w polu "BieżącyKraj", jeśli wartość w polu "Kraj" jest Null. Jeśli nie, wyświetlana jest wartość z pola "Kraj".

CzasDostawy: IIf(IsNull([DataWymagana] - [DataWysyłki]); "Znajdź brakującą datę"; [DataWymagana] - [DataWysyłki])

Używa funkcji IIf i IsNull do wyświetlania w polu "CzasDostawy" komunikatu "Znajdź brakującą datę" jeśli wartość w polu "DataWymagana" lub "DataWysyłki" jest wartością Null. Jeśli nie, wyświetlana jest różnica tych wartości.

9

SprzedażPółroczna: Nz([Sprz1Kwart];0) + Nz([Sprz2Kwart];0)

W polu "SprzedażPółroczna" wyświetla sumę wartości w polach opisujących sprzedaż w pierwszym i drugim kwartale, wykorzystując funkcję Nz do przekształcania wartości Null w zero.

Baza: Null.mdb

IIf(IsNull([CenaJednostkowa]); 0 ;[CenaJednostkowa])

Zmienia wartość Null na zero (0) w polu "CenaJednostkowa".

10b – liczba pracowników każdego z biur oraz ich sumaryczna pensja

10

Wyrażenie Opis

ImięINazwisko: [Imię] & " " & [Nazwisko]

Wyświetla wartości pól "Imię" i "Nazwisko" oddzielone spacją w polu "ImięINazwisko".

Addres2: [Miasto] & " " & [Region] & " " & [KodPocztowy]

Wyświetla wartości pól "Miasto", "Region" i "KodPocztowy" oddzielone spacjami w polu "Addres2".

IDproduktu: Left([NazwaProduktu]; 1) Wykorzystuje funkcję Left do wyświetlania w polu "IDproduktu" pierwszego znaku wartości z pola "NazwaProduktu".

KodTypu: Right([KodMajątkowy];2) Używa funkcji Right do wyświetlania w polu "KodTypu" ostatnich dwóch znaków wartości z pola "KodMajątkowy".

NumerKierunkowy: Mid([Telefon];2;3) Używa funkcji Mid do wyświetlania w polu "NumerKierunkowy" trzech znaków poczynając od drugiego znaku wartości w polu "Telefon".

Inne przykłady operacji wykonywanych na wartościach tekstowych w polach obliczeniowych:

11

Left(wyr; n) Right(wyr; n) Mid(wyr; start; n)

Argument wyr może być nazwą pola (ujętą w nawiasy) lub wyrażeniem tekstowym; Argument n oznacza liczbę znaków, które mają zostać wyodrębnione, a argument start określa położenie pierwszego wyodrębnianego znaku.

Wartość w polu IDczęści Wyrażenie Zwraca

BA-7893-R12 Left([IDczęści];2) BA

BA-7893-R12 Right([IDczęści];3) R12

BA-7893-R12 Mid([IDczęści];4;4) 7893

11-pierwszy znak z numeru nieruchomości itd.

Wyrażenia wprowadzane są w komórce Pole w siatce projektu kwerendy.

12

Przykłady wyrażeń, w których jako kryteria zastosowano wartości tekstowe

NazwaFirmy >="N" Wyświetla zamówienia wysłane do firm, których nazwy zaczynają się na litery od N do Z.

IDzamówienia Right([OrderID]; 2)="99" Używa funkcji Right aby wyświetlić zamówienia, których IDzamówienia kończy się na 99.

NazwaFirmy Len([NazwaFirmy])>Val(30) Używa funkcji Len i Val, aby wyświetlić zamówienia wysłane do firm, których nazwy są dłuższe niż 30 znaków.

12a – nieruchomości przy ulicach od A do K

12b – właściciele nieruchomości o numerach końcowych 46 lub 87

12c – miejscowości o nazwach dłuższych niż 5 znaków

13

Przykłady zmieniania i obliczania dat w polach obliczeniowych:

Year(date) Month(date) Day(date) Hour(time) Minute(time) Second(time)

Date(): 2005-04-28

Now(): 2005-04-28 13:07:52

Time(): 13:07:53

13-operacje na datach w polach obliczeniowych

Wyrażenie Opis

CzasDostawy: DateDiff("d"; [DataZamówienia]; [DataWysyłki])

Używa funkcji DateDiff do wyświetlania w polu "CzasDostawy" liczby dni między datą zamówienia i datą wysyłki.

RokZatrudnienia: DatePart("rrrr";[DataZatrudnienia])

Używa funkcji DatePart do wyświetlania w polu "RokZatrudnienia" roku, w którym został zatrudniony każdy z pracowników.

Date( )- 30 Używa funkcji Date do wyświetlania daty o 30 dni wcześniejszej od daty bieżącej.

14

Operacje na datach wykorzystywane w kryteriach:

Pole Wyrażenie Opis

DataWymagana Between Date( ) And DateAdd("m"; 3; Date( ))

Używa operatora Between...And i funkcji DateAdd i Date aby wyświetlić zamówienia, które mają zostać zrealizowane w ciągu trzech miesięcy od daty bieżącej.

DataZamówienia < Date( )- 30 Używa funkcji Date, aby wyświetlić zamówienia, które mają ponad 30 dni.

DataZamówienia Year([DataZamówienia])= 1996

Używa funkcji Year, aby wyświetlić zamówienia, które zostały złożone w roku 1996.

14a-nieruchomości których wynajęcie kończy się w ciągu 3 miesięcy

14b- nieruchomości których wynajęcie skończyło się więcej niż 30 dni temu

14c-nieruchomości, których wynajęcie skończyło się w 2004 roku

15

DataZamówienia DatePart("k"; [DataZamówienia])=4 Używa funkcji DatePart, aby wyświetlić zamówienia przypadające na czwarty kwartał.

DataZamówienia DateSerial(Year([DataZamówienia]); Month([DataZamówienia]) +1; 1)-1

Używa funkcji DateSerial, Year i Month, aby wyświetlić zamówienia do realizacji ostatniego dnia każdego miesiąca.

DataZamówienia Year([DataZamówienia])=Year(Now()) And Month([DataZamówienia])=Month(Now())

Używa funkcji Year i Month oraz operatora And, aby wyświetlić zamówienia na bieżący rok i miesiąc.

15a-nieruchomości których wynajęcie kończy się w tym kwartale

15b- nieruchomości których wynajęcie kończy się ostatniego dnia miesiąca

15c-nieruchomości których wynajęcie kończy się w bieżącym roku i miesiącu

16

Opis funkcji dotyczących dat:

DateDiff (część_daty, data1, data2[, firstdayofweek[, firstweekofyear]])

DatePart (część_daty, data[,firstdayofweek[, firstweekofyear]])

DateAdd (część_daty, liczba, data)

Weekday (data, [firstdayofweek])

część_daty Setting (SQL) Opis

rrrr yyyy rok

k q kwartał

m m miesiąc

r y dzień roku

d d dzień miesiąca

t w dzień tygodnia

tt ww tydzień

g h godzina

n n minuta

s s sekunda

17

firstdayofweek argument: firstweekofyear argument:

Wartość Opis

1 niedziela (domyślnie)

2 poniedziałek

3 wtorek

4 środa

5 czwartek

6 piątek

7 sobota

Wartość Opis

1 Rozpoczyna liczenie w tygodniu, w którym jest 1 stycznia (domyślne).

2 Rozpoczyna liczenie w tygodniu, który zawiera

co najmniej 4 dni nowego roku.

3 Rozpoczyna liczenie od pełnego tygodnia roku.

18

Użytkownik „Biuro” 4 (zarząd biura):

17. Podaj informacje o wynajęciach nieruchomości zarejestrowanych w danym biurze, których termin końcowy upłynie w następnym miesiącu.

18. Podaj całkowitą liczbę umów najmu zawartych w biurach w Łomży na okres krótszy niż jeden rok.

19

Kreowanie formularzy:

Kontrolowanie czasu wyświetlania:

1-Start - zamykany po 5 sekundach

Private Sub Form_Open(Cancel As Integer)

Me.TimerInterval = 5000

End Sub

Private Sub Form_Timer()

DoCmd.Close acForm, Me.Name

DoCmd.OpenForm "2-Przełączający hiperłącza"

DoCmd.OpenForm "3-Przełączający formularze"

End Sub

20

Formularze przełączane przy użyciu hiperłączy do przekazywania fokusu

2-Przełączający hiperłącza

Dla etykiety w właściwościach Formatu ustawiamy adres hiperłącza

Przełączanie przy użyciu przycisków i kodu w VBA

3-Przełączający formularze z zamykaniem pozostawionych

21

4 – korzystanie z kreatorów i formatowanie warunkowe

5 – podformularze

6 – formularz wyszukujący do kwerendy z pola tekstowego

7 – Personel wybierany bezpośrednio z pola kombi