Preface: Data at a Veterinary Clinic - Abu S. Arif



Vet Database: Lab ManualPreface: Data at a Veterinary ClinicA veterinary clinic is a hospital, doctor's office, or medical building for the treatment of non-human animals. The patients are mostly the pet animals who are very dear to their Owners. That is why it is very important to keep a track of the pet’s information as well as the owner’s information for further assistance. OrganizationOwner/Customer InformationThe owner of a pet is the customer of the Vet. Clinic. The owner bears the responsibility of a pet, therefore, owner details is of great importance like his/her name, occupation, phone number, and related information. We might consider to have the owners’ information in a separate table to reduce redundancies. A table is a set of columns and rows. Each column is called a field. The fields included in the above mentioned tables are given belowName of tableCustomersField (Primary Key)Customer IDFieldCustomer’s NameFieldContact no. FieldAddressFieldOccupationPet InformationThe pets are the patient. Information about the pets/patients can be kept in another table to reduce redundancies. The basic information about an animal includes type, breed, birthdate, and gender. We will consider creating a separate table with the pet’s information.Name of tablePetsField (Primary Key)Pets IDFieldPet’s NameFieldType Of AnimalFieldBreedFieldGenderVisits/TransactionKeeping a history about previous visits of the pet can provide the clinic with information to improve the service level. We will consider adding a third table that contains with information like the date and time of the visit(s), services rendered, amount paid, and notes. Name of tableVisitsField (Primary Key)Visits IDFieldDate & TimeFieldService RenderedThe Customer table is used from the prebuilt table of MS AccessThe Pets Table is created by importing an Excel file containing the information of PetsThe Visits Table is created using MS AccessTasks to PerformTask 1: Creating a Blank DatabaseSelect Blank DatabaseType Project Name (example: MyPetProject)Click on CreateFigure: Creating Blank DatabaseTask 2: Using Customer Table from the Prebuilt Tables in MS AccessThe table is created using Application Parts. A table named “Contacts” is used which is close to what is needed for the Customer Table.( Some minor changes are added for the Table)Figure: Selecting Prebuilt TableRename the TableTo rename the table from “Contacts” to “Customers”, Right- click on the table name (Contacts) and Select “Rename”. Type new table name that is “Customers” and press Enter twice.Figure: Using Rename optionFigure: Chaning the name and opening the tableRenaming the fieldTo Change the Field Name Right-click on that name and select “Rename Field”Type the New Field Name as “CustomerID” and press EnterFigure: Using Rename Field OptionFigure: Renaming the Field as CustomerID-90487518414900-90487512572900Right click on the table to Save and then close the tableFigure: Saving and closing the Customer TableTask 3: Importing the Pets Table from Excel to MS AccessAt first an Excel file is created (named Pets) containing the fields and data.Fig: Excel file data-91440014033400On MS Access, go to “External Data” then Select “Excel” from the Import and Link tool boxFig: External Data Import tool-91440014223900A new dialogue box will pop-up. Browse the location of the Excel File and click OK.Fig: Location identification of the Excel fileAnother Window will pop-up and check your data if it matches with the Excel file data and click on Next>Fig: Configuring Pets TableCheck the button to make the first Row as Column Headings and click Next >Figure : Creating Column HeadingsThe next window gives an option to change or rename the field and column data. If everything is correct click on Next>Figure: Configuring Pets tableThe Next window enables to choose primary key. Click on “let Access select the primary key” and click Next>Fig: Primary Key Selection in Pets TableChange the table name to “Pets” and click on “Finish” and on the next window click “close”. Fig: End of Pets Table configurationNow, to change the field name from the table using “Design View” , Right click on the table “Pets” and Select Design View.Fig: Changing Field name of the Pets TableNow change the field name from ID to PetIDFig: Changing field name-914400634900The PetID is already Selected as the Primary Key. But it is important to add another field named “CustomerID” as it works as the foreign key of this table. To do so, Right click on the field named “PetName” and select Insert Rows.Fig: Adding additional field nameWrite “CustomerID” on the new Row inserted and select “Number” as the Data Type.Fig: Selecting Data TypeThen Close and Save the table as shown earlier.Task 3: creating table on MS Access using Table DesignGo to “Create” from the menu bar and Select “Table Design”. A blank table will be created.Fig: Creating the 3rd tableFill up the Field and Data Type as shown below.Fig : Table informationTo make VisitID as the Primary key, select the VisitID and click on the Primary Key button.A key sign will be shown on the left side of VisitIDFig: Selecting Primary KeyThen to Save the table right click on the Table1 and click save. Change the name of the table to Visits. Close the table after changing the name.Fig: Change in Table NameNow we Have 3 Tables and 3 Primary Keys (CustomerID,PetID and VisitsID) and 2 Foreign Keys. You can identify the foreign keys by the field names. (They have an ID and named after a Table).Fig: Primary Key and Foreign KeyTask 4: Building Relationship between Primary Key and Foreign KeyTo link the Foreign Keys with the primary Keys, Relationship tool is used.Go to Database Tools and select Relationships and a table will pop up.Fig: Configuring Relationships Fig: RelationshipsNow to select the 3 tables (Customers, Pets & Visits) highlight them all by using the (click and hold) Shift Keyand click on the 2nd and 3rd tables (Pets & Visits) and click Add.The Relationships Table now will show 3 tables. Fig: Information of 3 tables created in the Relationships toolNow click the primary key CustomerID (from the 1st table) and drag it to the foreign key named CustomerID on the Pets table. Then the Edit Relationship Window opens.Check (tick mark) the Enforce Referential Integrity box and click on Create.Fig: Relationship of Foreign Key configurationFig: First Foreign Key configuration shown using relationshipsNow repeat the same procedure to connect the foreign key PetID with the Primary key PetID.Fig: Final view after configuring RelationshipsAccess reads relationships from one to many. To add a new pet, first add a customer. To add a visit, first you need to add a customer then add a pet and then you can fill the visit table.Task 5: Field PropertiesTo prevent Data entry error we can use the “back door” into code.Right click on the Visits and choose Design View. Fig: Design viewNow you can add default value,caption and decimal places in the table which will appear in the form.Fig: Configuring TableSave and close the table and open Pets Table by right clicking and selecting Design view as well.Now select the TypeOfAnimal field and select “Lookup Wizard” to add type of animals.Fig: Lookup WizardSelect the Option “I will type in the values I want” to add the animal types manually and click on next.Fig: Lookup Wizard configurationNow fill in the column by typing different names. Don’t use the Enter button to go to the next row, press tab to write name on the next row. Click on next when you are done writing on the column.Fig: Lookup Wizard ConfigurationIf you don’t have anything to edit like the table name then click on finish.Fig: Lookup Wizard ConfigurationSave and close the Pets table.Deleting FieldRight click on the field you want to delete and click on “delete fied”Fig: Deleting FieldTask 6: Creating AFormTo create a form, select the Customer Table then go to Create Tab and Select Form and it will create a form.Figure: Form toolIf you don’t have anything to change in the form then right click on the form name and save it.Fig: Creating FormAdding Information of Customers and Pets in DatabaseAt first the form named Customer is filled up with a new customer name and other information. If you scroll down the Pet table is found. Then the Pet table is filled up and on the top left corner of the pet table there is a plus sign (“+”) which is linked to the Visits table.Fig: Filling up FormNow Add some more Customers from the following table to get a clear idea about a DatabaseCustomers TablePets Table Visits TableFirst Name: MarkLast Name: RobinsonContact# +123456City: LondonCountry : UKName: SnoopyTypeOfAnimal: DogBreed: MixedDateOfBirth: 06/04/2011Gender: MVisit Date: 02/01/2014Total Amount: 30Invoiced: yesFirst Name: KhodezaLast Name: BegumContact# +143011City: LondonCountry : UKName: BilluTypeOfAnimal: CatBreed: PersianDateOfBirth: 10/12/2013Gender: FVisit Date: 03/01/2014Total Amount: 30Invoiced: yesFirst Name: KimLast Name: BrownContact# +000111City: LondonCountry : UKName: TommyTypeOfAnimal: DogBreed: MixedDateOfBirth: 04/09/2011Gender: MVisit Date: 03/01/2014Total Amount: 30Invoiced: yesFirst Name: BruceLast Name: MorkContact# +000111City: LondonCountry : UKName: SammyTypeOfAnimal: DogBreed: MixedDateOfBirth: 24/09/2010Gender: MVisit Date: 03/01/2014Total Amount: 30Invoiced: yesFirst Name: LauraLast Name: BrownContact# +000222City: LondonCountry : UKName: DinkyTypeOfAnimal: CatBreed: MixedDateOfBirth: 05/10/2008Gender: FVisit Date: 04/01/2014Total Amount: 30Invoiced: yesFirst Name: FarhanaLast Name: HaqueContact# +000333City: LondonCountry : UKName: ScoobyTypeOfAnimal: DogBreed: MixedDateOfBirth: 04/09/2010Gender: MVisit Date: 04/01/2014Total Amount: 30Invoiced: yesFirst Name: KimLast Name: BrownContact# +000111City: LondonCountry : UKName: ChappyacTypeOfAnimal: DogBreed: MixedDateOfBirth: 04/09/2010Gender: MVisit Date: 03/01/2014Total Amount: 30Invoiced: yesFirst Name: SalamLast Name: AlamContact#+000555City: LondonCountry: UkName: CruzerTypeofAnimal: DogBreed: MixedDateOfBirth: 23/12/2009Gender: MVisit Date: 04/01/2014Total Amount: 30Invoiced: yesFirst Name: JackLast Name: CruzeContact#+000666City: LondonCountry: UkName: SamTypeofAnimal: DogBreed: MixedDateOfBirth: 12/12/2012Gender: MVisit Date: 04/01/2014Total Amount: 30Invoiced: yesFirst Name: MaryLast Name: KomContact#+000777City: LondonCountry: UkName: TonyTypeofAnimal: DogBreed: MixedDateOfBirth: 11/11/2011Gender: MVisit Date: 04/01/2014Total Amount: 30Invoiced: yesTask 7: Query DesignIt is always difficult to fetch information from a complex and large database. Query Wizard tool helps to fetch information in a much easier way. Click on Query WizardFig: Query Wizard toolA new window will pop up. Select Simple Query Wizard Click OkFig: Type of Query Wizard SelectionSelect the Table Name and the Field Names you want in your Query. For Example, if you want to find number of dogs, you must add the field named “TypeOfTable”.Here three fields are selected, CustomerID, PetName and TypeOfAnimal. Fig: Query for Pets TableTo select field name, select the field name and click on the arrow sign, press next and proceedFig: Selection of Fields for the QueryGive a suitable query name and select the option “Modify the query design” . Click on Finish and the design view of the Query will be shown.Fig: Query Wizard ConfigurationIf you click on the View tab it will show you the result of your query. You can go back to your design view by clicking on it againFig: Query configurationQuery basically means the question you want to ask to find a particular set of information.Here the query is designed to find the list of dogs as patients in this clinic.Fig: Adding CriteriaYou will be now able to see the list of the dogs in the created databaseFig: Query ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download