ICT T1 2011

1. ICT T1 2011 Introduction to Databases with Microsoft Access

2. What Are Data Bases? MuchInformation comes out. Hundreds Facts stored and converted from Data into information of facts go in

3. Will I Create My Own Data Base? • Yes! • You will create your own data base! • First you need to think about what facts it will contain. • Then you think about what information you want it to give you. • Then you create various data base OBJECTS that the data base needs, to work correctly, and populate it with data. • Right-click and save the Movie Database from the ICT website

4. What Do Data Bases Look Like?

5. What Are The Parts of a Data Base? An Access Database has Four Objects and much Data REPORT Arranges the Information for people to read. TABLE Stores facts as data in records. FORM Gets data into & out of the table. QUERY Asks questions of the table.

6. What Are Tables?

7. What Is Data? • Data is lots of facts about many items. • The facts or data about each item all together are called a record • Each record has many different bits of data about it • Each bit of data is in a different field • There are different types of fields – text, currency, numbers, etc • Each record has the same fields, but with different data in each one • The important words are: DATA, FIELD, RECORD, FIELD-TYPE.

8. What Are Forms? • Forms may be of different shapes and sizes, but they all: • look at the data in the table(s) and • can put new data into tables.

9. What Are Queries?

10. What Are Reports? • This Report: • Can be sent to the printer. • Will tell people about the results from the query.

11. How Do Tables, Reports, Queries and Forms Work Together?? Tables obtain their data directly, or from Forms, or from Queriesand then STORE the data as records. Forms give data to Tables, and get Information from Tables Reports obtain their information either from Table(s) or from Queries TABLE REPORT FORM QUERY Queries give data to Forms, Tables and Reports, and get it from Tables

12. Using a database Save a copy of theUsingDatabasesWorksheet and call it YourNameMovieSearch. Use the Movie database to find the answers and type them into the boxes provided.

13. How Do We Start Our First Data Base? • Consider the problem and analyze the situation. • Your task is to solve the information problem by creating a database for the people in the story. • Ask your self two questions about the information problem. • What INFORMATION do the people need to get out of the database? • What facts or DATA will you need to put into the database? • Create a TABLE to hold the data you will be putting in.

14. What’s the problem? • Anna owns an antique market from which she sells old things. • In the market she has 6 stalls, each with different types of Antiques for sale (clothing, furniture, jewelry, toys, books, watches). • Anna wants to keep track of the various items she has for sale, so that she can help people who ring up enquiring. • Each item originally cost a certain amount. • Each item will be sold for 30% more. • Your job is to create a database of Anna’s stock.

15. What Data Will It Contain? • Each item will be a RECORD with a number of facts about each one. • For each record you need to know the following facts: • what the item is, • what group it belongs to, • what it cost Anna.

16. What Data Fields Will We Need? • Each item will be a RECORD. • For each record you need to know the following: • what the item is, • a Description of the item, • what group it belongs to, • what it cost Anna. A Unique ID Numberfor each item A Name for the item A Description A Group Name The Original Cost

17. Setting up your database • Open a new database and save it into your IT folder called ‘Access’. • Call the database ‘YourNameAnna’.

18. Then choose ‘Create table in Design View’. How Will We Set Up Our Table? • Name your table: tblStock • !!When naming records and fields never use spaces!!

19. How Do We Set Up ‘Fields’? Type in the first field name “stockID”, set its data type as Autonumber – select from the drop down menu. Type in the second field name “name”, set its data type as text – use the down arrow for this.

20. How Do We Create ‘Look-Ups’? Type in the third field name “group”, set its data type as ‘Lookup Wizard’. Choose “I will type in the values I want”, then insert clothing, furniture, jewelry, toys, books, watches Using the down arrow on your keyboard to get to the next line.

21. How Do We Make A ‘Cost Field’? Type in the fourth field name “cost”, set its data type as ‘Currency’. Type in the fifth field name “notes”, set its data type as ‘Memo’ – this will be for your description of the item..

22. How Will We Populate Our Table With Data? • You simply invent data for your table. • Enter at least 20 items – include every group type more than once. • You will need a lot of data so that you can manipulate it into information, so go to it!

23. What Information Can We Get From The Table Of Data? You can SORT the information for example.

24. How Will We Create Our Queries? Choose the Queries object, then ‘Create Query in Design View’. Choose ‘tblStock’ from the available tables & queries. Double-click the fields you want in your query. Type in your ‘Criteria’ and the sorting you want.

25. What Different Types of Queries Can We Create? • You can add a ‘calculate field’ to the query • Set it out like you see here • We are calculating what Anna will sell the antique item for by multiplying the cost by 1.3 • When we run the query we can see the selling prices for each item in the furniture group.

26. How Will We Create Our Forms?

27. How Will We Create Our Reports?

28. Anna’s Ancient Antiques