microsoft access - animal hospital database

profileHelpplease127
AnimalHospital-students.accdb
Home>Information Systems homework help>microsoft access - animal hospital database
AnimalID ClientID Species Breed Age AnimalName
1 1 Horse Quarterhorse 4 Blaze
2 2 Dog Chihuahua 3 Spot
3 2 Cat Mix 6 Nirmal
4 3 Cat Mix 8 Big
5 4 Dog Mix 5 Rover
6 5 Dog Collie 6 Sarah
7 6 Goat Nubian 4
8 6 Cow Herford 7
9 6 Cow Herford 8
10 7 Horse Morgan 11 Isty
11 8 Dog Doberman 6 Jake
12 9 Dog Terrier 5 Curley
13 10 Cat Manx 4 Little
14 11 Llama 3 Matisse
15 12 Hog 2 Petunia
16 13 Horse Saddlebred 9 Chug
17 13 Horse Saddlebred 8 Lightning
18 14 Cat Mix 4 Garfield
19 15 Cat Mix 7 Mittens
20 16 Dog Mix 6 Bozo
21 17 Dog Mix 5 Butler
22 18 Dog Collie 10 Shadow
23 19 Dog Chihuahua 5 Stimpy
24 19 Cat Persian 6 Queenie
25 20 Dog Terrier 4 Zippo
26 21 Cat Mix 8 Jimbo
27 22 Cat Persian 6 Sheba
28 23 Dog Terrier 4 Groucho
29 24 Sheep 6
30 25 Dog Mix 2 Hero
31 25 Cat Mix 1 Bartholemew
32 25 Cat Mix 1 Skippy
33 25 Cat Persian 7 Jezebel
34 26 Ostrich Blue 2
35 27 Dog Mix 4 Fluffy
36 28 Cat Mix 5 Tiger
37 29 Cat Mix 1 Sunshine
38 30 Dog Doberman 6 Friendly
39 31 Dog Collie 7 Lady
40 32 Cat Mix 8 Rainbow
41 33 Horse Saddlebred 4 Stonewall
42 33 Horse Morgan 1 Double Trouble
43 33 Horse Quarterhorse 8 Lucky
44 34 Cat Mix 3 Puffy
45 34 Cat Mix 5 Fluffy
46 35 Dog Mix 6 Tramp
47 36 Horse Mix 7 Sport
48 37 Llama 4 Inca
49 38 Hog 2
50 39 Dog Poodle 6 Alphonse
51 40 Dog Blue Heeler 4 Beowolf
52 41 Cat Mix 6 Bert
53 42 Cat Mix 1 Ernie
54 43 Dog Spaniel 9 Rex
55 44 Horse Pony 4 Daisy
56 44 Horse Quarterhorse 9 Jackson
57 6 Cow Angus 1
58 26 Cow Holstein 3
59 37 Emu Standard 1
60 26 Cow Angus 1
61 40 Dog Labrador 1 Harley
ClientID ClientFirstName ClientLastName ClientAddress ClientCity ClientPhone Account
1 Jennifer Logan 324 Eastside Ave Cawston 456-5443 false
2 Ken London 900 Wiltshire Blvd Cawston 456-3154 false
3 Britt Lizette 14-3214 27 Avenue Cawston 654-8852 true
4 Diana Stapleton 5232 Ida Road Seymour 441-6532 true
5 Lloyd Ray 124 E. 39 Street Cawston 332-9090 false
6 Duane Terrace 44-9090 Salmon St Cawston 123-9960 false
7 Bruce Moore 670 Monteith Rd Cawston 334-0987 true
8 Dennis Williams 5512 Lakeside Drive Brookwood 454-6622 false
9 Wayne Moravec RR1 Yankee Flats Abbot 434-0980 false
10 Pattie Dene 4403 Ida Road Seymour 441-6655 false
11 Gregory Paulette 775 39 Street Cawston 332-1505 false
12 Owen Belgo 8770 Delhart Ave Cawston 235-6541 true
13 Kevin Pavan 441 Potter Drive Cawston 551-6622 false
14 Wendy Schneider 133 Buckland St Brookwood 441-6023 false
15 Carl Herbert 902 Uplands Drive Cawston 551-6327 false
16 Stephanie Michael 443 Shelley Ave Cawston 665-9977 false
17 Jakob Schmidt 775 Ida Road Seymour 442-5102 false
18 Pam Stebner 345 Wiltshire Blvd Cawston 455-6632 false
19 Ingrid Corey 22-4587 42 Ave Cawston 125-6545 false
20 Leanne Stein 220 Lakeside Drive Brookwood 554-6500 false
21 Carmen Vallance 451-6625 38 St Cawston 451-3352 true
22 Lorne Valliere 545 Potter Drive Cawston 554-3652 false
23 Gerald Vanderburg 21 Eastside Ave Cawston 456-3211 false
24 Hank Gallagher RR3 Yankee Flats Abbot 225-6333 true
25 Elizabeth Norman 554 Cascade Pl Cawston 445-6632 false
26 Ron Widmann 756 Clara Drive Cawston 252-6411 false
27 Lonnie Wilkins 141 Uplands Drive Cawston 441-6655 false
28 Roland Sherwood 712 Shannon Way Cawston 225-5544 false
29 Bud Charming 8805 Lakeside Drive Brookwood 564-3332 false
30 Arnie Cramer 2061 Buchanan Cawston 455-6001 true
31 Nick Donnelly 123 Shelley Ave Cawston 665-3324 false
32 Timothy Collins 4456 Delhart Ave Cawston 255-3685 false
33 Allan Young 767 Renfrew St Cawston 124-9987 false
34 Joan Clements 213 Potter Drive Cawston 665-4422 false
35 Graham Sanderson 4305 Beach Ave Cawston 445-3328 false
36 Barry Desjardins 206 14 Avenue Cawston 332-6410 false
37 Linda Burke 477 Canyon Drive Cawston 124-6654 false
38 Darren James 62 Eastside Ave Cawston 456-1102 false
39 Spence Butler 855 Downie Cawston 335-6410 true
40 Lyle Adair 332 Ackerman Drive Cawston 441-6320 true
41 Dorothy Clifford 365-6630 38 St Cawston 451-6701 false
42 Jose Zurba 66 Cranbrooke Rd Cawston 225-3001 false
43 Lorna Peterson 201 14 Avenue Cawston 332-5413 false
44 Brendan Woods 233 Yuma St Cawston 441-2235 false
DoctorID DoctorName HomePhone
1 Dvorak 554-6684
2 Jesse 665-8744
3 Hopkins 441-6060
4 Lamaze 250-6300
5 Wilson 441-6544
6 Smith 992-2344
7 Chen 323-3456
Date AnimalID DoctorID Duration Treatment Fee Paid Travel
5/20/99 1 1 45 Prescription ¤ 35.00 ¤ 20.00 true
5/21/99 2 1 30 Prescription ¤ 40.00 ¤ 20.00 false
5/23/99 2 4 60 Surgery ¤ 150.00 ¤ 150.00 false
5/21/99 3 1 15 Check-up ¤ 25.00 ¤ 25.00 false
5/20/99 4 5 45 Miscellaneous ¤ 30.00 ¤ 30.00 false
5/19/99 5 3 45 First Aid ¤ 40.00 ¤ 0.00 false
5/20/99 5 1 45 Prescription ¤ 35.00 ¤ 20.00 true
5/22/99 5 1 45 Prescription ¤ 35.00 ¤ 20.00 true
5/23/99 5 1 30 First Aid ¤ 45.00 ¤ 45.00 false
5/22/99 6 1 30 Prescription ¤ 40.00 ¤ 40.00 false
5/19/99 7 2 45 First Aid ¤ 40.00 ¤ 0.00 false
5/19/99 8 2 15 Consultation ¤ 20.00 ¤ 10.00 true
5/20/99 8 5 45 Miscellaneous ¤ 30.00 ¤ 30.00 false
5/22/99 8 5 45 Miscellaneous ¤ 30.00 ¤ 30.00 false
5/21/99 9 2 90 Surgery ¤ 300.00 ¤ 0.00 true
5/19/99 10 5 60 Surgery ¤ 175.00 ¤ 175.00 true
5/21/99 10 1 30 Vaccination ¤ 55.00 ¤ 55.00 true
5/22/99 10 1 15 Check-up ¤ 25.00 ¤ 25.00 true
5/23/99 10 3 30 Prescription ¤ 40.00 ¤ 0.00 true
5/18/99 11 3 45 First Aid ¤ 50.00 ¤ 50.00 false
5/20/99 11 3 90 Surgery ¤ 225.00 ¤ 100.00 false
5/21/99 11 5 15 Check-up ¤ 25.00 ¤ 25.00 false
5/22/99 12 5 15 Consultation ¤ 20.00 ¤ 20.00 false
5/22/99 13 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/18/99 14 4 30 Vaccination ¤ 45.00 ¤ 10.00 true
5/20/99 14 5 60 Check-up ¤ 55.00 ¤ 55.00 true
5/18/99 15 5 45 First Aid ¤ 50.00 ¤ 50.00 false
5/19/99 15 1 60 Surgery ¤ 160.00 ¤ 100.00 false
5/20/99 15 3 90 Surgery ¤ 225.00 ¤ 100.00 false
5/22/99 15 3 90 Surgery ¤ 225.00 ¤ 100.00 false
5/23/99 15 5 45 First Aid ¤ 50.00 ¤ 50.00 false
5/18/99 16 1 60 Surgery ¤ 150.00 ¤ 0.00 true
5/19/99 16 2 45 Prescription ¤ 40.00 ¤ 40.00 true
5/20/99 16 3 45 First Aid ¤ 60.00 ¤ 35.00 true
5/18/99 17 1 60 Check-up ¤ 55.00 ¤ 55.00 true
5/23/99 17 5 60 First Aid ¤ 60.00 ¤ 0.00 true
5/18/99 18 3 30 Vaccination ¤ 45.00 ¤ 10.00 true
5/20/99 18 5 60 Check-up ¤ 55.00 ¤ 55.00 true
5/22/99 18 3 15 Check-up ¤ 25.00 ¤ 25.00 false
5/23/99 18 4 30 Vaccination ¤ 45.00 ¤ 10.00 true
5/18/99 19 5 15 Check-up ¤ 25.00 ¤ 0.00 false
5/20/99 19 4 60 Surgery ¤ 175.00 ¤ 75.00 false
5/18/99 20 1 60 Surgery ¤ 150.00 ¤ 0.00 true
5/20/99 20 3 45 First Aid ¤ 60.00 ¤ 35.00 true
5/22/99 20 3 15 Check-up ¤ 25.00 ¤ 25.00 false
5/23/99 20 1 60 Surgery ¤ 150.00 ¤ 0.00 true
5/18/99 21 1 60 Check-up ¤ 55.00 ¤ 55.00 true
5/20/99 21 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/23/99 21 1 60 Check-up ¤ 55.00 ¤ 55.00 true
5/19/99 22 4 30 Miscellaneous ¤ 30.00 ¤ 15.00 false
5/20/99 22 3 30 First Aid ¤ 50.00 ¤ 25.00 false
5/21/99 22 3 30 Vaccination ¤ 50.00 ¤ 0.00 false
5/23/99 22 2 45 First Aid ¤ 40.00 ¤ 40.00 false
5/18/99 23 1 15 Check-up ¤ 25.00 ¤ 0.00 false
5/20/99 23 4 60 Surgery ¤ 175.00 ¤ 75.00 false
5/22/99 23 4 60 Surgery ¤ 175.00 ¤ 75.00 false
5/23/99 23 5 15 Check-up ¤ 25.00 ¤ 0.00 false
5/21/99 24 3 30 Check-up ¤ 45.00 ¤ 45.00 false
5/18/99 25 4 30 First Aid ¤ 50.00 ¤ 50.00 false
5/20/99 25 4 60 Surgery ¤ 150.00 ¤ 50.00 false
5/22/99 25 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/20/99 26 2 30 First Aid ¤ 45.00 ¤ 20.00 false
5/22/99 26 3 30 First Aid ¤ 50.00 ¤ 25.00 false
5/18/99 27 1 15 Check-up ¤ 25.00 ¤ 0.00 false
5/21/99 27 4 15 Check-up ¤ 25.00 ¤ 15.00 false
5/23/99 27 1 15 Check-up ¤ 25.00 ¤ 0.00 false
5/21/99 28 5 45 First Aid ¤ 40.00 ¤ 40.00 false
5/18/99 29 4 30 First Aid ¤ 50.00 ¤ 50.00 false
5/19/99 29 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/20/99 29 4 60 Surgery ¤ 150.00 ¤ 50.00 false
5/22/99 29 4 60 Surgery ¤ 150.00 ¤ 50.00 false
5/23/99 29 4 30 First Aid ¤ 50.00 ¤ 50.00 false
5/19/99 30 4 60 Surgery ¤ 150.00 ¤ 150.00 false
5/20/99 30 2 30 First Aid ¤ 45.00 ¤ 20.00 false
5/22/99 30 2 30 First Aid ¤ 45.00 ¤ 20.00 false
5/21/99 31 5 45 First Aid ¤ 40.00 ¤ 20.00 false
5/22/99 32 4 30 Miscellaneous ¤ 45.00 ¤ 10.00 false
5/20/99 33 2 60 Surgery ¤ 175.00 ¤ 175.00 false
5/19/99 34 3 15 Consultation ¤ 20.00 ¤ 0.00 true
5/20/99 35 1 45 Prescription ¤ 40.00 ¤ 40.00 false
5/20/99 36 2 60 Consultation ¤ 50.00 ¤ 25.00 false
5/20/99 37 3 45 Prescription ¤ 40.00 ¤ 40.00 false
5/22/99 37 2 60 Surgery ¤ 175.00 ¤ 175.00 false
5/19/99 38 4 30 Prescription ¤ 40.00 ¤ 40.00 false
5/20/99 39 1 45 Prescription ¤ 40.00 ¤ 40.00 false
5/21/99 39 3 30 Miscellaneous ¤ 30.00 ¤ 30.00 false
5/22/99 39 1 45 Prescription ¤ 40.00 ¤ 40.00 false
5/19/99 40 1 15 Check-up ¤ 25.00 ¤ 20.00 false
5/20/99 40 2 60 Consultation ¤ 50.00 ¤ 25.00 false
5/22/99 40 2 60 Consultation ¤ 50.00 ¤ 25.00 false
5/20/99 41 5 45 First Aid ¤ 40.00 ¤ 40.00 true
5/22/99 41 3 45 Prescription ¤ 40.00 ¤ 40.00 false
5/21/99 42 2 60 Surgery ¤ 175.00 ¤ 100.00 true
5/19/99 43 5 90 Surgery ¤ 350.00 ¤ 200.00 true
5/18/99 44 2 30 First Aid ¤ 45.00 ¤ 0.00 false
5/20/99 45 5 45 First Aid ¤ 40.00 ¤ 40.00 true
5/22/99 45 1 45 Prescription ¤ 30.00 ¤ 15.00 false
5/22/99 46 2 60 Surgery ¤ 150.00 ¤ 100.00 false
5/22/99 47 4 15 Check-up ¤ 25.00 ¤ 0.00 true
5/18/99 48 2 30 First Aid ¤ 45.00 ¤ 0.00 false
5/22/99 48 5 45 Prescription ¤ 40.00 ¤ 40.00 true
5/23/99 48 2 30 First Aid ¤ 45.00 ¤ 0.00 false
5/22/99 49 3 30 Prescription ¤ 40.00 ¤ 15.00 false
5/21/99 50 4 15 Check-up ¤ 25.00 ¤ 12.00 false
5/22/99 51 1 30 Vaccination ¤ 45.00 ¤ 45.00 false
5/22/99 52 5 90 Surgery ¤ 250.00 ¤ 250.00 false
5/22/99 53 4 15 Consultation ¤ 20.00 ¤ 20.00 false
5/18/99 54 2 30 Check-up ¤ 45.00 ¤ 45.00 false
5/18/99 58 2 30 Check-up ¤ 45.00 ¤ 45.00 false
5/23/99 58 2 30 Check-up ¤ 45.00 ¤ 45.00 false
5/23/99 59 3 45 First Aid ¤ 50.00 ¤ 0.00 true
5/23/99 60 3 30 Vaccination ¤ 55.00 ¤ 55.00 true
5/23/99 61 4 15 Vaccination ¤ 35.00 ¤ 35.00 false
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;