Ms Access-Assignment (4th Nov 2011)

ASSIGNMENT-Ms Access 2003

(4^th NOV 2011)

01-Create a table named Suppliers

Field name	Data type
Supplier ID	Auto num
Supplier Name	Text
Address	Text
Contact name	Text
Web Address	Hyperlink
Tax	Yes/no

 02-Create a table named Products

Field name	Data type
Supplier ID	Auto num
Product  name	Text
Product type	Text
Responsible dept	Lookup wizard

03-Enter the given details to your Supplier table by creating a form called Supplier.

Supplier ID	Suplier Name	Address	Contact name	Web Address	Tax
Auto num shoud be added from 01	ABC	RATNAPURA	NIMAL	http://www.abc.com/	Yes
	DEF	COLOMBO	SUNIL	http://www.def.com/	No
	IJK	RATNAPURA	JEEWANTHA	http://www.ijk.com/	Yes
	LMN	MORATUWA	KASUN	http://www.lmn.com/	Yes
	OPQ	KURUWITA	RAJIKA	http://www.opq.com/	No
	RST	RATNAPURA	MANEL	http://www.rst.com/	Yes

04-Enter the given details to your Products  table by creating a form called Products.

Supplier ID	Suplier Name	Product  name	Product type	Responsible dept
Auto num that had added in supplier table.	ABC	Tables	Goods	Mkt
	DEF	Cleaning	service	General
	IJK	tables	Goods	Mkt
	LMN	Materials	Goods	Acc
	OPQ	Cleaning	service	General
	RST	Materials	Goods	Acc

05.Make a relationship between 2 tables from quarries and create 2 Quarry tables given bellow?

Name the Quarries as Quarry 001,002

1.Suppliers who pay taxes need bellow information

Supplier ID

Suplier Name

Address

Contact name

Web Address

2. Suppliers who provide Goods as their products need bellow information

Supplier ID

Suplier Name

Address

Contact name

Web Address

Tax

Product  name

Responsible dept

 (20 marks X 5=100)

Data base software-Ms Access 2003 Questions

CREATE A DATA TABLE.

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Distributer.

1 Open the Cars database in My exercises folder in the hard drive. 2 Create a table named Clients with the following fields: Field Name Data Type Client Code Number Client Name Text Client Surname Text Client Address Text Client City Text Client Postal Code Text Client State Text Client Phone Text Client Birth Date/Time The primary key will be Client code. 3 Create another table named Sold Cars with the following fields. Name of field Type of data Licence Text Make Text Model Text Colour Text Price Text Extras Memo The primary key will be License. 4 Create another table named Services with the following fields: Name of field Type of data Service Number Autonumber Oil Change Yes/No Filters Change Yesí/No Revise Brakes Yes/No Other Memo The primary key will be Service Number 5 Close the database.

Exercise 2: Clinic.

1 Open the Clinic database in My exercises folder in the hard drive. 2.Create another table named Patients with the following fields. Name of field Type of data Patient Code Number Patient Name Text Patient Surname Text Patient Address Text Patient City Text Patient Zip Code Text Patient State Text Patient Phone Text Patient Birth Date/Time For the moment we will not define a primary key. 3 Create another table named Doctors with the following fields. Name of field Type of data Doctor Code Text Doctor Name Text Doctor Surname Text Doctor Phone Text Doctor Speciality Text For the moment we will not define a primary key. 4 Create another table named Entries with the following fields: Name of field Type of data Entry Number Autonumber Entry Room Number Entry Bed Text Entry Date Date/Time Do not define a primary key. 5 Close the database

Exercise Unit 4: Modifying data tables.

If Access is not open, open it in order to be able to carry out the following exercises.

Ejercicio 1: Cars.

1 Introduce the following data into the Clients table in the Cars database in My exercises folder. Client code Client name Client surname Address City Postal code Province Telephone Date of birth 100 Antony Wood 58 Cedar Ave Denver 46011 CO 963689521 08/15/60 101 Charles Standwood 5 W Franklin Blvd. Chicago 45300 IL 962485147 04/26/58 105 Louis Wolf 11 Main st. Dallas 75201 TX 962965478 0330/61 112 James Smith 121 Cedar Ave Denver 46014 CO 963684596 01/31/68 225 Andrew Fields 23 Seneca rd Miami 33500 FL 963547852 04/28/69 260 Joseph Taylor 14 Cedar Ave Denver 46002 CO 963874569 05/15/64 289 Elisabeth Baker 4 Lake St. Miami 33500 FL 963547812 07/10/62 352 Eva Santos 34 Manor Rd. Austin 75300 TX 962401589 08/12/65 365 Gerard Swan 8 Steel St. Denver 46002 CO 963589621 02/01/65 390 Charles Prats 8 Alameda Ave Denver 46005 CO 963589654 03/05/67 810 Louisa Oliver 1562 Steel St. Denver 46007 CO 963587412 06/25/64 822 Samuel Larred 65 Steel St. Denver 46005 CO 963589621 12/25/67 860 James Tree 8 Main st. Austin 75300 TX 963758963 04/05/69 861 James Tree 8 Main st. Austin 75300 TX 963758963 04/05/69 2 Change the name of James Smith to Antony. 3 Delete the last record. 4 Close the table and the database.

Exercise 2: Clinic.

1 Modify the structure of the Patients table in the Clinic database in My exercises folder following these indications: Field name Data type Patient code Primary key Patient State Erase this field 2 Edit the structure of the Doctors table with the following data: Field name Data type Doctor code Primary key Doctor Phone Erase this field 3 Edit the structure of the Admissions table with the following data: Field name Data type Entry Number Primary key 4 Close the database

Unit 5 Exercises. The field properties

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Distributer

1 Edit the structure of Clients table in Cars database using the following data: Field name Properties Client code It is not possible to introduce clients whose code is not composed of values between 1 and 3000. Client name Size: 15 Client surname Size: 30 Client Address Size: 30 Client City Size: 15 Client Postal code Size: 5, only allows 5 digit numbers. Client State Size: 15 By default the value is: TX, as most of our clients are from this state. Client Phone Size: 10 with telephone Input Mask Client birth Format: Short date 2 Try to change the code of the first client to 3500: Observe how we are not allowed to as the validation rule has not been complied with, and the message you typed is displayed. 3 Answer OK and leave the code as 100. 4 Test all the rest of the properties that we have included. 5 Edit the structure of the Sold Cars table. Field name Data type License Size: 7 Make Size: 15 Model Size: 20 Colour Size: 12 Price Numeric currency format Extras installed Leave as is 6 Close the table saving changes. 7 Close the database.

Exercise 2: Clinic III.

1 Edit the Patients table in the Clinic database following these indications: Field name Data type Patient code Integer Patient Name Size: 15 Patient Surname Size: 30 Patient Address Size: 30 Patient City Size:15 Patient Zip code Size: 5 Patient Phone Size: 10 Patient Birth Format: Short date 2 Edit the Doctors table with the following fields: Field name Data type Doctor code Size:5 Doctor name Size:15 Doctor surname Size:30 Especiality Size:20 3 Edit the Entries table with the following fields. Field name Data type Entry Room Integer Entry Bed Size: 1 Entry Date Format: Short date 4 Close the database.

Unit 6 exercises. Relationships

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Cars

1 Open the Cars database from My exercises folder. 2 Add a Numeric field Client to the Sold cars table. This field will tell us which client has bought the car. 3 Add a Text field Car, Size 7, in the Services table which will tell us which car (of the Sold Cars) corresponds the service. 4 Create appropriate relationships between tables. 5 Introduce the following data into Sold cars: License Make Model Colour Price Extras Client V2360OX Chevrolet Cobalt Blue 12990 Electric aerial 100 V1010PB Ford Focus White 13995 101 V4578OB Ford Fusion Black 17900 Air conditioning 105 V7640OU Audi A4 Black 28960 Airbag 225 V3543NC Ford Taurus Red 21595 260 V7632NX Audi A3 Red 25600 Air conditioning, Airbag 289 V8018LJ Ford Fusion Blue 17850 Electric wipers 352 V2565NB Chevrolet Malibu White 16990 390 V7642OU Ford Focus White 13995 810 V1234LC Audi A3 Green 27000 Air conditioning 822 V9834LH Chevrolet Impala Red 20990 860 6 Introduce the following data into the Services table: Service Number Oil Change Filter Change Revise Brakes Other Car 1 Yes No No Lights service V7632NX 2 Yes Yes No Change washers V7632NX 3 No Yes Yes Repair alarm V4578OB 4 No Yes Yes Adjust panel V2360OX 5 Yes Yes Yes Change washers, fix alarm V2565NB 6 No No Yes Change interior light V7640OU 7 Yes Yes Yes V2565NB 8 No No No V8018LJ 9 Yes No Yes Lights service V3543NC 10 No Yes No Repair alarm V8018LJ 11 No No No V3543NC 12 Yes Yes Yes V1234LC 13 No Yes No Change washers V9834LH 14 No Yes No V1010PB 7 Close the database.

Exercise 2: Clinic IV.

1 Open the Clinic database from My exercises folder. 2 Add to the Entries table a numeric Patient field with Field Size Integer (this field will serve to show us which client corresponds the entry) and whitout Default value (remember what we said about numeric related fields!), and a text field (Size 5) named Doctor (this field will serve to know which doctor to entrust the entry to). 3 Create the appropriate relationship between the tables. 4 Introduce the following data into the Patients table. Patient code Patient Name Patient Surname Patient Address Patient City Patient Zip code Patient Birth 100 Joe Green 121 Cedar Ave Dallas 78600 03/31/75 102 David Smith 8 Main st. Austin 78767 10/30/47 103 David Buttom 34 Manor rd Austin 78767 06/11/87 110 Joe Beckam 8 Steel st. Dallas 78600 17/08/36 120 Sam Potter 1 Cedar Ave Dallas 78600 12/04/50 130 Joe Taylor 8 Steel st. Dallas 78600 01/23/32 131 Peter Green 45 Main st.. Austin 78767 12/08/90 140 Joe Williams 8 Main st. Dallas 78600 01/25/58 142 Joe Smith 14 Manor rd Austin 78767 03/25/58 200 David Lennon 18 Steel st. Dallas 78600 12/01/73 201 Sam Buttom 8 Steel st. Dallas 78600 05/05/55 207 Joe Weasley 11 Cedar Ave Dallas 78600 12/07/90 220 Johnny Granger 38 Steel st. Dallas 78600 07/19/5 231 Joe Sun 89 Main st. Dallas 78600 6/13/40 240 Andrew Smith 25 Main st. Austin 78767 02/07/85 300 Sam Beckam 64 Manor rd Austin 78767 05/05/77 5 Introduce the following data to the Doctors table. Doctor code Doctor Name Doctor Surname Doctor Speciality ASP Anthony Smith Pediatric RTP Rose Taylor Psychiatry SSG Sam Sanchez General PMP Peter Martin Pediatric ASR Anne Smith Radiology RMA Rose Martin Analysis BTI Bart Taylor Intensive API Anne Pons Intensive SFS Sam Flanders Ophtamology BSO Bart Smith Ophtamology RRG Rose Red Gynecology 6 Introduce the following data to the Admissions table. Entry Number Entry Room Entry Bed Entry Date Patient Doctor 1 101 A 04/23/98 300 RTP 2 105 A 05/24/98 103 RTP 3 125 B 06/15/98 300 RTP 4 204 B 12/09/98 120 SSG 5 205 B 12/10/98 100 SSG 6 204 A 04/01/99 102 SSG 7 201 A 01/02/99 240 SSG 8 201 A 02/04/00 110 SSG 9 305 A 03/05/00 131 API 10 304 B 12/05/00 201 BTI 11 306 A 13/05/00 201 API 12 303 B 06/15/00 220 BTI 13 302 A 06/16/00 131 BTI 14 504 B 06/30/00 130 BSO 15 504 B 02/07/00 231 SFS 16 405 B 05/07/00 207 PMP 17 401 A 08/08/00 220 PMP 18 408 B 10/08/00 207 ASP 19 504 A 12/08/00 120 BSO 20 509 B 08/20/00 240 SFS 7 Close the database.

Unit 7 exercises. Queries

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Cars

1 Open the Cars database from My exercises folder. 2 Create a query to visualize only the License, Make, and Model of the Sold Cars, name it Sold cars query. 3 Edit the previous query and add the price, and only visualize those cars that are Ford, save the query with the name Ford cars. 4 Edit the previous query to visualize those that are Ford, and that have a price superior to 16000, name it Ford superiors. 5 Create a query to see the surnames and cities of those clients that have bought a Ford or a Audi, the clients should appear in alphabetical order within each city. Name the query Ford and Audi clients. 6 Close the database.

Exercise 2: Clinic

1 Open the Clinic database from My exercises folder. 2 Create a query to see the surnames, addresses, and date of birth of those patients who were born before 1960 and whose postal code is 78767, name the query: Patients from 78767. 3 Create a query to see those patients whose admittance date was before the 12/31/98, their surnames, date of birth, admittance date, and surname of the doctor assigned to them as well as his speciality, save the query as Patients with doctor. 4 Close the database.

Unit 7 exercises. Queries

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Cars

1 Open the Cars database from My exercises folder. 2 Create a query to visualize only the License, Make, and Model of the Sold Cars, name it Sold cars query. 3 Edit the previous query and add the price, and only visualize those cars that are Ford, save the query with the name Ford cars. 4 Edit the previous query to visualize those that are Ford, and that have a price superior to 16000, name it Ford superiors. 5 Create a query to see the surnames and cities of those clients that have bought a Ford or a Audi, the clients should appear in alphabetical order within each city. Name the query Ford and Audi clients. 6 Close the database.

Exercise 2: Clinic

1 Open the Clinic database from My exercises folder. 2 Create a query to see the surnames, addresses, and date of birth of those patients who were born before 1960 and whose postal code is 78767, name the query: Patients from 78767. 3 Create a query to see those patients whose admittance date was before the 12/31/98, their surnames, date of birth, admittance date, and surname of the doctor assigned to them as well as his speciality, save the query as Patients with doctor. 4 Close the database.

Unit 8 exercises . Summary queries

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Cars

Create in the Cars database: 1 A query to know how many cars have been sold, how much money they cost, and the average sale amount. Audi Cars should not be counted, name the query Summary without Audi. 2 A query to know how many services we have with oil changes, how many with filter changes, and how many changes of brakes, name it Summary Services.

Exercise 2: Clinic

Create in the Clinic database: 1 A query to know the doctors that have more than three admissions, indicating for each one of these the name and surname of the each doctor, and how many admissions they have. Name the query Saturated doctors. 2 A query to know the oldest admission date within each speciality. Name the query Oldest admissions.
Unit 9 exercise. Crosstab queries

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Cars

Create a query in the Cars database in My exercises folder to obtain a crosstab query to know for each make, how many cars were sold in each colour, and also the total sold of each make should appear. Name it Cross make-colour.

Exercise 2: Clinic

Create a query in the Clinic database in the My exercises folder to obtain a crosstab query to know how many admissions in every speciality we have in each city. Name it Cross Entries-Patients-Doctors.
Unit 10 exercise. Action queries

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Distributer

Open the Cars database. 1 Create the Create Ford services query that will generate a new table named Ford services and will contain the records of the Services carried out on Ford cars. 2 Create an Increase price query that will permit the price of all Ford cars to increase by 5%. 3 Create a Delete Ford services query that will delete all records of Services carried out on Ford cars. 4 Create a Recover services query that will recover the records deleted in the previous query from the table that we created in part 1.

Exercise 2: Clinic

Open the Clinic database. 1 Create a Create non admitted patients that will generate a new table named Non admitted patients and will contain the records of the Patients that are not in the Entries table. 2 Create a Change room query to change the patients from room 504 to room 505. 3 Create a Create room 201 query that will generate a new table named Rooms 201 and will contain the records of the Entries in room number: 201. 4 Create a Delete entries that will delete the Entries in room number 201. 5 Create a Recover entries query to recover the records deleted in the previous query

Unit 11 exercise. Forms

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Cars

1 Open the Cars database. 2 Create a form with which to edit the records in the Clients table, naming it Client maintenance. 3 Create a form with which to introduce and edit the records of the Sold cars table, naming it Sold cars maintenance. 4 Create a form with which to introduce and edit the records of the Services table. Design it in such a way that at the time of introducing the license the user can see the name of the client who has the licence, naming itServices maintenance.

Exercise 2: Clinic

1 Open the Clinic database. 2 Create a form with which to edit the records in the Patients table, naming it Patients maintenance. 3 Create a form with which to introduce and edit the records in the Doctors table, naming it Doctors maintenance. 4 Create a form in which a doctors data will appear in a zone, and below this the list of admissions that the doctor has, naming it Entries per doctor.

Unit 12 exercise. Reports

If Access is not open, open it in order to be able to carry out the following exercises.

Exercise 1: Distributer

1 Open the Cars database. 2 Create a report to obtain a list of the clients sorted by surname in such a way that the clients of each city can be filed in a separate page. 3 Create a report to print the records of the Sold cars table, grouped by Make and sorted by Model and also extracting how many cars have been sold of each make and how much do they cost, and print it. 4 Create a report with which to print the records of the Services table, and print it.

Exercise 2: Clinic

1 Open the Clinic database. 2 Create a report to print the Name and surname of each patient as well as their Entry date and surname of the assigned doctor.