TASK 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.
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.
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.
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.
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 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
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
Each object in an Access database can be opened in different views:
Table - Datasheet, Design
Form - Form, Layout, Design
Queries - Datasheet, SQL, Design
Report - Report, Print Preview, Layout, Design
Each view has a different purpose and can be used to change the object in some way. 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 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
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.
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.
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 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
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
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.
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.
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.
5. Check every record in your table has one genre only.
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.