XP Microsoft Access 2007 New Perspectives on Microsoft

XP Microsoft Access 2007 New Perspectives on Microsoft Access 2002 Tutorial 1 1

Professor Information • • XP D Kris Morrison Work Number : 435 -1303 Home Number: 833 -4725 Email: morrisok@frankin. edu – I can not check email during the day. I will check email every evening and multiple times on the weekends. New Perspectives on Microsoft Access 2002 Tutorial 1 2

Course Assignments XP • All database assignments are due 72 hours after class. – Reason: All database assignments build on prior assignment(s) – For all database assignments, you can either use your prior assignment or my answer without penalty. • Papers are due at the beginning of class • There is a final project due the last week of class. New Perspectives on Microsoft Access 2002 Tutorial 1 3

Course Assignments XP • You must be able to email Access databases to and from whatever email account you use. – If you are using a work account, some business firewalls block databases. • You can test this by emailing one of the examples database to yourself. New Perspectives on Microsoft Access 2002 Tutorial 1 4

Course Assignments XP • I will check the dropbox Sunday morning and Sunday evening. When I see your assignment in the dropbox, I’ll send you my answer to the assignment. New Perspectives on Microsoft Access 2002 Tutorial 1 5

Course Assignments XP • I will send back the answer data base in four versions. • Access 2003 format extension ‘. mdb’ • Access 2007 format extension ‘. accdb’ • Access 2003 format extension ‘. doc’ • Access 2007 format extension ‘. doc’ New Perspectives on Microsoft Access 2002 Tutorial 1 6

Course Assignments XP • If you can receive the data base formats, you don’t need the ‘. doc’ format. • If you must use the ‘. doc’ format, you must rename it to have the correct extension before you can open it. New Perspectives on Microsoft Access 2002 Tutorial 1 7

Course Assignments XP • Any email I generate will be to your Franklin account. If you have your Franklin email forwarded, make sure that forwarding is valid. New Perspectives on Microsoft Access 2002 Tutorial 1 8

Course Assignments XP • We will be using the dropbox for all assignments. New Perspectives on Microsoft Access 2002 Tutorial 1 9

Course Assignments XP • If you must use email, the subject line for your assignments and papers must be: Comp 108 Last. Name. Assignment. Number For example: Comp 108 Morrison 1 -1 New Perspectives on Microsoft Access 2002 Tutorial 1 10

Course Assignments XP • All your assignments attached to your email must be named as Last. Name. Assignment. Number For example: Morrison 1 -1 New Perspectives on Microsoft Access 2002 Tutorial 1 11

Course Assignments XP If you email a question, please put the word “Question” in the subject line. New Perspectives on Microsoft Access 2002 Tutorial 1 12

Course Assignments XP • Please check what version of Access you are using. • I can only accept assignments done in Access 2000 and above. (Not Access 97) – There are too many changes between Access 97 and Access 2000. Many of the features we use are not supported in Access 97. New Perspectives on Microsoft Access 2002 Tutorial 1 13

The Kraig Kasper Rule XP • I do not expect anyone to spend over two hours trying to resolve an obvious software problem. • If you run into a software problem, document what you tried and send me what you have. • Software problem – Weird error message • Function doesn’t work or is not installed (especially on home/work machines) New Perspectives on Microsoft Access 2002 Tutorial 1 14

Introduction to Databases XP Definition : an organized collection of data New Perspectives on Microsoft Access 2002 Tutorial 1 15

Database Management Systems. XP • Usually abbreviated – DBMS • Complex software that maintains the data inside the database and the relationships among the data New Perspectives on Microsoft Access 2002 Tutorial 1 16

Types of DBMS XP • Different organizations – Hierarchical (IBM – Information Management System) – Relational (Oracle, IBM – DB 2, Microsoft – Access) New Perspectives on Microsoft Access 2002 Tutorial 1 17

Relational Databases XP • Theory behind databases is mathematically rigorous. • We will not be going into any theory. • If you are interested, the original paper by E. F. Codd is available online at http: //www. acm. org/classics/nov 95/toc. html New Perspectives on Microsoft Access 2002 Tutorial 1 18

Why use a database? XP • A “flat file” contains redundant information. An example of this would be a table that has a list of borrowers with name and address and other information listed for each book that person has borrowed. • You have numerous changes. Correcting the errors in multiple places in a spreadsheet can be time-consuming. In a relational database the change is made in one location and all other documents will incorporate that change automatically. • You have a very large file. A relational database is designed to handle large amounts of related information easily. New Perspectives on Microsoft Access 2002 Tutorial 1 19

XP Define some key database terminology • Field – A single characteristic or attribute of a person, place, object, event, or idea. • Record – A set of related field values. • Table – A collection of records that identify a category of data, such as Customers, Orders, or Inventory. New Perspectives on Microsoft Access 2002 Tutorial 1 20

Basic Table Types XP • A who table - Your who table contains all of the information about the "whos" in your database. These would be your customers, your students, your buyers and whomever you are doing business with. • A what table - Your what table contains all of the information about the "whats" in your database. This is your product, your services, your classes or whatever it is that you are selling or providing for business. • A tie together table - Your tie together table is the result of your activity in your business. This is your order table, your roster table or whatever your activity is going to be that ties your who and your what together. When we talk about designing your tables, we will discuss in more detail exactly what should and should NOT go into these tables, as well as how to tie them together. New Perspectives on Microsoft Access 2002 Tutorial 1 21

An additional basic table XP • A details table – Depends upon the business – Most obvious example - Invoices New Perspectives on Microsoft Access 2002 Tutorial 1 22

Microsoft Access XP • Relational Database • Everything in Access is an object – – – – Tables Queries Forms Reports Pages – (not covered) Macros – (only very simple macros are covered) Modules – (not covered) New Perspectives on Microsoft Access 2002 Tutorial 1 23

XP Naming Conventions • Component name standards are that you should preface the name of the component with the type of component it is, in an abbreviated format. • Examples include: tbl. Customer as the name for your customer table, qry. Customer. List for the query that runs your customer list. The abbreviations for each component are listed here: – – – tbl = table qry = query frm = form rpt = reports mac = macro New Perspectives on Microsoft Access 2002 Tutorial 1 24

Naming Conventions XP Object Possible object names Table tblcust. Last. Name Tbl_Cust_Last_Name Form frm. Netcust Frm_New_Cust Query qry. Out. Books Qry_Out_Books Report rpt. Fines. Due Rpt_Fines_Due Macro mac. Monthly. Rept Mac_Monthly_Report New Perspectives on Microsoft Access 2002 Tutorial 1 25

Compacting a database XP • It is a good idea to periodically compact and repair a database to recover wasted space created by adding, deleting, and modifying records. • To set compact on close: – Click on the Office Button – Click on Current Database – Check Compact on Close New Perspectives on Microsoft Access 2002 Tutorial 1 26

Compacting reduces database storage size XP Compacting a database reduces the space used by the database, making the space available for other uses. New Perspectives on Microsoft Access 2002 Tutorial 1 27

Compacting reduces database storage size XP • Because we will be sending databases as email attachments, it is very important to compact the database. • I can often tell which databases are compacted because of the size. I will deduct points for this EVERY TIME. New Perspectives on Microsoft Access 2002 Tutorial 1 28

Assignments XP • Assignment 1 -1 – Lending Library – 40 points Due: 72 hours after class • Assignment 1 -2 – Referential Integrity Paper – 40 points Due: Start of class April 17 (NOT April 10) New Perspectives on Microsoft Access 2002 Tutorial 1 29

Turnitin. com XP • Numbers you will need – Class ID 2194819 Password comp 108 w 8 • You will need these numbers again at the end of the class. • If you have not used turnitin. com, there is a tutorial at http: //cs. franklin. edu/~varnerp/Turn. It. In. How. To. doc New Perspectives on Microsoft Access 2002 Tutorial 1 30

Referential Integrity Paper XP • What is referential integrity? – Relational Database definition- A relational database is a collection of tables that are related to one another based on a common field. • Referential Integrity – rules maintained by the DBMS to guarantee that relations are valid New Perspectives on Microsoft Access 2002 Tutorial 1 31

Referential Integrity Paper XP • Two examples of excellent papers are available at cs. franklin. edu/~morrisok/comp 108/Example Paper 1 a. doc cs. franklin. edu/~morrisok/comp 108/Example Paper 1 b. doc New Perspectives on Microsoft Access 2002 Tutorial 1 32

APA Guidelines XP • Information is available at: http: //www. franklin. edu/students/library/citeanddo c. html New Perspectives on Microsoft Access 2002 Tutorial 1 33

APA Guidelines XP • "APA style" is a set of specific citation formatting conventions sanctioned by the American Psychological Association. New Perspectives on Microsoft Access 2002 Tutorial 1 34

APA Guidelines • • • XP Do not have sources in your reference or works cited page unless they are cited in the text of your paper. Always provide a citation for anyone else's work you paraphrased or quoted. Always provide a page number for any quotations you use. New Perspectives on Microsoft Access 2002 Tutorial 1 35

APA Examples XP • Go to webpage. New Perspectives on Microsoft Access 2002 Tutorial 1 36