|
|
DBMS DataBase Management System
TASK
What is a DBMS? Search Wikipedia and find out.
What is a DBMS? Search Wikipedia and find out.
VIDEO TUTORIALS: The Global Community Fund Learn for Free website is a great resource for Access 2016 tutorials.
GCFlearnfree - Microsoft Access 2016
TASK
Click the link and find the tutorials.
GCFlearnfree - Microsoft Access 2016
TASK
Click the link and find the tutorials.
|
Pin Access 2016 to your Start or Taskbar
TASK On your computer you should have Microsoft Access 2016 installed. Pin this application to your taskbar. If the taskbar is becoming too crowded unpin applications, you no longer regularly use. |
Getting started
Here are three videos to help you to start learning the concepts around databases.
|
|
|
|
|
|
|
|
The Getting Started video has a practice file that can be downloaded from the website: https://www.gcflearnfree.org/access2016/getting-started-in-access/1 Follow along with the instructions on the website as shown in the video.
Records/Fields/Datatypes Activity
To create a DBMS or database for short you must think about all the different types of data you want to store. In a database a record is one entry or item in the database and it is a collection of one or more fields. Each field must be of one datatype.
Some datatypes used by MS Access are: short text, long text, number, date/time, currency, autonumber, yes/no, ole object, hyperlink, attachment, calculated, and lookup wizard.
There are some tricks to using datatypes for example the number datatype is generally used for numbers that can be used in a calculation e.g. to find the amount of GST on a product you might multiple the sales price by 15%, so the sales price would be a number. But a telephone number might be short text.
Some datatypes used by MS Access are: short text, long text, number, date/time, currency, autonumber, yes/no, ole object, hyperlink, attachment, calculated, and lookup wizard.
There are some tricks to using datatypes for example the number datatype is generally used for numbers that can be used in a calculation e.g. to find the amount of GST on a product you might multiple the sales price by 15%, so the sales price would be a number. But a telephone number might be short text.
Designing a database
Before making a database, we must think carefully about what fields we would want.
TASK
1. Think of the fields that should be in a database of your favourite songs. Everyone must think of at least one.
2. Now research a song you would add to a favourite songs database. It must have some data or details for each of the agreed fields in the database. Record the details in a Google doc table.
TASK
1. Think of the fields that should be in a database of your favourite songs. Everyone must think of at least one.
2. Now research a song you would add to a favourite songs database. It must have some data or details for each of the agreed fields in the database. Record the details in a Google doc table.
Write the fields names on one side and the data on the other. Save the doc as My Favourite Music data.
3. When the LL askes you, look into your school Gmail and you should find an email from your LL with a link to a Google form. The form will request that you enter the data saved in your My Favourite Music data doc.
Making a Database from a Comma Separated Values file
A dot CSV file or .csv is a spreadsheet file that doesn’t have any formatting in it. It is a basic file format for sharing data. It is very useful as an application can save data that can then be used by a different application. For example, the data you entered into the Google form can be downloaded as a .csv file.
This file can then be opened by MS Access and used to create an Access table. A table is the structure used to store the original data in Access.
This file can then be opened by MS Access and used to create an Access table. A table is the structure used to store the original data in Access.
Folder Structure
This PC\ID\10DIG\ Applications\ Database
The above path shows the folder structure that should exist on your school network account. Put the Music spreadsheet CSV file into the Database folder.
TASK
The above path shows the folder structure that should exist on your school network account. Put the Music spreadsheet CSV file into the Database folder.
TASK
- Open MS Access
- Make a New Database and call it My Favourite Music
- Save it in your Database folder
- Click on the External Data menu
- Choose Import Excel Spreadsheet
- As shown in the picture specify the source file the Music CSV file
- Leave selected Import the source into a new table in the current database and click OK
- You may need to make a primary key (The primary key ensures a unique identifier for all records it is often the autonumber datatype in increment setting)
- You can also preview the records. Some duplicate records may need to be deleted.
- Rename the new table to Music
- You may need to delete another empty table that was made when the database was made
Views
Each object in an Access database can be opened in different views:
TASK
Open your Favourite Music database
Open the Music table
Use the View button (top left corner under the File tab) to change the view. To change back to the Datasheet view use the same button.
- Table - Datasheet, Design
- Form - Form, Layout, Design
- Queries - Datasheet, SQL, Design
- Report - Report, Print Preview, Layout, Design
TASK
Open your Favourite Music database
Open the Music table
Use the View button (top left corner under the File tab) to change the view. To change back to the Datasheet view use the same button.
Input Masks
Input masks can be used to make users enter data in a certain way. They can only be used for fields which are text or data/time datatypes. One way to find out more about input masks is to use Access help (?).
TASK
Click on the question mark in the right side of the Access screen.
Type in input mask. Click on the link that says "Control data entry formats with input masks".
Click on "Characters that define input masks". You can read about what characters mean in an input mask. Note a "character" in computer language refers to one letter, number or symbol.
Create a 'Date entry' input mask for your Music table
TASK
Click on the question mark in the right side of the Access screen.
Type in input mask. Click on the link that says "Control data entry formats with input masks".
Click on "Characters that define input masks". You can read about what characters mean in an input mask. Note a "character" in computer language refers to one letter, number or symbol.
Create a 'Date entry' input mask for your Music table
- Firstly open the table
- Choose Design view
- Click in the "Year" field box
- In the Field Properties (below the Field names, Data types etc.) click in the Input Mask box
- Click on the three dots at the end of the box to activate the "Input Mask Wizard". A wizard is a way to do something by answering a series of questions.
- Click on the "Short date" input mask and then the "Try it" box. Remember it expects two numbers for a day and month. If the the day for example is 1 you must enter 01
- The default input mask for short date should work fine so click next and then finish.
- Try it out? Look at the table and the form. The table in datasheet view shows the input mask.
Forms
The table is sometimes referred as part of the backend of the database. The table is best left alone unless it is essential to make changes to it. It acts as the source of information in the database.
A form can be made and it can be used to add new records or locate old ones. It can also delete records if required.
Try out the form, you can search for records. Go to the last record etc.
TASK
Find two more songs you would like to add to the database. Add them to the My Favourite Music data doc in Google drive.
Add the two records to the database using the form
A form can be made and it can be used to add new records or locate old ones. It can also delete records if required.
- Go to the Create menu
- Choose Form Wizard
- Select the Music table as the forms source
- All records can be included
- Make the form
- Name it Music form
Try out the form, you can search for records. Go to the last record etc.
TASK
Find two more songs you would like to add to the database. Add them to the My Favourite Music data doc in Google drive.
Add the two records to the database using the form
Queries
Queries are used to create specialised lists from specific occurrences in the data in the database. For example, a query could find all the records of a specific genre. Or all the records of a particular singer. Queries with multiple criteria are also possible.
TASK
TASK
- Create a query the finds all records of your favourite music genre
- Create a query which finds all the records of your favourite singer in 2016
Wildcards
Use the Access help to find out what wildcards are and can do. Implement a wildcard in the last query. Did it make a difference?
An example of how to use wildcards.
An example of how to use wildcards.
Create a Lookup for the Genre field in the Music table
A table with one field can be created for the genre field in the Music table of the Favourite music database. Having a table for this allows us to quickly and easily change all the genre entries to a standard entry.
TASK
1. Firstly create the Genre table in your Favourite music database.
TASK
1. Firstly create the Genre table in your Favourite music database.
2. Enter the genres you want into the Genre table.
3. Open the Music table in Design View and click on the Lookup Tab in the Field Properties.
4. The choose the options to setup the Lookup table.
3. Open the Music table in Design View and click on the Lookup Tab in the Field Properties.
4. The choose the options to setup the Lookup table.
5. Check every record in your table has one genre only.
Reports
Access 2016 help (the ?) has some good videos to explain how to create a report.
Watch the video and create a report using grouping e.g. by genre, and artist.