1 / 88

ACG 4401

ACG 4401. Access 2007 Tables Part I Datasheet vs. Design View. Creating the Database. Start Access Getting Started Page Appears Choose Blank Database Type in filename Extension will be .accdb Choose your folder Click Crate Button. Backing up the Database. Office Button Manage

melyssa-jennings
Download Presentation

ACG 4401

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. ACG 4401 Access 2007 Tables Part I Datasheet vs. Design View

  2. Creating the Database • Start Access • Getting Started Page Appears • Choose Blank Database • Type in filename • Extension will be .accdb • Choose your folder • Click Crate Button

  3. Backing up the Database • Office Button • Manage • Back Up Database

  4. What the database file contains? • Tables • New database starts with one (blank) • Forms • Reports • Queries • Macros • Modules

  5. Creating Tables • Datasheet View • Fast and Easy • Similar in Appearance to Excel • Columns become fields • Drawbacks • Data types are “guessed” by Access • Design View • Full Control over each field in your table • Full control over Data Types • Full control over other Field Properties

  6. Datasheet – Creating Table • Select your table (or create a new one) • Enter data in each column • Use a different column for each field • Add Column Names • These will be the names of your fields in your table • Double-click the column heading and edit • Save your table

  7. Multiple Datasheets • Each table will appear as a Tab (Access 2007) • Each table will appear as a separate Window (Access 2003 or earlier)

  8. Design View • Complete Control over the structure of your Tables • Fields/Attributes • Data Types • Other Data Properties • Used to: • Add Fields • End of Table • Move to last row of field list • Between Fields • Move to the row below where you want the new field • Right-click, Choose Insert Rows • Rearrange Fields • Just drag and drop • Delete Fields

  9. Fields Descriptions • Description will appear in the Status Bar • Control: Provide prompting/feedback to users

  10. For Each Field • Enter Field Name • Enter Data Type • Enter Description (optional) • Enter/Change Data Type Properties

  11. What are the Data Types • Text • Memo • Number • Currency • Date/Time • Yes/No • Hyperlink • Attachment • AutoNumber • OLE Object

  12. Text • Any Character accepted • Limited to 255 characters • ~ 1 paragraph • Properties: • Text Length • For most fields use 50 • If you know exact length (e.g. SS#) use the exact length (e.g. 9)

  13. Memo • Used to store a large amount of Unformatted text • 65k worth of data • ~ 1 average book chapter • To Edit large blocks of text (or anything) • Shift-F2

  14. Number • Field Size depends on Number Type • Byte • 0-255 • No decimals • Integer • -32,768 – 32,768 • No decimals • Long Integer (Access Default) • -2,147,483,648 – 2,147,483,648 • No decimals • Single (Best choice for decimals) • +/- up to 38 zeroes • Decimals to 7 places • Double • +/- up to 308 zeroes • Decimals to 15 places • Decimal • +/- up to 28 zeroes • Decimals to 28 places

  15. Currency • Most Efficient for Financial Calculations

  16. Date/Time • Access stores the Date and Time • Choose how it’s Displayed in the Format Field

  17. Yes/No • Any field that can be one of two states • Yes/No • On/Off • True/False • Male/Female

  18. Hyperlink • URL • File Address • Email Address

  19. Attachment • Access Stores the file inside the database • Pictures • Music Files • Word Files • Excel Files

  20. AutoNumber • Remember Primary Keys • Access automatically creates this field type when you crate a new Table • Unless: Create>>Tables>>Table Design • Default is sequential numbering • Two Options • Random # • Replication ID • Guaranteed to be unique • Useful if database is distributed but will later be merged

  21. Access 2007 Tables Part II Data Input Controls

  22. Input Control • From Design • Format the Datasheet • Home>Font • Gridlines • Every other Row Color • Fonts • Text Color • Background

  23. Data Entry Controls • Preventing Blank Fields • Required Property • Allowed Zero Length • Preventing Duplicate Values • Default Values • Input Masks • Validation Rules • Lookups

  24. Preventing Blank Fields • For any required field • Change Required Property to “Yes” • But Access considers “ “ to be data • Change Allow Zero Length Property to “No” • This also works for number field types if you want do not want to accept “0” as a number

  25. Preventing Duplicate Values • Indexed Property • Sorts a field in your table • Improves Performance when searching/querying • Three Options • No (Default) • Yes [Allow Duplicates] • Yes [No Duplicates]

  26. Default Values • Eliminates Need for User Input • Use Default Value Property • Same data is usually entered • For Example, Orlando for city • Dynamic default values • Access inputs the data but it changes based on condition • Date() Today's date is entered • Text default values must be enclosed in “”

  27. Input Masks • Enforce data value pattern • Phone Number • (407) 823-5739 • (999) 000-0000 • Social Security Number • 111-23-7456 • 999-99-9999 • Inventory Item Number • MB678-Z123 • >LL999->L999

  28. Input Mask • Open table in Design View • Select field that will have pattern • Choose Input Mask Property • Click on the small ellipse ... • Choose from the Access provided Masks or Create your Own • Choose how to store the Data • Store only the data • 4078235739 • Store the data and extra characters • (407) 823-5739

  29. Creating Your Own Mask • Placeholders • Where should the user input data, and what type of data is allowed • Special Characters • How to treat data that is entered in the mask • Literals • Formatting for the mask • () in phone number, - for SS #

  30. Placeholder Characters

  31. Special Characters

  32. Field Validation Rules • Restrict data allowed • If data matches rule it can be entered • If data does not match rule Access keeps it out • Applying Validation Rule • Open table in Design View • Select field • Go to Validate Rule Property • Type Expression • Type Error Message in Validation Text Property

  33. Writing Expressions • Validating Numbers • <, >, <>, <=, >=, =, Between • Validating Dates • Dates must be entered between ## • #05/23/2008# • <, >, <=, >= • Date Functions • Date () Today’s date, one second after midnight • Now(0 Today’s date and current time • <, > • Validating Text • Starts with • Ends with • Contains • Like Operator and * wildcard or ? Single character • Combining Validation Conditions • And • Or

  34. Validating Text Examples • Start of text • Like “ACG*” • End of text • Like “*4401” • Contains text anywhere • Like “*A*B*” • Contains text specific place • Like “????7Y2” • if 1st 4 characters are text • Like “####7Y2” • if 1st 4 characters are numbers • Restrict character to certain letters or symbols • Like “[AE]*” • Must begin with A or E • Like “[ABCD]*” or Like “[A-D]*” • Must begin with A, or B, or C, or D

  35. Table Validation Rules • Allows comparison of multiple fields from a table • For example: • Ship Date can NOT be before Order date • Validation Rule = Order Date < Ship Date • To create table validation rule • Open table in Design View • Table Tools > Design > Show/Hide > Property Sheet • Set Validation Rule • Set Validation Text • field names must appear between [] • [DateOrdered] < [DateShipped] or • [DateOrdered] <= [DateShipped]

  36. Lookups • Helps to standardize data • Enter a value by choosing from a list • Two Types • List has fixed set values • List comes from another table

  37. Fixed Lookup Lists • Open Table in Design View • Go to field which will have lookup • Change Data type to Lookup Wizard • Choose “I will type in the values that I need” • Enter the values • Choose if lookup can store multiple values

  38. Lookup List Property • Disable user ability to add to or override list • Set the Limit to List property = “Yes” • Allow list modification • Set the Limit to List property = “No”

  39. Access 2007 Tables Part III Linking Tables

  40. Defining a Relationship • Each relationship links two tables • Primary Key from one table • Foreign key from another table • Primary key value from 1st table • Choose: • Database Tools > Show/Hide > Relationships • Add tables that participate in relationship • Drag the field from one table (the one side) to the related field in the other table (the many side) • Edit relationship • Set Enforce Referential Integrity • Prevents Insert, Deletion or Update anomalies • Cascade Deletes • You don’t want to do this, but... • It deletes every record in all related tables • Cascade Updates • Any changes made in a link field (the one side) are automatically made in the linked field (the many side)

  41. Lookups with Related Tables • 2nd Lookup Option • Data values come from related table • You must remove any defined relationship before doing the lookup • Same steps as Lookup described earlier • Choose “I want the lookup column to look up values in a table or query” • Choose the table • Choose the field for the lookup • Choose a field that describes the lookup field • Choose a field to sort • Choose “Hide Key Column” • Name the lookup • Finish

  42. Access 2007 Queries

  43. Types of Queries • Select • Display data that matches criteria • Action Queries • Append data • Delete data • Update data • Matches criteria • All Queries are Objects stored in the database

  44. Steps to Create Select Query • Create > Other > Query Design • Select table(s) • Select fields you want displayed • Double click or • Drag • Choosing * field will select all fields • Arrange fields from left to right • Best done by selecting in order you want • But you can drag fields around to re-arrange • To Hide a Column • Uncheck the Show checkbox • Choose a field to sort by • Create your Criteria • Run Your Query • Save the Query

  45. Join Queries • Combine two related tables • Primary key • Foreign key • Just add two (or more) tables to your Query Design • Create Relationship • If relationship doesn’t already exist • Everything else is the same.

  46. Inner Joins vs Outer Joins • Inner Joins • Only shows linked records • Each Inventory item related to a Vendor • Each Sales order related to a Customer • Outer Joins • Show linked records AND • Other records from one of the two linked tables • All Sales order that match a criteria based on Customer AND remaining Customers not linked to a Sales Order

  47. Creating Outer Joins • Right Click Join line in Query • Choose: • Only choose rows where joined fields from both tables are equal (inner join) • Include All records from “xxxxx” table and only those records from “yyyyy” table where joined fields are equal (outer join) • Include All records from “yyyy” table and only those records from “xxxx” table where joined fields are equal (outer join)

  48. Creating a Calculated Field • Two Parts: • Calculated field name: Expression • Using field names for the table(s) • Enclose in [] and • [TableName.FieldName] • [Customer.FirstName]

  49. Math Expressions • + • - • * • / • ^ - Exponentiation 22 • \ - Integer Division 5/2 = 2 • Mod – Modulas 5/2 = 1 (the remainder)

  50. Writing Expressions • Long Expression? • Shift-F2

More Related