Since Access is a Windows application, Access shares the user interface and many conveniences that Windows applications support. First, Access has the Window's pull-down menu scheme which is similar to other Windows alps. A more sophisticated feature Access offers is Object Linking and Embedding (OLE) which allows one to include complex data types like pictures as data fields in a database. Finally, Access offers Dynamic Data Exchange (DDE), which allows the user to export reports and query results from Access into a format which is easily read and incorporated by other applications (like Excel, for instance).
The Access database format differs fundamentally from other systems (like dBase) in that it considers a database to be more than the raw data /records. Instead, Access considers a database to consist of data tables, relations between the tables, queries performed on the tables, and reports and forms to be generated from the tables.
So an Access database is the collection of tables, queries, reports and forms created for a particular data set. All these items are collected and saved under a common single file name which is stored with a .mdb extension. One source indicates that the top range for the size of an .mdb file is 1 Gig, but it is not clear what aspects of the database that figure includes (whether this means 1 gig of data or 1 gig of data-organizing manipulations).
When one opens an Access database, all the database items are pulled onto the desktop for use. It is not possible to have more than one database opened at one time. However, all aspects of a database: tables, queries, and reports, may be simultaneously open. I'm not sure how multi-user (network) problems are handled, but i believe that Microsoft has dealt with issues of multiple users trying to Access a common data table.
The data component of an Access database are the tables. Tables are like spreadsheets where each row is a data record and each column is a distinct field or data feature. The maximum number of fields in a single table is 255. One can include 32,768 tables in a single database .mdb file.
Each field in a table has its own data type. Field data types include: Text, Number, Currency, Date/Time, Yes/No and OLE (eg. pictures). Each data type has its own storage limitation in terms of byte size. Also, there are options for changing this size definition at the time of table construction. For example, numerical fields can be Single, Double, Integer, Long Integer and Byte ([1] p. 76). Choice of field data type must be made when a table is set up.
Each table in Access has a primary data key. The primary data key is the field (column) which uniquely identifies each record (row). For instance, a customer ID number may be the primary key in a table of customer information. Or Social Security number in a table of Employee information.
The primary data key must distinguish/be unique for each record (row entry)in the table. While all other fields may have entries repeated many times /in different records, the primary data key cannot. If a table does not have a natural primary data key specified by the user, Access will generate one by numbering the rows (it uses a row counter field to distinguish records like a spreadsheet would).
An essential feature of relational databases is the capacity to relate different tables. Tables are related in Access by having a field (column) repeated in another table. For instance, the employee information table contains an employee ID field. In the Product Sales table, there is a field which is the Salesman ID. The Salesman ID is the ID number of the Employee who made the sale.
Relationships in Access are primarily what is called one to many relationships. In the one to many scheme, a primary data table has a field that has repeated use in many records of a related table. For instance, in the Employee Info and Sales example, a given employee makes many different sales. The ID number of one employee listed in the Employee ID table appears many times in the Sales table. In this example, the Employee ID plays the role of the primary table and the Sales table is the secondary table related to the primary one.
Queries are a way to generate particular subsets of data with certain properties from a database. Queries are essentially logical relations which are processed against the member records of particular database tables. Those records meeting the criteria of the query are marked and output in a temporary table (dynast) which the query generates as output.
The temporary table can then be exported for further data processing (ie to Excel of S plus or such). By using queries, one can do pre-processing of data before moving on to more sophisticated statistical analysis.
To create queries for a database, one opens the database and chooses the Query option from a pull down menu. Then one selects Add Query. From the Design View of the Query, tables are added to form the universe of records from which the query will select. Designing the query consists of specifying the logical relations that records will have to fulfill in order to be included in the dynaset.
Each column in a query contains a field name and reference table. In the criteria row of the column one enters the specifications which the records in a table must satisfy in order to be included in the dynaset.
By including multiple related tables in a query, one has an easy way to obtain certain sets of information. Suppose one wanted the list of names of all employs who had sold products in the past 6 months and the income produced off of each sale. Using the Sales and Employee Information tables, one could accomplish this task.
Query criteria fields can be very complicated. Many Access Basic (like Visual Basic?) routines can be used/are built into Access to make query design easier. The Help facility provided with Access is a good way to explore neat criteria building functions as you neat them. example: the DateAdd function has a facility for adding date type variables together to generate another date type variable. This is good for defining a date range query. Finally, one can do queries on the dynaset of other queries.
references:
Campbell, Mary. The Microsoft Access Handbook. 1994: McCgraw-Hill Inc. Berkley, California.