Introduction to Access 2010

1. Introduction to Access 2010 CIS120first.accdb is the database I am creating.

2. I want you to use a different layout to design the table in the database.

3. I clicked on View and got prompted to name the table.

6. The first field is idno and I made the datatype text and the field size 5.

7. The second field is the empname and I made it text and changed the field size to 40.

8. The third field on the record is named jobcode and it is a text field that has a field size of 2 because the code is only 2 characters.

9. I have set up a forth field called numdep which I am going to make numeric and an integer. This shows the Data Type choices.

10. Once I pick Data type of Number I can chose a type depending on the numbers I want. I will use integer.

11. Now click on view and you can populating the table with data. For decimal numbers I use single for small decimals and double for large decimals like salary.

12. I have populated the table with data.

13. Now I click on Create. Next I click on Query Design. A query lets you specify fields and conditions that you want to use to access the table. It will return records that meet the query criteria.

14. Next I select the table by clicking on Add.

15. Now you see the fields in the table so you can select fields to use in your query.

16. Click on datasheet view to see results. I brought down four of the fields by double clicking on them (you can also drag). I want to see all IT employees so I put IT in the criteria line under the jobcode. IT is in quotes because it is a string or non-numeric literal.

17. Logic for query checking For jobcode = IT Jobcode = IT N Y Display

18. Click on the design icon to go back to design.

20. I named the query ITemp.

21. The query has been saved

22. Creating another Query.

23. You can also click on the arrow and choose the field.

24. I have now got two conditions in an AND relationship. The two conditions are put on the criteria line and both must be true for the record to be displayed.

25. LOGIC for two things in an AND relationship Jobcode = IT Y N Salary < 60000 N Y Display

26. The results

27. In this example, the criteria is that numdep is > 3 OR salary is > 55000. Since these are in an OR relationship, one criteria goes on the criteria line and the other criteria goes on the or line.

28. Logic for two criteria in an OR relationship. numdep > 3 N Y salary > 55000 Display Y N Display

29. The criteria is numdep > 3 OR salary > 55000. The first result has both true. The second result has just numdep > 3. The third result has just numdep > 3. The fourth result has just salary > 55000.

30. In this query the jobcode has to be IT and then in addition either the numdep has to be > 3 or the salary has to be > 55000. Since the IT has to be true it has to be on both the criteria line and the or line. Essentially you now have jobcode IT and numdep > 3 OR jobcode IT and salary > 55000.

31. Jobcode has to be IT and then either numdep has to be > 3 OR salary has to be > 55000. Jobcode = IT N Y numdep > 3 N Y salary > 55000 Display Y N Display

32. All the records have a jobcode of IT. The first record is IT and both numdep > 3 and salary > 55000. The second one is IT and jobcode > 3. The third one is IT and salary > 55000.