1 / 44

CHAPTER 1 – INTRODUCTION TO ACCESS

CHAPTER 1 – INTRODUCTION TO ACCESS. Akhila Kondai akhila.kondai@mail.wvu.edu October 07, 2013. Introduction.

ronia
Download Presentation

CHAPTER 1 – INTRODUCTION TO ACCESS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CHAPTER 1 – INTRODUCTION TO ACCESS AkhilaKondai akhila.kondai@mail.wvu.edu October 07, 2013

  2. Introduction • A database consists of one or more tables to store data, one or more forms to enter data into the tables, and one or more reports to output the table data as organized information. • The Navigation Pane organizes and lists the database objects in Access database. • The Datasheetview is where you add, edit, and delete the records of a table. • The Design view is where you create tables, add and delete fields, and modify field properties.

  3. Introduction • A Primary Key is the field that uniquely identifies each record in a table. • A Form is an object that enables you to enter, modify, or delete table data. • A Query is a question that you ask about the data in the tables of your database. • A Criterion is a number, a text phase, or an expression used to filter the records in a table. • A Report contains professional looking formatted information from tables or queries.

  4. Opening a Table • Download the file Bookstore.accdb • In the Tables areas of the Objects Bar, • Double-click Books • Notice a table with 22 records showing in Datasheet view

  5. Looking in Datasheet View • Datasheet view shows a table’s contents in rows and columns like a spreadsheet would • Each Field Name is inside a clickable “Field Selector” that will highlight that column • Each row has a clickable “Record Selector” to its left…

  6. Table in Datasheet View Field Names & Selectors Records Navigation

  7. Navigating a Datasheet • The current record is shown with an amber highlight • Navigation buttons at the bottom of the table window allow for moving around the table easily…

  8. Navigation Bar

  9. Adding Records • Click the “New Record” button at the bottom • Enter5-555555-55-5 for the ISBN Number • Enter “Access is Your Friend” for a title • Enter your last name as an Author… • Put 2010 for the year • Make the book 95.00 • Make the publisher: Que Publishing

  10. Editing a Record • Let’s say that a price changed on the “Presentation Design Book” • Change the price from $31.95 to $42.95

  11. Deleting a Record • Delete Cobol book from Prentice Hall . • Right click the record selector and then select Delete Record. • Confirm the “Permanent” deletion. • Close the table clicking its “X”…

  12. Managing Fields OF BOOKs table • View -> Design view • Click on last empty cell of FIELD NAME and give one field name and its appropriate DATA TYPE. • Note: You can observe the Field properties at bottom • Switch to DATA SHEET view and observe the new Field added to the records.

  13. Forms Form– Object one uses to add, maintain, & view records in a database • Gives a different view to do this in than from Datasheet View • Easier to use for less knowledgeable people…

  14. Using a Existing Form Forms make it easy for anyone to work with information in a table in a simple interface… • Under “Forms” in the object bar, • Double click Books • Click the last record button to see your entry !

  15. Adding/DELETING/FINDING Records with Forms • Use the controls to return to the first record • Note there being 22 records • Create a new record ( ISBN ‘6-666666-66-6, Year 2010 and give other details also) • Find the record with ISBN Number ‘0-07-070318-3’ • Delete this record

  16. Using an Existing Report Report – Nicely formatted display of information suitable for printout • Close the form only • In Reports object bar area Double click All Books • Find your two entries under the appropriate publishers

  17. Queries • The idea is to save the time of having to manually look through a myriad of records to try and find the information needed • Let’s take a look at an existing query…

  18. Queries • A Query is a question one asks about the data stored in a database • Access responds by displaying specific records that answer the question • In creating a query, we tell access which fields are needed and what criteria needs to be met

  19. An Existing Query • Close the report • In the Queries area of the objects bar, • Double-click Publication Year • Enter 2010 and click OK • You should see your two records.

  20. Compact and repair • Reduces the size of the database • Perform this after you done working with access database. • File->info->Compact and repair database

  21. Get the Second Database • Close the Bookstore database • Download and open the file Look_ahead.accdb * Premise: We are running a company and this database tracks information on our employees

  22. Filters • Give a temporary view of desired data to help isolate portions • Filter by Selection – lets us specify an example • Filter by Form – more powerful, can search for records meeting multiple conditions and by operators such as: <, >, <=, >=

  23. Filter by Selection Example Say we wish to isolate our employees with poor performance… • Open theEmployees table • Click in one of the field values containing Poor • Click Selection in the Sort & Filter group • Select Equals Poor • Click Toggle Filter to turn off

  24. Filter by Form Example Say we want to see all Females making over $40k… • Select Advancedin Sort & Filter group • Choose Filter By Form • Remove Poorfrom performance • Select F for Gender • Enter >40000 for Salary • Click the Toggle Filter button • Review the results • Toggle the filter off

  25. Sorting on a Single Field Sorting allows us to arrange the way the table data looks… • Click in a record under the Last Name field • Click the Sort Ascending button • Next, sort ascending on Salary

  26. Sorting on Multiple Fields • Fields must be side by side to do this • Click and Drag to highlight both the LastName and FirstNamefields • Sort Descending (Note the Smith’s first names) • Do it again Ascending

  27. Databases & Relationships • A Database is a collection of “Related” tables • This can also be called a “Relational Database” • A Common Field between tables is what allows a relationship to exist. This works by way of “Primary” and “Foreign” keys…

  28. Primary Keys • Primary Key – Field in a every table whose field values are unique for every record • Examples might be things like: • Student ID numbers • SSN number • Cell Number • Anything that will not have duplicates as additional records are entered

  29. Relationships • Foreign Key– Similar/Same field in the second related table in the relationship • Primary key field in relationship will have similar data type and contentto foreign key field • The two tables are joined together on the primary and foreign keys and form a Common Field.

  30. Relationships • The link formed through this Common Field which is dubbed in one case as a “primary key”, and in another as a “foreign key”, allows tables to share data • Note that the “Look Ahead” database has 3 tables, but we will only look at 2 of them..

  31. 1: Employees Table… EmployeeID is the Primary Key as all numbers are unique for employee records

  32. 2: Locations Table… LocationID is the primary key and each record has a unique LocationID designator. This will tie back to the Employees table.

  33. Relationships • Databases are all about efficiency and not having to store the same information more than once if multiple records need it • Types of Relationship: • One to One • One to Many • Many to Many

  34. Relationships • In this example, we know that multiple people work at the same office address. One address can have many related Employees (One to Many)… • Instead of typing the same address in each record for every Atlanta office employee: “450 Peachtree Road…” • We can assign a simple relationship to a “Locations” table and reference this information to a given location with a short common code of “L01” between the tables…

  35. One to Many Relationship (One instance here) (Many here) Typing “L01” when entering an employee and having it equate to the entire address saves MANY characters in databases as it is reused over and over. “L01” is 3 characters and the entire address is over 30 !

  36. Making the Relationship These tables need to be tied together (related) to work this way… • Open the Relationships Window • Database Tools ribbon > Relationships • Click Show Table • Double clickEmployees &Locations, then Close

  37. The Relationships Window • The Relationship window depicts included table structures and their associations • Within the structures, we see each table’s fields • Primary keys are shown with a key icon

  38. Making the Relationships • Drag “LocationID” from the “Locations” table to “LocationID” in the “Employees” table. • Notice the “One-To-Many” reference • Click to Enforce Referential Integrity • Click Create…

  39. Note the One to Many (1 to infinity symbol) that formed on the join line… Close and save the relationship Run the “Employee Information” query to see data pulled together from both tables… Relationship Formed (Relationship Primary Key) (Relationship Foreign Key) Note, the relationship we made was already created in the query we ran and we did this only for increased understanding of how to create relationships.

  40. Query Results Information is pulled from the “Employee” and “Locations” tables through their relationship and shown united within the query results to give us a complete picture of the information ! The LocationID field was used behind the scenes to pull it all together with the L01 value The full address is actually only stored (taking up space) once in the database, and we are able it multiple times as needed.

  41. Referential Integrity • Enforce reference integrity : You cannot enter a foreign key value in a related table unless the primary key value exists in the primary table. And you cannot delete a primary key value if there is a foreign key referencing that primary key. • Example : Here you cannot enter location information into Employees table unless the location information is first entered into the Locations table. Also you cannot delete records from Locations table if there are any related records for that location in the Employees table

  42. Referential Integrity • Cascade Update / Delete Related Records

  43. How to delete relationship between fields? • Click on the line between the fields (which shows relation) and press delete key.

  44. Questions ?

More Related