DATABASE OBJECTS Putting the Objects to Work Database

DATABASE OBJECTS Putting the Objects to Work

Database Objects • When creating a database, you are creating a database that will store objects. Database Objects Table Report Query Macro Form Module

About Database Objects • Table – Stores all the data in the datasheet in a format called datasheet – Similar to an Excel worksheet • Query – Used to search for and retrieve data from table using conditions – It is a question you ask • Form – Data is displayed in one or more tables or queries in a format similar to a paper form

More About Database Objects • Report – Data is displayed from one or more tables or queries in a customized format – It is commonly used to summarize data and to calculate totals • Macro & Module – These are advanced features that we will not use in this unit.

Creating Table Relationships • Relationships can be created when a database contains more than one table • A relationship lets you connect the data in the tables • The most common type is a one-to-many relationship • Tables must contain a matching field – Matching field • • A field that appears in both tables Must have the same data type Must contain the same values Also called a common field

One-to-Many Relationships • Primary Table – Contains the records on the “one” side of the relationship – One record in the primary table can match many records in the second table (related table) • Related Table – The table that contains the records on the “many” side of the relationship – The common field is called the foreign key • Foreign Key – The common field in the related table – The field is usually the table’s primary key

Queries • In Office 2007, it is on the Create Tab in the Other section • Two ways to create: – Create a simple query by using the query wizard – Create a query using the Query Design View • Calculated fields: – When a field displays a value that is calculated using other fields in the query.

Sorting • Ascending Sort – arranges records from A to Z, or smallest to largest • Descending Sort – arranges records from Z to A, or largest to smallest

Reports • Report Tool – Located on the Create Tab in the Reports section – Create a simple report that includes all the fields in the selected table or query – Uses a simple columnar format • Report Wizard – Located on the Create Tab in the Reports section – Allows you to choose the tables and fields to use in a report

Report Terms • Grouping – organizes data on one or more fields into groups • Sorting – Sort in Ascending or Descending Order • Layout – Choose between Stepped, Block, and Outline • Orientation – Choose between portrait and landscape • Style – Choose from many styles including Apex, Aspect, Civic, Concourse, etc.

Filters • Temporarily displays records in a datasheet based on the condition you specify • Types of Filters: – Filter by Selection – you select a field value in a datasheet and click on the selection button in the Sort and Filter group on the Home Tab – Filter by Form – you need to display records that contain one or more values based on the values stored in one or more fields – Auto. Filter – menu that opens when you click the arrow on the right side of a field selector