ACCESS TABLES QUERIES OBJECTIVES Create tables Establish table
ACCESS TABLES & QUERIES
OBJECTIVES • • • Create tables Establish table relationships Create queries Specify criteria for different data types Compact & Repair Database 2
CREATE TABLES • Create fields in Design View • Import data from another database or application – Examples: Excel spreadsheets or Word text files • Enter data directly into rows in Datasheet views 3
FIELDS IN TABLES • Field names should be meaningful! • Access uses Camel. Case notation • Use uppercase letters for each first letter of each new word • Example: Product. Cost 4
FIELD DATA TYPES • Every field has a data type • Determines: – The type of data that can be entered – The operations that can be performed on that data • Access recognizes 10 data types 5
DATA TYPES Notice Yes/No and Lookup Wizard 6
EXAMPLE • Download the file European_Union database • Rename the file as lastname_firstname_eu. accdb. 7
LETS CREATE OUR FIRST TABLE • Create a table named Member. States • Fields are Member. ID and Member with data type as Text 8
9
ENTER DATA INTO TABLE 10
PRIMARY KEYS Designate appropriate primary keys • Member. States: Member. ID • Demographics: Member. ID • Economics: Country. ID 11
RELATIONSHIPS Create appropriate relationships between • Member. States and Demographics • Member. States and Economics • Enforce referential integrity and enable cascade updates, but do not enable cascade deletes. 12
QUERY 1 Create a query that lists the ID, full name, capital, year of entry, population, and political system of EU member states. • Run the query to see the unrestricted results. • Restrict the query to return records for only those member states that are Republics. 13
14
15
QUERY 2 -QUERY WITH OPERATORS Create a new query that lists the full name, GDP, employment rate, and population of member states. Only display states where the population is greater than 50 million and the employment rate is 60% or higher. Member states with GDP greater than 900 should be included irrespective of the population and employment rate. 16
17
18
QUERY 3 -FROM QUERY 2 • Create a copy of Query 2. Sort the results by GDP and then by name, both in descending order. 19
20
21
COMPACTING & REPAIRING A DATABASE • Access databases increase in size over time • Compacting – Avoids loss of data – Recovers unclaimed space – Defragments fragmented databases – Repairs corrupt databases
- Slides: 22