Mamy spojrzał w pułapkach używania arkusza kalkulacyjnego, takiego jak Excel, do przechowywania list danych. Na początku takie podejście może wydawać się najlepszym rozwiązaniem, ale możesz napotkać problemy z udostępnianiem tych danych wielu użytkownikom, sprawdzaniem ich zawartości, a nawet nawigacją po danych. Dlaczego? Ponieważ używasz narzędzia, które nie zostało zaprojektowane do tego zadania.
Teraz rozważymy wyimaginowany (ale typowy) przypadek firmy korzystającej z listy opartej na arkuszu kalkulacyjnym i przyjrzymy się, jak można ją przekonwertować na aplikację bazy danych, aby przezwyciężyć takie problemy.
Jak skoroszyty wymykają się spod kontroli
Nasza lista zaczęła się od prostego zapisu projektów podjętych dla klientów. Wraz z rozwojem firmy wzrosła również liczba klientów, których nazwiska i dane kontaktowe zostały dodane do skoroszytu. Potrzebny był również sposób na zapisanie tego, co różni pracownicy robili w tych projektach, więc jeszcze więcej danych zostało dodanych do tego skoroszytu.
W tym momencie podejście oparte na arkuszu kalkulacyjnym stało się niewykonalne: zbyt wiele osób próbowało aktualizować go, często w tym samym czasie. Firma próbowała wprowadzić grafik, tak aby ludzie po kolei aktualizowali skoroszyt, ale oznaczało to, że o niektórych zadaniach zapomniano, zanim zostały zapisane.
W końcu ludzie zakładają własne skoroszyty, aby śledzić swoje zadania, czasami pamiętając o skopiowaniu danych do głównego skoroszytu na koniec tygodnia. Pracownicy opracowali własne skróty dla tych książek, a niektórzy zmienili formatowanie i kolejność kolumn, aby dopasować je do swojego sposobu pracy. Skopiowanie tych danych do głównego skoroszytu spowodowało okropny bałagan.
To może być zmyślony przykład, ale w rzeczywistości widziałem wszystkie te praktyki w prawdziwym życiu. Przyjrzyjmy się bliżej niektórym problemom wynikającym z tej metody pracy.
Wiele problemów
Możesz zobaczyć pierwszy arkusz naszego wyimaginowanego arkusza kalkulacyjnego. Pierwsza kolumna zawiera szczegółowe informacje o nazwie projektu, do którego odnosi się każdy wpis. Niektóre z tych nazw są jednak długie, więc pracownicy mogli pokusić się o użycie skrótów; w rezultacie wkradły się literówki. Utrudnia to ustalenie, które zadania należą do danego projektu. Rozwiązanie nie musi być trudne: możesz wybrać krótką nazwę dla każdego projektu, na którą wszyscy się zgadzają, lub nadać każdemu projektowi numer identyfikacyjny i automatycznie przetłumaczyć go na nazwę projektu.
Podobny problem występuje w kolumnie Rozpoczęte. Niektóre komórki zawierają datę, ale inne rejestrują tylko miesiąc - a jeden lub dwa rekordy mówią po prostu Tak. Program Excel obsługuje sprawdzanie poprawności danych, więc można zapewnić, że określone komórki zawsze zawierają dane określonego typu, ale gdy arkusz kalkulacyjny jest opracowywany ad hoc, jest rzadko używany.
W tym momencie podejście oparte na arkuszu kalkulacyjnym staje się niewykonalne: zbyt wiele osób starało się go aktualizować
Nie będziesz mieć tego problemu w aplikacji bazodanowej, ponieważ typ danych pola zostanie ustalony od samego początku. Jeśli nie znasz dokładnej daty rozpoczęcia pracy, możesz podać pierwszy dzień miesiąca lub 1 stycznia, jeśli znasz tylko rok. Jeśli projekt nie został jeszcze uruchomiony, możesz pozostawić to pole puste - wartość NULL w kategoriach bazy danych. Jeśli wiesz, że projekt został rozpoczęty, ale nie wiesz, kiedy, możesz użyć daty, która normalnie byłaby niemożliwa dla Twoich danych, na przykład 01.01.1900. Od razu łatwo jest sortować projekty i uzyskiwać chronologiczny przegląd działań.
Bardziej subtelne wyzwanie przedstawia kolumna oznaczona jako Klient. Wpisy w tej kolumnie nie są połączone z niczym innym w skoroszycie, ale w arkuszu 1 znajduje się lista klientów, do której prawdopodobnie się odnosi. Przechowywanie wielu list tych samych elementów, do których odnoszą się różne nazwy, jest mylące. Musisz sprecyzować nazewnictwo i ustalić jednoznaczną nazwę dla tego podmiotu: czy są to klienci czy klienci?
Kolumna Status to kolejna kolumna, w której nie przeprowadzono weryfikacji, więc ludzie ponownie zdecydowali się pisać, co chcą. Lepiej byłoby sporządzić krótką listę wszystkich dopuszczalnych wartości.
Drugi arkusz - Arkusz 1 - jest równie problematyczny. Po pierwsze, nazwa arkusza nie jest opisowa. W rzeczywistości zawiera listę z nagłówkiem Klienci, ale nie jest ona sformatowana jako tabela w programie Excel: adres znajduje się w jednym polu, co ogranicza możliwość korzystania z wbudowanych narzędzi programu Excel do wyszukiwania lub sortowania. Możesz na przykład filtrować adresy zawierające Cardiff, ale wyniki obejmowałyby również adresy z Cardiff Road w Newport.
Jeśli chodzi o adresy, najlepszym podejściem jest użycie oddzielnych pól dla kodu pocztowego, hrabstwa, miasta i ulicy (chociaż informacje o hrabstwie są opcjonalne w przypadku adresów w Wielkiej Brytanii - patrz Brak hrabstw, jesteśmy Brytyjczykami). Ulica powinna zawierać wszystko, czego nie ma w innych częściach adresu.
Jest tam pole Kontakt, które również przedstawia problemy. W przypadku, gdy mamy kilka kontaktów w firmie z jednym klientem, wszystkie ich nazwiska zostały umieszczone w tym polu, a ich numery telefonów i adresy e-mail zostały podobnie umieszczone w innych polach. Oddzielenie ich będzie trudne - zwłaszcza jeśli w polu Kontakt znajdują się trzy nazwiska, ale tylko dwa numery telefonów.
Ostatnia kolumna w tym arkuszu nosi tytuł Ostatni kontakt: pracownicy powinni aktualizować tę informację za każdym razem, gdy nawiązują kontakt z klientem. Ponieważ te informacje są dla pracownika dodatkową rzeczą do zapamiętania i nie ma gwarancji, że to zapamiętają - zwłaszcza, że są one ukryte na drugim arkuszu - są one zawodne. To jest coś, co komputer powinien śledzić automatycznie.
Na koniec dochodzimy do arkuszy Zadań, które szczegółowo opisują zadania i komentarze dla każdego pracownika. Nie mają spójnych nazw i nie zawierają tych samych kolumn w tej samej kolejności. Podczas gdy indywidualni użytkownicy mają sens, aby wprowadzać swoje dane we własnych arkuszach, brak spójności utrudnia zestawianie i analizowanie danych. Gdy menedżer chce zobaczyć, na przykład, jaka praca została wykonana w każdym projekcie, wszystkie zadania muszą zostać ręcznie skopiowane z poszczególnych arkuszy na jedną listę, zanim będzie można je posortować i opisać.
Budowanie bazy danych
Rozwiązanie tych problemów zajmie trochę pracy, prawdopodobnie kilka dni. Ponieważ użytkownicy będą prawdopodobnie musieli nadal korzystać ze starego systemu podczas tworzenia nowego, najlepiej jest wykonać kopię istniejących skoroszytów, na podstawie których będą pracować. Oznacza to, że będziemy chcieli dokumentować każdy krok w procesie konwersji danych, abyśmy mogli szybko zrobić to ponownie, gdy nadejdzie czas przejścia na nowy system.
Pierwszą rzeczą, którą musisz zrobić, jest wyczyszczenie danych w skoroszycie programu Excel. Korzystanie z funkcji Znajdź i zamień może pomóc i należy usunąć każdą kolumnę lub wiersz, który nie zawiera danych (z wyjątkiem wiersza z nagłówkami kolumn, które należy zachować). Dodaj kolumnę ID do każdego arkusza w kolumnie A i wypełnij ją liczbami przyrostowymi, wpisując 1 w pierwszej komórce, zaznaczając na dole danych (Shift + End, Down), a następnie używając polecenia Wypełnij w dół (Ctrl + D ). Utwórz główną listę nazw projektów i wszędzie tam, gdzie zapisana jest nazwa projektu, użyj funkcji VLookup (), aby potwierdzić jej główny numer ID; jeśli nie ma numeru, dane są niespójne.
Gdy dane są już czyste, czas zaprojektować nową bazę danych, która będzie je przechowywać. Będziemy używać Access 2013, ponieważ w naszym teoretycznym przykładzie jest on dostępny dla wszystkich naszych użytkowników w ramach subskrypcji Office 365. Tworząc nową bazę danych programu Access, możesz wybrać utworzenie jej jako aplikacji sieci Web programu Access lub bazy danych programu Access dla komputerów stacjonarnych. Aplikacje internetowe mają uproszczony interfejs i mogą być używane tylko wtedy, gdy masz Office 365 z SharePoint Online lub SharePoint Server 2013 z Access Services i SQL Server 2012. Będziemy używać tradycyjnej Desktop Database, ponieważ oferuje ona więcej opcji i większą kontrolę nad doświadczenie użytkownika.
Wybierz, aby utworzyć nową bazę danych dla komputerów stacjonarnych i nadaj jej nazwę: program Access utworzy nową tabelę o nazwie Tabela 1 i umieści Cię w widoku projektu z jedną kolumną o nazwie ID. Tutaj możesz zaprojektować tabele, których będziesz potrzebować w swojej bazie danych. Każda tabela powinna mieć pole ID (automatycznie zwiększająca się liczba całkowita), ale aby uniknąć nieporozumień, najlepiej nadać jej bardziej opisową nazwę. W tabeli Projekty będzie to ProjectID, CustomerID w tabeli Klienci i tak dalej.
Możesz ustawić typ danych dla każdej utworzonej kolumny i musisz nadać każdej kolumnie nazwę oraz ustawić inne właściwości i formatowanie odpowiednie dla pola. Podobnie jak w przypadku pola ID, upewnij się, że nazwy kolumn jasno wskazują, jakie dane powinny znaleźć się w polu - na przykład użyj nazwy projektu, a nie tylko nazwy, daty ukończenia, a nie terminu. Możesz użyć przycisku Nazwa i podpis na wstążce, aby utworzyć skrócony podpis, a także jawną nazwę. Możesz używać spacji w nazwach kolumn, ale podczas pisania zapytań i raportów musisz otaczać je nawiasami kwadratowymi.
Chociaż wprowadzanie danych do własnych arkuszy przez użytkowników ma sens, brak spójności utrudnia analizę
Ustaw formatowanie kolumn, takich jak PercentageComplete na Percent, a daty na ShortDate, a także maksymalną długość pól tekstowych na rozsądną wartość lub wszystkie będą miały 255 znaków. Pamiętaj, że niektóre słowa (takie jak data) są zarezerwowane, więc nie możesz ich używać jako nazw kolumn: zamiast tego użyj TaskDate lub czegoś bardziej opisowego.
Jeśli chodzi o kolumny, w których chcesz wyszukać wartość w innej tabeli (na przykład kolumna Klient w tabeli Projekty), zdefiniuj te inne tabele w programie Access przed dodaniem kolumny odnośnika. Jeśli chodzi o Status, najprostszą opcją jest po prostu wpisanie wartości, które mają być wyświetlane na liście rozwijanej - ale utrudnia to późniejsze dodawanie lub edytowanie listy możliwych wartości. O ile nie masz do czynienia z krótką listą, na której możliwe wartości prawdopodobnie się nie zmienią - na przykład pole rejestrujące czyjąś płeć - lepszym pomysłem jest utworzenie kolejnej tabeli dla wpisów, takich jak ProjectStatus. Umożliwia to łatwe dodawanie dodatkowych opcji do listy w przyszłości bez zmiany programowania.
Ulepszenia
Podczas projektowania naszej bazy danych możemy wprowadzić ulepszenia w stosunku do starego sposobu działania opartego na arkuszach kalkulacyjnych. Jedną skargą, jaką nasi użytkownicy mieli w swoich skoroszytach programu Excel, było to, że każde zadanie zawierało tylko jedną komórkę na komentarze, a czasami musieli napisać więcej niż jeden komentarz do zadania - lub przełożony musiał skomentować zadanie, a następnie użytkownik odpowiedz na to. Upchnięcie wszystkiego do jednej komórki utrudniało stwierdzenie, kiedy i przez kogo poczyniono komentarze. Możemy zrobić lepiej, tworząc osobną tabelę na komentarze, połączoną z tabelą Zadania. W ten sposób każde zadanie może mieć tyle komentarzy, ile potrzeba, z osobnymi polami na datę, nazwę użytkownika i tekst każdego z nich.
Innym ulepszeniem, które możemy wprowadzić, jest ustawienie wpisów, takich jak ProjectStatus, tak, aby były wyświetlane w określonej kolejności, a nie alfabetycznie - na przykład możesz chcieć, aby Zakończono znajdowało się na dole listy. Aby to zrobić, dodaj kolumnę DisplayOrder i użyj jej do sortowania listy odnośników. Nie ulegaj pokusie korzystania z pola ID; w związku z tym wszelkie nowe rekordy mogą znaleźć się tylko na końcu listy.
Aby upewnić się, że nasze dane pozostają czyste, możemy oznaczyć pola, które użytkownik musi wypełnić jako Wymagane, i dodać walidację, aby upewnić się, że wprowadzone dane są w prawidłowej formie. Możesz ułatwić sobie życie, ustawiając rozsądne wartości domyślne: pole CommentDate w tabeli Comments może mieć domyślną wartość = Date (), co spowoduje automatyczne ustawienie aktualnej daty za każdym razem, gdy zostanie utworzony nowy komentarz. Możesz użyć walidacji wraz z kolumną Wycofane w tabeli (wartość logiczna), aby uniemożliwić użytkownikom dodawanie nowych rekordów z określonymi wartościami. Pozwala to zachować wartości historyczne, które były prawidłowe, ale nie są już używane. Wszystkie te funkcje można znaleźć w Narzędziach tabel | Karta Pola na wstążce lub we Właściwościach pola w widoku projektu tabeli.
Importowanie danych
Po skonfigurowaniu tabel możesz użyć danych zewnętrznych | Importuj i podłącz | Przycisk programu Excel na wstążce, aby dołączyć dane ze skoroszytu programu Excel do tabel w bazie danych programu Access. Przed rozpoczęciem wykonaj kopię zapasową pustej bazy danych programu Access, na wypadek gdyby coś poszło nie tak, i w razie potrzeby zacznij od ręcznego wypełnienia małych tabel. Po wykonaniu tej czynności wykonaj kolejną kopię zapasową, aby móc wrócić do tego punktu, jeśli coś pójdzie nie tak w kolejnych krokach.
Teraz zaimportuj główne tabele, które nie są zależne od innych tabel, takich jak Klienci, przed zakończeniem pracy z tabelami, które mają relacje, takimi jak Projekty i Zadania. Jeśli zmienisz rozmieszczenie i nazwy kolumn w skoroszycie programu Excel, tak aby jak najściślej pasowały do pól w bazie danych programu Access, nie powinieneś mieć żadnych trudności z zaimportowaniem danych. Pamiętaj, aby zanotować wszystko, co robisz, aby móc powtórzyć to później, jeśli zajdzie potrzeba ponownej konwersji danych.
Po zaimportowaniu danych tabele w widoku arkusza danych powinny działać podobnie jak arkusze programu Excel - ale z dużo lepszą walidacją, wyszukiwaniem i sortowaniem danych. Jeśli chcesz, możesz teraz rozpocząć projektowanie nowych formularzy i raportów w oparciu o te dane: na przykład formularz główny / szczegółowy dla projektów może wyświetlać dane jednego projektu u góry formularza i siatkę zadań dla tego projekt na dole.
Możesz również skonfigurować formularz Moje zadania, który zawiera listę wszystkich zaległych zadań dla bieżącego użytkownika oraz raport Zaległe zadania, który zawiera listę wszystkich zaległych zadań dla wszystkich użytkowników, których termin upłynął.
Żadnych hrabstw, proszę, jesteśmy Brytyjczykami
Jeśli przechowujesz adresy w swojej bazie danych, ważne jest, aby zrozumieć, jakich informacji faktycznie potrzebujesz. Chociaż informacje o hrabstwach mogą być przydatne w celach marketingowych - i mogą być potrzebne w przypadku niektórych adresów zagranicznych - nie są już oficjalnie używane w adresach w Wielkiej Brytanii.
jak otworzyć zamkniętą kartę
Powodem jest to, że adresy pocztowe w Wielkiej Brytanii opierają się na koncepcji miasta pocztowego, w którym poczta jest wysyłana i sortowana przed dostarczeniem do Twoich drzwi. Nie wszystkie miasta i wsie są obsługiwane przez miasta pocztowe w tym samym hrabstwie - na przykład Melbourn (w Cambridgeshire) odbiera pocztę przez Royston (w Hertfordshire) - więc określenie hrabstwa w adresie niekoniecznie pomaga nikomu.
Aby uniknąć nieporozumień, urząd pocztowy przestał używać hrabstw w adresach w 1996 roku, opierając się zamiast tego na informacjach o kodzie pocztowym - a do 2016 roku planuje usunąć nazwy hrabstw z pliku danych aliasów dodatkowych informacji adresowych. Jeśli więc podasz hrabstwo w adresie w Wielkiej Brytanii, zostanie on po prostu zignorowany.
