MS Access Manual for Biologists

INDEX

1. Introduction
2. Data Im/Export and Tables
3. Queries
4. Reports
5. Forms
6. Useful Utilities

• Simple, user-friendly and effective table management tool
• Great for organizing and manipulating large data sets
• Powerful query functionality
• Allows design of relational databases without knowing programming languages
• Integrates with spreadsheet programs (Excel)
• Saves storage space
• Web interface via ASP technology (Active Server Pages)
• Many more reasons

• Mostly for local networks
• Limited scalability

• Free: MySQL, PostgreSQL, etc.
• Commercial: Oracle, Sybase, etc.

• MS Access stores imported tables, like most databases, in one big file (mdb)!
• The data can be exported from there in almost any format and rearrangement.

• Start → Programs → MS Access → New → Blank Database → Provide Name

• 'Database Window' switches between seven main levels:
1. Tables: 'container of data'
2. Queries: 'connector between tables'
3. Forms: 'data overview pages'
4. Reports: 'data printing and reading'
5. Pages: 'web interface'
6. Macros: 'task automation'
7. Modules: 'code organizer'

• Make sure the tables for import have only one title row with unique title fields
• File → Get External Data → Import → Select File Type → Browse to Table → Walk through Import Wizard → Choose: "First Row Contains Field Names" & "Let Access Define the Primary Key"

• This is a more economic import alternative for large databases
• File → Get External Data → Link Tables → Browse to Database → Select Table

• Open table by double-clicking on its name. This opens it in 'Datasheet View'
• Do the following to switch to 'Design View': View → Design View
• View → Pivot Table opens the table in Pivot Table View

• In the 'Database Window' click on New → Design View → Enter Table Headings and define Data Types
• Data can be entered manually in Datasheet View

• Primary Key: field/column that uniquely identifies each record in a table
• Data types
• No save after data change required
• Can hold almost unlimited number of records (rows)

• Sort: highlight table column → Records → Sort → Ascending/Descending
• Filter: select entire or part of record → Records → Filter by Selection or Excluding Selection

• Highlight table in database window → File → Export → Choose File Format and Location

• Import the following tables from Workshop_Data folder: At#_Single_TU.txt, Kinases.txt, Fromm8K.xls, Jones22K.xls, 22K.txt, 8K.txt
• Get familiar with the functions: Search, Sort and Filter
• Edit column titles in design view.
• Identify columns with duplicates by moving the "primary key" in design view to those columns.
• Remove primary index column where possible.
• Get familiar with differnt data types in design view: Text, Memo, Number, etc.
• Export one table in Excel format and one in tab-delimited text format.
• Open table "Fromm8K" in "Pivot Table View" and compare total averages of 'Signal Intensity' columns.

• Queries are the most important feature for data analysis and management. They provide functionality for joining tables, performing calculations and filtering data with complex queries. This allows you to effeciently structure your data which reduces human errors and saves a lot of storage space.

• Switch in Database Window to 'Queries'
• New → Design View → Double Click on Tables that you want to join (moves them to Query window) → define joining field → select join properties by double clicking on 'join line' → select fields that you want to display → switch to Datasheet View

• Open Query in Design View → specify query criteria in Criteria field → use the following examples:

Boolean Query Syntax (Venn Diagrams)
Symbol	Description
>	greater than
>=	greater than or equal
<>	not equal
>=10 AND <=90	range
*	wildcard (group of characters)
?	wildcard (one character)
AND	both criteria true
OR	either one true
Xor	one or other true, but not both
Not	untrue to match
Is Null	display empty fields
Is Not Null	display non-empty fields

• Open query in design view → right click on empty title field → select 'Build'
• In the Expression Builder window you can build expressions by selecting the columns from tables/queries. Mathematical/statistical functions can be selected under Functions.
• Useful examples:
1. Calculate Log2 or log10 ratio:
• Formula: Log (intensity1/intensity2)/Log(2)
• Access uses by default the natural log, dividing by log(2) or log(10) gives Log2 or Log 10, respectively.
2. Calculate -/+ linear ratios with if function:
   • Formula: Iif[(Intensity1/Intensity2)≤1, -1/(Intensity1/Intensity2), (Intensity1/Intensity2)]
   • Structure: if [condition (x) is true, perform operation (y), else perform operation (z)]
3. Calculations with domain aggregate function
• Formula: Davg("[ColumnName]","TableName")
• Prints average of chosen column in each field

• Creates a table from a query
• Open query in Design View → Query → Make Table Query → provide name for new table → hit run (exclamation mark)

• Open an empty query window in design view → Query → Append Query → bring up the table you wish to append → select table to which to append data → hit run (exclamation mark)

• New → Find Duplicates Query Wizard → Follow instructions

• Copy table with duplicates into new table → Remove all of its records → Set the primary key to duplicate containing field → open an empty query window → Run Append Query (see above) and ignore error message (yes)

• Select a closed query in Database Window → File → Export → Choose location and file format

• Join two or more tables
• Apply query criteria to view data subsets
• Perform calculations on intensity values: ratios, normalization, etc.
• Create new table from query with Make Table Query
• Append to tables with Append Query
• Identify duplicates in table with Duplicates Query Wizard
• Remove duplicates
• Export constructed query into Excel

• Reports are an elegant way to maintain readable and printable summaries. To maintain up-to-date information and allow maximum flexibility, reports should always be based on queries and not on tables!

• Switch in Database Window to Reports
• New → Report Wizard → Follow instructions

• Open report in Print View → Click on 'Publish in MS Word'

• Create a report from one of your queries
• Add some data to one of the included table fields and check whether the report gets updated
• Export report to MS Word

• Forms are an efficient tool to summarize a lot of data for individual records on a single page. They can also be used as a manual data upload tool. As reports, forms should always be based on queries and not tables!

• To show data in forms with a one-to-many relationship, it is necessary to define first the fields that have this relationship.
• Tools → Relationships → Select tables → Drag connection line (one field needs to be primary key → double click on connection line → select Enforce Referential Integrety

• Switch in Database Window to Forms
• New → Form Wizard → Follow instructions

• View form in design view → click on subform button on main menu bar → follow instructions

• Create a Form from one of your queries
• Add some data to one of the included table fields and check whether the Form gets updated.
• Add a subform for data with one-to-many relationship
• Print single form pages

• Access databases maintain their maximum size even when a lot of data has been deleted. To shrink them to their necessary size, you may want to use occasionally the 'Compact and Repair Database' function.
• Tools → Utilities → Compact and Repair Database