PHS badge

PHS CyberSchool

Computer Studies SG

Modified 05 June 2006

Maintaining Hardware Database - Simple subform

The network administrator has asked you to assist in keeping data on the maintenance of hardware at the business. To solve the problem a database of two tables must be created, one for the hardware data and the other for the maintenance.

Create a new database in which to store all the data about the hardware. Call the database Hardware Maintenance.
Create the Hardware table using the structure shown below. (Use the tip sheet for assistance.)
1. Use the name Hardware for the table.
2. Add the data shown below to the Hardware table. (Note what happens to the order of the records - why? Why must you add the Serial Number first?)
3. Print the Hardware table on ONE page and submit.

Structure of the Hardware table

Field name	Data type	Field size	Primary key	Description
PC Name	Text	50		PC Name
Location	Text	50		Where found in the building
Serial Number	Text	10	Primary key	Serial number of each computer (Primary key)
Operating System	Text	50		Operating System installed on each computer
Hard Drive	Text	10		Hard drive
CPU Type and Speed	Text	10		CPU type and speed
RAM	Text	10		RAM
Replacement Cost	Currency			Replacement cost

Data for Hardware table

PC Name	Location	Serial Number	Operating System	Hard Drive	CPU Type and Speed	RAM	Replacement Cost
Phsserver	Comp Room	2001.116	Linux Red Hat 6	2X36+6GB	450x2	592MB	R 30000
Store3	Lab1	2002.123	Windows 2000	10GB	1Ghz	128MB	R 8000
Lab2-3-8	Lab2	2002.131	Windows 2000	10GB	1Ghz	128MB	R 8000
Staff_08	Staff Workroom	2001.123	Windows 2000	10GB	Cel 633	128MB	R 7000
Clsgeo01	Geography	2001.133	Windows 2000	10GB	Cel 667	128MB	R 7000
Lab1-2-4	Lab1	2001.15	Windows 2000	4GB	Cel 366	64MB	R 6000
Adm01	Fundraiser	2001.159	Windows 2000	4GB	Cel 366	64MB	R 6000
Admsport2	Guidance Upstairs	2001.169	Windows 2000	4GB	Cel 366	64MB	R 6000
Admbook	Bookshop	2001.137	Windows 2000	4GB	Cel 366	64MB	R 6000
Deputy 2	Deputy 2	2001.103	Windows 2000	4GB	Cel 433	64MB	R 6000

Create the Maintenance table using the structure below.
1. Use the name Maintenance for the table.
2. Add the data shown below to the Maintenance table.
3. Add a default value to the Date field so that it shows the date now. =Date() (It does not work on the computers on my LAN although it does work on my stand alone computer.)
4. Make the Signature automatically appear as capital letters.
5. Print the Maintenance table on ONE page and submit.

Structure of the Maintenance table

Field name	Data type	Field size	Additional formatting	Primary key	Description
Serial Number	Text	10		Do NOT make it a primary key. There is NO primary key.	Serial number of each computer
Date	Date/Time	Short Date	Default value =Date()		When maintenance done
Maintenance	Memo				Work done
Signature	Text	5	Format >		Initials of those persons who did the maintenance

Data for Maintenance table

Serial Number	Date	Maintenance	Sign
2001.15	2003/06/04	Replaced mouse	PM
2001.123	2003/09/09	Upgraded memory to 10GB	PB
2001.15	2003/05/04	Replaced mouse	PM
2001.103	Today's date	You decide what can be done to the computer	Your initials

Join the Hardware table to the Maintenance table by the Serial Number.

Joining tables

Use the wizard to create a form for the Hardware table illustrated below. (Use the tip sheet for assistance.)
1. Use the name Hardware Form for the form.
2. The layout is not important in this activity at this stage.

Hardware form

Use the wizard to create a form for the Maintenance table
1. Use the name Maintenance for the form.
2. Do not bother to add the Serial number field to the form.
3. Use the datasheet layout.
In design view add a subform of the Maintenance form to the Hardware Form.

Hardware form with Maintenance subform

Using the Hardware form add an imaginary new computer to the database with all of its details.
1. Using the Hardware form, record the maintenance done on two occassions today to the new computer.
2. Print JUST the records of the new computer and submit.
Save the database for future tasks.
Consult the Access Help pages on Datatype and Fieldsize Property.
Know and understand the concepts of Redundancy and Primary key in database design.

Return to Computer Studies lesson plan index. This work may be freely copied for non-commercial use only. Copyright � Pam Miller.