Wednesday, 21 December 2016

Reasons to Use Ms Access Database

Is Microsoft Access the best small business database? 
Microsoft Access is a Relational Database Management System (RDBMS), like Oracle & SQL/Server among others.   This RDBMS combines the database engine known as JET, Forms Design, Report Design, Graphics, and Visual Basic for custom program execution. 

Is it the right database development system for your needs?  This discussion will help you identify the pros and cons of using MS Office Access database as your software development system. We also present and discuss typical problems found in non-professionally developed databases - particularly Microsoft Access.
The following list provides answers to the question - why use Microsoft Access for consideration for your company's database development platform:
  • Microsoft Access is available with the Microsoft Office Professional suite of business products therefore no additional database software is required if your company purchases computers with this suite of products already installed.
  • MS Access database is likely to be available and supported for years to come because Microsoft is the premier software company in the world.
  • MS Access is the most widely used desktop database system in the world.
  • You can now put an Access database on the Internet Cloud with no programming changes!  Typical cost is less than $150 per user per month.  Contact us if you need help with the cloud.  Users from multiple cities can be sharing one database in a day. And from laptops in the field too.
  • If database support is important to you then Access may be your best choice since Access has more support and development consultants than any other desktop database system.
  • Access is significantly cheaper to implement and maintain compared to larger database systems such as Oracle or SQL Server.
  • Access can provide a cost benefit since consulting rates are typically lower for Access consultants compared to Oracle or SQL Server consultants.
  • Fairly complex databases can be setup and running in 1/2 the time and cost of other large database systems (the simpler the database the greater the cost advantage).
  • Microsoft Access integrates well with the other members of the Microsoft Office suite of products (Excel, Word, Outlook, etc.).
  • Other software manufacturers are more likely to provide interfaces to MS Access than any other desktop database system.
  • When designed correctly, Access databases can be ported (usually with some difficulty) to SQL Server or Oracle.  This is important if you want to start small or develop a pilot database system and then migrate to the larger database management systems.
  • A Microsoft Access system can be placed on a website for access for remote users.  Simple screens can be developed within Access, Data Access Pages. Or full control and functionality can be implemented using Active Server Page (ASP) programming.  Note that you still have the same simultaneous connection limitations described above.

    This article has been adopted from You may access the webpage to see further discussions, including limitations  and comments

    The key thinks is my take include:
    >> Access database is easy to use, and any organization is likely to have at least one person with knowledge of access or one that is willing enough to study the many online resourses.

    There are many forums in which you can ask questions and they will be answered. Examples include,,, as well as Microsoft support websites.

    Installation, backup and moving the database from one computer to another is as easy as copy--Paste. Other software require installation CDs, multiple licenses, Serial numbers and sometimes complicated installation procedures. wait until your main computer crashes... you have to call a specialist installer, and pay.

Tuesday, 20 December 2016

How to Promote Students to next Class in a Database- Ms Access Example

While designing a database for students, you may have come across various templates, which in my opinion are quite lacking in important features. One of these is that students are in one level, and then they graduate, or are promoted to another class.

In my implementation, I'm going to show a sample of how this promotion can be done.

I have a studentClass table which has fields... SchYear, StudentID/ Adm, ClassID, StreamID.

The table indexes are such that one student can only be in one class and year once... Let me explain. StudentID and SchYear are set as unique. Ms access table in design view, click indexes. You will already see the primary key as an index. Then add another index give it any name eg studentClass Select the fields StudentID and SchYear below it. (SchYear wont have an index name) then set the property as unique.

Set index in ms access database

 Back to the key information,

The idea is to create a query that selects students in the current, or previous year who are in form 1, 2,3 and 4, Re-Insert them into the table with the SchYear+1, ClassID+1 and the same Stream. This is done through an Insert/Append query.

To accomplish this you need a parameter... I chose to use YearID... The user selects the year to promote students to. eg. To promote students to Year 2017, There's a combo box which shows SchYear. The user selects 2017 and clicks a button. That button runs the query as described, ..

INSERT INTO StudentClass ( SchYear, StudentID, ClassID, StreamID...)
SELECT StudentID, StreamID From.... WHERE..., Name of the combo box +1 AS SchYear, ClassID +1 AS ClassID....

Wednesday, 4 July 2012

How To Design A Free Exams Marks Analysis System | Microsoft Access

How To Design A Free Exams Analysis System Using Microsoft Access 2007
So now let’s get working. You need a computer with Ms Office Access Installed. I will use Microsoft Access 2007, but you may use Ms Access 2010 or any other later system. Some of the queries and forms may not be supported in the 2003 Ms Access.

Set up the tables.
In my design, I have used various tables to store data in the database.
The main table is 
The exams table, which will contain all the results/ marks for the students.
Some of the important fields in this exams table include the examyear, term, student adm no, exam date, Subject, ExamType and Marks.

All these fields (Except Marks) get their data from lookup tables (They are not lookup fields directly at table level. The lookup is implemented in the form level) The fields are related to the tabes that hold information about those details... schoolyear, term, examtype, subject etc. The relationships are through the primary keys, so all the fields in this table are numbers... not text.

The second most important table is the Students table that holds info about the students... names, gender, date of birth adm no, etc. The Primary key is the student adm no.

Other tables maybe designed as follows:
(SubjectID, SubjectName, SubjectType, ShortName etc )

ExamDate Table
(ExamDateID, ExamDate, TermID, YearID, ExamType)

ExamType Table (ID, ExamName)... Opener, Mid-Term, End of term exams)

Term/Semester Table (TermID, TermName).. Normally just 2 or 3

SchoolYear Table (YearID, SchYear)

(StudentID, YearID, ClassID, StreamID)... A student will be in Class1 Stream 1 a certain Year

Class (ID, Name)... In KE, Form 1,... Form 4 - I added a Leaver class to hold those who have exited the school. Very important for promotions

Stream table (ID, Stream Name, Str..ShortCode/name.

Grade Table (ID, LetterGrade, Points, MarksLow, MarksHigh)... This implements a complex system used in Kenya to calculate Mean Standard Score... as well as letter grades.
...Other tables will be implemented as you advance... Fees, stores, library etc

How To Create Database Tables in Microsoft Access 2007 Design View
We have already determined the tables we will use for our database application
  • Step 1: Click the Create Tab in Microsoft Access window
  • Step 2: Click Table Design
  • Step 3: Enter the following in the table that is created
Field Names: This will afterwards become the columns in which you will enter the data.
Data Type: This determines the kind of information that can be stored in that field. With our simple database, we will only work with Number and Text.

Needs Analysis for a Computer Based Examinations Analysis System

Needs Analysis for a Computer Based Examinations Analysis System

Having been a teacher, I have experienced the challenges of manual exams analysis. The following are some of the checklist of the things I have had to do manually in order to complete exam analysis.. This is quite typical for a Kenyan secondary school.
  1. Record student’s performance per subject
  2. Convert Student’s Marks to letter grades A, A-, B+, B-,…E
  3. Calculate total marks for each student for the exam
  4. Make a list Ranking all the students according to their performance.. a lot of work.
  5. Enter student’s name and admission number in their report book/card
  6. Enter Student’s Marks in each student’s report book/ report card
  7. Check student’s performance for last term and enter it in report cards
  8. Write down comments for the subjects
  9. Lots of unnecessary paper work you might agree with me.
  10. Aims and Objectives of this project
  11. Remove the burden of examinations analysis, report cards writing and hordes of other record keeping from the backs of Teachers. Let the Teachers Teach.
  12. Minimize the amount of time required to analyze the exams. School closing preparation shouldn’t be a tiresome experience for the teachers
  13. Make exam results very accessible.