Access

The above screen shot is of the relationship report from the Individual Access Project the Lime Class performed during ITEC 3650 in Spring 2012.

Reflection on the process of making this Access Database.

Please note the one to many relationship between Projects in the Project table and Project in the Billing Table. There is also a one to many relationship between EmployeeID in the Employess Table and the EmployeeID in the Billing table. This makes the Billing table a joined table since more than one Access Table has a many relationship to it.

Professor David Luneau did an excellent job in teaching the Lime Class how to understand, work with and build Access Databases. I had taught myself Access 2003 using Professor Teaches software, but still had some gaps in my understanding of what should and what should not be done in the process of building and maintaining an Access database. We learned how to use Access 2007, I have Access 2010 on my home laptop and my office desktop computers. It is slightly different between the two versions, but not nearly as much as 2003 differs from both 2007 and 2010.

Tables are the data storage areas of access databases.

The above screen shot shows the Tables, Queries, Forms, Reports and some of the Macros built in this project.  Tables are used to store the data that Queries, Forms and Reports draw from for their answers.  Be mindful to keep the information unique and does not duplicate.  Use separate spreadsheets for each subset of data and use of queries to gather that information as needed.

The above screen shot shows the Property Sheet and the Field Properties screens which allow different options for modifying the able in specific ways to be selected.

The above screen shot illustrates how useful an input mask such as (!\(999)’000\-0000.0;_) for Home Phone number formatting can prove to be.  Data Type gives meaning to the information provided, such as numbers or text.

Queries are the questions we ask of the tables in a database.  Reports draw from queries to give specific answers to these questions.  Think of queries and reports are the way we get information out of a database while tables and forms are the manner we put information into the database.  It’s important to note that forms do not store the data entered into them, that is the job of the table(s) that they are linked to.

The above screen shot illustrates how the Billing table forms a “join table” between the Projects and Employees tables in multiple one to many relationships.  In the queries properties sheet we also see under Total: that there is a Group by, First and Sum options available to us.  These come in handy in drilling down the information we need displayed in the query.

The above screen shot shows a Date being pulled from the Billing Table, being sorted in ascending order and the Criteria being “Between 02/1/2011 and 02/29/2011”.  Criteria is very helpful for pulling specific information that we are looking for in a query.  “If Null” is another helpful Criteria command should you wish to locate any record with a null value in the field that you specify.  It is also handy to check or not check show, not checking it will hide the field in the query, but will still run it.

Forms feed the data keyed into them into the table(s) linked with them, and it is important to note that forms do not store information in themselves directly.  Forms are just a nice way to organize the data we want to enter for end users.

The above screen shot shows the Billing Form in layout view.

The above screen shot shows the Billing Form in form view.  It is interesting to note the difference between these two views.  Form View includes the Property Sheet which gives us the ability to reconfigure the Format, Data, Event, Other and All elements of the form.  We can even lengthen or shorten the fields height and width in Form View.

The above screen shot is of the Employee Hierarchical Form in Layout View.  We can see something special about this form, in that it has a secondary form embedded within it for Project Hours Billed by Employee.   This is what makes it Hierarchical, Employee Billing Hours Form is the parent of the Project Hours Billed by Employee Form.

The above screen shot is of the Employee Hierarchical Form in Form View.  We can see here that there are Command Buttons at the top of the form under the Header.  In Form View we also see the tools to build these buttons three icons to the right of the highlighted cursor button.  These make navigating and operating within the record much easier for the end user to work with.

Reports are the exported information drawn from queries based on the information stored in tables in Access.  An example of such a report is as follows:

In Layout View we have access to the Property Sheet and the ability to format the columns and cells of the report to appear as we would like them to as a paper document.

Macros come in very handy and are essential in order to get Queries to work properly in the Switchboard.  In the switchboard you can’t run Queries directly in Access 2010. You must first build a macro to run the Query and then go into the Switchboard and display the Macro for the Query to be able to run Queries successfully from the Switchboard.