MS Access Demo – Buildings, Rooms, Sensors, Measurements

The second homework assignment asks you to build a database of sensors.  I’ll be showing an example in class (assuming there isn’t a snow problem) of building a Database that is aimed at recording measurements of any type in rooms of buildings, using sensors from a list that you’ve created.  It doesn’t exactly meet the requirements of the assignment, but should give you a good headstart on how to complete the assignment.  In some ways it goes well beyond the assignment requirements, though it certainly does not illustrate all of the capabilities of MS Access.

Video of making the database

There are five videos with a total length of 45 minutes that show you the steps.  The overview of these videos is on this DragonDrop Playlist.

The specific steps of the process are as follows – with links for each:

Description & Link	Length
Create Tables	9:38
Build Relations	5:44
Build Queries	11:01
Build Forms for Input	11:12
Create Report for Output	7:48

 

The purpose of the database

It’s always important to have a clear goal for a database, particularly a relational database.  Professionals have a well-developed method for defining the elements of database that relies on a mathematically rigorous theory, but that isn’t necessary for our fairly simple one.

In our case I define the goal as being able to record the following about a measurement:

• The measurement reading – a number
• The date and time it was taken
• The room and building in which it was taken
• The specific type of sensor used including: model & manufacturer (for your assignment you’ll want to extend what you record about the sensor greatly)

How the Database is Set Up

What is minimally necessary for a reasonable database to address this goal are four tables that have “relations” between them.  You could easily increase the number of tables (e.g. for “categories” of sensors and/or buildings and rooms) but this should be sufficient.  Those tables are:

• Building
• Room
• Sensor
• Measurement

The relation between these tables (you can see the fields too) is:

When you’re creating a database design it’s useful to think of what your goals are (in this case a record of measurements) and then work out from there to think about what is necessary to record for each measurement and how can I be sure that I enter the basic information only once.

Jim Mitchell