- •2. Data, Information, knowledge
- •Flat file database model is a database that stores data in a plain text file. Each line of the text file holds one record, with fields separated by delimiters, such as commas or tabs.
- •Advantages
- •For every record in a child table (the “many” side), one and only one matching record must be in the parent table (the “one” side).
- •12. Designing the database
- •Specifying Field Data Types
- •Sorting records
- •Sorting by two or more fields
- •19. Filtering
- •22. Filters and Queries
- •Adding Selection Criteria
- •23. Performing Calculations in a Query
- •Using Aggregate calculations (Totals) in a Query
- •24. Parameter queries
- •25. Update queries
- •26.Make-table query
- •27. Three ways to arrange information for display or distribution
- •28. Record source can be
- •Creating Simple Forms with the Form Wizard
- •32. Creating a form in design view
- •33. The list of properties depends on current selection.
- •The easiest way to create an input mask is to use the Input Mask Wizard.
- •41. You can specify customized responses to user actions, such as clicking a button, opening a form, or selecting an option in an option group.
- •Events are grouped into eight categories, depending on the effects of the event. For example, some events relate to the data, others to filters, and still others to keyboard actions.
- •Associating the Macro with a Report Event
- •Some PivotTable Terms
- •Item – an element in a field.
- •To create a one-dimensional PivotTable that shows the data field details, you do the following:
- •Creating a PivotChart Report
Sorting records
Access automatically sorts records by the value in the primary key field.
Also sorting can be
on a single field.
by two or more fields.
As for the data type, we can classify it as
1. Sorting on text values
Sorting on numeric values
Sorting on Date / Time values
In ascending order
Yes / No fields sort by Yeses first, then Nos.
In descending order
Yes / No fields sort by Nos first, then Yeses.
You can sort Memo fields using the first 255 characters.
Access sorts hyperlink fields by the Text to Display (if any) or the address.
You cannot sort on OLE or Attachment fields.
18. Sorting on Date / Time values
There are also two options:
Sort Option Also Called
Sort Oldest to Newest ascending
Sort Newest to Oldest descending
Sorting by two or more fields
To sort by more than one field, the sorted fields must be adjacent in the datasheet. In addition, Access uses a sort precedence from left to right, so the records are sorted first by the values in the left column. If duplicate values appear in that column, a secondary sort is performed on those records by the values in the next column to the right. If the columns involved in the sort are not adjacent or are in the wrong relative position in the datasheet or subdatasheet, you must move the columns before sorting the records.
Make sure that the order in which you select the fields reflects the sort priority you want to use.
When all are in position, select the columns you want to sort on and click one of the Sort commands or choose a command from the shortcut menu
19. Filtering
When you want to see only certain records in your datasheet, subdatasheet, or form, you can filter out those you do not want to see the filter process screens the records and lets through only those that meet your criteria. The criteria is a set of conditions you specify to limit the display to a certain subset of records.Filtering does not remove the records from the table, it only removes them from your view of the table. A filter consists of conditions you specify, which can be as simple as “all records for work orders in October 2011” or as complex as “all bids submitted during June or July that exceeded $750 and were awarded within 30 days of the bid offer.”
Four ways to filter records in Access
Common context filters are available in the shortcut menus depending on the field type.
Filter By Selection leaves only the records with the same value as the one you select in one of the records or the records that do not include the same value.
Filter By Form screens records with the criteria you enter into a table skeleton.
Advanced Filter/Sort gives you, in addition to filtering, the capability of specifying a complex sort. With a complex sort, you can sort the records by two or more fields using different orders – ascending or descending.
Filtering by context
Access 2007 provides many common filter options that are type-specific. These common filters are available in every view that displays data. The set of specific filters that are available depends on the type of data and the values in the selected column. All you have to do is right-click in the field and choose an option from the context menu. You can filter by the displayed value or by a selected partial value in the field.
Context filters are not available for Yes / No, OLE Object, or Attachment fields
Filtering by Partial Values
If you want to filter on only part of the value in a field, select the characters you want to use and then right-click in the field.
You can choose records with the selected characters somewhere in the field or at the end of the field.
If you selected characters embedded in the value, you have only the choice of displaying records containing or not containing the selected value.
20. Four ways to filter records in Access
Common context filters are available in the shortcut menus depending on the field type.
Filter By Selection leaves only the records with the same value as the one you select in one of the records or the records that do not include the same value.
Filter By Form screens records with the criteria you enter into a table skeleton.
Advanced Filter/Sort gives you, in addition to filtering, the capability of specifying a complex sort. With a complex sort, you can sort the records by two or more fields using different orders – ascending or descending.
You can also use the Filter By Selection to filter to a few selected characters or numbers. This works the same as when you use the context filters. The filter choices depend on where the selected characters appear in the value.
If the characters are at the beginning of the value, the choices are Begins With or Does Not Begin With as well as Contains or Does Not Contain.
If the characters are within the value, the choices are Contains or Does Not Contain.
If the characters are at the end of the value, the choices are Ends With or Does Not End With as well as Contains or Does Not Contain.
Filter By Form is one of the choices in the Advanced Filter Options context menu.
Filter By Form is not much different from Filter By Selection. Instead of selecting a value from the datasheet or subdatasheet as a filter criterion, you enter the value in a filter grid.
The grid is a table skeleton that resembles a blank record showing all the filterable fields in the table with space to enter filter values. One advantage of using Filter By Form is that you can combine filter criteria.
You can specify two or more conditions, so a record must meet any one or all of them to survive the filter. The multiple criteria can apply to a single field or to more than one field.
Filtering with Advanced Filter / SortThe Advanced Filter / Sort feature is the most flexible and comprehensive of the Access filtering tools. Not only does this feature include all the features of Filter By Form, it also enables you to specify mixed sort orders for different fields in the table. You enter all the filtering and sorting specifications in a single window.
The Advanced Filter / Sort window is divided horizontally into two parts. The upper part contains a box with a list of the fields in the table. The lower part is the design grid where you specify which fields you want to filter, the values to use as filters, and how you want the records sorted in the resulting record set.
If you applied a filter recently, the criteria appear in the Criteria row of the grid. Right-click and choose Clear Grid from the shortcut menu to remove it.
21.Query is a general term that is synonymous with question, inquiry, or quiz. In Access, to query a database is to ask a question about the information in the database. A query can be about the data in a single table or in multiple related tables.
Access provides several types of queries, ranging from the popular select query that extracts specific data to the more exotic action query that can insert, update, and delete records.
With Access queries you can do the following:
View data from multiple tables sorted in a specific order.
Perform many types of calculations on selected groups of records.
Find and display duplicate or unmatched records.
Update data, delete records, or append new records to a table.
Create a new table with records from one or more tables
. The Access select queries include:
Simple Select Queries that display data from one or more tables sorted in a specific order. You can also perform many types of predefined or custom calculations on values in all records or within groups of records.Find Duplicate records queries display all records with duplicate values on one or more specified fields. For example, you can query to find customers who have more than one work order. You can include any other fields you want.Find Unmatched queries display records in one table that have no related records in another table. For example, you can query to find customers who have no current work orders.