DATABASE

1
DATABASE

• A database is a collection of organized
  information.
• A database uses table / list to keep data.
• Data is made up of fields and records.
• Columns serve as fields, rows serve as records.

2
Sample Database
Fields
Invoice Month Sales Person Product Sales Value
A001 Feb John Smith Wooden Chair 4500
A002 Feb Steve Brown Wooden Desk 2300
A003 Feb Mary Davis Metal Chair 3300
A004 Feb John Smith Wooden Chair 3700
A005 Feb Mary Davis Wooden Desk 5400
A006 Mar John Smith Metal Chair 3100
A007 Mar John Cage Wooden Desk 5200
Records
3
How to Add a New Record to DB?

• The Form button that displays a data form isn't
  included on the Excel 2010 Ribbon, but you can
  add this button to the Quick Access toolbar by
  using the Quick Access Toolbar settings in the
  Excel Options dialog box. Click the Form button
  on the Quick Access toolbar.
• Click any cell in the DB list
• Click Form (This command is not in the Ribbon)
• Click the New button
• Type the record
• Click the Close button

4
FORM SHEET
5
How to Search for the Record?

• Click any cell in the DB list
• Click Form
• Click the Criteria button
• Type the criteria
• Click the Find Next button to find the next
  record
• Click the Find Previous button to find the
  previous record
• Click the Close button

6
How to Edit / Delete the Record?

1. Click any cell in the DB list
2. Click Form
3. Click the Criteria button
4. Type the criteria
5. Click the Find Next button to find the next
   record
6. Edit the record
7. Click the Close button

7
SORTING Putting data into sequence

• Ascending Sort Arranging data from low to high
  sequence 1 to 9, A to Z.
• Descending Sort Arranging data from the high to
  low sequence 9 to 1, Z to A.

8
How to Perform Simple Sort

• Create a contiguous list with headings specifying
  the contents of each column.
• In the column you want to sort by, click any cell
  containing data.
• Click the Sort A to Z button in the Sort Filter
  group on the Data tab (ascending sort).
• OR
• Click the Sort Z to A button in the Sort
  Filter group on the Data tab (descending sort).

9
How to Perform Multiple Sort

• Select or click in the list of data you want to
  sort.
• Click the Sort button in the Sort Filter group
  on the Data tab.
• From the Sort By drop-down list, select the
  column by which you want to sort.
• From the Sort On drop-down list, choose Values.
• From the Order drop-down list, select how you
  want to sort the data.
• Click the Add Level button.
• Repeat steps 3 through 5 for the new sorting
  level
• Click OK.

10
AUTOFILTERING Filtering the records by applying
simple criteria

• In order to activate the basic Excel filter,
  select the Data tab at the top of your
  spreadsheet, and from this, select the option
  Filter.

11
ADVANCED FILTER Filtering the records by setting
up and defining the criteria range on the
worksheet

• To define the Criteria Range
• Use any cell on the worksheet to define the
  criteria
• The criteria value is defined below the field
  name
• Use a different row for OR criteria
• Use the same row but different column for AND
  criteria
• Advanced Filter can be applied to more than 2
  criteria.

12
Applying the Excel Advanced Filter

• On the Excel Ribbon's Data tab, click Advanced,
  to open the Advanced Filter dialog box

13

• You can choose to filter the list in place, or
  copy the results to another location.
• Excel should automatically detect the list range.
  If not, you can select the cells on the
  worksheet.
• Select the criteria range on the worksheet
• If you are copying to a new location, select a
  starting cell for the copyNote If you copy to
  another location, all cells below the extract
  range will be cleared when the Advanced Filter is
  applied.
• Click OK

14
Simple DB FUNCTIONS

• Dfunction(Database, Field, Criteria)
• DSUM Sum up the total value for records that
  meet the criteria
• DAVERAGE Calculate the average value for records
  that meet the criteria
• DMAX Find the maximum value for records that
  meet criteria
• DMIN Find the minimum value for records that
  meet criteria
• DCOUNT Count the occurrences of records that
  contain numbers in the
  specific field that meet the criteria
• DCOUNTA Count the occurrences of records that
  contain non-blank cell in
  the specific field that meet the criteria
• DGET Retrieves from a database a single record
  that matches a given criteria.

15
Using DB Functions

• To use DB Functions, you must click the Function
  Wizard (fx) button on the Formula bar, click
  Database in the Select a Category drop-down list
  box, and then click the function to use or you
  can type the Database function directly into the
  cell.