Microsoft Access – Animal Hospital Database Create the following queries using the animal hospital database (see attached document) Please make sure my que

Create the following queries using the animal hospital database (see attached document) Please make sure my query’s are correct – if not please re-do them.

Query1: Get the AnimalID, Breed, AnimalName of all the dogs who are 3 years old.  

Query2: Get the number of animal visits by each animal (AnimalID).

Hint: You can refer to database lab tutorial query 10 for the use of Totals.

Query3: Get the first name and last name of all the clients whose first name starts with letter B or whose last name contains letter e.

Hint: You can refer to database lab tutorial query 3 for the use of wildcard character.

Query4: Get the AnimalID and Names of the animals who had at least two visits with Doctor Hopkins.

Query5: For each ClientID, get the total balance that the client still needs to pay for all the visits together. The balance for each visit is calculated as the difference between Fee and Paid.

Hint: For calculating the balance, you may refer to database lab tutorial query 12 for the use of [ ], etc.

AnimalIDClientIDSpeciesBreedAgeAnimalName
11HorseQuarterhorse4Blaze
22DogChihuahua3Spot
32CatMix6Nirmal
43CatMix8Big
54DogMix5Rover
65DogCollie6Sarah
76GoatNubian4
86CowHerford7
96CowHerford8
107HorseMorgan11Isty
118DogDoberman6Jake
129DogTerrier5Curley
1310CatManx4Little
1411Llama3Matisse
1512Hog2Petunia
1613HorseSaddlebred9Chug
1713HorseSaddlebred8Lightning
1814CatMix4Garfield
1915CatMix7Mittens
2016DogMix6Bozo
2117DogMix5Butler
2218DogCollie10Shadow
2319DogChihuahua5Stimpy
2419CatPersian6Queenie
2520DogTerrier4Zippo
2621CatMix8Jimbo
2722CatPersian6Sheba
2823DogTerrier4Groucho
2924Sheep6
3025DogMix2Hero
3125CatMix1Bartholemew
3225CatMix1Skippy
3325CatPersian7Jezebel
3426OstrichBlue2
3527DogMix4Fluffy
3628CatMix5Tiger
3729CatMix1Sunshine
3830DogDoberman6Friendly
3931DogCollie7Lady
4032CatMix8Rainbow
4133HorseSaddlebred4Stonewall
4233HorseMorgan1Double Trouble
4333HorseQuarterhorse8Lucky
4434CatMix3Puffy
4534CatMix5Fluffy
4635DogMix6Tramp
4736HorseMix7Sport
4837Llama4Inca
4938Hog2
5039DogPoodle6Alphonse
5140DogBlue Heeler4Beowolf
5241CatMix6Bert
5342CatMix1Ernie
5443DogSpaniel9Rex
5544HorsePony4Daisy
5644HorseQuarterhorse9Jackson
576CowAngus1
5826CowHolstein3
5937EmuStandard1
6026CowAngus1
6140DogLabrador1Harley
ClientIDClientFirstNameClientLastNameClientAddressClientCityClientPhoneAccount
1JenniferLogan324 Eastside AveCawston456-5443false
2KenLondon900 Wiltshire BlvdCawston456-3154false
3BrittLizette14-3214 27 AvenueCawston654-8852true
4DianaStapleton5232 Ida RoadSeymour441-6532true
5LloydRay124 E. 39 StreetCawston332-9090false
6DuaneTerrace44-9090 Salmon StCawston123-9960false
7BruceMoore670 Monteith RdCawston334-0987true
8DennisWilliams5512 Lakeside DriveBrookwood454-6622false
9WayneMoravecRR1 Yankee FlatsAbbot434-0980false
10PattieDene4403 Ida RoadSeymour441-6655false
11GregoryPaulette775 39 StreetCawston332-1505false
12OwenBelgo8770 Delhart AveCawston235-6541true
13KevinPavan441 Potter DriveCawston551-6622false
14WendySchneider133 Buckland StBrookwood441-6023false
15CarlHerbert902 Uplands DriveCawston551-6327false
16StephanieMichael443 Shelley AveCawston665-9977false
17JakobSchmidt775 Ida RoadSeymour442-5102false
18PamStebner345 Wiltshire BlvdCawston455-6632false
19IngridCorey22-4587 42 AveCawston125-6545false
20LeanneStein220 Lakeside DriveBrookwood554-6500false
21CarmenVallance451-6625 38 StCawston451-3352true
22LorneValliere545 Potter DriveCawston554-3652false
23GeraldVanderburg21 Eastside AveCawston456-3211false
24HankGallagherRR3 Yankee FlatsAbbot225-6333true
25ElizabethNorman554 Cascade PlCawston445-6632false
26RonWidmann756 Clara DriveCawston252-6411false
27LonnieWilkins141 Uplands DriveCawston441-6655false
28RolandSherwood712 Shannon WayCawston225-5544false
29BudCharming8805 Lakeside DriveBrookwood564-3332false
30ArnieCramer2061 BuchananCawston455-6001true
31NickDonnelly123 Shelley AveCawston665-3324false
32TimothyCollins4456 Delhart AveCawston255-3685false
33AllanYoung767 Renfrew StCawston124-9987false
34JoanClements213 Potter DriveCawston665-4422false
35GrahamSanderson4305 Beach AveCawston445-3328false
36BarryDesjardins206 14 AvenueCawston332-6410false
37LindaBurke477 Canyon DriveCawston124-6654false
38DarrenJames62 Eastside AveCawston456-1102false
39SpenceButler855 DownieCawston335-6410true
40LyleAdair332 Ackerman DriveCawston441-6320true
41DorothyClifford365-6630 38 StCawston451-6701false
42JoseZurba66 Cranbrooke RdCawston225-3001false
43LornaPeterson201 14 AvenueCawston332-5413false
44BrendanWoods233 Yuma StCawston441-2235false
DoctorIDDoctorNameHomePhone
1Dvorak554-6684
2Jesse665-8744
3Hopkins441-6060
4Lamaze250-6300
5Wilson441-6544
6Smith992-2344
7Chen323-3456
DateAnimalIDDoctorIDDurationTreatmentFeePaidTravel
5/20/991145Prescription¤ 35.00¤ 20.00true
5/21/992130Prescription¤ 40.00¤ 20.00false
5/23/992460Surgery¤ 150.00¤ 150.00false
5/21/993115Check-up¤ 25.00¤ 25.00false
5/20/994545Miscellaneous¤ 30.00¤ 30.00false
5/19/995345First Aid¤ 40.00¤ 0.00false
5/20/995145Prescription¤ 35.00¤ 20.00true
5/22/995145Prescription¤ 35.00¤ 20.00true
5/23/995130First Aid¤ 45.00¤ 45.00false
5/22/996130Prescription¤ 40.00¤ 40.00false
5/19/997245First Aid¤ 40.00¤ 0.00false
5/19/998215Consultation¤ 20.00¤ 10.00true
5/20/998545Miscellaneous¤ 30.00¤ 30.00false
5/22/998545Miscellaneous¤ 30.00¤ 30.00false
5/21/999290Surgery¤ 300.00¤ 0.00true
5/19/9910560Surgery¤ 175.00¤ 175.00true
5/21/9910130Vaccination¤ 55.00¤ 55.00true
5/22/9910115Check-up¤ 25.00¤ 25.00true
5/23/9910330Prescription¤ 40.00¤ 0.00true
5/18/9911345First Aid¤ 50.00¤ 50.00false
5/20/9911390Surgery¤ 225.00¤ 100.00false
5/21/9911515Check-up¤ 25.00¤ 25.00false
5/22/9912515Consultation¤ 20.00¤ 20.00false
5/22/9913430Prescription¤ 40.00¤ 40.00false
5/18/9914430Vaccination¤ 45.00¤ 10.00true
5/20/9914560Check-up¤ 55.00¤ 55.00true
5/18/9915545First Aid¤ 50.00¤ 50.00false
5/19/9915160Surgery¤ 160.00¤ 100.00false
5/20/9915390Surgery¤ 225.00¤ 100.00false
5/22/9915390Surgery¤ 225.00¤ 100.00false
5/23/9915545First Aid¤ 50.00¤ 50.00false
5/18/9916160Surgery¤ 150.00¤ 0.00true
5/19/9916245Prescription¤ 40.00¤ 40.00true
5/20/9916345First Aid¤ 60.00¤ 35.00true
5/18/9917160Check-up¤ 55.00¤ 55.00true
5/23/9917560First Aid¤ 60.00¤ 0.00true
5/18/9918330Vaccination¤ 45.00¤ 10.00true
5/20/9918560Check-up¤ 55.00¤ 55.00true
5/22/9918315Check-up¤ 25.00¤ 25.00false
5/23/9918430Vaccination¤ 45.00¤ 10.00true
5/18/9919515Check-up¤ 25.00¤ 0.00false
5/20/9919460Surgery¤ 175.00¤ 75.00false
5/18/9920160Surgery¤ 150.00¤ 0.00true
5/20/9920345First Aid¤ 60.00¤ 35.00true
5/22/9920315Check-up¤ 25.00¤ 25.00false
5/23/9920160Surgery¤ 150.00¤ 0.00true
5/18/9921160Check-up¤ 55.00¤ 55.00true
5/20/9921430Prescription¤ 40.00¤ 40.00false
5/23/9921160Check-up¤ 55.00¤ 55.00true
5/19/9922430Miscellaneous¤ 30.00¤ 15.00false
5/20/9922330First Aid¤ 50.00¤ 25.00false
5/21/9922330Vaccination¤ 50.00¤ 0.00false
5/23/9922245First Aid¤ 40.00¤ 40.00false
5/18/9923115Check-up¤ 25.00¤ 0.00false
5/20/9923460Surgery¤ 175.00¤ 75.00false
5/22/9923460Surgery¤ 175.00¤ 75.00false
5/23/9923515Check-up¤ 25.00¤ 0.00false
5/21/9924330Check-up¤ 45.00¤ 45.00false
5/18/9925430First Aid¤ 50.00¤ 50.00false
5/20/9925460Surgery¤ 150.00¤ 50.00false
5/22/9925430Prescription¤ 40.00¤ 40.00false
5/20/9926230First Aid¤ 45.00¤ 20.00false
5/22/9926330First Aid¤ 50.00¤ 25.00false
5/18/9927115Check-up¤ 25.00¤ 0.00false
5/21/9927415Check-up¤ 25.00¤ 15.00false
5/23/9927115Check-up¤ 25.00¤ 0.00false
5/21/9928545First Aid¤ 40.00¤ 40.00false
5/18/9929430First Aid¤ 50.00¤ 50.00false
5/19/9929430Prescription¤ 40.00¤ 40.00false
5/20/9929460Surgery¤ 150.00¤ 50.00false
5/22/9929460Surgery¤ 150.00¤ 50.00false
5/23/9929430First Aid¤ 50.00¤ 50.00false
5/19/9930460Surgery¤ 150.00¤ 150.00false
5/20/9930230First Aid¤ 45.00¤ 20.00false
5/22/9930230First Aid¤ 45.00¤ 20.00false
5/21/9931545First Aid¤ 40.00¤ 20.00false
5/22/9932430Miscellaneous¤ 45.00¤ 10.00false
5/20/9933260Surgery¤ 175.00¤ 175.00false
5/19/9934315Consultation¤ 20.00¤ 0.00true
5/20/9935145Prescription¤ 40.00¤ 40.00false
5/20/9936260Consultation¤ 50.00¤ 25.00false
5/20/9937345Prescription¤ 40.00¤ 40.00false
5/22/9937260Surgery¤ 175.00¤ 175.00false
5/19/9938430Prescription¤ 40.00¤ 40.00false
5/20/9939145Prescription¤ 40.00¤ 40.00false
5/21/9939330Miscellaneous¤ 30.00¤ 30.00false
5/22/9939145Prescription¤ 40.00¤ 40.00false
5/19/9940115Check-up¤ 25.00¤ 20.00false
5/20/9940260Consultation¤ 50.00¤ 25.00false
5/22/9940260Consultation¤ 50.00¤ 25.00false
5/20/9941545First Aid¤ 40.00¤ 40.00true
5/22/9941345Prescription¤ 40.00¤ 40.00false
5/21/9942260Surgery¤ 175.00¤ 100.00true
5/19/9943590Surgery¤ 350.00¤ 200.00true
5/18/9944230First Aid¤ 45.00¤ 0.00false
5/20/9945545First Aid¤ 40.00¤ 40.00true
5/22/9945145Prescription¤ 30.00¤ 15.00false
5/22/9946260Surgery¤ 150.00¤ 100.00false
5/22/9947415Check-up¤ 25.00¤ 0.00true
5/18/9948230First Aid¤ 45.00¤ 0.00false
5/22/9948545Prescription¤ 40.00¤ 40.00true
5/23/9948230First Aid¤ 45.00¤ 0.00false
5/22/9949330Prescription¤ 40.00¤ 15.00false
5/21/9950415Check-up¤ 25.00¤ 12.00false
5/22/9951130Vaccination¤ 45.00¤ 45.00false
5/22/9952590Surgery¤ 250.00¤ 250.00false
5/22/9953415Consultation¤ 20.00¤ 20.00false
5/18/9954230Check-up¤ 45.00¤ 45.00false
5/18/9958230Check-up¤ 45.00¤ 45.00false
5/23/9958230Check-up¤ 45.00¤ 45.00false
5/23/9959345First Aid¤ 50.00¤ 0.00true
5/23/9960330Vaccination¤ 55.00¤ 55.00true
5/23/9961415Vaccination¤ 35.00¤ 35.00false
SELECT Animals.AnimalID, Animals.Breed, Animals.AnimalName, Animals.Age
FROM Animals
WHERE (((Animals.Age)>3));
SELECT Animals.Species, Sum(Visits.AnimalID) AS SumOfAnimalID
FROM Animals INNER JOIN Visits ON Animals.AnimalID = Visits.AnimalID
GROUP BY Animals.Species;
SELECT Clients.ClientFirstName, Clients.ClientLastName
FROM Clients
WHERE (((Clients.ClientFirstName)=”B%”)) OR (((Clients.ClientLastName) Like “*E*”));
SELECT Animals.AnimalID, Animals.AnimalName, Visits.DoctorID, Visits.AnimalID, Doctors.DoctorName
FROM Doctors INNER JOIN (Animals INNER JOIN Visits ON Animals.AnimalID = Visits.AnimalID) ON Doctors.DoctorID = Visits.DoctorID
GROUP BY Animals.AnimalID, Animals.AnimalName, Visits.DoctorID, Visits.AnimalID, Doctors.DoctorName
HAVING (((Visits.DoctorID)=”3″) AND ((Visits.AnimalID)=11 Or (Visits.AnimalID)=15 Or (Visits.AnimalID)=18 Or (Visits.AnimalID)=20 Or (Visits.AnimalID)=22) AND ((Doctors.DoctorName)=”hopkins”));
SELECT Clients.ClientID, Sum(Visits.Fee) AS SumOfFee, Sum(Visits.Paid) AS SumOfPaid
FROM Clients, Visits
GROUP BY Clients.ClientID;

Looking for this or a Similar Assignment? Click below to Place your Order