Weg met rommelige spreadsheets en schakel over naar een database

We hebben keek op de valkuilen van het gebruik van een spreadsheet-applicatie zoals Excel om lijsten met gegevens op te slaan. Deze aanpak lijkt in eerste instantie misschien de beste oplossing, maar u kunt problemen tegenkomen bij het delen van die gegevens met meerdere gebruikers, het valideren van de inhoud of zelfs het navigeren door uw gegevens. Waarom? Omdat u een tool gebruikt die niet is ontworpen om de klus te klaren.

Nu bekijken we een denkbeeldig (maar typisch) geval van een bedrijf dat een op spreadsheets gebaseerde lijst gebruikt, en bekijken hoe dit kan worden omgezet in een databasetoepassing om dergelijke problemen op te lossen.

Hoe werkboeken uit de hand lopen

Onze lijst begon als een eenvoudig overzicht van projecten die voor klanten werden ondernomen. Naarmate het bedrijf groeide, groeide ook het aantal klanten, met namen en contactgegevens toegevoegd aan het werkboek. Er was ook een manier nodig om vast te leggen wat verschillende personeelsleden aan het doen waren bij deze projecten, dus er werden nog meer gegevens aan dit werkboek toegevoegd.

Op dat moment werd de spreadsheetbenadering onwerkbaar: er waren veel te veel mensen die probeerden het up-to-date te houden, vaak tegelijkertijd. Het bedrijf probeerde een rooster in te stellen, zodat mensen om de beurt het werkboek moesten bijwerken, maar dit betekende dat sommige taken werden vergeten voordat ze werden opgenomen.

Uiteindelijk zetten mensen hun eigen werkboeken op om hun taken bij te houden, waarbij ze soms onthouden om de gegevens aan het einde van de week naar het hoofdwerkboek te kopiëren. Werknemers ontwikkelden hun eigen steno voor deze boeken, en sommigen veranderden de opmaak en de volgorde van de kolommen om aan te sluiten bij hun manier van werken. Het kopiëren van deze gegevens naar de hoofdwerkmap resulteerde in een vreselijke puinhoop.

Dit is misschien een verzonnen voorbeeld, maar ik heb al deze praktijken in het echt gezien. Laten we enkele van de problemen die door deze manier van werken naar voren worden gebracht eens nader bekijken.

Veel problemen

U kunt het eerste blad van onze denkbeeldige spreadsheet zien. In de eerste kolom staat de naam van het project waarnaar elk item verwijst. Sommige van deze namen zijn echter lang, dus het personeel is misschien in de verleiding gekomen om afkortingen te gebruiken; Daardoor zijn typefouten binnengeslopen. Hierdoor is het moeilijk om vast te stellen welke taken bij welk project horen. De oplossing hoeft niet moeilijk te zijn: je zou voor elk project een korte naam kunnen kiezen waar iedereen het over eens is, of elk project een ID-nummer geven en dit automatisch naar de projectnaam vertalen.

Er is een soortgelijk probleem met de kolom Gestart. Sommige cellen bevatten een datum, maar andere slechts een maand - en een of twee records zeggen gewoon Ja. Excel ondersteunt gegevensvalidatie, dus het is mogelijk om ervoor te zorgen dat bepaalde cellen altijd gegevens van een bepaald type bevatten, maar wanneer een spreadsheet op een ad hoc-manier wordt ontwikkeld, wordt deze zelden gebruikt.

Op dit punt wordt de spreadsheetbenadering onwerkbaar: er waren veel te veel mensen die probeerden het up-to-date te houden

U zult dit probleem niet hebben in een databasetoepassing, aangezien het gegevenstype van het veld vanaf het begin wordt vastgesteld. Als u de exacte datum waarop het werk is begonnen niet weet, kunt u de eerste van de maand gebruiken, of 1 januari als u alleen het jaar kent. Als het project nog niet is gestart, kunt u het veld leeg laten - een NULL in databasetermen. Als u wist dat het project was gestart, maar niet wanneer wist, kunt u een datum gebruiken die normaal gesproken niet mogelijk is voor uw gegevens, zoals 1/1/1900. Het wordt meteen gemakkelijk om projecten te sorteren en een chronologisch overzicht van de activiteiten te krijgen.

Een meer subtiele uitdaging wordt gepresenteerd door de kolom met het label Client. De vermeldingen in deze kolom zijn niet gekoppeld aan iets anders in de werkmap, maar er staat een lijst met klanten op blad 1, waar het waarschijnlijk naar verwijst. Het is verwarrend om meerdere lijsten met dezelfde items op te slaan, waarnaar met verschillende namen wordt verwezen. U moet de naamgeving verduidelijken en genoegen nemen met een ondubbelzinnige naam voor deze entiteit: zijn het klanten of klanten?

De Status-kolom is een andere kolom waarvoor geen validatie heeft plaatsgevonden, dus mensen hebben er opnieuw voor gekozen om te schrijven wat ze maar willen. Het is beter om een ​​korte lijst op te stellen van alle toegestane waarden.

Het tweede blad - Blad 1 - is net zo problematisch. Om te beginnen is de bladnaam niet beschrijvend. Wat het eigenlijk bevat, is een lijst met de kop Klanten, maar dit is niet opgemaakt als een tabel in Excel: het adres bevindt zich in één veld, waardoor je de mogelijkheid om de ingebouwde tools van Excel te gebruiken om het te zoeken of te sorteren, beperkt. Je zou bijvoorbeeld kunnen filteren op adressen die Cardiff bevatten, maar de resultaten zouden ook die op Cardiff Road in Newport bevatten.

Als het om adressen gaat, is de beste aanpak om aparte velden te gebruiken voor de postcode, provincie, stad en straat (hoewel provincie-informatie optioneel is voor adressen in het VK - zie Geen provincies, we zijn Brits). Straat moet alles bevatten wat niet in de andere delen van het adres staat.

Er is een contactveld, dat ook voor problemen zorgt. Waar we verschillende contacten hebben binnen een bedrijf met één klant, zijn hun namen allemaal op één hoop gegooid in dit veld, met hun telefoonnummers en e-mailadressen op dezelfde manier in de andere velden. Het zal een uitdaging zijn om deze van elkaar te scheiden, vooral als er drie namen in het veld Contact staan, maar slechts twee telefoonnummers.

De laatste kolom in dit blad is getiteld Last Contacted: medewerkers worden geacht dit bij elke contact met een klant bij te werken. Aangezien deze informatie een extra ding is voor de werknemer om te onthouden, en er is geen garantie dat ze dat zullen doen - vooral omdat het op een tweede blad verborgen is - is het onbetrouwbaar. Dit is echt iets dat de computer automatisch zou moeten volgen.

Ten slotte komen we bij de Takenbladen, die de taken en opmerkingen voor elke werknemer gedetailleerd weergeven. Deze worden niet consistent genoemd en bevatten niet dezelfde kolommen in dezelfde volgorde. Hoewel het logisch is dat individuele gebruikers hun gegevens op hun eigen bladen invoeren, maakt het gebrek aan samenhang het moeilijk om de gegevens te verzamelen en te analyseren. Wanneer een manager bijvoorbeeld wil zien wat voor werk er aan elk project is gedaan, moeten alle taken met de hand van de afzonderlijke bladen naar één lijst worden gekopieerd voordat ze kunnen worden gesorteerd en gerapporteerd.

Uw database opbouwen

Het oplossen van deze problemen zal wat werk vergen, mogelijk enkele dagen. Aangezien gebruikers waarschijnlijk het oude systeem zullen moeten blijven gebruiken terwijl we een nieuw systeem aan het bouwen zijn, is het het beste om een ​​kopie te maken van de bestaande werkmappen van waaruit ze kunnen werken. Dit betekent dat we elke stap bij het converteren van de gegevens willen documenteren, zodat we het snel opnieuw kunnen doen als het tijd is om over te schakelen naar het nieuwe systeem.

Het eerste dat u hoeft te doen, is de gegevens in uw Excel-werkmap opschonen. Het gebruik van Zoeken en vervangen kan helpen, en u moet elke kolom of rij verwijderen die geen gegevens bevat (behalve de rij met de kolomkop, die moet worden behouden). Voeg een ID-kolom toe aan elk blad, in kolom A, en vul het met oplopende getallen door 1 in de eerste cel te typen, onderaan de gegevens te selecteren (Shift + End, Omlaag) en vervolgens de opdracht Omlaag vullen te gebruiken (Ctrl + D ). Maak een hoofdlijst met projectnamen, en waar een projectnaam ook is opgenomen, gebruik de functie VLookup () om het master-ID-nummer te bevestigen; als er geen nummer is, is er een inconsistentie in uw gegevens.

Zodra uw gegevens schoon zijn, wordt het tijd om een ​​nieuwe database te ontwerpen om deze in te bewaren. We zullen Access 2013 gebruiken, omdat het in ons theoretische voorbeeld beschikbaar is voor al onze gebruikers via ons Office 365-abonnement. Wanneer u een nieuwe Access-database maakt, heeft u de keuze om deze te maken als een Access Web App of als een Access Desktop Database. Webapps hebben een vereenvoudigde interface en kunnen alleen worden gebruikt als u Office 365 met SharePoint Online of SharePoint Server 2013 met Access Services en SQL Server 2012 heeft. We gebruiken de traditionele desktopdatabase, omdat deze meer opties en meer controle biedt over de gebruikerservaring.

Selecteer om een ​​nieuwe bureaubladdatabase te maken en geef deze een naam: Access maakt een nieuwe tabel met de naam Tabel 1 en plaatst u in de ontwerpweergave met één kolom, genaamd ID. Hier kunt u de tabellen ontwerpen die u nodig heeft in uw database. Elke tabel moet een ID-veld hebben (een automatisch oplopend geheel getal), maar om verwarring te voorkomen is het het beste om het een meer beschrijvende naam te geven. In de tabel Projecten zou dit ProjectID zijn, CustomerID in de tabel Klanten, enzovoort.

U kunt het gegevenstype voor elke gemaakte kolom instellen, en u moet elke kolom een ​​naam geven en eventuele andere eigenschappen en opmaak instellen die geschikt zijn voor het veld. Net als bij het ID-veld, moet u ervoor zorgen dat de kolomnamen duidelijk maken welke gegevens in het veld moeten worden geplaatst - gebruik dus bijvoorbeeld Projectnaam in plaats van alleen Naam, Vervaldatum in plaats van Vervaldatum. U kunt de knop Naam en bijschrift op het lint gebruiken om een ​​afgekort bijschrift te maken, evenals de expliciete naam. U kunt spaties in kolomnamen gebruiken, maar u moet ze tussen vierkante haken plaatsen bij het schrijven van vragen en rapporten.

Hoewel het logisch is dat gebruikers hun gegevens op hun eigen bladen invoeren, is het door het gebrek aan samenhang moeilijk te analyseren

Stel de opmaak van kolommen zoals PercentageComplete in op Percentage en datums op ShortDate, en ook de maximale lengte van tekstvelden op een redelijke waarde, anders zijn ze allemaal 255 tekens lang. Onthoud dat sommige woorden (zoals Datum) gereserveerd zijn, dus u kunt ze niet als kolomnamen gebruiken: gebruik in plaats daarvan TaskDate of iets anders dat meer beschrijvend is.

Als het gaat om kolommen waarin u een waarde in een andere tabel wilt opzoeken (zoals de kolom Klant in de tabel Projecten), definieert u die andere tabellen in Access voordat u de opzoekkolom toevoegt. Als het op Status aankomt, is de eenvoudigste optie om gewoon de waarden in te voeren die in de vervolgkeuzelijst moeten worden weergegeven, maar dit maakt het moeilijk om de lijst met mogelijke waarden later toe te voegen of te bewerken. Tenzij u te maken heeft met een korte lijst waarvan het onwaarschijnlijk is dat mogelijke waarden zullen veranderen, zoals een veld waarin iemands geslacht wordt geregistreerd, is het een beter idee om een ​​andere tabel te maken voor items zoals ProjectStatus. Hierdoor kunt u in de toekomst eenvoudig extra opties aan de lijst toevoegen zonder een programmeerwijziging.

Verbeteringen

Terwijl we onze database ontwerpen, kunnen we verbeteringen doorvoeren ten opzichte van de oude manier van werken op basis van spreadsheets. Een klacht die onze gebruikers hadden met hun Excel-werkmappen was dat elke taak slechts één cel bevatte voor opmerkingen, en soms moesten ze meer dan één opmerking maken over een taak - of de supervisor moest een opmerking maken over een taak en vervolgens de gebruiker antwoord hierop. Alles in één cel proppen, maakte het moeilijk om te zien wanneer en door wie er opmerkingen werden gemaakt. We kunnen het beter doen door een aparte tabel voor opmerkingen te maken, gekoppeld aan de Taken-tabel. Op deze manier kan elke taak zoveel opmerkingen bevatten als nodig is, met aparte velden voor de datum, gebruikersnaam en tekst van elke taak.

Een andere verbetering die we kunnen aanbrengen, is om items zoals ProjectStatus zo in te stellen dat ze in een bepaalde volgorde worden weergegeven in plaats van alfabetisch. U wilt bijvoorbeeld dat Voltooid onder aan de lijst staat. Om dit te doen, voegt u een DisplayOrder-kolom toe en gebruikt u deze om de opzoeklijst te sorteren. Laat u niet verleiden om het ID-veld te gebruiken; hiermee konden nieuwe records alleen aan het einde van de lijst komen te staan.

Om ervoor te zorgen dat onze gegevens schoon blijven, kunnen we velden markeren die de gebruiker moet invullen als vereist, en validatie toevoegen om ervoor te zorgen dat de ingevoerde gegevens in de juiste vorm zijn. U kunt het leven gemakkelijker maken door verstandige standaardwaarden in te stellen: voor het veld CommentDate in de tabel Opmerkingen kan de standaardwaarde zijn ingesteld op = Datum (), waardoor deze automatisch wordt ingesteld op de datum van vandaag wanneer er een nieuwe opmerking wordt gemaakt. U kunt validatie gebruiken samen met een Ingetrokken kolom in een tabel (een Booleaanse waarde) om te voorkomen dat gebruikers nieuwe records met specifieke waarden toevoegen. Hierdoor kunt u historische waarden behouden die vroeger geldig waren, maar die niet meer worden gebruikt. Deze functies zijn allemaal te vinden in de Hulpmiddelen voor tabellen | Tabblad Velden op het lint of in de Veldeigenschappen in de tabelontwerpweergave.

Uw gegevens importeren

Zodra uw tabellen zijn ingesteld, kunt u de externe gegevens | gebruiken Importeren en koppelen | Excel-knop op het lint om de gegevens uit uw Excel-werkmap toe te voegen aan de tabellen in uw Access-database. Maak een back-up van uw lege Access-database voordat u begint, voor het geval er iets misgaat, en begin met het handmatig vullen van de kleine tabellen indien nodig. Maak nog een back-up zodra dit is gebeurd, zodat u op dit punt kunt terugkomen als er iets misgaat in de volgende stappen.

Importeer nu de hoofdtabellen die niet afhankelijk zijn van andere tabellen, zoals klanten, voordat u eindigt met de tabellen die wel relaties hebben, zoals projecten en taken. Als u de kolommen in uw Excel-werkmap herschikt en hernoemt zodat ze zo goed mogelijk overeenkomen met de velden in uw Access-database, zou u geen problemen moeten ondervinden bij het importeren van de gegevens. Vergeet niet alles wat u doet te noteren, zodat u het later kunt herhalen als u de gegevens opnieuw moet converteren.

Nadat de gegevens zijn geïmporteerd, zouden de tabellen in de gegevensbladweergave ongeveer hetzelfde moeten werken als de Excel-werkbladen, maar met een veel betere gegevensvalidatie, zoeken en sorteren. Als u wilt, kunt u nu beginnen met het ontwerpen van nieuwe formulieren en rapporten op basis van deze gegevens: een hoofd- / detailformulier voor projecten kan bijvoorbeeld de gegevens van één project bovenaan het formulier weergeven en een raster met de taken daarvoor. project onderaan.

U kunt ook een formulier Mijn taken instellen met een lijst van alle openstaande taken voor de huidige gebruiker en een rapport met achterstallige taken met een overzicht van alle openstaande taken voor alle gebruikers die hun vervaldatum hebben overschreden.

Geen provincies, alsjeblieft, we zijn Brits

Als u adressen in uw database opslaat, is het belangrijk om te weten welke informatie u werkelijk nodig heeft. Hoewel provinciale informatie nuttig kan zijn voor marketingdoeleinden - en mogelijk nodig is voor sommige buitenlandse adressen - wordt deze niet langer officieel gebruikt in Britse adressen.

windows 10 home-knop gaat niet open

De reden is dat Britse postadressen vertrouwen op het concept van een poststad, waar de post voor u wordt verzonden en gesorteerd voordat deze bij u aan de deur wordt afgeleverd. Niet alle steden of dorpen worden bediend door poststeden in dezelfde provincie - Melbourn (in Cambridgeshire) ontvangt zijn post bijvoorbeeld via Royston (in Hertfordshire) - dus het specificeren van een provincie in het adres helpt niemand per se.

Om verwarring te voorkomen, stopte het postkantoor in 1996 met het gebruik van provincies in adressen, maar vertrouwde het in plaats daarvan op postcode-informatie - en tegen 2016 is het van plan provincienamen te verwijderen uit het aliasgegevensbestand met aanvullende adresinformatie. Dus als u een provincie opneemt in een adres in het VK, wordt deze eenvoudigweg genegeerd.