1 / 14

Access 101

Access 101. Using Microsoft Access to create database tables and relationships. A quick look at an Access Database. In the last lecture, we looked at a talking spreadsheet called “ Guate Tours.xlsm”. Here is the student’s Access database from which I made the spreadsheet.

sloan
Download Presentation

Access 101

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. Access 101 Using Microsoft Access to create database tables and relationships

  2. A quick look at an Access Database • In the last lecture, we looked at a talking spreadsheet called “Guate Tours.xlsm”. • Here is the student’s Access database from which I made the spreadsheet. • GuateTours.accdb is available in today’s lecture materials.

  3. Pay no attention! • When I open the file, notice the security warning below the ribbon. • You can ignore these for now, but when it the time comes to run action queries (insert, update, delete), you will need to enable this content.

  4. The Database • When you open the database, you should see this:

  5. Tables • As I said in the previous lecture, a relational database is a collection of tables and the relationships between them. • Here is the collection of tables. • Double-click where it says “Tours : Table” • The Tours table appears, looking not too different from what you’ve seen in Excel. • This is called the “Datasheet” view of the table.

  6. Datasheet View of Tours Table • Note the category names across the top: TourID, TourName, Origin, etc. • These are the fields (aka attributes, aka columns). • Each horizontal row below the field names is a record (aka tuple, aka row).

  7. Datasheet View • In this datasheet view, you can make changes to the data—inserts, updates, and deletes. • To insert a row, start typing data in the blank row at the bottom—the one with the asterisk (*). • To delete a row, select it by clicking in the light-blue column at the left of the row and then hit the delete key. • You can update (change particular values in the data) just as you do in Excel; click in the cell and start typing/deleting/editing.

  8. Table Views • In the Home tab of the ribbon, the left-most button says “View.” • The down arrow will show you your choices: Datasheet View, Pivot Table View, Pivot Chart View, and Design View. • For this class, we will only use Datasheet View and Design View. • When in Datasheet View, the button defaults to Design View, and vice versa. This means that to switch from one to the other, you only need to click on the View button (ignoring the down arrow).

  9. Design View • So—switch the Tours table to Design View. • It should now look something like this:

  10. Design View Features • Note that each field has a name and a data type. • Note that clicking on a field’s selector (blue square on the left) will change the info displayed below. • Note that clicking one of the data types will cause a combo box to appear, offering several data type choices. • Data types can be further refined on the general tab below.

  11. Primary Key • Note the little key icon next to TourID • This indicates that TourID is the primary key for this table.

  12. Rules for Table and Field Names • No Spaces! Access will allow you to name a table “My Company’s Employees”, but this causes many problems down the road, especially when interfacing with VB. • “Employees” would be a much better name • The whole database probably relates to “My Company”, so there’s no need to include that in the table name. Even if there were—NO SPACES!!!!!

  13. Rules for Table and Field Names • No punctuation: Most DBMS’s (except for Access) won’t allow any punctuation in the name of a field or table except the underscore (_). • I’m not a fan of underscores, either—they tend to be obscured by hyperlinks and such. • For this class, if your table or field needs a multi-word name, use InteriorCapitals.

  14. Access Keywords • Access has many keywords with special meaning that should not be used as field or table names. • Highlighted words are the ones most likely to cause trouble when trying to name tables and fields in Access: ·         ADD ·         ALL ·         Alphanumeric ·         ALTER ·         AND ·         ANY ·         Application ·         AS ·         ASC ·         Assistant ·         AUTOINCREMENT ·         Avg ·         BETWEEN ·         BINARY ·         BIT ·         BOOLEAN ·         BY ·         BYTE ·         CHAR, CHARACTER ·         COLUMN ·         CompactDatabase ·         CONSTRAINT ·         Container ·         Count ·         COUNTER ·         CREATE ·         CreateDatabase ·         CreateField ·         CreateGroup ·         CreateIndex ·         CreateObject ·         CreateProperty ·         CreateRelation ·         CreateTableDef ·         CreateUser ·         CreateWorkspace ·         CURRENCY ·         CurrentUser ·         DATABASE ·         DATE ·         DATETIME ·         DELETE ·         DESC ·         Description ·         DISALLOW ·         DISTINCT ·         DISTINCTROW ·         Document ·         DOUBLE ·         DROP ·         Echo ·         Else ·         End ·         Eqv ·         Error ·         EXISTS ·         Exit ·         FALSE ·         Field, Fields ·         FillCache ·         FLOAT, FLOAT4, FLOAT8 ·         FOREIGN ·         Form, Forms ·         FROM ·         Full ·         FUNCTION ·         GENERAL ·         GetObject ·         GetOption ·         GotoPage ·         GROUP ·         GROUP BY ·         GUID ·         HAVING ·         Idle ·         IEEEDOUBLE, IEEESINGLE ·         If ·         IGNORE ·         Imp ·         IN ·         INDEX ·         Index, Indexes ·         INNER ·         INSERT ·         InsertText ·         INT, INTEGER, INTEGER1, INTEGER2, INTEGER4 ·         INTO ·         IS ·         JOIN ·         KEY ·         LastModified ·         LEFT ·         Level ·         Like ·         LOGICAL, LOGICAL1 ·         LONG, LONGBINARY, LONGTEXT ·         Macro ·         Match ·         Max, Min, Mod ·         MEMO ·         Module ·         MONEY ·         Move ·         NAME ·         NewPassword ·         NO ·         Not ·         Note ·         NULL ·         NUMBER, NUMERIC ·         Object ·         OLEOBJECT ·         OFF ·         ON ·         OpenRecordset ·         OPTION ·         OR ·         ORDER ·         Orientation ·         Outer ·         OWNERACCESS ·         Parameter ·         PARAMETERS ·         Partial ·         PERCENT ·         PIVOT ·         PRIMARY ·         PROCEDURE ·         Property ·         Queries ·         Query ·         Quit ·         REAL ·         Recalc ·         Recordset ·         REFERENCES ·         Refresh ·         RefreshLink ·         RegisterDatabase ·         Relation ·         Repaint ·         RepairDatabase ·         Report ·         Reports ·         Requery ·         RIGHT ·         SCREEN ·         SECTION ·         SELECT ·         SET ·         SetFocus ·         SetOption ·         SHORT ·         SINGLE ·         SMALLINT ·         SOME ·         SQL ·         StDev, StDevP ·         STRING ·         Sum ·         TABLE ·         TableDef, TableDefs ·         TableID ·         TEXT ·         TIME, TIMESTAMP ·         TOP ·         TRANSFORM ·         TRUE ·         Type ·         UNION ·         UNIQUE ·         UPDATE ·         USER ·         VALUE ·         VALUES ·         Var, VarP ·         VARBINARY, VARCHAR ·         WHERE ·         WITH ·         Workspace ·         Xor ·         Year ·         YES ·         YESNO

More Related