• Author / Uploaded
• Erfan Ahmed

Citation preview

Vet Database: Lab Manual Preface: Data at a Veterinary Clinic A 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.

Organization Owner/Customer Information The 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 below Name of table Field (Primary Key) Field Field Field Field

Customers Customer ID Customer’s Name Contact no. Address Occupation

Pet Information The 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 table Field (Primary Key) Field Field Field Field

Pets Pets ID Pet’s Name Type Of Animal Breed Gender

Visits/Transaction Keeping 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 table Visits Field (Primary Key) Visits ID Field Date & Time Field Service Rendered  The Customer table is used from the prebuilt table of MS Access  The Pets Table is created by importing an Excel file containing the information of Pets  The Visits Table is created using MS Access

Vet Database: Lab Manual

Tasks to Perform Task 1: Creating a Blank Database   

Select Blank Database Type Project Name (example: MyPetProject) Click on Create

Figure: Creating Blank Database

Task 2: Using Customer Table from the Prebuilt Tables in MS Access The 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)

Vet Database: Lab Manual

Figure: Selecting Prebuilt Table

Rename the Table  

To 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 option

Figure: Chaning the name and opening the table Vet Database: Lab Manual

Renaming the field  

To Change the Field Name Right-click on that name and select “Rename Field” Type the New Field Name as “CustomerID” and press Enter

Figure: Using Rename Field Option



Figure: Renaming the Field as CustomerID

Right click on the table to Save and then close the table

Figure: Saving and closing the Customer Table

Vet Database: Lab Manual

Task 3: Importing the Pets Table from Excel to MS Access 

At first an Excel file is created (named Pets) containing the fields and data.

Fig: Excel file data 

On MS Access, go to “External Data” then Select “Excel” from the Import and Link tool box

Fig: External Data Import tool



A new dialogue box will pop-up. Browse the location of the Excel File and click OK.

Vet Database: Lab Manual

Fig: Location identification of the Excel file 

Another Window will pop-up and check your data if it matches with the Excel file data and click on Next>

Vet Database: Lab Manual

Fig: Configuring Pets Table 

Check the button to make the first Row as Column Headings and click Next >

Figure : Creating Column Headings 

The next window gives an option to change or rename the field and column data. If everything is correct click on Next>

Vet Database: Lab Manual

Figure: Configuring Pets table 



The Next window enables to choose primary key. Click on “let Access select the primary key” and click Next>

Fig: Primary Key Selection in Pets Table Change the table name to “Pets” and click on “Finish” and on the next window click “close”.

Vet Database: Lab Manual

Fig: End of Pets Table configuration 

Now, to change the field name from the table using “Design View” , Right click on the table “Pets” and Select Design View.

Vet Database: Lab Manual

Fig: Changing Field name of the Pets Table 

Now change the field name from ID to PetID

Fig: Changing field name Vet Database: Lab Manual



The 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 name Write “CustomerID” on the new Row inserted and select “Number” as the Data Type.

Fig: Selecting Data Type Then Close and Save the table as shown earlier.

Task 3: creating table on MS Access using Table Design Go to “Create” from the menu bar and Select “Table Design”. A blank table will be created.

Vet Database: Lab Manual

Fig: Creating the 3rd table

Fill up the Field and Data Type as shown below.

Fig : Table information



To 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 VisitID Vet Database: Lab Manual

Fig: Selecting Primary Key

Then 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 Name

Now 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).

Vet Database: Lab Manual

Fig: Primary Key and Foreign Key

Task 4: Building Relationship between Primary Key and Foreign Key To 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

Vet Database: Lab Manual

Fig: Relationships

Now to select the 3 tables (Customers, Pets & Visits) highlight them all by using the (click and hold) Shift Keyand click on the 2 nd and 3rd tables (Pets & Visits) and click Add.The Relationships Table now will show 3 tables.

Vet Database: Lab Manual

Fig: Information of 3 tables created in the Relationships tool

 

Now 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 configuration

Vet Database: Lab Manual

Fig: First Foreign Key configuration shown using relationships 

Now repeat the same procedure to connect the foreign key PetID with the Primary key PetID.

Fig: Final view after configuring Relationships Access 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 Properties To prevent Data entry error we can use the “back door” into code. 

Right click on the Visits and choose Design View.

Vet Database: Lab Manual

Fig: Design view Now you can add default value,caption and decimal places in the table which will appear in the form.

Fig: Configuring Table 

Save and close the table and open Pets Table by right clicking and selecting Design view as well. Vet Database: Lab Manual



Now select the TypeOfAnimal field and select “Lookup Wizard” to add type of animals.

Fig: Lookup Wizard 



Select the Option “I will type in the values I want” to add the animal types manually and click on next.

Fig: Lookup Wizard configuration Now 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.

Vet Database: Lab Manual

Fig: Lookup Wizard Configuration



If you don’t have anything to edit like the table name then click on finish.

Fig: Lookup Wizard Configuration  

Save and close the Pets table. Deleting Field Right click on the field you want to delete and click on “delete fied”

Vet Database: Lab Manual

Fig: Deleting Field

Task 6: Creating AForm 

To create a form, select the Customer Table then go to Create Tab and Select Form and it will create a form.

Figure: Form tool 

If you don’t have anything to change in the form then right click on the form name and save it.

Vet Database: Lab Manual

Fig: Creating Form

Adding Information of Customers and Pets in Database 



At 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.

Vet Database: Lab Manual

Fig: Filling up Form 

Now Add some more Customers from the following table to get a clear idea about a Database

Customers Table 1. First Name: Mark Last Name: Robinson Contact# +123456 City: London Country : UK 2. First Name: Khodeza

Pets Table Name: Snoopy TypeOfAnimal: Dog Breed: Mixed DateOfBirth: 06/04/2011 Gender: M

Visits Table Visit Date: 02/01/2014 Total Amount: 30 Invoiced: yes

Name: Billu TypeOfAnimal: Cat

Visit Date: 03/01/2014 Total Amount: 30 Vet Database: Lab Manual

Last Name: Begum Contact# +143011 City: London Country : UK

Breed: Persian DateOfBirth: 10/12/2013 Gender: F

Invoiced: yes

3. First Name: Kim Last Name: Brown Contact# +000111 City: London Country : UK

Name: Tommy TypeOfAnimal: Dog Breed: Mixed DateOfBirth: 04/09/2011 Gender: M

Visit Date: 03/01/2014 Total Amount: 30 Invoiced: yes

4. First Name: Bruce Last Name: Mork Contact# +000111 City: London Country : UK

Name: Sammy TypeOfAnimal: Dog Breed: Mixed DateOfBirth: 24/09/2010 Gender: M

Visit Date: 03/01/2014 Total Amount: 30 Invoiced: yes

5. First Name: Laura Last Name: Brown Contact# +000222 City: London Country : UK

Name: Dinky TypeOfAnimal: Cat Breed: Mixed DateOfBirth: 05/10/2008 Gender: F

Visit Date: 04/01/2014 Total Amount: 30 Invoiced: yes

6. First Name: Farhana Last Name: Haque Contact# +000333 City: London Country : UK

Name: Scooby TypeOfAnimal: Dog Breed: Mixed DateOfBirth: 04/09/2010 Gender: M

Visit Date: 04/01/2014 Total Amount: 30 Invoiced: yes

7. First Name: Kim Last Name: Brown Contact# +000111 City: London Country : UK

Name: Chappyac TypeOfAnimal: Dog Breed: Mixed DateOfBirth: 04/09/2010 Gender: M

Visit Date: 03/01/2014 Total Amount: 30 Invoiced: yes

8. First Name: Salam Last Name: Alam Contact#+00055 5 City: London Country: Uk

Name: Cruzer TypeofAnimal: Dog Breed: Mixed DateOfBirth: 23/12/2009 Gender: M

Visit Date: 04/01/2014 Total Amount: 30 Invoiced: yes

9. First Name: Jack Last Name: Cruze Contact#+00066 6

Name: Sam TypeofAnimal: Dog Breed: Mixed DateOfBirth: 12/12/2012

Visit Date: 04/01/2014 Total Amount: 30 Invoiced: yes Vet Database: Lab Manual

City: London Country: Uk 10.First Name: Mary Last Name: Kom Contact#+00077 7 City: London Country: Uk

Gender: M Name: Tony TypeofAnimal: Dog Breed: Mixed DateOfBirth: 11/11/2011 Gender: M

Visit Date: 04/01/2014 Total Amount: 30 Invoiced: yes

Task 7: Query Design It 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 Wizard

Fig: Query Wizard tool



A new window will pop up. Select Simple Query Wizard  Click Ok

Vet Database: Lab Manual

Fig: Type of Query Wizard Selection 

Select 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 Table 

To select field name, select the field name and click on the arrow sign, press next and proceed

Vet Database: Lab Manual

Fig: Selection of Fields for the Query



Give 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 Configuration 

If 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 again

Vet Database: Lab Manual

Fig: Query configuration

Vet Database: Lab Manual

Query 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.

Vet Database: Lab Manual

Fig: Adding Criteria You will be now able to see the list of the dogs in the created database

Fig: Query

Vet Database: Lab Manual