LIKE THE BLOG? DID MY COURSEWORK HELP YOU?

Feel free to donate to support me and be able to host this site as a free educational tool for everyone to use.

Any amount is appreciated and is not required!

Unit 18 (P3, P4, P5, P6, M2, M3, M4): Creating a Relational Database

Create and Populate a Database

Create and populate a database (P3)

I created a new desktop database with the name 'ITSupport.accdb'


I created each table and populated it within the database. Each table that I created followed my design that I did before creating the database. The table name, field name, data type and field size has been correctly inputted.


Below shows that I have correctly inputted the validation rule for gender that I specified in my design.


If I do not put 'Male' or 'Female' value in the field then it will come up with this message:


I put >"IT000" as the validation rule for the 'DepartmentCode' as I want all values to start with ‘IT’ and end with 3 numbers after that.


After I created the tables in the database, I then would need to make relationships with them as described in my design. This is an example of one of the relationships (This one has been referential integrity enforced and has cascade update and delete options ticked):


This is an overview of all the relationships:

Creating Features in Data Entry Forms

Create features in data entry forms to ensure validity and integrity of data (P4)

Forms allow users to easily manage data in the database without directly messing with the database itself. Sometimes you do not want employees in your company to edit the database manually, thus, making forms will reduce the complexity and the chance that the database produce errors.


Each table will have a form to easily manage the data in it. I already designed the forms for each table and now I am going to create the forms in the database. To do this, I clicked on the create tab at the top of the database then I clicked on form.


From here, a form is automatically created, but it is different to my initial design. This means that I would need to change it so it matches my initial design idea:


Old


New

The buttons below the form are added to give it extra simplicity to the design and easy use for any user who is adding/removing information in the database.


To ensure integrity of the database and to make sure that the data is accurate, I added in combo boxes to the form which only allows the user to enter a certain value such as ‘Male’ or ‘Female’:

This insures that no other value apart from the values in the combo box is entered which means that the database will not produce errors as the values will be correct and accurate.


In addition to this, I also set the DateCreated field in the TicketT table to automatically input the current date to pre-populate new tickets:


I did this by making an input mask to '"DD"/"MM"/"YYYY";;_' which automatically sets the date in this format (I also did this with the 'DateCompleted' field too). I then set the default value of 'Date()' which means that the current date will be placed into this field when a new ticket is created. This insures the accuracy of the data inputted into the table as you can only input data in a certain format.



Database Queries

Perform queries using multiple tables and multiple criteria (P5)

Queries allow you to filter your database for a specific need. If you want to find something, then a query would be the best way to get to the information you are looking for.


To make a query; you click on the create tab at the top of the document and then click Query Design to create a query from scratch. Add the tables you need from the show table menu into the query. You can then define the criteria of your query in the section at the bottom of the document.


Query 1

All female clients who work for the company IT4U (ID of 3).



Query 2

All tickets that have been made by the company PCFixer (ID of 4) that still needs completion.



Query 3

All tickets that is not completed, however, there is an employee currently working that specialises in the department where the ticket needs help in. For example, if a client needs help with an internet issue and an employee who specialises in internet issues is currently in working hours, then that ticket will show up in the query.



Query 4

All tickets that has been created in the past 5 days including the date today. As of writing this query; the current date is 09/05/2015 so this would include all dates within 05/05/2015 – 09/05/2015 using this criteria 'Between Date() And Date()-4'.


Advance Features

Include an advanced feature in a database design (P6)

Going back to my form, there are still areas in which I can improve using advanced features in the database.


The first thing I can do is to make a macro that if a value in the form returns null, then send a warning message saying 'Fill in form to continue' which then stops it from adding it to database.


To do this, I first need to be in design view of a form (I will be using 'EmployeeF') so I can edit the form. Then I right-clicked on the 'Add Employee' button in the form and clicked 'Build-Event…' then 'Macro Builder'. This brings up a plus sign and a box that I can type in.

In the box, I typed 'If' so I can make an if statement with the following code:


'IsNull([FirstName]) Or IsNull([LastName]) Or IsNull([Gender]) Or IsNull([DOB]) Or IsNull([Address]) Or IsNull([City]) Or IsNull([County]) Or IsNull([PostalCode]) Or IsNull([Phone]) Or IsNull([Email])'

This simply states that if one of the values in the form such as FirstName, County etc. returns null, then do something. What I want the macro to do is send a warning message box when this occurs. The message box is as follows:


MessageBox

Message: Fill in form to continue

Beep: Yes

Type: Warning!

Title: Form


This will make a message box with the message 'Fill in form to continue' on the screen. However, even when the message box appears it will still let add it to database. What I need to do is create an else statement to stop this from happening. The else statement consist of actually adding whatever is in the form to the database, but because I have an if statement before this saying no null values, then the add button will only add the form to database if there is no null values. The else statement is as follows:


Else

GoToRecord

Object Type:

Object Name:

Record: New

Offset


Now, if there are any null values in the database, a message box will appear and the form will not be added.


The second thing I can do to make this form even easier to use and input data into, I am going to use an advance feature called a combo box.


This combo box will allow users to not input incorrect information into the database and will force users to use only certain values specified by the database. In this example, I am going to add a combo box to the gender entry box so it only allows you to select 'Male' or 'Female'. As it is right now, I can input any value into this box like 'Maleeee' and it would create an error because I already specified in the validation rule to only use Male or Female values. This combo box will remove this error and make it easy for the user to input information.


First, I select combo box from the design tab:


Secondly, I select 'I will type in the values that I want' and click next.


After this, I type in two values 'Male' and 'Female' in each individual record in the same field.


Next, I selected 'Store that value in this field' and then select 'Gender' from the combo box.


Then, I typed in a name for the combo box, in my case, I named it 'Gender'.


I then replaced my old box with the new combo box that I had created simply by dragging and dropping. Now the form is complete and you can only put in two values 'Male' or 'Female'.



I did this with the other forms as well such as the 'ClientF' form.


However, there is also an opportunity for error when typing in the 'CompanyName'. The user would need to know what company we have in the database, but the user can mistype or not capitalise where necessary. I want the user to easily know what companies are in the database and if the database gets a new company, I want the combo box to automatically detect and add that to the company name list without having to manually add the name.


Firstly, I select combo box from the design tab just like before but this time, I select 'I want the combo box to get the values from another table or query'.


I then select the 'ClientCompanyT' table where all the company information is kept about our clients.


From here, I select 'CompanyName' and move it over to the right side so it is selected.


I will then select 'CompanyName' from the combo box and put it as ascending so the 'CompanyName' will go in alphabetical order starting from a-z.


I then tick 'Hide key column' as I only want the 'CompanyName' to show up in the combo box.


I then select 'Store that value in this field' then I selected 'CompanyName' from the combo box and clicked next. I named the combo box 'CompanyName' and clicked finish.


I replaced the combo box with the old 'CompanyName' box and now I can select the 'CompanyName' that is associated with each client. The 'CompanyName' will update if I change the 'ClientCompanyT' table by adding or removing a company and it will also update th