Essential Access - University of York
[Pages:40]Information Services
Essential Access
Book 1: Parts 1-4
IT
york.ac.uk/it-services
Microsoft Access is a component of Microsoft Office, available on all IT Services managed computers at the University. This material has been written to be used with Access 2016/2019 on a University of York PC. Every attempt has been made to ensure the accuracy of the information provided, however you may find some minor differences when working with personalised systems or other versions.
Last Updated: January 2020
~Contents~
Part 1: Understanding Databases ...............................................................................1 1 ~ Why Databases? ..........................................................................................................1
1.1 - Data Structures .......................................................................................................... 1
2 ~ Data tables in MS Access ..........................................................................................3
2.1 - Table datasheet view ................................................................................................. 4
3 ~ Relationships................................................................................................................5
3.1 - One-to-many.............................................................................................................. 5 3.2 - Three-table relationships ........................................................................................... 5 3.3 - Data Integrity............................................................................................................. 6
Part 2: Query Essentials ..................................................................................................7 4 ~ Introducing Queries...................................................................................................7
4.1 - Constructing queries .................................................................................................. 7 4.2 - Configuring queries .................................................................................................... 9 4.3 - Filtering in queries ..................................................................................................... 9
5 ~ Combining data from related tables ................................................................ 12
5.1 - Query quick tools ..................................................................................................... 13
6 ~ Data editing ............................................................................................................... 14 Part 3: Data Tables......................................................................................................... 15 7 ~ Configuring Fields ................................................................................................... 15
7.1 - Data Types ............................................................................................................... 16 7.2 - Field Properties........................................................................................................ 17
8 ~ Key Fields .................................................................................................................... 19
8.1 - Primary Key.............................................................................................................. 19 8.2 - Foreign Keys............................................................................................................. 20 8.3 - Composite Keys........................................................................................................ 20
9 ~ Defining relationships ........................................................................................... 21
9.1 - Referential Integrity................................................................................................. 21 9.2 - Creating relationships .............................................................................................. 21
10 ~ External Data.......................................................................................................... 24
10.1 - Importing Data....................................................................................................... 24 10.2 - Post Import Checks ................................................................................................ 26 10.3 - Linked Data ............................................................................................................ 27
Part 4: Creative Queries ............................................................................................... 28 11 ~ Data Manipulation................................................................................................ 28
11.1 - Calculated fields with numeric data ....................................................................... 28 11.2 - Fields with text data............................................................................................... 29 11.3 - Grouping and totals ............................................................................................... 30
12 ~ Parameter Queries ............................................................................................... 32 13 ~ Alternative Joins .................................................................................................... 33
13.1 - Configuring an outer join in a query ....................................................................... 33
14 ~ Action Queries ........................................................................................................ 34
14.1 - Constructing an action query ................................................................................. 34 14.2 - Using action queries............................................................................................... 35
Part 1: Understanding Databases
Part 1: Understanding Databases
1 ~ Why Databases?
A database is a system for collecting, organising and retrieving information; databases are particularly good at working with complex sets of related information. A database system such as MS Access facilitates a task-driven approach, encouraging you to decide the most effective way to collect, process and present information. MS Access also includes features to help maintain the accuracy of data by incorporating appropriate checks on validity and data type. 1.1 - Data Structures Sets of data can be divided into two broad types: flat-file and relational. The distinction is easiest to explain using an example. Example 1: You need to store personal detail for a group of students. A flat-file data structure for this would be a simple two-dimensional table, each student recorded as a row:
Example 2: You want to extend this to record which modules are taken by each student, but will need to filter the data set to display details for students taking a particular module. One way to ensure student details will always be visible is to repeat them for each module, but this is a poor solution:
Disadvantages: It provides multiple opportunities to introduce errors
1
Data repetition
Essential Access Book 1
It takes up more storage space It will require multiple records to be changed if one item of personal data changes. The main problem with this solution, however, is that it does not reflect the relationship between students and modules. One student can take several modules, and likewise one module can be taken by several students; the data is relational and can never be adequately represented in one two-dimensional table ? it requires two:
Student information
Module information
MS Excel Both examples could be implemented using MS Excel, but although the first example is more `sensible' in Excel, it still suffers from disadvantages:
The same `interface' is used for data input, processing and presentation ? programming is required in order to create a data collection form
Data types cannot easily be enforced (a date could easily be entered as text) The integrity of each record cannot be enforced ? columns can be re-ordered
independently Users cannot easily work with a sub-set of the data The file cannot be edited simultaneously by multiple users without risks to data integrity
Google Sheets Most of the disadvantages of Excel also apply to Google Sheets. Even though simultaneous editing is possible, multiple editors do not have genuinely separate views.
Data can be collected using a Google form, but these cannot also be used to view or present data.
2
Part 1: Understanding Databases
2 ~ Data tables in MS Access
MS Access is designed to facilitate working with relational data. Data are stored in separate tables, but the relationships between these can be clearly defined, enabling you to work with data from multiple tables in a way that reflects their connections. Tables are one type of `object' used in Access, with a specific purpose. Other `objects' serve other purposes and will be used later. Navigation Panel All Access objects can be opened for viewing and editing via the configurable navigation pane on the left (this can be minimised to a narrow vertical bar when not in use). To ensure all objects are visible, set to show Object type > All Access Objects Table Views An Access table has two views: Design view and Datasheet view. To open a table in Datasheet View:
Locate the table in the navigation pane and double-click Or locate the table in the navigation pane and choose Right-click > Open
To open in Design View: Locate the table in the navigation pane and choose Right-click > Design View
To switch between views when a table is already open: Choose Home > Views > View
Note: This control is both a toggle control and a drop-down. When working with Access objects you will mostly wish to toggle between Design and Datasheet views so choose the upper portion of the control, not the drop-down.
View control
3
Essential Access Book 1
2.1 - Table datasheet view The datasheet view presents data in tabular format, where: Each column is a field of data Each row is a record New records are added in the empty bottom row or using the New (blank) record control next to the record navigation controls
Current record
Edited and unsaved record
Record navigation
Add New record
Bear in mind, particularly if you are an Excel user:
The integrity of each record (row) is always maintained ? you cannot `shuffle' data in one column independently of others; the record is a key building-block
The is always only one blank row at the bottom of the table
When a new record is added, or existing data edited, the unsaved record is indicated by the pencil symbol. Moving to another record will automatically save the edited record. Unlike Excel, you do not need to remember to save changes to data
The order of records in a table is not important. Later you will use queries to define your view of the data
You can open and work with several tables (and other Access objects) at once within the main programme window
Column widths and row heights can be manually adjusted, but all rows will always have the same height
4
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- guide to table relationships
- access relationships table relationships
- relational databases and microsoft access
- microsoft access 2 managing data in tables and creating
- microsoft access 2007 module 1 jtpkrajang
- intermediate microsoft access 2007
- 12 ms access tables relationships and queries
- essential access university of york
- creating tables and relationships
Related searches
- skyward family access university place
- university of minnesota college of education
- university of minnesota school of social work
- wharton school of the university of pennsylvania
- cost of university of scranton
- university of new york ranking
- skyward access university place wa
- matlab access element of array
- python access list of lists
- pace university new york ranking
- columbia university new york cost
- family access university place wa