INF2070 Database 2

1. INF2070Database 2

2. What Will You Do? • Using skills learned in the last Database module, you will yet again make more tables • This time we are going to learn about Queries, which give us the ability to look up things • We will also learn more about Relationships • We will also learn how to create Reports that display the data we want to see

3. First Steps • Open up Access and click on Blank Database • We are going to create 3 tables. Use the steps shown below: • Call the tables Computers, Computer Techs, and Techs

4. Here are the categories in the 3 tables. Go to Design View to put in the categories

5. Next….Relationships • Don’t put anything in the tables yet! • You have a many-to-many relationship when a single record in one table can relate to many records in another, and a single record in that second table can also relate to many records in the first. For example, say your company has several types of computers and several technicians, with each technician certified to work on some, but not all, of the computers. Each technician can be related to more than one computer, and in turn, each computer can be related to more than one technician. • To track who can work on a given machine, you create a many-to-many relationship by adding the primary keys from both sides of the relationship to a third table, called a junction or link table. The picture shows a typical junction table, and you can see that its structure is relatively simple — a pair of primary key fields and some related information. • Now your data can answer questions such as, "Which computers did a given technician work on last month?"

6. Relationships • Open the Relationships tab and put in the tables. • Look where the Primary Keys are. The middle one doesn’t have one.

7. Primary Keys • If you forget how to get the primary keys, do this: Click where you want it to go, then click the primary key button

8. Make the tables have these Data Types: If you don’t do this just right, The relationships will not work Correctly later because you do Not have all the similar data Types to line up if you don’t.

9. Open the Relationships tab • Arrange your tables so they are in this order:

10. More… Grab the Tech ID primary key from The Techs table and drag it onto the Tech ID for the Computer Techs table Click This

11. Next Relationship Move the Computer ID Primary Key Over to the Computer Techs table Which does not have the Primary Key.

12. You should have this:

13. Now the fun part! • Under the Computers table And Techs table we get a drop-down for Computer Techs

14. Entries • Create 10 entries for each of the tables. All of the data has to be the same • I know its boring • But it is important for later! • Make up fake names and numbers to make it go faster. Make sure you use a variety of makes, like Sony, HP, Dell, Toshiba, Acer, etc. • For the serial numbers, they should be in the thousands, ranging from the number 1000 up to 8000.

15. Queries • Do you have questions that you want to answer with your data? • For example: • Who worked on which computer? • Which computer needed work? • What computer had the most value? • Which computer was the last one repaired?

16. Queries • Queries can answer those questions by assembling stored data from your database, or by performing calculations with the data to provide further information. • To answer questions, queries retrieve, filter, sort, and assemble data on command. Another important power of queries is to combine the data from several tables into a single view. • When a query finds data and shows it to you, it can also process that data according to your instructions. A query can perform calculations using the data: What is the sales total minus shipping costs? • A query can also remove data: Delete names of members who have not paid dues for 24 months. You should use caution when executing queries that change data, and consider backing up your data first.

17. First, Go Here:

18. Add a Query for The First Table

19. Down Below! • So what we want to do is to select the Table and the fields that we want to use from the drop-down menu. Use the fields listed below to make it look like this:

20. Look Here • In this case, we want to be able to look up a serial number under ‘Model’ that will show us which computer serial numbers that we made up are over 500. Just type > 500 right there.

21. Next • We want to look up any manufacturer that uses the letter S as the first part of their name. For example, Sony. We put what you see below in the table for Manufacturer.

22. Under DataSheet view • It tells me which computer starts with an S and is over 5000.

23. Hit Save • To save the table

24. Now we want to see our table Use an existing query as the record source of a form or report • In Design view, set the Record Source property to an existing query that you want to use. • Open the form or report in Design view. • If the property sheet is not already open, press F4 to open it. • In the property sheet, on the Data tab, click the Record Source property box

25. Next • Do one of the following: • Start typing the name of the query that you want to use. • Access automatically fills in the name of the object as you type. • - or - • Click the arrow and then select the query that you want to use.

26. Create a query as the record source of a form or report • In Design view, use the Build button (…) in the Record Source property box to create a new query to use as the record source. • Open the form or report in Design view. • If the property sheet is not already open, press F4 to open it. • In the property sheet, on the Data tab, click the Record Source property box. • Click .

27. Our Report • You should see something similar to this, depending on which information you included in your database:

28. Save your Report! • On the right-hand side of your screen, where all the tables and stuff are, right click on your report and see if you can print it!